Register an adapter-free named parameter query

By Batu · English technical notes

Also published in our primary archive.

A named SQLite placeholder connects a :name token in SQL to a key in a Python dictionary. It is useful when a query has several values with different meanings: the SQL here names :minimum_year and :wanted_title, while the dictionary supplies their values. The Python placeholder guide specifies that named placeholders use a dictionary and that it must contain every named key; extra dictionary items are ignored.

This fixture needs no adapter registration because it binds only values SQLite already understands: Python str becomes TEXT and int becomes INTEGER. executemany() uses named dictionaries to add two fixed book rows. The subsequent execute() applies both named conditions and fetches the one matching tuple. The assertion verifies that the concrete input minimum_year=1900 plus wanted_title="Grace" produces ("Grace", 1952), which the program prints as Grace: 1952.

Do not interpolate either value into the SQL string with formatting. Placeholders bind values; they do not substitute SQL identifiers, keywords, or fragments such as an ORDER BY direction. If a custom Python object is needed later, adapt it explicitly to one of SQLite’s native types rather than assuming this adapter-free pattern will accept it. This example uses no newer API and works with supported Python 3 sqlite3 versions.

AI assistance disclosure: Batu Lab Notes used AI assistance to draft this article; verify behavior in the Python and SQLite versions you deploy.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE book (title TEXT, published INTEGER)")
con.executemany(
    "INSERT INTO book VALUES (:title, :published)",
    [
        {"title": "Ada", "published": 1843},
        {"title": "Grace", "published": 1952},
    ],
)

params = {"minimum_year": 1900, "wanted_title": "Grace"}
row = con.execute(
    "SELECT title, published FROM book "
    "WHERE published >= :minimum_year AND title = :wanted_title",
    params,
).fetchone()
assert row == ("Grace", 1952)

print(f"{row[0]}: {row[1]}")
con.close()

Grace: 1952