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.

score 1 · Accepted Answer · answered Sep 06 '18 at 18:35

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))])

answered Sep 06 '18 at 18:35

Parfait

104,375
17
94
125

Thanks Parfait -- the exact number of items may not always be known at runtime, so I'm wondering if there is a way to have the IN dynamically recognize one or more values. – ETLJ Sep 06 '18 at 21:25
Yes, you can dynamically build prepared SQL statements (like any string in Python) depending on number of items. But this aspect was not mentioned in your above question. – Parfait Sep 06 '18 at 21:42
1

General solutions and links for dealing with unknown numbers of IN parameters are in https://oracle.github.io/node-oracledb/doc/api.html#sqlwherein I know this is for a different language, but the solutions apply to cx_Oracle, PHP OCI8, and APIs for other languages. – Christopher Jones Sep 06 '18 at 22:27

Using cx_Oracle with an IN statement (Python 3)

1 Answers1