Bind an IN list by creating placeholders from trusted length

By Batu · English technical notes

Bind an IN list by creating placeholders from trusted length

To bind ['A', 'C'] to a SQLite IN predicate, create one ? per value, then pass the list as the parameter sequence: IN (?,?) with ['A', 'C']. Binding the Python list to one placeholder fails because one placeholder accepts one value, not a container that SQLite expands into several values.

The fixture prints its table definition and three seeded codes. Its naïve query attempts IN (?) with the list as the only bound parameter. The resulting ProgrammingError is caught solely to display the boundary case; catching it should not be used to silently continue with an invalid query. The correction computes placeholders from the trusted list length, not from the list’s contents. The values remain bound parameters, so A and C are not interpolated into the SQL text. The assertion verifies that the query returns exactly those two rows in deterministic sorted order.

Python’s sqlite3 documentation specifies parameter substitution and warns against constructing SQL values with string operations (Python sqlite3 documentation). This pattern is for a non-empty, already-chosen sequence; define an explicit empty-list policy, such as skipping the query or using a false predicate, rather than generating IN () accidentally. No newer Python-only API is used; Python 3 with standard-library sqlite3 is sufficient. The assertion validates this synthetic list and schema, not arbitrary SQL assembled from untrusted identifiers.

AI assistance disclosure: Batu Lab Notes used AI assistance to draft this reproducible synthetic example; the assertions define its claimed result.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE items(code TEXT PRIMARY KEY)")
con.executemany("INSERT INTO items VALUES (?)", [("A",), ("B",), ("C",)])
values = ["A", "C"]
print(con.execute("SELECT sql FROM sqlite_master WHERE name='items'").fetchone()[0])
print("seed=A,B,C")

try:
    con.execute("SELECT code FROM items WHERE code IN (?)", (values,))
except sqlite3.ProgrammingError:
    print("naive list binding=ProgrammingError")
else:
    raise AssertionError("a Python list must not bind to one placeholder")

placeholders = ",".join("?" for _ in values)
rows = con.execute(
    f"SELECT code FROM items WHERE code IN ({placeholders}) ORDER BY code", values
).fetchall()
assert rows == [("A",), ("C",)]
print(f"corrected placeholders={placeholders}; bound_values={len(values)}")
print("result=" + ",".join(row[0] for row in rows))

CREATE TABLE items(code TEXT PRIMARY KEY)
seed=A,B,C
naive list binding=ProgrammingError
corrected placeholders=?,?; bound_values=2
result=A,C