i'm trying to use an IN statement to return results which match one of a list of strings
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:
table unless you use backticks around the table name.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')
