Spatialite with Python 2 and 3

Question

I am attempting to use spatialite with both Python 2 and 3 on Windows 7.

Rather than try to patch pyspatialite for Python 3, I decided to use the load_extension approach with sqlite3 Python built-in package, similar to how is done here: Sqlite load_extension fail for spatialite in Python and here: Use spatialite extension for SQLite on Windows.

But, on the official (C)Python 2.7 installer, the load_extension was disabled for an issue related to MacOS. This is not with the counterpart for Python 3.4. Furthermore, both installers are built without SQLITE_ENABLE_RTREE=1 (that I'd also like to have).

At first, for Python 2.7, a workaround was to build pysqlite tweaking the setup files to have both R*Tree and extensions. This way does not work for Python 3, since it does not seem to be supported by the current setup.py. From my understand, this is because the package moved to the core Python repository: https://github.com/ghaering/pysqlite/issues/72#issuecomment-94319589

My current solution was to re-build both Python 2.7 and 3.4 with required settings for sqlite3 package. It worked, and I was able to load spatialite as an extension and to create R*Tree.

Does it exist an alternative simpler solution? Did somebody find an alternative solution by working on the setup.py of pyspatialite or pysqlite?

Answer 1

There is an easier solution that I just got working. I'm running Windows 10 with Python 3.5 using the default sqlite3 package, and Spatialite 4.3.

This is how I did it:

Let start with a simple program that loads the spatialite extension (which we haven't installed yet):

import sqlite3

with sqlite3.connect(":memory:") as conn:
    conn.enable_load_extension(True)
    conn.load_extension("mod_spatialite")

If you run this program you get:

C:\> python test.py
Traceback (most recent call last):
  File "test2.py", line 5, in <module>
    conn.load_extension("mod_spatialite")
sqlite3.OperationalError: The specified module could not be found.

That means it can't find the mod_spatialite extension. Let's download it from the Spatialite website. At the bottom there's links to "MS Windows binaries". I picked the x86 version, current stable version, and downloaded the file called mod_spatialite-4.3.0a-win-x86.7z. Unzipping that file to the same directory your test.py file is in, and running the program now produces no errors (ie. it works!):

C:\> python test.py
C:\>