Search code examples
pythonpython-3.xsqlitepipenvspatialite

Impossible to enable extensions with Python SQlite3

After checking some related posts with SQlite3 and how to enabling extensions, I found myself with no answer to my needs and, desesperately, I am asking you guys here.

I am using Python 3.7 in an specific pipenv environment (I think those are venv environments). The Python path within the env is: /home/my_user/.local/share/virtualenvs/my_current_env/lib/python3.7

The code:

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

The error:

AttributeError: 'sqlite3.Connection' object has no attribute 'enable_load_extension'

The solutions I have tried so far:

(i) Go to the main Py37 installation and change the setup.py and replace: SQLITE_OMIT_LOAD_EXTENSION for SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION I couldn't find a setup.py file in the pipenv environment Python path... Didn't work

(ii) Try generating a cursor of SQlite and from the cursor, executing the extension:

cursor=conn.cursor() 
res=cursor.execute("SELECT sqlite_compileoption_used('ENABLE_LOAD_EXTENSION');", "mod_spatialite.so") 
res.fetchall()

Didn't work either: The error:

ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 17 supplied.

I am new to SQlite and quite bad in Python, my target is to activate a database with Spatialite. Please your help will be much appreciated.

Best and lot of health, Joueswant

Solution

  • I think your question is answered across GIS Stack Exchange, I try to summarize how you should achieve it working:

    • First ensure you have proper package installed - for example in Ubuntu mod_spatialite corresponds to libsqlite3-mod-spatialite
    • (optional) check if working in sqlite3

      sqlite> .load 'mod_spatialite.so'

    So to cope with the problem, you would in Ubuntu's case do:

    sudo apt install libsqlite3-mod-spatialite

    Then run this set of commands

    import sqlite3
conn = sqlite3.connect(':memory:')
conn.enable_load_extension(True)
conn.load_extension("mod_spatialite.so")

    And this just works for me. Presonally I think you are just missing the package installed.