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