From 48a0df55c1cfb8746eec8073c0feb05be1652665 Mon Sep 17 00:00:00 2001
From: Gord Thompson <gord@gordthompson.com>
Date: Wed, 29 Jun 2022 07:38:38 -0600
Subject: [PATCH] Document user-defined functions for sqlite

Change-Id: I64e4d4dce8c5f5aced3190f9e3682c630462a61e
---
 lib/sqlalchemy/dialects/sqlite/aiosqlite.py |  8 +++++
 lib/sqlalchemy/dialects/sqlite/pysqlite.py  | 33 +++++++++++++++++++++
 2 files changed, 41 insertions(+)

diff --git a/lib/sqlalchemy/dialects/sqlite/aiosqlite.py b/lib/sqlalchemy/dialects/sqlite/aiosqlite.py
index 93c1a499d5..97ea1b2b36 100644
--- a/lib/sqlalchemy/dialects/sqlite/aiosqlite.py
+++ b/lib/sqlalchemy/dialects/sqlite/aiosqlite.py
@@ -36,6 +36,14 @@ This dialect should normally be used only with the
 The URL passes through all arguments to the ``pysqlite`` driver, so all
 connection arguments are the same as they are for that of :ref:`pysqlite`.
 
+.. _aiosqlite_udfs:
+
+User-Defined Functions
+----------------------
+
+aiosqlite extends pysqlite to support async, so we can create our own user-defined functions (UDFs)
+in Python and use them directly in SQLite queries as described here: :ref:`pysqlite_udfs`.
+
 
 """  # noqa
 
diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
index f155fed365..19949441fb 100644
--- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py
+++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
@@ -406,6 +406,39 @@ by adding the desired locking mode to our ``"BEGIN"``::
     `sqlite3 module breaks transactions and potentially corrupts data <https://bugs.python.org/issue10740>`_ -
     on the Python bug tracker
 
+.. _pysqlite_udfs:
+
+User-Defined Functions
+----------------------
+
+pysqlite supports a `create_function() <https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function>`_
+method that allows us to create our own user-defined functions (UDFs) in Python and use them directly in SQLite queries.
+These functions are registered with a specific DBAPI Connection.
+
+SQLAlchemy uses connection pooling with file-based SQLite databases, so we need to ensure that the UDF is attached to the
+connection when it is created. That is accomplished with an event listener::
+
+    from sqlalchemy import create_engine
+    from sqlalchemy import event
+    from sqlalchemy import text
+
+
+    def udf():
+        return "udf-ok"
+
+
+    engine = create_engine("sqlite:///./db_file")
+
+
+    @event.listens_for(engine, "connect")
+    def connect(conn, rec):
+        conn.create_function("udf", 0, udf)
+
+
+    for i in range(5):
+        with engine.connect() as conn:
+            print(conn.scalar(text("SELECT UDF()")))
+
 
 """  # noqa
 
-- 
2.47.3