Using SpatiaLite with Python 3 on x64 Windows

SQLite 3 itself is included in official Python 3 distribution, so it would be nice to add spatial functionality. Alas, attempts to use SpatiaLite with Python 3 on x64 Windows 7 turned out to be a very messy task. Python had an extension for SpatiaLite, PySpatiaLite, but it has not been updated since 2013 and project seems to be inactive, and throws errors during installation. Probably, these errors could be solved, but I wanted to test SpatiaLite, not debug installation package.

There are plenty of stories how SpatiaLite extension does not load, throws errors at installation etc. After some research and testing, the solution turned out to be relatively simple – just use SQLite’s built-in functionality to load extensions.

Step 1. Dependencies

It is assumed Python 3 is already installed. Download mod_spatialite from SpatiaLite binaries page; x86 or x64, according to your platform. Be sure to download the archive that starts with mod_spatialite, this is the library intended to be loaded dynamically.

Extract it somewhere on the disk from where it will be referenced in your script. For example, C:/projects/proj1/bin.

Step 2. Standard stuff

In python script, import sqlite3 that comes with Python distribution, create an empty or open an existing SQLite database. Enable extension loading for the connection.

import sqlite3
connection = sqlite3.connect('C:/temp/test.db')
connection.enable_load_extension(True)

Step 3. Set up dll seach paths

In the script, add the path where mod_spatialite is located, to the dll search path. This is very important. mod_spatialite dll itself uses some other dlls that come with it in the distribution package.

os.environ['PATH'] = 'C:/projects/proj1/bin' + ';' + os.environ['PATH']

If some dll cannot be fould, you will get error, something like this:

Traceback (most recent call last):
File <pyshell#10>, line 1, in <module>
connection.load_extension('C:/projects/proj1/bin/mod_spatialite')
sqlite3.OperationalError: The specified module could not be found.

Step 4. Load SpatiaLite dll and initialise spatial database

Load extension using sqlite3 function. DO NOT specify file extension. If the database is freshly created, initialize spatial database structures.

connection.load_extension('C:/projects/proj1/bin/mod_spatialite')
cur = connection.cursor()

# parameter 1 means execute everything in one transaction. It is faster.
cur.execute('SELECT InitSpatialMetaData(1)')
connection.commit()
connection.close()

Sample code

Sample code with x64 mod_spatialite binaries included can be found at GitHub.

TODO

Test if there are no memeory leaks, sudden hangs etc.

3 thoughts on “Using SpatiaLite with Python 3 on x64 Windows”

  1. But still this is not enough! What if the version of sqlite3 has not had extensions compiled in? Then it will not work whatever you do. You have to replace the sqlite3.dll with a properly compiled one which is hard to find and impossible to check. Maybe a PRAGMA setting? Anyway I made this exercise work only by downloading the sqlite3.dll and replacing the default.

  2. I’ve run into basically the same issue, but with a Django application. It’s attempting to use mod_spatialite/sqlite for it’s testing database, but it breaks with the same sort of error:

    django.core.exceptions.ImproperlyConfigured: Unable to load the SpatiaLite library extension “mod_spatialite” because: A dynamic link library (DLL) initialization routine failed.

    I’ve got the environment variable set (SPATIALITE_LIBRARY_PATH=’mod_spatialite’), and have tried installing mingw-x64. Attempting to run the sample code in the GitHub repo results in the same error. Any guesses? (It’s a Windows 10 machine, Python 3.6.8).

Leave a Reply

Your email address will not be published. Required fields are marked *