Reputation: 191
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.
Upvotes: 3
Views: 1642
Reputation: 107587
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))])
Upvotes: 1