In this tutorial, I am giving an example to export CSV file from Oracle table in Python. I am using CSV module to write the data and using the cx_Oracle module to interact with Oracle database. Earlier I have written many programs to export CSV file using PL/SQL, but I found it easier to write in Python. The following are some examples.
Export CSV from Oracle Table in Python Example
For the CSV module in Python, the following steps should be used to write a file.
1. import csv
2. csv_file = open("csv-file-name.csv", "w")
3. writer = csv.writer(csv_file, delimiter=',', lineterminator="line-terminator", quoting=quote-option)
4. writer.writerow(row)
5. csv_file.close()
You can see the above in the first step we should import CSV module in our Python program, then open a CSV file in write mode, specify the writer settings, for example, delimiter and line terminator, etc. Then write the rows and close the file.
Complete Python Program
In the following example, it will connect to HR schema in Oracle and will export the data from Location table. Fields in the CSV file delimited by the comma ',' and the quotes used for non-numeric fields.
import csv
import cx_Oracle
con = cx_Oracle.connect('hr/[email protected]/orcl')
cursor = con.cursor()
csv_file = open("locations.csv", "w")
writer = csv.writer(csv_file, delimiter=',', lineterminator="\n", quoting=csv.QUOTE_NONNUMERIC)
r = cursor.execute("SELECT * FROM locations")
for row in cursor:
writer.writerow(row)
cursor.close()
con.close()
csv_file.close()
You can now check your current directory for the generated CSV file using Python program.
Example to Export CSV Delimited by Pipe '|' and Without Quoted Fields
The following Python program will export CSV delimited with the pipe '|' and without quoting the fields.
import csv
import cx_Oracle
con = cx_Oracle.connect('hr/[email protected]/orcl')
cursor = con.cursor()
csv_file = open("employees.csv", "w")
writer = csv.writer(csv_file, delimiter='|', lineterminator="\n", quoting=csv.QUOTE_NONE)
r = cursor.execute("SELECT * FROM employees")
for row in cursor:
writer.writerow(row)
cursor.close()
con.close()
csv_file.close()