How to Import CSV Into Oracle Table Using Python?

In this tutorial, I am giving some examples of importing a CSV file into Oracle table using Python. You will learn how to use csv.reader and csv.DictReader methods to load data from CSV file. The following are the examples.

Import CSV into Oracle Table Using csv.reader Method in Python

In the below example, I am using the CSV file format as shown below from the locations table of HR schema in Oracle. The file is containing delimiter pipe '|' and will import this file into the new_locations table of HR schema. You can download this CSV file from the following link Download locations.csv file, and you can also download the HR schema script from this link Download HR Schema. The fields in the CSV file are as below:

1400|"Jabber Rd"|"26192"|"Southlake"|"Texas"|"US"

The csv.reader method from the CSV module is used to import the file in this example. You need to specify the field's number to get the value of each delimited column. Below is an example of a single record of CSV file, the first column should treat as 0, the second is 1 and so on.

Python Program Example

Put the location.csv file into the current directory of your Python program and then create a file import_test.py with the following code.

# import_test.py
import cx_Oracle
import csv

con = cx_Oracle.connect('hr/hrpsw@localhost/orcl')
cur = con.cursor()
with open("locations.csv", "r") as csv_file:
    csv_reader = csv.reader(csv_file, delimiter='|')
    for lines in csv_reader:
        cur.execute(
            "insert into new_locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID) values (:1, :2, :3, :4, :5, :6)",
            (lines[0], lines[1], lines[2], lines[3], lines[4], lines[5]))

cur.close()
con.commit()
con.close()

After running the code, you can check your Oracle database table for the inserted data.

Skip The First Row If CSV Has Header

The locations.csv file has no header row in it. But if the CSV file contains a header row and you want to skip the first row then just put the next(csv_reader) command before the loop. Below is an example.

# skip first line if header row
next(csv_reader)
for lines in csv_reader:

Python Program Example to Import CSV Using csv.DictReader method

In the following example, it will import the CSV file using csv.DictReader method. You can notice in the below code that fields are being specified in the fields array and using it while inserting the data into Oracle table. Suppose if CSV file has a header row then we don't need to specify the fields, we can use header row fields for the reference. I will give another example of that.

import cx_Oracle
import csv

con = cx_Oracle.connect('hr/hrpsw@localhost/orcl')
cur = con.cursor()
with open("locations.csv", "r") as csv_file:
    fields = ['F_LOCATION_ID', 'F_STREET_ADDRESS', 'F_POSTAL_CODE', 'F_CITY', 'F_STATE_PROVINCE', 'F_COUNTRY_ID']
    csv_reader = csv.DictReader(csv_file, fieldnames=fields, delimiter='|')
    for lines in csv_reader:
        cur.execute(
            "insert into new_locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE,"
            " CITY, STATE_PROVINCE, COUNTRY_ID) values (:1, :2, :3, :4, :5, :6)",
            (lines['F_LOCATION_ID'], lines['F_STREET_ADDRESS'], lines['F_POSTAL_CODE'],
             lines['F_CITY'], lines['F_STATE_PROVINCE'], lines['F_COUNTRY_ID']))

cur.close()
con.commit()
con.close()

Import CSV With Header Row Using csv.DictReader Method Example

In the following example, I am using locations_wheader.csv with a header row which you can download with the following link Download locations_wheader.csv. The file is containing header row as below:

LOCATION_ID|STREET_ADDRESS|POSTAL_CODE|CITY|STATE_PROVINCE|COUNTRY_ID

Now we no need to create a field array to specify column names; we can use header row columns for each field. Also, we no need to skip the first header; it will automatically skip the row. Below is the example.

import cx_Oracle
import csv

con = cx_Oracle.connect('hr/hrpsw@localhost/orcl')
cur = con.cursor()
with open("locations_wheader.csv", "r") as csv_file:
    csv_reader = csv.DictReader(csv_file, delimiter='|')
    for lines in csv_reader:
        cur.execute(
            "insert into new_locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE,"
            " CITY, STATE_PROVINCE, COUNTRY_ID) values (:1, :2, :3, :4, :5, :6)",
            (lines['LOCATION_ID'], lines['STREET_ADDRESS'], lines['POSTAL_CODE'],
             lines['CITY'], lines['STATE_PROVINCE'], lines['COUNTRY_ID']))

cur.close()
con.commit()
con.close()

See also:

• How to Export CSV from Oracle Table in Python?