SQLite Basics: Register a Small Python Function for SQL Queries

Import reports sometimes need one normalization rule in both Python and SQL. Register a small scalar function on the connection so a query can apply the same rule to each value without embedding application code in the database file.

Prepare a scratch directory

Tested on Linux with CPython 3.12.14 and SQLite 3.53.1. You need Python 3.11 or newer with SQLite support, Bash, a text editor, and standard mktemp, rm, and rmdir utilities. No third-party packages are required. Run Python without -O so the checks remain enabled.

Keep the same terminal open. Stop if setup fails, before saving the source:

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Run the example

Save this as normalize-sql-labels.py:

import sqlite3


def normalized_label(value):
    if value is None:
        return None
    if not isinstance(value, str):
        raise TypeError("label must be text or NULL")
    return " ".join(value.split()).casefold()


connection = sqlite3.connect(":memory:")
try:
    connection.create_function("normalized_label", 1, normalized_label,
                               deterministic=True)
    connection.execute("CREATE TABLE imports (id INTEGER PRIMARY KEY, label TEXT)")
    connection.executemany("INSERT INTO imports VALUES (?, ?)",
                           [(1, "  Blue   Pen "), (2, "Straße"), (3, None)])
    rows = connection.execute(
        "SELECT id, normalized_label(label) FROM imports ORDER BY id"
    ).fetchall()
    assert rows == [(1, "blue pen"), (2, "strasse"), (3, None)]
    for item_id, label in rows:
        print(f"{item_id}: {label!r}")
    other = sqlite3.connect(":memory:")
    try:
        try:
            other.execute("SELECT normalized_label('Blue')")
        except sqlite3.OperationalError as error:
            assert "no such function" in str(error)
            print("Other connection: function is not registered")
        else:
            raise AssertionError("Function unexpectedly exists")
    finally:
        other.close()
finally:
    connection.close()

Run it:

python3 -B normalize-sql-labels.py

Observed output:

1: 'blue pen'
2: 'strasse'
3: None
Other connection: function is not registered

Apply the result

SQLite calls the Python function once for each value it needs to evaluate. SQL NULL arrives as Python None; returning None produces NULL. The normalization collapses whitespace and uses Unicode case folding, which is stronger than merely making letters lowercase. It does not perform Unicode normalization or establish that two labels identify the same person or product.

The second connection cannot call the function until it registers its own implementation. Registration lives on the connection, not in a database file. Mark a function deterministic only when equal arguments produce equal results. Avoid callbacks that depend on the current time, mutable external state, or network responses.

This example uses trusted SQL and a database it creates itself. Keep callbacks free of filesystem or network side effects, especially when an application opens database files from other parties. A callback exception is reported through SQLite rather than preserving an ordinary Python call stack at the query site.

See the official reference for the API and its constraints. The output above comes from this controlled fixture.

Clean up

Remove the saved source, return to the starting directory, then remove the empty practice directory:

rm -- normalize-sql-labels.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

If the directory is not empty, inspect its contents before removing anything else.

Acknowledgements

This post was created with assistance from AI.