How to create a user-defined SQLite function with optional arguments

Question

I'm trying to extend SQLite in Python. So I have several of these:

connection.create_function("MICROSECONDS_SUB", 2, microseconds_sub)

corresponding to python implementations:

def microseconds_sub(date, microseconds)
    # some kind of logic to subtract microseconds from a date

I'd like to be able to supply functions that can take optional arguments (for example, two required arguments and a third optional one), but I'm not sure how.

Answer 1

You can pass -1 for the number of arguments to prevent SQLite from doing any verification on the number of arguments.

If the function accepts whatever is passed to it, then the call will succeed, otherwise, it'll throw a sqlite3.OperationalError.

import sqlite3
def microseconds_sub(date, microseconds=-1):
    return f"Called with ({date}, {microseconds})"
db = sqlite3.connect(":memory:")
db.create_function("MICROSECONDS_SUB", -1, microseconds_sub)

print(db.execute("SELECT MICROSECONDS_SUB('1234-12-12');").fetchone()[0])
# Called with (1234-12-12, -1)
print(db.execute("SELECT MICROSECONDS_SUB('1234-12-12', 42);").fetchone()[0])
# Called with (1234-12-12, 42)

# This next line will raise sqlite3.OperationalError
print(db.execute("SELECT MICROSECONDS_SUB('1234-12-12', 42, 'Boom');").fetchone()[0])