Python SQLite IN operator with a variable value

Question

In Python SQLite, how do I make (1, 2) a variable, like in:

cursor.execute('SELECT * FROM basicinfo WHERE id IN (1, 2)')

Doing the following doesn't work:

cursor.execute('SELECT * FROM basicinfo WHERE id IN ?', (1, 2))

It gives the error:

sqlite3.OperationalError: near "?": syntax error

forpas · Accepted Answer · 2020-04-30 15:39:43Z

1

There is only 1 ? placeholder in your sql statement but you want to pass a list of values and this is syntactically wrong.
You could use:

cursor.execute("SELECT * FROM basicinfo WHERE id IN ?", ("(1, 2)",))

but this would lead to this sql statement:

SELECT * FROM basicinfo WHERE id IN '(1, 2)'

which is not what you want because the ? placeholder would be replaced by the string literal '(1, 2)' and not a list of values.

In this case you can use the operator LIKE:

cursor.execute("SELECT * FROM basicinfo WHERE ',' || ? || ',' LIKE '%,' || id || ',%' ", ("1,2",))

You will have to remove parentheses and spaces from the comma separated list of values.

answered Apr 30, 2020 at 15:39

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

simonzack Over a year ago

Hmm tricky way of using LIKE. I'm guessing if there is no more direct way then it might be better to just format the query myself in Python.

forpas Over a year ago

SQLite does not provide string functions which you could use to parse a comma separated list (for example MySql has find_in_set()), so like is the only option if you don't want to create the sql statement with concatenations in a loop. And yes it would work with strings. It would create a statement like this: like ',a,b,c,' like '%,b,%'

Collectives™ on Stack Overflow

Python SQLite IN operator with a variable value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related