Using cx_Oracle with an IN statement (Python 3)

Question

I am trying to pass in parameters into a SQL "IN" statement using cx_Oracle. This gives the correct result:

sql = """select * from
           (select level numb from dual connect by level <= 4)
         where numb = :var"""

print([row[0] for row in cur.execute(sql, (1,))])
Output: [1]

However I have not been able to figure out how to use an "IN" statement.

sql = """select * from
           (select level numb from dual connect by level <= 4)
         where numb in :var"""

print([row[0] for row in cur.execute(sql, (1, 2))])
Output: cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

I've tried variations of the IN statement, and also with using a dictionary to pass in the parameters.

Answer 1

When using single values or literals, the IN clause in SQL requires values wrapped in parentheses. And since you pass two parameters, include two placeholders within the paranetheses.

sql = """select * from
           (select level numb from dual connect by level <= 4)
         where numb in (:1, :2)"""

print([row[0] for row in cur.execute(sql, (1, 2))])