Python SQLite No Such Function REGEXP: Register Each Connection

If Python raises sqlite3.OperationalError: no such function: REGEXP, register a two-argument regexp function on the connection that executes the query. Opening the same database in a command-line application does not give Python that application's extra functions. Check the failing connection before changing the database file.

Identify the library-versus-shell mismatch

A reader reported this exact contrast in October 2026: a word-filter query worked in the SQLite shell but failed from a Python application. SQLite documents REGEXP as operator syntax that calls an application-defined function. The core library does not provide that function by default. The official shell includes additional extensions, including regular-expression support; a distributor's build may differ.

Start with a constant probe on your actual application connection: con.execute("SELECT 'a1' REGEXP '[0-9]'").fetchone(). A missing-function error on this probe isolates registration from your table, column, and data. If it returns a value, a function is already installed there: investigate its implementation and input contract rather than registering another blindly. Python and the shell can also use different SQLite versions, but changing versions alone does not establish that both environments expose identical extensions.

Register a text-only Python implementation

This small example uses Python's regular-expression engine and closes its demonstration connection explicitly. The callback returns 1 for a match, 0 for a nonmatch, and SQL NULL for unknown inputs. It rejects non-text operands rather than silently converting numbers or binary data into a different search representation.

import re
import sqlite3

def regexp(pattern, value):
    # Keep unknown values unknown, including under SQL NOT.
    if pattern is None or value is None:
        return None
    if not isinstance(pattern, str) or not isinstance(value, str):
        raise TypeError("REGEXP expects text operands")
    return int(re.search(pattern, value) is not None)

con = sqlite3.connect(":memory:")
try:
    con.create_function("regexp", 2, regexp)
    result = con.execute(
        "SELECT ? REGEXP ?", ("item42", "[0-9]")
    ).fetchone()[0]
    print(result)  # 1
finally:
    con.close()

The operand order matters. SQL value REGEXP pattern invokes regexp(pattern, value). The first callback argument is the pattern. Bind the pattern and value with placeholders; do not splice them into SQL. A bound pattern avoids SQL quoting problems, although it does not make an expensive regular expression cheap.

Register this function after each connection is created, before any query that needs it. A registration belongs to the connection, not the database file. A second connection—even in the same process—needs its own initialization. For a framework or connection pool, use its documented connection-creation hook and ensure every newly created connection receives the function. A startup call on a separate throwaway connection will not initialize the pool.

Decide what NULL should mean before filtering

Returning false for NULL is one possible application policy, but it changes exclusion queries. With the callback above, NULL REGEXP '[0-9]' returns NULL, and applying NOT still returns NULL. A WHERE clause keeps only true rows. Thus WHERE NOT (word REGEXP ?) excludes unknown values as well as words containing a digit.

If the product requirement is to retain unknown words too, state that separately: WHERE word IS NULL OR NOT (word REGEXP ?). An empty string is a known value: it does not match this digit pattern and is retained by the NOT filter. Test NULL, empty text, matching text, and nonmatching text as separate cases.

Validate patterns and keep engine differences visible

For a pattern supplied by a user, run re.compile(pattern) before executing the SQL and handle re.error at the input boundary. Invalid expressions raised inside a callback can surface as a generic user-defined-function error, which is less useful for explaining what went wrong.

This implementation uses Python re.search, which looks for a match anywhere in the text. It is not a promise of identical syntax, Unicode handling, or performance to the shell's regular-expression extension. If you need entire-string matching, choose and document that contract explicitly. Test representative patterns against both environments before treating their results as interchangeable. Do not load an arbitrary native extension just to silence this error.

For large tables or untrusted patterns, separately assess execution cost and the application's resource controls. Placeholders prevent SQL interpolation; they do not prevent a costly pattern from consuming excessive CPU. This example is a connection-initialization fix, not a production search-performance design.

Check the repaired connection

1. Confirm the constant probe returns 1 after registration.
2. Check 'abc' and the empty string return 0 for '[0-9]'.
3. Check NULL inputs remain NULL under the chosen policy.
4. Open a second connection and verify that it fails until initialized, then succeeds.
5. Run the real query with bound parameters and compare a small, known set of rows.

Verification limits: The local fixture passed on Python 3.11.6 with SQLite 3.42.0 on November 10, 2026. It reproduced the initial error, operand order, NULL/nonmatch behavior, and independent connection registration. We did not reproduce the reader's ASGI application, inspect their data, or test every SQLite shell build. Current API behavior was checked against the linked Python and SQLite documentation. Search demand volume and advertising returns remain unmeasured.