I have following SQL
select * from my_table
where my_param in :params
How do I bind python list or another array to cx_Oracle cursor execute expression?
This does not work
cursor.execute(my_sql, {'params': [1, 2, 3]}
-
I found at least one duplicate after all, but still think, that would be good to receive actual answers and left this as is for better search. There it is stackoverflow.com/questions/40954293/…Nikolay Prokopyev– Nikolay Prokopyev2019-02-22 17:12:25 +00:00Commented Feb 22, 2019 at 17:12
-
1In case anyone finds this question and want to bind an array of values for insert, see the cx_Oracle example github.com/oracle/python-cx_Oracle/blob/master/samples/….Christopher Jones– Christopher Jones2019-02-22 23:28:33 +00:00Commented Feb 22, 2019 at 23:28
Add a comment
|
1 Answer
After searching around for a long time, I found this solution most elegant for me to pass a list to a oracle sql statement.
It requires that you can create new types within the database.
create a nested table type in the database
create type t_id_tab as table of number;
Please note that in oracle 11 this must be done on schema level and not within a package. Otherwise our statement (see next step) will not work.
the python script
With cx_Oracle one can use the object api to create a new object of custom types. This object can than be passed to the query.
import cx_Oracle
db = cx_Oracle.connect("my_user", "my_password", "<host>:<port>/<sid>")
cursor = db.cursor()
id_list_type = db.gettype("t_id_tab")
id_list = id_list_type.newobject([1, 2, 3, 4])
cursor.execute("select * from my_table where my_param in (select column_value from table (:id_values))", id_values=id_list)
Untested version for oracle 12 onwards
It is said,that from oracle12 onwards the same sql syntax as above works also for associative arrays. I cannot test it. But if this is right, this should work without creating a custom collection type;
import cx_Oracle
db = cx_Oracle.connect("my_user", "my_password", "<host>:<port>/<sid>")
cursor = db.cursor()
id_list = cursor.arrayvar(cx_Oracle.NUMBER, [1, 2, 3, 4])
cursor.execute("select * from my_table where my_param in (select column_value from table (:id_values))", id_values=id_list)
