Work around the SQLite parameter limit in a select query

Question

I have a GUI application with a list of people which contains the person's database id and their attributes. Something like this:

+----+------+
| ID | Name |
+----+------+
|  1 | John |
|  2 | Fred |
|  3 | Mary |
[...]

This list can be filtered, so the amount and type of people depend from time to time. To get a list of Peewee Person objects I first get the list of visible IDs and use the following query:

ids = [row[0] for row in store]
Person.select().where(Person.id.in_(ids))

Which in turn translates to the following SQL:

('SELECT "t1"."id", "t1"."name" FROM "person" AS "t1" WHERE ("t1"."id" IN (?, ?, ?, ...))', [1, 2, 3, ...])

This throws an OperationalError: too many SQL variables error on Windows with more than 1000 people. This is documented in the Peewee and SQLite docs. Workarounds given online usually relate to bulk inserts and ways to split the action in chunks. Is there any way to work around this limitation with the mentioned SELECT ... WHERE ... IN query?

Getting the separate objects in a list comprehension is too slow:

people = [Person.get_by_id(row[0]) for row in store]

Maybe split the list of IDs in max 1000 items, use the select query on each chunk and then combine those somehow?

Answer 1

Where are the IDs coming from? The best answer is to avoid using that many parameters, of course. For example, if your list of IDs could be represented as a query of some sort, then you can just write a subquery, e.g.

my_friends = (Relationship
              .select(Relationship.to_user)
              .where(Relationship.from_user == me))
tweets_by_friends = Tweet.select().where(Tweet.user.in_(my_friends))

In the above, we could get all the user IDs from the first query and pass them en-masse as a list into the second query. But since the first query ("all my friends") is itself a query, we can just compose them. You could also use a JOIN instead of a subquery, but hopefully you get the point.

If this is not possible and you seriously have a list of >1000 IDs...how is such a list useful in a GUI application? Over 1000 anything is quite a lot of things.

To try and answer the question you asked -- you'll have to chunk them up. Which is fine. Just:

user_ids = list_of_user_ids
accum = []
# 100 at a time.
for i in range(0, user_ids, 100):
    query = User.select().where(User.id.in_(user_ids[i:i+100]))
    accum.extend([user for user in query])
return accum

But seriously, I think there's a problem with the way you're implementing this that makes it even necessary to filter on so many ids.