Python and Spatialite (32-bit) on 64-bit Windows

For some reason it is insanely difficult to use Spatialite from Python on Windows. In theory the following should create and connect to an in-memory database, then load the Spatialite extension (after mod_spatialite and friends have been put somewhere and added to your system Path):

import sqlite3

with sqlite3.connect(':memory:') as conn:

	conn.enable_load_extension(True)

	conn.execute("SELECT load_extension('mod_spatialite')")

However, this fails stating: OperationalError: The specified module could not be found.

Numerous posts on StackExchange referring to this error^{(1, 2,
3 and 4)} suggest that this is something to do with 64-bit Windows DLL Hell. Interestingly if you point a 32-bit Python/SQLite at a 64-bit mod_spatialite (or vice versa) the error is: OperationalError: %1 is not a valid Win32 application., suggesting that in the initial case the module was in fact found, but some other error is occurring along the line and the error message is a lie.

After a lot of head scratching and some horrific workarounds I did manage to find a simple solution that works for the following setup (all 32-bit on 64-bit Windows 10):

• Python 2.7.10
• SQLite 3.9.2
• Spatialite 4.3.0a

Clean up

First: make sure to clean up and restore any bits of the above that you may have interfered with while trying to get things working (system Path, overwritten DLLs, etc.).

Download

Download clean libraries of mod_spatialite and SQLite (I would strongly suggest actually using cyqlite, which is SQLite compiled for Windows with some useful options enabled – notably R-Tree, meaning that you can use Spatial Indexes):

• either:
  • normal SQLite: sqlite-dll-win32-x86-[version].zip from https://www.sqlite.org/download.html – scroll down to Precompiled Binaries for Windows, or
  • cyqlite: sqlite-dll-win32-x86-[version].zip from http://sourceforge.net/projects/cyqlite/ – go to Browse All Files then select highest/relevant [version]
• mod_spatialite: mod_spatialite-[version]-win-x86.7z from http://www.gaia-gis.it/gaia-sins/ – scroll down to the bottom (pink box) for MS Windows binaries > current stable version > x86

Extract files and add to Path

Extract files from the archives you have downloaded and put them all into a single folder in a handy location – I chose C:\Program Files (x86)\Spatialite. Extract the SQLite archive first then mod_spatialite, and overwrite any files if prompted. Now add that folder to the System Path (which is just a list of folders that Windows checks out to try and find files).

WARNING: You could break your system if you are not careful here: don’t delete anything unnecessarily!

To bring up the Environment Variables dialogue in Windows 10:

• click on the Start Menu
• type (starts searching) “SystemPropertiesAdvanced”
• then click the Environment Variables button at the bottom

You have two options regarding the kind of variable you want to create:

• User variables: are applied to your login only (preferred option)
• System variables: will be applied for all users of the operating system

In either the User or System variables pane, select the ‘Path’ (or ‘PATH’) variable, then hit Edit. Click New and then paste the folder path, i.e.:

C:\Program Files (x86)\Spatialite

If there is no ‘Path’ variable under User variables you will need to create one by clicking New (set the name to Path and then you can paste or browse to the directory).

 

Override old sqlite3.dll

The final step is to convince Python to use our new sqlite3.dll, rather than the one it came with. To do this, open the Python DLLs folder (i.e. C:\Python27\DLLs), find sqlite3.dll and rename it to something like sqlite3_old.dll. As the new sqlite3.dll and mod_spatialite are all on the system Path, these will now be loaded by default.

Test it out

The same code we ran above can be used to check that everything works:

import sqlite3

with sqlite3.connect(':memory:') as conn:

	conn.enable_load_extension(True)

	conn.execute("SELECT load_extension('mod_spatialite')")

Bonus

To actually use a spatial database, spatial metadata must first be initialised, otherwise you will receive a AddGeometryColumn() error: unexpected metadata layout error when trying to add geometry columns. The command for doing this is SELECT InitSpatialMetaData(), run directly after the extension is loaded, which works fine for in-memory databases but is very slow for physical databases as it consists of a large number of transactions. The best way around this is to pass the function the value 1, i.e. in Python:
conn.execute("SELECT InitSpatialMetaData(1)")

If you are doing Python development, you may be interested in my Windows Dev Stack, which describes my development environment from high level technologies down to specific apps, and how they all work together.