CODEWITHSUNDEEP
pythonpython-3.xsqlitesqlalchemyspatialite
benrussell80
benrussell80

Reputation: 347

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

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.

Upvotes: 2

Views: 1635

Answers (1)

Thomas
Thomas

Reputation: 10145

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']}")

Upvotes: 1

Related Questions