Python: Bulk upload records of Oracle table with executemany() in python

Question

  data=[]
  dataToInsert = [ ]
  for index, row in df.iterrows():
   contentid = row['CONTENTID']
   Objectsummary = row['OBJECT_SUMMARY']
   Title=row['TITLE']
   if Title is None:
    Title = ""
   if Objectsummary is None:
    Objectsummary = ""
   allSummeries =Title  + ' ' + Objectsummary
   lists=function_togetNounsAndVerbs(allSummeries)
   verbList =lists[0]
   nounList =lists[1]

   NounSet = set(nounList)
   VerbSet = set(verbList)

   verbs = " "
   verbs=verbs.join(VerbSet)

   nouns=" "
   nouns=nouns.join(NounSet)
   verbs=re.sub(r" ", ", ", verbs)
   nouns=re.sub(r" ", ", ", nouns)
 
  # Here we are going to create the data sdet to be updated in database table in batch form.
   data.append(nouns)
   data.append(verbs)
   data.append('PROCESSED')
   data.append(contentid)
   dataToInsert.append([data[0],  data[1], data[2], data[3]])

  print("ALL DATA TO BE UPDATED IN TABLE IS :---> ",dataToInsert)
  statement = """UPDATE test_batch_update_python SET NOUNS = ?, Verbs = ?  where CONTENTID = ?"""
  a = cursor.executemany(statement, dataToInsert)
  connection.commit()

In above code function_togetNounsAndVerbs(allSummeries) this function will return the lists. I am getting following exception:

 **a = cursor.executemany(statement, dataToInsert)
cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number**

Please help me with this. Or what are the other ways I can do this. Initially I used to update single row at a time using cursor.execute() but it was very time consuming. To minimize the time i am using bulk upload (i.e. cursor.executemany() )

You've got 3 placeholders in your query: `UPDATE test_batch_update_python SET NOUNS = ?, Verbs = ? where CONTENTID = ?` but you're appending 4 items to `data`. Either you have missed a column in the query or you need to remove `'PROCESSED'`. — jignatius, Jul 02 '20 at 11:27
With Oracle, you need to use ":name" instead of "?" for bind placeholders in the SQL statement. Check the cx_Oracle doc [Batch Statement Execution and Bulk Loading](https://cx-oracle.readthedocs.io/en/latest/user_guide/batch_statement.html) and [Using Bind Variables](https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html). — Christopher Jones, Jul 02 '20 at 22:41

score 0 · Answer 1 · answered Jul 03 '20 at 03:08

Here's a related example that works. The table is created as:

DROP table test_batch_update_python;
CREATE TABLE test_batch_update_python (contentid NUMBER, nouns VARCHAR2(20), verbs VARCHAR2(20));
INSERT INTO test_batch_update_python (contentid) VALUES (1);
INSERT INTO test_batch_update_python (contentid) VALUES (2);
COMMIT;

cursor = connection.cursor()

dataToInsert = [
       ('shilpa', 'really fast', 1),
       ('venkat', 'also really fast', 2),
   ]

print("ALL DATA TO BE UPDATED IN TABLE IS :---> ", dataToInsert)

connection.autocommit = True;
statement = """UPDATE test_batch_update_python SET nouns=:1, verbs=:2 WHERE contentid=:3"""
cursor.setinputsizes(20, 20, None)
cursor.executemany(statement, dataToInsert)

The output is:

ALL DATA TO BE UPDATED IN TABLE IS :--->  [('shilpa', 'really fast', 1), ('venkat', 'also really fast', 2)]

And then querying the data gives:

SQL> select * from test_batch_update_python;

 CONTENTID NOUNS                VERBS
---------- -------------------- --------------------
         1 shilpa               really fast
         2 venkat               also really fast

score 0 · Answer 2 · answered Jul 04 '20 at 01:10

0

check this link for an and update statement

https://blogs.oracle.com/oraclemagazine/perform-basic-crud-operations-with-cx-oracle-part-3

thanks

answered Jul 04 '20 at 01:10

Himanshu Kandpal

1,261
8
11

Python: Bulk upload records of Oracle table with executemany() in python

2 Answers2