How to query a SQLite database using values from a Python list?

Question

This is Python specific therefore this is not completely helpful.

I have a list of ids

[120931, 129301923, 1293019, 193923, 42939]

and instead of running a command for each of them with, e.g.

for row in c.execute(f'SELECT * from sdk WHERE app = 120931'):
    print(row)

I would like to pass in the entire list of ids for 'app' and get every row where those ids appear. Similar to the below, however with a variable (a python list) in the query

for row in c.execute(f'SELECT * from sdk WHERE app IN (120931, 129301923, 1293019, 193923, 42939)'):
    print(row) # passes entire list in parens but as a Python variable

I've tried string interpolation but it doesn't work.

Answer 1

You can use str.join to provide proper formatting for a sql IN statement:

apps = [120931, 129301923, 1293019, 193923, 42939]
c.execute(f'SELECT * from sdk WHERE app IN ({", ".join(map(str, apps))})')

However, if apps contains string values, you can use ? notation in your statement. That way, you let sqlite3 handle the proper formatting for the statement:

c.execute(f'SELECT * from sdk WHERE app IN ({", ".join(["?"]*len(apps))})', apps)