Insert into oracle table from CSV using python

Question

I am reading a csv file with 5 columns and push to oracle table

I know there are lots of resources on this .. But even then I am unable to find a solution for my problem

Code to read the CSV to python :

 import csv
 reader = csv.reader(open("sample.csv","r"))
 lines=[]
 for line in reader:
      lines.append(line)
  print lines

Output :

[['Firstname', 'LastName', 'email', 'Course_name', 'status'], ['Kristina', 'Bohn', '[email protected]', 'Guide to Capnography in the Management of the Critically Ill Patient (CE)', 'Registered'], ['Peggy', 'Lutz', '[email protected]', 'Guide to Monitoring EtCO2 During Opioid Delivery (CE)', 'In Progress']]

Code to push the list to Oracle table :

import cx_Oracle
con = cx_Oracle.connect('username/password@tamans*****vd/Servicename')
ver=con.version.split(".")
print(ver)
cur=con.cursor()
cur.execute("INSERT INTO TEST_CSODUPLOAD ('FIRSTNAME','LASTNAME','EMAIL','COURSE_NAME','STATUS') VALUES(:1,:2,:3,:4,:5)",lines)
con.commit ()
cur.close()

I am getting the error :

DatabaseError: ORA-01484: arrays can only be bound to PL/SQL statements

Please help me solve the issue Thanks in advance

Anthony Tuininga · Accepted Answer · 2019-10-01 22:07:28Z

6

The problem is that you are trying to pass an array to a single insert statement. You have two options here:

1) Use a loop to insert each row separately:

for line in lines:
    cursor.execute("insert into ...", line)

2) Use cursor.executemany() instead to do an array insert

cursor.executemany("insert into ...", lines)

The second option is more efficient but you have to make sure that the type of data is consistent for each line. If you have a number in one row and a string in the next row an error will be raised.

The documentation gives more detail.

edited Oct 1, 2019 at 22:07

answered Aug 31, 2016 at 13:44

Anthony Tuininga

7,2062 gold badges17 silver badges25 bronze badges

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

Comments

gowtham Y.R · Accepted Answer · 2016-08-31 15:52:19Z

3

Thanks for your answers :) The issue got resolved once I changed from

cur.execute("INSERT INTO TEST_CSODUPLOAD ('FIRSTNAME','LASTNAME','EMAIL','COURSE_NAME','STATUS') VALUES(:1,:2,:3,:4,:5)",lines)

to

cur.executemany("insert into TEST_CSODUPLOAD(Firstname,LastName,email,Course_name,status) values (:1, :2, :3, :4,:5)", lines)

answered Aug 31, 2016 at 15:52

gowtham Y.R

1211 gold badge2 silver badges11 bronze badges

1 Comment

Anthony Tuininga Over a year ago

Glad to hear it. Why did you create a new answer, though, instead of simply accepting the one I gave?

lauralacarra · Accepted Answer · 2016-08-31 12:33:37Z

0

That error means what it said here

This is not a bug. You are issuing a SQL query, not a PL/SQL statement and this sort of thing is not supported by Oracle. In this particular case you can simply pass the string. If you have multiple strings you will need to use a subquery instead. Look at the table() and cast() operators for inspiration!

You can also dump into a sql file and execute it.

answered Aug 31, 2016 at 12:33

lauralacarra

6208 silver badges10 bronze badges

3 Comments

Anthony Tuininga Over a year ago

Good point. Although in this case the OP is not issuing a query! :-)

lauralacarra Over a year ago

Wow the solution of cursor.executemany is awesome!! i sould change my code!! @AnthonyTuininga how big might me the array? Beacuse i'm working with too many inserts. Are they respect the nextval an currval? thank u

Anthony Tuininga Over a year ago

The array can be as big as you like -- within the constraints of memory and the database itself, of course! I'm not sure exactly what you mean about nextval and currval. You can use those within your insert statement and they will behave as they do everywhere else.

Collectives™ on Stack Overflow

Insert into oracle table from CSV using python

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related