CODEWITHSUNDEEP
pythonpython-3.xoracle-databasecx-oracle
Jack Smith
Jack Smith

Reputation: 81

How to use a list in Oracle query from Python

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?

Upvotes: 1

Views: 2407

Answers (2)

Anthony Tuininga
Anthony Tuininga

Reputation: 7086

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.

Upvotes: 6

Joe Thor
Joe Thor

Reputation: 1260

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

Upvotes: 0

Related Questions