python string substitution in sqlite query

i'm trying to use an IN statement to return results which match one of a list of st开发者_开发问答rings

for example

strings = ['string1', 'string2', 'string3'] c.execute('select count(*) from table where foo in ?', strings)

i know this is incorrect and doesnt work but i hope that it highlights what im trying to do...

You can't do that. There are three problems:

• You can't have a table called table unless you use backticks around the table name.
• An IN clause must have parentheses.
• You want three parameters, not one.

Try this instead:

sql = 'SELECT COUNT(*) FROM yourtable WHERE foo IN (?, ?, ?)'

If the number of strings is variable, use this instead:

params = ','.join('?' for x in strings)
sql = 'SELECT COUNT(*) FROM yourtable WHERE foo IN (' + params + ')'

You can do a ','.join(strings) as @Mark Byers suggests, that works most times. However if the number of strings is very long it will fail because SQL queries have bounded length.

Another way of doing it is creating a temp table, inserting there all the strings and doing a join to perform the intersection, something like

c.execute('CREATE TEMP TABLE strings (s STRING)')
c.executemany('INSERT INTO strings (s) VALUES (?)', ((s,) for s in strings))
c.execute('SELECT COUNT(*) FROM table JOIN strings ON table.foo == strings.s')