Python Cx_Oracle; How Can I Execute a SQL Insert using a list as a parameter

Question

I generate a list of ID numbers. I want to execute an insert statement that grabs all records from one table where the ID value is in my list and insert those records into another table.

Instead of running through multiple execute statements (as I know is possible), I found this cx_Oracle function, that supposedly can execute everything with a single statement and list parameter. (It also avoids the clunky formatting of the SQL statement before passing in the parameters) But I think I need to alter my list before passing it in as a parameter. Just not sure how.

I referenced this web page: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html

ids = getIDs()
print(ids)

[('12345',),('24567',),('78945',),('65423',)]

sql = """insert into scheme.newtable
     select id, data1, data2, data3
     from scheme.oldtable
      where id in (%s)"""
cursor.prepare(sql)
cursor.executemany(None, ids)

I expected the SQL statement to execute as follows:

Insert into scheme.newtable select id, data1, data2, data3 from scheme.oldtable where id in ('12345','24567','78945','65423')

Instead I get the following error: ORA-01036: illegal variable name/number

Edit: I found this StackOverflow: How can I do a batch insert into an Oracle database using Python? I updated my code to prepare the statement before hand and updated the list items to tuples and I'm still getting the same error.

score 1 · Accepted Answer · answered Feb 06 '19 at 23:12

You use executemany() for batch DML, e.g. when you want to insert a large number of values into a table as an efficient equivalent of running multiple insert statements. There are cx_Oracle examples discussed in https://blogs.oracle.com/opal/efficient-and-scalable-batch-statement-execution-in-python-cx_oracle

However what you are doing with

insert into scheme.newtable
     select id, data1, data2, data3
     from scheme.oldtable
      where id in (%s)

is a different thing - you are trying to execute one INSERT statement using multiple values in an IN clause. You would use a normal execute() for this.

Since Oracle keeps bind data distinct from SQL, you can't pass in multiple values to a single bind parameter because the data is treated as a single SQL entity, not a list of values. You could use %s string substitution syntax you have, but this is open to SQL Injection attacks.

There are various generic techniques that are common to Oracle language interfaces, see https://oracle.github.io/node-oracledb/doc/api.html#sqlwherein for solutions that you can rewrite to Python syntax.

I see where I was getting confused. Thank you for clarifying. So I still have to do the sql formatting. This works: `placeholders = ','.join(":%d" % i for i,_ in enumerate(ids))` `cursor.execute(sql % placeholders, ids)` — Drew Aschenbrener, Feb 07 '19 at 13:56

score 1 · Answer 2 · answered Apr 27 '19 at 07:18

using temporary table to save ids (batch insert)

cursor.prepare('insert into temp_table values (:1)')
dictList = [{'1': x} for x in ids]
cursor.executemany(None, dictList)

then insert selected value into newtable

sql="insert into scheme.newtable (selectid, data1, data2, data3 from scheme.oldtable inner join temp_table on scheme.oldtable.id = temp_table.id)"
cursor.execut(sql,connection)

the script of create temporary table in oracle

CREATE GLOBAL TEMPORARY TABLE temp_table
(
  ID  number
);
commit

I hope this useful.

Python Cx_Oracle; How Can I Execute a SQL Insert using a list as a parameter

2 Answers2