Python cx_Oracle. banging head with executemany()

Question

I spent most of yesterday looking at questions here and around the web and Couldn't really, still, figure out what I'm missing. I'm pretty sure it has to be something really stupid but I'm burn out by now.

So, code:

temp_table_name = 'COMPTEMP.i'+todo_name+'_MSGRUN'
sql_create_table = "CREATE TABLE "+temp_table_name+" (CL VARCHAR2(255)) NOLOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING"
DB_GATEWAY.execute(sql_create_table)

(this gets created ok)

sql_fill_table_header = """INSERT INTO """+temp_table_name+""" (CL) VALUES (:1)"""
sql_fill_table_build = []
sql_fill_table_build = ['1', '2', '3', '4', '55']

DB_GATEWAY.executemany(sql_fill_table_build, sql_fill_table_header)

(this goes now to a session pool guard, which ends up below)

def executemany( self, db_operation, db_prepare ):
    self.cursor_.prepare(db_prepare)
    self.cursor_.executemany(None, db_operation)

When running this I get the fopllowing error

OracleException:  ORA-01036: illegal variable name/number

If I remove the value '55' from the list, all others get inserted fine, so looks like it's accepting only values of 1 char length.

When I run each statement via execute() alone they get inserted. I'm pretty sure it has to do something with either the code or how Oracle works with positional arguments, but I'm lost by now.

Appreciate the help !

Answer 1

executemany() expects a sequence of rows; the entries in the list must themselves be sequences of parameters, even when the inserted rows consist of just one value each:

sql_fill_table_build = [('1',), ('2',), ('3',), ('4',), ('55',)]

or use lists:

sql_fill_table_build = [['1'], ['2'], ['3'], ['4'], ['55']]

The reason it works with just the flat list of strings minus the '55' entry is because strings are sequences too, but '55' is a sequence of length two. Thus, the last entry tries to insert two parameters, '5' and '5', but your query only expects one parameter.

If some API gives you just a flat list, you can convert the flat list to a nested sequence with a list comprehension on insertion:

DB_GATEWAY.executemany(sql_fill_table_build, [(v,) for v in sql_fill_table_header])

I haven't tested this with cx_Oracle, but some database adapters also accept an iterable, so a generator expression might be more efficient:

DB_GATEWAY.executemany(sql_fill_table_build, ((v,) for v in sql_fill_table_header))

but it could be that cx_Oracle demands a sequence.