pushing complete python list to oracle db using cx_Oracle

Question

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?

Which version of cx_Oracle are you using?

APC
– APC

2018-03-16 07:58:06 +00:00
Commented Mar 16, 2018 at 7:58 — APC
– APC, Commented Mar 16, 2018 at 7:58
@APC cx-Oracle==6.0.2

theNextBigThing
– theNextBigThing

2018-03-16 08:39:44 +00:00
Commented Mar 16, 2018 at 8:39 — theNextBigThing
– theNextBigThing, Commented Mar 16, 2018 at 8:39

Anthony Tuininga · Accepted Answer · 2018-03-17 16:25:50Z

1

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)

answered Mar 17, 2018 at 16:25

Anthony Tuininga

7,2062 gold badges17 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

pushing complete python list to oracle db using cx_Oracle

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related