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/hrpsw@localhost/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/hrpsw@localhost/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()

See also:

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.

Leave a Reply

This Post Has 5 Comments

  1. Sagar Linge

    Nice article . One question . Consider an Critical Production Oracle database with 1 TB of data and you using this script . What are the performance impact on the Oracle database ? If no then this script is good . Did you test this ever ?

    1. Vinish Kapoor

      I didn’t test with too much data. But definitely, if there is a large volume of data, then you should try to run the script in threads.

    2. Sagar Linge

      Too much of data is not the concern . The concern here is the read operation that would happen on the production database that would definitely impact the DB performance . I was looking for an utility that reads data from the export dump of oracle and creates the csv file .

    3. SUDHIR RAO

      Export dump is Oracle proprietary format and you cannot export it to CSV. It can only be imported in to a Oracle database table.

    4. vallish

      can you show one example multi threads