Sqlite load_extension fail for spatialite in Python

Question

I am trying to use the Spatialite beta version 3.0 because I am using Windows 7 on a 64-bit machine.

I consistently get the dreaded sqlite3.OperationalError: The specified module could not be found. error when I try to load libspatialite-4.dll.

I have tried the following:

• put libspatialite-4.dll and all the other dlls in the same folder
• use the full path to the dlls
• add the dll location to the 'PATH' environment variable
• append the dll location to the sys.path attribute as part of the Python code
• copy all the dlls in the c:\windows\system32 folder (complete with restart of the machine)
• copy all the dlls in the c:\windows\sysWoW64 folder (complete with restart of the machine this is supposed to be for 32 bit dlls but I tried it anyway)

my code is as follows:

import sqlite3
conn = sqlite3.connect(":memory:")
conn.enable_load_extension(True)
conn.execute('SELECT load_extension("libspatialite-4.dll")')

NOTE - I have tried the full path too with no luck. I remember having the same problem with Windows XP 32-bit. I got it working but can't remember what I did :(

UPDATE

I have tested the setup on 32-bit Windows 7 and putting all the dlls in the System32 folder works. So, this suggests that there is some problem with the 64-bit setup. Could it be that I need another version of MSVC (I don't think the Spatialite website says which is necessary so I might just have to guess - I have MSVC2010 installed)?

Answer 1

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.