Python – Problem Loading SpatiaLite Extension with SQLite3 Python Connector

pythonspatial-databasespatialitesqlite

I am trying to load the Spaltialite extension using SQLite3 python module. I am getting the following error when I run my script.

EOFError: [Errno 10054] An existing connection was forcibly closed by the remote host

import sqlite3

conn = sqlite3.connect(r'C:\folder1\TestDB.sqlite')
conn.enable_load_extension(True)

conn.execute("select load_extension('libspatialite-4.dll')")


for row in conn.execute('SELECT AsText(Geometry) FROM alaska'):
    print row

I am able to connect to this db and run a non-spatial query without any issues.

Best Answer

In native (out of the box) Python installations extensions are disabled within sqlite3. Unfortunately this means that you will need to build the base pysqlite2 library manually and use that instead.

On Windows this will mean that you need a C compiler (Visual Studio comes with one - I've used Visual Studio 2010 for this before). However (with massive props) Christoph Gholke's Unofficial Windows Binaries for Python Extension Packages provides a pysqlite2 download that does have the extensions enabled by default. I haven't tested these binaries with 3rd party extensions though, so for a guarantee I'd try building it myself.

  1. First download the pysqlite2 package.
  2. In setup.cfg comment out the define=SQLITE_OMIT_LOAD_EXTENSION so the config looks like:

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

  1. Build from scratch with python setup.py build_static install. This will download and build the sqlite3 libraries for you.

We can then test this in Python with the following:

import pysqlite2.dbapi2 as sqlite3 #alias over the top of the standard sqlite3 module

conn = sqlite3.connection(":memory:")

### NOTE: Don't use the following line with Christolph Gholke's build - extensions are enabled by default
conn.enable_load_extension(True)

#Now test extensions are working by trying the inbuilt rtree extension
conn.execute("CREATE VIRTUAL TABLE testrtree USING rtree(id,minX,maxX,minY,maxY);")

#then you can try loading spatialite
conn.execute("select load_extension('libspatialite-4.dll')")

Assuming that's all worked with no errors thrown then you should be good to go using spatialite in Python.

Related Question