Pass BOTH single bind variable and list variable to SQL query cx

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.

CodePudding user response：

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)

CodePudding user response：

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.