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.

Parfait · Accepted Answer · 2018-09-06 18:35:38Z

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 6, 2018 at 18:35

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ETLJ Over a year ago

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.

Parfait Over a year ago

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.

Christopher Jones Over a year ago

General solutions and links for dealing with unknown numbers of IN parameters are in 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.

Collectives™ on Stack Overflow

Using cx_Oracle with an IN statement (Python 3)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related