Im trying to load csv file of size 1GB , the file has about 1 Million records , I got below code working ,however performance has been horrible with load time for million records taking about 2 hours, is there a way I can improve performance ?
import cx_Oracle
import csv
from datetime import datetime
start=datetime.now()
connection1=cx_Oracle.connect("username", "password","oradb")
cur1 = connection1.cursor()
with open("test_file.csv", "r") as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',')
batch_size=3000
sql1="""insert into TEST_CSV_FILE ( START_TS, END_TS,FLDR, TSK, INST,
SRC_ERR,tgt_ERR)
values (:1, :2, :3, :4, :5, :6, :7)"""
data = []
for line in csv_reader:
data.append((line[0],line[1],line[2],line[3],line[4],line[5],line[6]))
if len(data) % batch_size == 0:
cur1.executemany(sql1, data)
data = []
if data:
cur1.executemany(sql1, data)
connection1.commit()
cur1.close()
connection1.close()
print(datetime.now()-start)
