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

Solution 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>

Solution 2:

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'