How to use a list in Oracle query from Python

Question

I have the following query:

SOUTHERN_STATE = ["AL", "GA", "TN", ....]

query = """
    SELECT
        *
    FROM
        USERS
    WHERE
        STATE in ({})
""".format(SOUTHERN_STATE)

but I got this error: ORA00936: missing expression

How can I use a list in an Oracle query in Python?

Does this answer your question? [imploding a list for use in a python MySQLDB IN clause](https://stackoverflow.com/questions/589284/imploding-a-list-for-use-in-a-python-mysqldb-in-clause) — Brown Bear, May 26 '21 at 14:36
See the cx_Oracle manual [Binding Multiple Values to a SQL WHERE IN Clause](https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html#binding-multiple-values-to-a-sql-where-in-clause). — Christopher Jones, May 26 '21 at 21:43

score 4 · Answer 1 · answered May 26 '21 at 19:58

4

The answer supplied by Joe Thor will work well so long as the values in the SOUTHERN_STATE variable are well-known and cannot result in SQL injection. If the values are not well-known, this solution can be used instead:

SOUTHERN_STATE = ["AL", "GA", "TN", ....]
bind_names = ",".join(":" + str(i + 1) for i in range(len(SOUTHERN_STATE)))
sql = f"""
    SELECT *
    FROM USERS
    WHERE STATE in ({bind_names})"""
for row in cursor.execute(query, SOUTHERN_STATE):
    print(row)

You can see more details about this approach in the cx_Oracle user documentation.

answered May 26 '21 at 19:58

Anthony Tuininga

6,388
2
14
23

That's using python format strings rather than cx_oracle bind parameters and would be unsafe if any of the values came from input. – AdamC Apr 20 '22 at 23:05
1

Nope, its using bind variables. Note the colon (:) in tthe bind names! – Anthony Tuininga Apr 22 '22 at 02:56

Joe Thor · Answer 2 · 2021-05-26T14:56:37.970

Join the python list into a string and pass the string. Per the comments, using the logic in this post:

Python cx_Oracle module: unable to format query in code


SOUTHERN_STATE = ["AL", "GA", "TN", ....]
SOUTHERN_STATE_STRING ','.join(['%s'] * len(SOUTHERN_STATE))


query = """
    SELECT
        *
    FROM
        USERS
    WHERE
        STATE in ({})
""".format(SOUTHERN_STATE_STRING, tuple(SOUTHERN_STATE))

How to use a list in Oracle query from Python

2 Answers2