Python executemany function

Question

I have a problem when running executemany with cx_oracle

WHen I run the following statement, I receive ORA-01036: illeagal variablename/number

infotext_list is a list of strings that should be compared with the "SOMETHING" it looks like ["abc", "bcd", "def", ...] and every string in it should be compared with the SOMETHING in that other databasetable!

insert_stmt = 'INSERT INTO data_table (...) SELECT ... FROM other_table WHERE SOMETHING = ? '
curs.executemany(insert_stmt, infotext_list)

If I iterate over the infotext_list and use the standard execute() method it just works fine but it takes forever.

what is `infotext_list` and what is the exact error message you get. — Burhan Khalid, Jan 14 '15 at 11:49

score 0 · Answer 1 · answered Dec 11 '15 at 07:43

A couple things I changed to get this to work for me.

1- Change infotext_list to be a list of mappings or sequences

infotext_list = [("abc",), ("bcd",), ("def",)] --notice the extra , inside the ()

or

infotext_list = [{'value':"abc"}, {'value':"bcd"}, {'value':"def"}]

2- Change ? to be :value if you want to use a mapping or :1 (or any other :name you want) if you want to use a sequence

Both of these work for me

infotext_list = [("abc",), ("bcd",), ("def",)]
insert_stmt = 'INSERT INTO data_table (...) SELECT ... FROM other_table WHERE SOMETHING = :1 '
curs.executemany(insert_stmt, infotext_list)

infotext_list = [{'value':"abc"}, {'value':"bcd"}, {'value':"def"}]
insert_stmt = 'INSERT INTO data_table (...) SELECT ... FROM other_table WHERE SOMETHING = :value '
curs.executemany(insert_stmt, infotext_list)

I have a short write up on using cx_Oracle for crud operations here.

Python executemany function

1 Answers1