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. Everything below is registered on a Connection, and functions and collations are automatically re-registered if the connection is closed and re-opened.

See the Connection methods in the API reference for details.

Scalar functions

A scalar function maps its arguments to a single value.

Use 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).

Use 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()
# (24,)

Window functions

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

Use 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)
db.execute('select value, mysum(value) over '
           '(order by value rows 1 preceding) from data').fetchall()
# [(1, 1), (2, 3), (3, 5), (4, 7)]

Table-valued functions

Expose a generator (or any function returning an iterable of rows) as a table. Each signature parameter becomes a function argument and parameters with a default are optional.

Use Connection.table_function():

@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

You can also use Connection.create_table_function(). 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.

Use Connection.create_collation():

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

db.create_collation(collate_reverse, 'reverse')
db.execute('select name from users order by name collate reverse').fetchall()
# [('carol',), ('bob',), ('alice',)]

Connection hooks

Commit and rollback hooks fire at 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 for each row inserted, updated or deleted in a rowid table:

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':
        # Do not allow updates to the `log` table.
        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().