Spatialite and SQLAlchemy

To be able to use Spatialite under SQLAlchemy (or GEOAlchemy), it must be loaded with every new connection to the DB. Since SQLAlchemy can and will tear down and create new connections to the DB at some time, we must register a hook that is invoked at connection creation time. Just issuing the load command once would result in an pysqlite2.dbapi2.ProgrammingError: Cannot operate on a closed database.

Also, to initialise the geospatial functions, SELECT InitSpatialMetaData(); has to be issued at least the first time the DB is opened.

from sqlalchemy import create_engine, event
from pysqlite2 import dbapi2 as sqlite

LIBSPATIALITE_PATH = './libspatialite.so.5.1.0'
DB_PATH = 'sqlite:///db.sqlite3'

engine = create_engine(DB_PATH, module=sqlite)

@event.listens_for(engine, "connect")
def connect(dbapi_connection, connection_rec):
    dbapi_connection.enable_load_extension(True)
    dbapi_connection.execute("SELECT load_extension('{0}');".format(LIBSPATIALITE_PATH)

engine.execute("SELECT InitSpatialMetaData();")

Since the batteries-included sqlite3 module does not support extension loading, pysqlite2 has to be compiled from scratch: Download it from the pysqlite2 project page, extract the archive, and comment out the line in setup.cfg that prevents extension loading. setup.cfg should look like this:

[build_ext]
#define=
#include_dirs=/usr/local/include
#library_dirs=/usr/local/lib
libraries=sqlite3
#define=SQLITE_OMIT_LOAD_EXTENSION

Finally, install the extension via python setup.py install.