[GIS] Using Sqlite, Python 2.7 and Spatialite

pythonpython-2.7spatialitesqlsqlite

I am trying to spatially enable my sqlite database:

import sqlite3
db = r'c:\temp\mydata.sqlite'
conn = sqlite3.connect(db)
conn.enable_load_extension(True)
cur = conn.cursor()
cur.execute(r"SELECT load_extension("<path>\libspatialite-4.dll");")
conn.commit()
conn.close()
del conn

Python just stops working when I run this code. It doesn't crash in windows 7 x64, it just closes. It's very strange. No error is raised.

When I run the commands directly from python.exe in the command propmpt, I see python crashing:
spatialite crashing python

Am I doing anything wrong?

Best Answer

The version of sqlite3.dll included with Python doesn't seem to want to play nice with Spatialite. The only thing I could get to work (short of compiling everything from source) was:

  1. Download SQLite (or cyqlite - a recompile of SQLite for Windows with some handy features enabled, such as R-Tree so you can do spaital indexes) i.e. sqlite-dll-win32-x86-[version].zip
  2. Download mod_spatialite (Windows binaries are in the pink box at the bottom of the page) i.e. mod_spatialite-[version]-win-x86.7z
  3. Unzip first SQLite/cyqlite then mod_spatialite into the same folder (overwrite if there are any conflicts)
  4. Add this folder to your system Path
  5. Rename the sqlite3.dll that is in your Python DLLs directory, to something like sqlite3_old.dll, so that Python will use the new one on your path

See this blog post for more info.

Related Question