Custom SQL Functions and Aggregates

Call Python from inside SQL

SQLite lets you register Python functions that can be called from SQL. They're real first-class functions inside the engine — usable in WHERE clauses, SELECT lists, indexes, and triggers. The two main APIs:

conn.create_function(name, n_args, callable, deterministic=False) — scalar function (one row in, one row out).
conn.create_aggregate(name, n_args, AggregateClass) — aggregate (many rows in, one value out).

Use cases include: regex matching (when a regex extension isn't available), domain-specific scoring functions, JSON manipulation Python does better than the JSON1 extension, and custom comparison functions.

Warning: Python-side functions cross the C/Python boundary on every call. They're slow by SQL standards. Use them when correctness matters more than throughput — not in hot loops over millions of rows.

Code

Scalar function — Python regex from SQL·python

import sqlite3, re

def regexp(pattern, value):
    if value is None:
        return False
    return re.search(pattern, value) is not None

conn = sqlite3.connect('demo.db')
conn.create_function('regexp', 2, regexp, deterministic=True)

rows = conn.execute(
    "SELECT * FROM users WHERE email REGEXP '^a.*@gmail\\.com$'"
).fetchall()

Aggregate — running average of a window·python

class StdDev:
    def __init__(self):
        self.values = []

    def step(self, value):
        if value is not None:
            self.values.append(value)

    def finalize(self):
        if not self.values:
            return None
        m = sum(self.values) / len(self.values)
        v = sum((x - m) ** 2 for x in self.values) / len(self.values)
        return v ** 0.5

conn.create_aggregate('stddev', 1, StdDev)
row = conn.execute('SELECT stddev(score) FROM submissions').fetchone()
print(row[0])

Exercise

Register a Python regexp function and use it in a WHERE clause to find rows matching a complex pattern. Then build a Python aggregate that computes the median (SQLite has no built-in median). Compare the wall-clock time of the Python aggregate vs an equivalent SELECT ... ORDER BY ... LIMIT 1 OFFSET n/2 query on a large table.

Custom SQL Functions and Aggregates

Call Python from inside SQL

Code

External links

Exercise

Progress

Comments 0