5

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?

VPfB
  • 14,927
  • 6
  • 41
  • 75
  • Have a look at how to [use raw DBAPI connections](http://docs.sqlalchemy.org/en/latest/core/connections.html#working-with-raw-dbapi-connections) in SQLA. Alternatively you could hook to the [connect event](http://docs.sqlalchemy.org/en/latest/core/events.html#sqlalchemy.events.PoolEvents.connect), which has access to the raw DBAPI connection. – Ilja Everilä Feb 18 '18 at 18:45
  • @IljaEverilä Thank you. I posted my result. Would you mind to take a look at it? – VPfB Feb 19 '18 at 19:09

1 Answers1

6

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.

VPfB
  • 14,927
  • 6
  • 41
  • 75
  • My only gribe is: don't use string formatting for passing values to queries, even in such a simple case so that you don't make a habbit of it. Instead `dbapi_conn.execute('SELECT icu_load_collation(?, 'ICU_EXT_1'), (db_collate,))`. – Ilja Everilä Feb 19 '18 at 19:12
  • I am not sure how you managed to find this, as information is very scarce, but I really appreciate what you have provided to correctly load extensions in a flask app. – Rboreal_Frippery Jul 08 '19 at 17:49
  • 1
    See also for alternative method to hook into 'connect' event: https://docs.sqlalchemy.org/en/14/core/engines.html#modifying-the-dbapi-connection-after-connect-or-running-commands-after-connect – James Aug 31 '21 at 09:34