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.

Ajax1234 · Accepted Answer · 2021-02-17 22:02:22Z

2

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)

edited Feb 17, 2021 at 22:02

answered Feb 17, 2021 at 16:29

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

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

1 Comment

JL Peyret Over a year ago

The ? notation is also much safer wrt SQL injection. 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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related