Force Python to forego native sqlite3 and use the

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>

Answer 2

Instead of the standard Python sqlite3 module, you might be able to use the apsw module, a third-party SQLite module that more closely follows the SQLite API. It includes support for loading extensions, as well as basically anything that is allowed in the SQLite C/C++ API. It also tries as much as possible to keep up to date with any new changes in SQLite.

Answer 3

You need to look at your Python path and make sure that the sqlite you want is installed in an earlier directory than the built-in sqlite.

You can see the path with:

import sys
print(sys.path)

If you want to find out where a module is from, try:

print(sqlite3.__file__)

Answer 4

I have python 2.7 on a windows machine and the built-in sqlite3.sqlite_version was 3.6.x. By performing the following steps I was able to get it to use sqlite 3.7.9.

1. Download and unzip the pre-compiled binary DLL ("sqlite-dll-win32-x86-3070900.zip" on http://www.sqlite.org/download.html)
2. (probably should close all instances of python at this point just to be safe) Go to C:\Python27\DLLs and change filename of "sqlite3.dll" to "sqlite3.dll.old"
3. Copy the file "sqlite3.dll" to the folder C:\Python27\DLLs
4. Open python shell and import sqlite3
5. Verify sqlite3.sqlite_version shows up as '3.7.9'