Sqlite on Python 3 with Spatialite and full support for spatial indices (i.e. rtree)

Question

This is the scenario:

I am using Python 3 (3.6 through 3.8 on Windows 10, using Pipenv and vanilla Python) to create an SQLite file with Spatial support and several triggers based on Spatial Indices.

Creating the database and adding records works just fine after loading Spatialite

  conn.enable_load_extension(True)
  conn.load_extension("mod_spatialite")

However, adding spatial links with code such as below

  SELECT CreateSpatialIndex( 'nodes' , 'geometry' );"""

returns the following error

updateTableTriggers: "no such module: rtree"

I tried compiling the rtree extension following some recommendation from Compiling SQLite RTREE in MSVC? and using VS 2016 (16.4.2).

But I get all sorts of errors when trying to load that in SQL (Might not be compiling it properly, but I tried multiple things and nothing worked). My best attempt was a successful compilation using pretty much the instructions I referred to above, but when I attempted

   p.conn.load_extension("libSqliteRtree.dll")

I got

sqlite3.OperationalError: The specified procedure could not be found.

I am really at loss here, as there seems to be very little discussion on this topic everywhere I looked. A few questions that come to mind are:

Are the specific compilation instructions/tricks/compiler versions that I should be using?

Is it even possible to compile and load rtree in Python 3 using the standard sqlite3 library?

Is this particular to Windows?

Are there alternative SQLite Python packages that could do the job (I didn't find any on PyPI)?

It is critical, however, that the solution works across different platforms.

Can you specify the environment? IS it Conda? Do you have a conda env, or a virtualenv or such? This often helps... — MartinT, Jan 08 '20 at 22:51
Pipenv with vanilla Python. I edited the question to add that. — PCamargo, Jan 10 '20 at 02:15

gdesidera · Answer 1 · 2021-09-13T13:35:39.777

I was just having the exact same problem, with Python 3.8 x64. I believe the problem was that the sqlite3.dll file inside my Python's installation DLLs folder had been compiled without RTREE enabled (https://sqlite.org/rtree.html).

To resolve this I visited the SQLite website, downloaded the .zip with the latest sqlite3.dll for Windows x64 (hoping RTREE would be enabled on that version, because I tried compiling it on my own and it didn't work), and swapped the old DLL in the DLLs folder with the newly downloaded DLL from the website. The RTREE error was gone! Detailed steps below:

Access https://sqlite.org/download.html and choose the "Precompiled Binaries for Windows" of your system. Mine was x64 because I was running Python x64. Download the .zip and unzip it.
Find your Python's installation folder (the folder which contains the python.exe you're running), and open the DLLs folder. You'll see there is a file there called sqlite3.dll. That's the one that comes with the Python installation.
Copy the sqlite3.dll from the unzipped folder in step 1 and paste into the DLLs folder, click Yes to substitute the file in the DLLs folder for the new one. That should solve the problem.

This is correct. I actually figured this out a while later but forgot to post the answer. I also wrote a blog post about it. https://xl-optim.com/spatialite-and-python-in-2020/ — PCamargo, Feb 27 '21 at 10:44

Sqlite on Python 3 with Spatialite and full support for spatial indices (i.e. rtree)

1 Answers1