User-defined functions and hooks

cysqlite lets you extend SQLite using ordinary Python callables: scalar functions, aggregates, window functions, table-valued functions and collations. It also exposes a set of connection hooks for observing or vetoing activity. Everything below is registered on a Connection. The SQL functions and collations are automatically re-registered if the connection is closed and re-opened.

This page is a quick tour; see the Connection methods in the API reference for the full signatures and options.

Scalar functions

A scalar function maps its arguments to a single value. Register any callable that returns a value SQLite understands with Connection.create_function():

def title_case(s):
    return s.title() if s else ''

db.create_function(title_case)
db.execute('select title_case(?)', ('heLLo wOrLd',)).fetchone()
# ('Hello World',)

Aggregate functions

An aggregate is a class with a step() method (called once per row) and a finalize() method (returns the result). Register it with Connection.create_aggregate():

class Product:
    '''Like SUM(), but multiplies.'''
    def __init__(self):
        self.product = 1

    def step(self, value):
        self.product *= value

    def finalize(self):
        return self.product

db.create_aggregate(Product)
db.execute('select product(value) from data').fetchone()

Window functions

A window function is an aggregate that additionally implements inverse() (remove a row from the window) and value() (the current running value). See Connection.create_window_function():

class MySum:
    def __init__(self):
        self._value = 0

    def step(self, value):
        self._value += value

    def inverse(self, value):
        self._value -= value

    def value(self):
        return self._value

    def finalize(self):
        return self._value

db.create_window_function(MySum, 'mysum', 1)

Table-valued functions

Expose a generator (or any function returning an iterable of rows) as a table. Each signature parameter becomes a function argument; parameters with a default are optional. The Connection.table_function() decorator registers the function and returns it unchanged:

@db.table_function(columns=['value'])
def series(start, stop, step=1):
    i = start
    while i < stop:
        yield (i,)
        i += step

db.execute('select value from series(0, 10, 2)')  # 0, 2, 4, 6, 8

The imperative Connection.create_table_function() is equivalent. For writable tables, with_rowid, or full control over the per-query lifecycle, subclass TableFunction directly.

Collations

A collation is a function of two strings returning a negative number, zero, or a positive number, used by ORDER BY ... COLLATE and similar. Register it with Connection.create_collation():

def collate_reverse(s1, s2):
    return (s1 < s2) - (s1 > s2)

db.create_collation(collate_reverse, 'reverse')
db.execute('select value from data order by value collate reverse')

Connection hooks

Hooks observe (and in some cases veto) what happens on a connection. Unlike the functions above, a hook is cleared by passing None.

Commit and rollback hooks fire around transaction boundaries. Returning a truthy value from the commit hook (or raising) turns the COMMIT into a ROLLBACK:

readonly = True

db.commit_hook(lambda: readonly)        # truthy return aborts the commit
db.rollback_hook(lambda: log.info('rolled back'))

db.commit_hook(None)                    # clear the hook

The update hook fires after every INSERT, UPDATE or DELETE:

def on_update(query_type, dbname, table, rowid):
    log.info('%s %s.%s rowid=%s', query_type, dbname, table, rowid)

db.update_hook(on_update)

An authorizer is consulted as statements are compiled and can allow, deny, or silently ignore individual operations:

def authorizer(op, p1, p2, p3, p4):
    if op == SQLITE_UPDATE and p1 == 'log':
        return SQLITE_DENY
    return SQLITE_OK

db.authorizer(authorizer)

See Connection.commit_hook(), Connection.rollback_hook(), Connection.update_hook() and Connection.authorizer(). For SQL tracing, see Connection.trace().