CODEWITHSUNDEEP
pythonsqlite
crosswired
crosswired

Reputation: 1441

python string substitution in sqlite query

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

Upvotes: 2

Views: 957

Answers (2)

Giuseppe Ottaviano
Giuseppe Ottaviano

Reputation: 4633

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

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 839144

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

Upvotes: 6

Related Questions