How to load csv file to Oracle table in faster way using Python

Question

I am researching over the internet how to load CSV file to Oracle DB table in a faster way

Below is the way I was loading the table which as 1000 records in file and got loaded within no time.

But if there are 50,000 records it takes approx. 5 minutes to load.

Is there any faster and efficient way to load csv file to Oracle table?

My code :

import cx_Oracle
import csv

myquery='insert into TABLE (COLUMNNAME1,COLUMNNAME2,COLUMNNAME3,COLUMNNAME4,COLUMNNAME5,COLUMNNAME6) values (:1, :2, :3 ,:4,:5,:6)'
separator='|'

oracleConnection='user/password@hostIP/database'

def fileLoading(oracleconnection, file_name,myquery,separator):
    try:
        con = cx_Oracle.connect(oracleConnection)
        print("Connection established")
        reader = csv.reader(open("demp.csv", "r"), header=None,delimiter=separator)
        columns = []
        for line in reader:
            columns.append(line)
            cur = con.cursor()
            for line in columns:
                print("Inserting record to table")
                insrt_stmt = myquery
                cur.execute(insrt_stmt, line)
                con.commit()
                cur.close()
                print("load completed")
    except Exception as er:
        print('ERRO:',er)

That rate corresponds to your 1000 records taking 6 seconds, which some might consider "no time". — Scott Hunter
– Scott Hunter, Commented Jul 31, 2021 at 12:02
As this is Oracle, I'd use SQL Loader which is really fast. — Littlefoot
– Littlefoot, Commented Jul 31, 2021 at 12:12
You could try cursor.executemany that could help in improving the speed and also try having one commit statement. — Himanshu Kandpal
– Himanshu Kandpal, Commented Jul 31, 2021 at 12:49
just setup an external table docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm — OldProgrammer
– OldProgrammer, Commented Jul 31, 2021 at 13:06
Remove the "print". Use insrt_stmt = myquery only once outside the loop. Use only one commit at the end. Open and close the cursor only once. — Wernfried Domscheit
– Wernfried Domscheit, Commented Jul 31, 2021 at 18:51

Christopher Jones · Accepted Answer · 2024-03-05 22:02:35Z

3

[Update: cx_Oracle was renamed to python-oracledb. See the release announcement - use this version instead of cx_Oracle. This answer has been updated to use python-oracledb.]

Use executeMany() as shown in the documentation. This is much faster than repeated calls to execute():

import oracledb
import csv

. . .

# Predefine the memory areas to match the table definition
cursor.setinputsizes(None, 25)

# Adjust the batch size to meet your memory and performance requirements
batch_size = 10000

with open('testsp.csv', 'r') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',')
    sql = "insert into test (id,name) values (:1, :2)"
    data = []
    for line in csv_reader:
        data.append((line[0], line[1]))
        if len(data) % batch_size == 0:
            cursor.executemany(sql, data)
            data = []
    if data:
        cursor.executemany(sql, data)
    con.commit()

There is a runnable example in samples/load_csv.py.

edited Mar 5, 2024 at 22:02

answered Aug 1, 2021 at 6:13

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

codeholic24 Over a year ago

Can you provide your input here : data is not getting loaded to table using execute many stackoverflow.com/questions/68607481/… and improvements in code which will make my code work I followed the above execute many , but not loading to table

Christopher Jones Over a year ago

Start by reading the doc, making sure you have the correct setinputsizes values (for simple testing just delete that line), try with just a few rows of data, telling us whether you got an error etc.

Wernfried Domscheit · Accepted Answer · 2021-07-31 19:01:29Z

1

I am not familiar with python but I would try like this:

con = cx_Oracle.connect(oracleConnection)
print("Connection established")
reader = csv.reader(open("demp.csv", "r"), header=None,delimiter=separator)
cur = con.cursor()
insrt_stmt = myquery

for line in reader:
   columns = []
   for line in columns:
       columns.append(line)
       cur.execute(insrt_stmt, line)
con.commit()
cur.close()
print("load completed")

answered Jul 31, 2021 at 19:01

Wernfried Domscheit

60.4k10 gold badges92 silver badges132 bronze badges

1 Comment

Christopher Jones Over a year ago

Nope! Use executeMany() which is much faster than repeated called to execute()

Collectives™ on Stack Overflow

How to load csv file to Oracle table in faster way using Python

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related