CODEWITHSUNDEEP
pythondatabaseoracle-databaseplsqlcx-oracle
theNextBigThing
theNextBigThing

Reputation: 131

pushing complete python list to oracle db using cx_Oracle

I have two list having 100 elements in each (say class_db_col, and class_id_col). I want to push all the items in class_db_col list to one column (say class_result) present in oracle DB.

statement = 'update TRANSFERS_TXN_MT set CLASS_RESULT = :1 where id= :2'
for i in range(len(class_db_col)):
     cursor.execute(statement,(class_id_col[i],class_db_col[i]))
conn.commit()

getting this error

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

can anyone help me with this problem?

Upvotes: 1

Views: 5047

Answers (1)

Anthony Tuininga
Anthony Tuininga

Reputation: 7086

If you have an array of tuples you can use cursor.executemany() instead. It looks like you have two parallel arrays which you can create tuples out of via this code:

data = list(zip(class_id_col, class_db_col))

This should result in an array that looks like this:

[(1, 4), (2, 6), ..., (8, 15)]

Then you can use this code:

cursor.executemany("update TRANSFERS_TXN_MT set CLASS_RESULT = :1 where id = :2", data)

Upvotes: 1

Related Questions