Use cx-Oracle in Python to bulk load arbitrary tables using csv files

I recently got a request to load hundreds of database tables in a day and a half using csv files our developers created. I considered using SQL Loader but I didn’t want to spend the time understanding the table structure and creating the control file for each table. Another alternative was to use SQL Developer to import the csv files. That goes against my nature because it would be a manual, repetitive and error prone process.

The csv files were fairly small so plain inserts would work. I looked at writing a BASH script to generate the insert statements and it became very complex very quickly. PL/SQL was an option too but I ruled it out because of the requirement to read flat files. I have been using the Python cx-Oracle module recently so I decided to write a Python script for this task.

Python turned out to be the right choice and the script was very easy to write. The only tricky part was recognizing the date fields. Those are unquoted like a numeric field but contain literals like JAN,FEB, etc. Python and cx-Oracle handled everything with a minimum of effort on my part.

Create the csv files in a dedicated subdirectory like : tablename.csv. The first row of each csv file must contain the column names. The script truncates the target table before inserting the csv records. Any double quoted field is considered a literal and unquoted fields are numeric except date fields which must have the format 01-JAN-16.

Use these GitHub links for more information : ReadmeSource code

12 thoughts on “Use cx-Oracle in Python to bulk load arbitrary tables using csv files

  1. Hi Steve

    This is absolutely brilliant. I can use it for files that only have dates, but I also have a file that has dates and times in separate columns. I can’t figure out how to make that work. Any ideas?


  2. Hi Steve – great script and thanks for posting! – Is this version specific for python 2.x?
    run I run this line: column_string = ‘,’.join(first_row).translate(None,'”‘) is erroring out with
    TypeError: translate() takes exactly one argument (2 given)

    I’ve read a few places that say the translate function with one argument is only to python 2.x..any ideas?


    • Hi John,

      Yes, translate() changed in Python 3.x .
      I don’t have Python 3 installed on my Linux boxes so all my scripts are 2.x
      If you want to run it on Python 3 you will need to modify this section.
      If you do, please post another comment with the changes.


      Return a copy of the string in which each character has been mapped through the given translation table. ”
      “static str.maketrans(x[, y[, z]])
      This static method returns a translation table usable for str.translate().”


  3. I am trying to load a table with 193 columns, the line cursor.prepare(insert_string) gave me a databaseerror ORA-00972 identifier is too long. Do you know how to solve it ?


  4. Hi Steve,
    Thanks for this nice python code that’s generic and very useful!!
    I have a requirement to extract millions of data from one Oracle DB and insert them into another Oracle DB. I am using Python for this project. CSV file creation is very fast but writing the data into the table on the target db is not that fast, e.g. it took 20 mins for 2M records with executemany and batch size = 100000. Can we improve the performance with using parallel hint on the insert query? Or do you have other options? I want to know few solutions for my learning.



  5. Hi Steve,
    Thanks for the code snippet.
    Just wanted to know whether this code works for csv zipped file as well ?
    If so what is the tweek that needs to be performed. Please advise.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s