How to Export CSV from Oracle Table in Python?

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
Vinish Kapoor

Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

5 Comments

  1. 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 ?

    • 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.

    • 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 .

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

Comments are closed.