How can I upgrade the sqlite3 package in Python 2.6?

Question

I was using Python 2.6.5 to build my application, which came with sqlite3 3.5.9. Apparently though, as I found out in another question of mine, foreign key support wasn't introduced in sqlite3 until version 3.6.19. However, Python 2.7 comes with sqlite3 3.6.21, so this work -- I decided I wanted to use foreign keys in my application, so I tried upgrading to python 2.7.

I'm using twisted, and I couldn't for the life of me get it to build. Twisted relies on zope.interface and I can't find zope.interface for python 2.7 -- I thought it might just "work" anyway, but I'd have to just copy all the files over myself, and get everything working myself, rather than just using the self-installing packages.

So I thought it might be wiser to just re-build python 2.6 and link it against a new version of sqlite3. But I don't know how--

How would I do this?

I have Visual Studio 2008 installed as a compiler, I read that that is the only one that is really supported for Windows, and I am running a 64 bit operating system

• msw: could you throw just one more bleeding edge dependency in there? ;)
• Carson Myers: @msw I understand upgrading to the very latest python and expecting existing libraries to support it is a tad ridiculous. But I don't think that reaching for built-in support for a rdbms that supports basic features is so far fetched, especially when you're relatively new to sql and want the security that your tables will back you up if garbage tries to make its way into your database.
• msw: I didn't think it far-fetched and wasn't serious, whence the smiley ;) I did think that you were buying yourself in to cross-dependency limbo which in fact you turned out to be. Peace.
• Carson Myers: true, just trying to explain my motivation :) and yes... dependencies are horrible

Answer 1

download the latest version of sqlite3.dll from sqlite website and replace the the sqlite3.dll in the python dir.

• Barton: +1000 (if I could). This just upgraded my python 2.6 install to sqlite 3.7.17 (x86) sqlite.org/download.html
• Radical Edward: Works with python 2.7 as well

Answer 2

sqlite3 is not a built-in module; it's an extension module (the binary is C:\Python26\DLLs_sqlite3.pyd (on my machine)). A pyd is a DLL with a different filename extension and only 1 entry point. There's also a sqlite3.dll, which contains the SQLite code. python.exe is not linked against either of those, and thus rebuilding python.exe has no point.

The next idea is to go to the pysqlite2 download site, and get the latest Windows installer for Python 2.6. Unfortunately there's no docs about which version of SQLite it contains; one needs to install it and then muck about:

>>> import sqlite3 as standard
>>> from pysqlite2 import dbapi2 as latest
>>> for m in (standard, latest):
... print m.sqlite_version
...
3.5.9
3.6.2
>>>

So, it contains only SQLite version 3.6.2, which doesn't have the real foreign key support that you want.

I suggest that you check the mailing list to see if your question is answered there, and if not ask about the possibility of a Python 2.6 installer containing a later SQLite (e.g. the one included with Python 2.7).

• Carson Myers: I see, thank you, I'm not quite familiar with python internals.

Answer 3

I decided I'd just give this a shot when I realized that every library I've ever installed in python 2.6 resided in my site-packages folder. I just... copied site-packages to my 2.7 installation, and it works so far. This is by far the easiest route for me if this works -- I'll look further into it but at least I can continue to develop now.

I won't accept this answer, because it doesn't even answer my question, but it does solve my problem, as far as I can tell so far.

• msw: +1 this is an acceptable answer given the maze of twisty little dependencies, all alike, that you have found yourself in. I have back-ported select, minor lib/python3.x/foo.py into python2.6 when a particular project needed it (and tested the hell out of it, and wouldn't think of inflicting it on anything but my local install) but it does work and in comparison to the alternatives, is an engineering trade-off that was satisficing.
• John Machin: This presumes that none of the libraries that you've ever installed in site-packages contain any C or Cython extensions -- on Windows at least, those .PYD files are tied to a particular version of Python.
• Carson Myers: @John uh oh -- I did notice that all of my .pyc files failed with "bad magic number" which I assumed to mean wrong version of python. I just deleted them. Anyways, the installation of twisted that I had was installed from the windows installer from the official site and it appears to be working (I hope) (the twisted package has C extensions). In any case, this is for my local version. Trying to get the interpreter and all its dependencies set up on a live server is... A problem for another day!