Using Python to connect to Oracle DB, and extract results to CSV

I knew it would’nt take much code to do this, but there was still a little fiddling around and some odd errors along the way. Anyhow if you want to do this here is some helpful tips

Firstly download the latest stable version of Python. Next download and install the cx_Oracle extension, from here if your using a windows platform cx-oracle.sourceforge.net. You will then need to download and install the Oracle Client ensuring you select the version specific to your installation ie. 10g or 11g etc. You can get the download from here Oracle software downloads.

Make sure that you have the ORACLE_HOME variable set and the bin is in your PATH variable, otherwise you will get cx_Oracle DLL load failure messages.

If you have a fairly, long complex SQL statement then encase the query within triple single quotes. It works a treat and takes care of new lines without need for you to do any formatting or
string manipulation. OK here is the code : -

import os
import cx_Oracle
import csv

SQL="SELECT * FROM SOME_TABLE"

# Network drive somewhere
filename="S:\Output.csv"
FILE=open(filename,"w");
output=csv.writer(FILE, dialect='excel')

# You can set these in system variables but just in case you didnt
os.putenv('ORACLE_HOME', '/oracle/product/10.2.0/db_1') 
os.putenv('LD_LIBRARY_PATH', '/oracle/product/10.2.0/db_1/lib') 

connection = cx_Oracle.connect('userid/password@99.999.9.99:PORT/SID')

cursor = connection.cursor()
cursor.execute(SQL)
for row in cursor:
    output.writerow(row)
cursor.close()
connection.close()
FILE.close()