How can I allow loading sqlite extensions with a sqlalchemy engine?

Question

Overview

I want to include sqlite extensions in sqlalchemy.

Issues

When I try to load the extension I get a not authorized error.

MVE

Setup engine

import sqlalchemy

engine = sqlalchemy.create_engine('sqlite:///:memory:')
extension = '/path/to/extension.dll'
with engine.begin() as conn:
    conn.execute(
        'SELECT load_extension(:path)',
        path=extension
    ).fetchall()

Error

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) not authorized
[SQL: SELECT load_extension(:path)]
[parameters: {'path': '/path/to/extension.dll'}]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Known Alternative

The sqlite3 library's connection has the enable_load_extension method. I cannot use sqlite3 because I am using sqlalchemy's ORM heavily. The sqlite3 method loads the extension without issue. Something similar to that method - but in sqlalchemy - would be ideal.

Are you able to load the extension in sqlite.exe? I don't think SQLA imposes any authority restrictions — snakecharmerb, Apr 11 '22 at 16:02
I have not tried that. One thing I didn't explicitly state is that I'm only using in-memory sqlite databases. Will sqlite load that extension each time in the in-memory dbs if I load it with the CLI first? — benrussell80, Apr 11 '22 at 16:09
I don't know - I was thinking more about the permissions angle. — snakecharmerb, Apr 11 '22 at 16:13
You could try creating a [handler for the "connect" event](https://docs.sqlalchemy.org/en/14/core/events.html#sqlalchemy.events.PoolEvents.connect) and see if you can enable loading extensions there. — Gord Thompson, Apr 11 '22 at 16:13
When trying to load it with the CLI (`.load ./extension.dll`) I am getting `Error: The specified module could not be found.` The file does exist at that path though. — benrussell80, Apr 11 '22 at 16:24
@GordThompson I tried loading the extension during the connect handler but received the same `Not Authorized` error. — benrussell80, Apr 11 '22 at 16:40
When I loaded the extension with the sqlite3 library it worked completely as expected, and I could use the contained functions. — benrussell80, Apr 11 '22 at 16:44

Thomas · Answer 1 · 2022-08-23T00:16:28.517

Based on Gord Thompson's hint and VPfB's answer for a similar Stackoverflow question, I was successfully able to enable the extension by attaching a handler for the "connect" event.

Ubuntu

from sqlalchemy import event, create_engine, text


engine = create_engine("sqlite:///:memory:")

@event.listens_for(engine, "connect")
def receive_connect(connection, _):

    connection.enable_load_extension(True)
    connection.execute("SELECT load_extension('mod_spatialite');")
    connection.enable_load_extension(False)


with engine.connect() as connection:

    result = connection.execute(text("SELECT spatialite_version() as version;"))
    row = result.fetchone()
    print(f"Spatialite Version: {row['version']}")

Windows

The path to the folder containing the DLL file (mod_spatialite.dll) must be added in front of the PATH variable. Windows binaries for SpatiaLite can be downloaded from gaia-gis.it.

import os

from sqlalchemy import event, create_engine, text


SPATIALITE_PATH = r"C:\apps\mod_spatialite-5.0.1-win-amd64"

os.environ["PATH"] = f"{SPATIALITE_PATH};{os.environ['PATH']}"


engine = create_engine("sqlite:///:memory:")

@event.listens_for(engine, "connect")
def receive_connect(connection, _):

    connection.enable_load_extension(True)
    connection.execute("SELECT load_extension('mod_spatialite')")
    connection.enable_load_extension(False)


with engine.connect() as connection:

    result = connection.execute(text("SELECT spatialite_version() as version;"))
    row = result.fetchone()
    print(f"Spatialite Version: {row['version']}")