Pass BOTH single bind variable and list variable to SQL query cx_Oracle Python

Question

I have a Oracle SQL query:

SELECT * from table1 WHERE deliveredDate = ? AND productID IN (?,?,?,...);

I would like to pass a single variable to deliveredDate and a list with length unknown to the productID using cx_Oracle and Python

From the Oracle Using Bind guide (https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html) I understand that you can bind either the single variable or list of items, but I'm not sure if we can bind both.

Please help me with this issue.

Thank you.

Answer 1

Of course you can, but convert the notation for bind variables from ? to :-preceeded integers such as

import pandas as pd
import cx_Oracle
import datetime

conn = cx_Oracle.connect('un/pwd@ip:port/db')
cur = conn.cursor()

sql  = """
       SELECT *
         FROM table1
        WHERE deliveredDate = :0 AND productID IN (:1,:2)   
       """
cur.execute(sql,[datetime.datetime(2022, 5, 3),1,2])

res = cur.fetchall()

print(res)

Answer 2

The key part of your question was the 'unknown length' for the IN clause. The cx_Oracle documentation Binding Multiple Values to a SQL WHERE IN Clause shows various solutions each with some pros & cons depending on size of the list and the number of times the statement will be executed. For most cases you will not want to bind to a single placeholder in your statement IN list because of performance implications. If there is an upper bound on the size of the IN list, then put that many placeholders and bind None for all unknown values. The doc example explains it better:

cursor.execute("""
        select employee_id, first_name, last_name
        from employees
        where last_name in (:name1, :name2, :name3, :name4, :name5)""",
        name1="Smith", name2="Taylor", name3=None, name4=None, name5=None)
for row in cursor:
    print(row)

(This uses keyword parameters to match the bind placeholders, but you can use a list instead).

Other solutions are shown in that doc link.

Answer 3

You can use connect by to split a string by a delimiter. You only have to pass one variable, a string like '46083,46092,46093,46096,46098,46111,46119' .

It also works with constantly changing item number in the string.

It might add a little overhead to the SQL but if your query runs several seconds or more, it doesn't matter. In my case it gave about ~0,064 seconds to the run time with 30-60 items.

select * from MYTABLE where
id in
(
    SELECT regexp_substr( :MYLIST ,'[^,]+', 1, level) myid FROM dual
    CONNECT BY regexp_substr( :MYLIST , '[^,]+', 1, level) IS NOT NULL
)