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 !

Martijn Pieters · Accepted Answer · 2013-12-05T12:58:58.480

4

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.

edited Dec 05 '13 at 12:58

answered Dec 05 '13 at 12:53

Martijn Pieters

1,048,767
296
4,058
3,343

Thanks, that helped figure out the issue ! – jdq2013 Dec 05 '13 at 14:09
Unfortunately cx_Oracle insists on lists; generators are not allowed :( – Anto Nov 23 '17 at 12:23
@Anto: then just put `[...]` around the generator expression instead of `(...)` to turn it into a list comprehension. – Martijn Pieters Nov 23 '17 at 12:24
@Anto: I've just put that there for other database drivers. – Martijn Pieters Nov 23 '17 at 12:25
Yes I know, it was just to answer your "I haven't tested" and clarify. – Anto Nov 23 '17 at 12:33
2

@Anto: Right. I'm still holding out hope that a future version might still accept any iterable. :-) – Martijn Pieters Nov 23 '17 at 12:33

Python cx_Oracle. banging head with executemany()

1 Answers1