SQLite: too many SQL variables

What it means

Every ? in a statement (and every ?NNN, :name, @name or $name) is a variable, a place where your code binds a value. SQLite caps how many one statement may have, and checks while preparing it: over the cap, the statement fails with SQLITE_ERROR (code 1) and too many SQL variables, and nothing runs.

The cap, SQLITE_MAX_VARIABLE_NUMBER, is fixed when SQLite is compiled. By default it’s 999 before SQLite 3.32.0 and 32,766 from 3.32.0, but builds differ: we measured 500,000 in macOS’s /usr/bin/sqlite3 (3.51.0) and 250,000 in Python 3.14’s sqlite3 module (SQLite 3.53.4, from Homebrew). A program can lower the cap at run time, never raise it. So the same code can work on your Mac and fail on a server or phone with a different SQLite.

A numbered parameter above the cap gives a related message: variable number must be between ?1 and ?999.

Common causes

A long IN list, built with one ? per value: WHERE id IN (?, ?, ?, …) for thousands of ids. ORMs do this for “fetch these ids” and for prefetching related rows.
A big multi-row INSERT. The count is rows × columns: 1,000 rows of 40 columns is 40,000 variables. pandas’ to_sql(method="multi") without chunksize builds one statement for the whole table.
A lower cap where the code runs: an older SQLite (999), or a build or program that set a lower limit.

How to fix it

Pass the list as one JSON value

One parameter holds the whole list, whatever its length:

SELECT id, name FROM users
WHERE id IN (SELECT value FROM json_each(?));

Bind a JSON array such as [2,4,5] (in Python, json.dumps(ids)). JSON functions are built in from SQLite 3.38.0; older versions need the JSON1 extension compiled in.

Split the work into batches

Keep each statement under the cap, with room to spare: for example 500 ids per IN list, or rows per INSERT such that rows × columns stays below 999 if the code must run on old SQLite. In pandas, pass chunksize to to_sql. Run the batches in one transaction so they’re saved together.

Insert with executemany instead of one huge statement

Prepare an INSERT for one row and run it once per row (executemany in Python, a loop over a prepared statement in C or Swift). Each run binds only one row’s values, and inside a transaction it’s fast.

Use a temporary table for very long lists

Insert the ids into a temporary table, then join to it:

CREATE TEMP TABLE wanted (id INTEGER PRIMARY KEY);
-- insert the ids with executemany
SELECT u.* FROM users u JOIN wanted w ON w.id = u.id;

Check the cap your code meets

In Python 3.11 and later, connection.getlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER); in C, sqlite3_limit(db, SQLITE_LIMIT_VARIABLE_NUMBER, -1); in the sqlite3 shell, .limit variable_number. Raising it beyond what the build allows has no effect, so don’t depend on a high cap.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0:

sqlite3 :memory: ".limit variable_number"

     variable_number 500000

A statement with 500,001 ?s (SELECT 1 IN (?,?,…);, written to vars.sql by a script):

sqlite3 :memory: < vars.sql

Parse error near line 1: too many SQL variables
  ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
                                      error here ---^

The shell adds “Parse error near line 1:”; SQLite’s message is too many SQL variables. With .limit variable_number 32766 (the documented default), 32,766 variables ran and 32,767 failed the same way. A numbered parameter over a lower cap:

sqlite3 :memory: ".limit variable_number 999" "SELECT ?1000;"

Error: in prepare, variable number must be between ?1 and ?999

Through Python’s sqlite3 module (SQLite 3.53.4), after setlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER, 999), an IN list of 1,000 ?s raised sqlite3.OperationalError: too many SQL variables. The json_each version returned the rows for [2,4,5] with one parameter, and took a list of 100,000 ids the same way.

In Inlet

This error comes from statements a program builds. To try the json_each version on your data first, run it in an Inlet query tab with the JSON array written into the query.

too many SQL variables