Force Python to forego native sqlite3 and use the (installed) latest sqlite3 version

Question

The error message I am trying to get rid of is:

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

I have 'easy_install'-ed the latest sqlite3 version and python somehow know it is there since sqlite3.version_info produces 3.6.13. In this version the Connection should have the 'enable_load_extension' attribute.

What I think is going on is that python still uses the native sqlite3 module which I think is 2.4.1 since sqlite3.version (i.s.o. sqlite3.version_info) produces 2.4.1.

The question is how do I force python to use the new sqlite3 module for all sqlite3 calls?

Answer 1

sqlite3 support in Python can be a bit confusing. The sqlite database adapter started out as a separate project, pysqlite2, but for Python 2.5 a version of it was incorporated into the Python standard library under the name sqlite3. The original adapter continues to be developed as that separate project while periodically the version in Python itself is updated to match it. If you are trying to use a newer version of the adapter, it is usually installed as pysqlite2 so as not to conflict with the version included in the standard library. And, depending how it was built, it may link to a different version of the underlying sqlite3 database library. So make sure you are importing it properly:

>>> import sqlite3 >>> sqlite3.version_info (2, 4, 1) >>> sqlite3.sqlite_version_info (3, 6, 11)  >>> from pysqlite2 import dbapi2 as sqlite3 >>> sqlite3.version_info (2, 5, 5) >>> sqlite3.sqlite_version_info (3, 6, 18) 

version_info is the version of the sqlite3 (pysqlite2 or built-in sqlite3) database adapter. sqlite_version_info is the version of the underlying sqlite3 database library.

Using from ... import ... as sqlite3 is suggested so that the rest of your code does not need to change if you move from one version to the other.

Note, enable_load_extension first appeared in pysqlite2 2.5.0.

EDIT: enable_load_extension is disabled by default when you build the adapter. To enable it, you can build pysqlite2 manually. The following recipe assumes a unix-y system and the lastest version of pysqlite2, which as of this writing is 2.5.5.

First, if you installed the adapter originally via easy_install, uninstall it by first running:

$ sudo /path/to/easy_install -m pysqlite # or whatever package name you first used 

There will be some output from that including lines like:

Removing pysqlite 2.5.5 from easy-install.pth file  Using /path/to/site-packages/pysqlite-2.5.5-py2.x-something.egg 

Remove the egg using the file name listed (the name will vary depending on your platform and version and it may refer to a file or a directory):

$ sudo rm -r /path/to/site-packages/pysqlite-2.5.5-py2.x-something.egg 

Now download and extract the pysqlite-2.5.5 source tarball:

$ mkdir /tmp/build $ cd /tmp/build $ curl http://oss.itsystementwicklung.de/download/pysqlite/2.5/2.5.5/pysqlite-2.5.5.tar.gz | tar xz $ cd pysqlite-2.5.5 

Then edit the setup.cfg file to comment out the SQLITE_OMIT_LOAD_EXTENSION directive:

$ ed setup.cfg <<EOF > /SQLITE_OMIT_LOAD_EXTENSION/s/define=/#define=/ > w > q > EOF 

Since the version of sqlite3 is so old (3.4.0), you should also build with the latest sqlite3 library. This is made easy in the pysqlite2 setup.py script:

$ /path/to/python2.x setup.py build_static 

This will automatically download the latest sqlite3 amalgamation source and build the adapter along with an up-to-date statically-linked version of sqlite3. This step may take a long while to finish.

UPDATE (2015/07/21): According to the latest pysqlite 2.6.3 commit you have to download sqlite source code by yourself and put them in pysqlite root folder.

Now, install the adapter:

$ sudo /path/to/python2.x setup.py install 

and run the tests:

$ cd     # somewhere out of the build directory $ /path/to/python2.x >>> from pysqlite2 import test >>> test.test() 

and, if they pass, you should be all set.

As a bonus, if the reason you want load extension support is to use sqlite3's full text search extension, FTS3, you should find that it was included as part of the static library and no further work is necessary:

>>> from pysqlite2 import dbapi2 as sqlite3 >>> con = sqlite3.connect(":memory:") >>> con.execute("create virtual table recipe using fts3(name, ingredients)") <pysqlite2.dbapi2.Cursor object at 0xca5e0>