I've prepared a list in Python, and would like to use that list to filter the items I query from a SQL database.
I have a query like this:
sql_query = "
SELECT item
FROM database
WHERE item IN (*list*) "
And want to place a list that I prepared earlier in the script in the place of list. How would I do this? %s and %d don't seem to do it.
Without knowing the SQL library being used (which could make this easier), the safest way would be to use a %s (or ?, or other appropriate parameter marker) for each item in the list, and pass the list in the parameters. Example:
sql_query = """
SELECT item
FROM database -- This is actually a table.
WHERE item IN ({})
""".format(','.join(['?'] * len(items)))
cursor.execute(sql_query, items)
This will create a query like:
SELECT item
FROM database
WHERE item IN (?,?,...)
Which is still safe from SQL injection. If items is empty, this will fail with a syntax error because item IN () is not valid in any SQL database that I'm aware of.
? and not %s?
?, %s, or some other marker.? bind variables.
len(items) many ?s joined by ,s.