Correct way to "select * from tbl where field in ?" and the placeholder is a list without string interpolation

Question

I have a query of this form using pysqlite:

query = "select * from tbl where field1 in ?"
variables = ['Aa', 'Bb']

In a query, I'd like this to work:

with conn.cursor() as db:
  res = db.execute(query, (variables,)).fetchall()

eg, interpreted into SQLITE command line as:

select * from tbl where field1 in ("Aa", "Bb");

But this fails with:

pysqlite3.dbapi2.InterfaceError: Error binding parameter 0 - probably unsupported type.

I understand I can just string.join([mylist]), but this is unsafe. How can I use placeholder parameters and a list in sqlite with python?

Update

Differentiating this from similar questions on Stackoverflow, they seem to be looking to use %s string interpolation where I am looking to avoid this

`fields = ("Aa", "Bb")` =================== `result_set = c.execute('select * from tbl where field1 IN (%s)' % ("?," * len(fields))[:-1], fields)` — Anup Yadav, Dec 31 '18 at 06:49
Thanks, I'm asking for how to do this safely using placeholder ? syntax rather than with string interpolation. — Mittenchops, Dec 31 '18 at 06:52
This already takes care of sql injection is when you use `?`, But still I'm too looking for that. reading the same too. — Anup Yadav, Dec 31 '18 at 06:54
This appears to be a duplicate of [Parameter substitution for a SQLite “IN” clause](https://stackoverflow.com/questions/1309989/parameter-substitution-for-a-sqlite-in-clause) — Bryan Oakley, Dec 31 '18 at 06:59
@BryanOakley, I believe that user was looking for something different, because the accepted answer there uses string interpolation. — Mittenchops, Dec 31 '18 at 07:18
I'm assuming the length of `variables` is not known and may vary? — Anoop R Desai, Dec 31 '18 at 07:58

score 0 · Answer 1 · answered Dec 31 '18 at 08:30

Question: WHERE field IN ? and the placeholder is a list without string interpolation

Prepare your Query with the number of bindings

bindings = '?,'*len(values)
QUERY = "SELECT * FROM t1 WHERE id IN ({});".format(bindings[:-1])

print("{}".format(QUERY))

Output:

SELECT * FROM t1 WHERE id IN (?,?,?);