sqlite3.OperationalError "too many SQL variables" when selecting rows from given range

Question

I have a website hosted on a Linux server. The backend is done in Flask, and the database I am using is SQLite3 (with Flask-SQLAlchemy). The problem is that I needed to do a large query like in the next line.

for lhour in hour.query.filter(hour.id.in_([id_hour for id_hour in range(start_at , hour.query.count()+1)])):

The code above just does a query for all the hours where the id is bigger than the start_at variable, however the number of rows in the hours table is well over 999 (the maximum allowed in SQLite3 prior to version 3.32.0) so this would throw an error whenever the start_at variable was lower than 1600 (as the number of rows in the hour table is 2600).

After reading that for versions after 3.32.0 the maximum allowed is 32766, I compiled the source code and installed version 3.34.1 of SQLite3 on my Linux server.

However, the line of code would still throw the exact same error, so what version of SQLite3 does Flask-SQLAlchemy use and how do you set it so it uses the correct one?

mkrieger1 · Accepted Answer · 2021-02-14 09:48:51Z

3

Selecting values from a numeric range by using column.in_(list_of_values) will lead to a query where literally every item in the list_of_values occurs as a variable in the query.

What's limited in SQLite is the number of variables in a single statement, certainly not the number of rows in a table (in practice – the theoretical limit is 2⁶⁴)!

See sections 9 and 13 in https://www.sqlite.org/limits.html.

Use a simple comparison instead:

for lhour in hour.query.filter(hour.id >= start_at):

You do not need to check or install a particular SQLite version for that.

As a bonus, this only performs one query instead of two.

edited Feb 14, 2021 at 9:48

answered Feb 14, 2021 at 8:59

mkrieger1

24.2k7 gold badges68 silver badges84 bronze badges

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

Collectives™ on Stack Overflow

sqlite3.OperationalError "too many SQL variables" when selecting rows from given range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related