CODEWITHSUNDEEP
pythonsqlitesqlalchemy
Fine Baby
Fine Baby

Reputation: 49

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

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.

Preview of currently installed version on the 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?

Error 1-st page Error 2-nd page

Upvotes: 0

Views: 1591

Answers (1)

mkrieger1
mkrieger1

Reputation: 23235

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 264)!

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.

Upvotes: 3

Related Questions