Creating a minimal SpatiaLite database with Python#

When writing a test for datasette-leaflet-freedraw I realized I didn’t have a simple tiny recipe for creating an in-memory SpatiaLite database in Python. I came up with this:

1
import sqlite3
2

3
SPATIALITE = "/usr/local/lib/mod_spatialite.dylib"
4

5
db = sqlite3.connect(":memory:")
6
db.enable_load_extension(True)
7
db.execute("SELECT load_extension(?)", [SPATIALITE])
8
db.execute("SELECT InitSpatialMetadata(1)")
9
db.execute("CREATE TABLE places_spatialite (id integer primary key, name text)")
10
db.execute(
11
    "SELECT AddGeometryColumn('places_spatialite', 'geometry', 4326, 'POINT', 'XY');"
12
)
13
# Then to add a spatial index:
14
db.execute(
15
    "SELECT CreateSpatialIndex('places_spatialite', 'geometry');"
16
)

Datasette and sqlite-utils both have find_spatialite() utility functions. Here’s how to call the Datasette one as a one-liner:

1
% python -c 'import datasette.utils; print(datasette.utils.find_spatialite())'
2
/usr/local/lib/mod_spatialite.dylib

I also remembered I have this script: build_small_spatialite_db.py