How to load a SQLite3 extension in SQLAlchemy?

Question

I built an SQLite extension (i.e. a .so library) that I want to use in my app using the SQLAlchemy. It is a Flask app, but I don't think Flask plays a role here.

The extension can be loaded from CLI and seems to work:

$ sqlite3

SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load ./libSqliteIcu.so

But I need to do it in my app. There is an example in the Python docs:

import sqlite3

con = sqlite3.connect(":memory:")

# enable extension loading
con.enable_load_extension(True)

# Load the fulltext search extension
con.execute("select load_extension('./fts3.so')")

But in my app I have to access the database using db:

db = flask_sqlalchemy.SQLAlchemy()

I can rewrite the last statement to:

db.session.execute('select load_extension("./libsqliteicu.so")')

But it fails with "not authorized" error.

How can I call enable_load_extension() or otherwise succesfully load an extension?

Answer 1

After some searching and testing, and based on various sources, this works for me. That's all I can say about the code quality. I'm posting it only because it might help someone. Don't hesitate to comment if you see a problem.

from sqlalchemy.event import listen

# initialization routine
# app: this Flask application
# db: the database, see the question 
db_collate = 'sk_SK.UTF-8'   # Slovak language for example
def load_extension(dbapi_conn, unused):
    dbapi_conn.enable_load_extension(True)
    dbapi_conn.load_extension('/path/to/libSqliteIcu.so')
    dbapi_conn.enable_load_extension(False)
    dbapi_conn.execute("SELECT icu_load_collation(?, 'ICU_EXT_1')", (db_collate,))
with app.app_context():
    listen(db.engine, 'connect', load_extension)

and usage:

from sqlalchemy.sql.expression import collate

...query.order_by(collate(Table.column, 'ICU_EXT_1'))

The name ICU_EXT_1 is fully arbitrary.