3

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.

uber
  • 4,163
  • 5
  • 26
  • 55

1 Answers1

3

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)
Ajax1234
  • 69,937
  • 8
  • 61
  • 102
  • 1
    The `?` notation is also **much** safer wrt [SQL injection](https://www.explainxkcd.com/wiki/index.php/327:_Exploits_of_a_Mom). And it works just fine with the OPs integers too (I started writing my own answer until I realized you also covered parametrized queries). In fact, parametrized queries will also avoid bombing out if one of the list's values was `None`, which needs `null`, rather than `'None'` in a non-parametrized query. – JL Peyret Feb 17 '21 at 20:08