How to use the latest sqlite3 version in python

Question

I need to use sqlite version 3.8 or higher with python in Amazon Linux.

I updated my sqlite installation to the latest version:

$ sqlite3 -version
3.22.0 2018年01月22日 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d

I also updated my pysqlite version

pip install --upgrade pysqlite

However, my pysqlite still only seems to support sqlite version 3.7:

$ python
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.7.17'
>>>
>>> from pysqlite2 import dbapi2 as sqlite
>>> sqlite.version
'2.8.3'
>>> sqlite.sqlite_version
'3.7.17'

How can I update the sqlite python API to support a newer version of sqlite?

• tread: Or a better idea is don't use 2.2.x yet, and sticks with 2.1.x for now.
• Chris: Does this answer your question? Python sqlite3: run different sqlite3 version

Answer 1

Option 1: Use the binary version of pysqlite3 from here (which already comes with a newer version of sqlite3 lib precompiled and linked): https://github.com/coleifer/pysqlite3. Basically install with

pip install pysqlite3-binary

and in python code, use pysqlite3 instead of sqlite3 like:

import pysqlite3
 (...) conn = pysqlite3.connect(r"filename")

Alternative: Reinstall python, when installing python, a built in python's module sqlite3 (for working with sqlite) is compiling and uses (compiles) its own version of sqlite3 lib regardless of what you currently have in your system (this is the case at least on windows and mac systems, may be also the case for unix based systems).

Answer 2

Two ideas...

1. Use pyenv to install a separate interpreter, and provide the path to the newer sqlite when creating it. eg:

   PYTHON_CONFIGURE_OPTS="LD_RUN_PATH=/usr/local/opt/sqlite/lib LDFLAGS=-L/usr/local/opt/sqlite/lib CPPFLAGS=-I/usr/local/include" pyenv install 3.4.3
   

   This pyenv issue provides some more details/ideas.

2. APSW provides an up to date implementation of SQLite, but has different goals from the pysqlite library.

   APSW provides an SQLite 3 wrapper that provides the thinnest layer over the SQLite database library possible. Everything you can do from the SQLite C API, you can do from Python. Although APSW looks vaguely similar to the PEP 249 (DBAPI), it is not compliant with that API because instead it works the way SQLite 3 does. (pysqlite is DBAPI compliant - see the differences between apsw and pysqlite 2).

   APSW embeds the SQLite "amalgamation" distribution statically, so you can safely use it in an environment (eg. Python) which might also have another SQLite loaded.

Answer 3

So, I just spent a few days trying to figure this out in Cygwin. I need to use the latest SQLite with python39 on a new project. And if you want to keep using python's sqlite3 library follow these steps:

• Download the latest SQLite Pre-release Snapshot from the sqlite.org site

  • $ wget https://sqlite.org/snapshot/sqlite-snapshot-202302131932.tar.gz

• Untar the snapshot

  • $ tar xvf sqlite-snapshot-202302131932.tar.gz

• Cd to the untarred directory:

  • $ cd sqlite-snapshot-202302131932

• Run ./configure --prefix=/usr (This will overide whatever version of SQLite3 library)

  • $ ./configure --prefix=/usr

• Install that new configuration

  • $ make install

And that's it. This script will make you see the change:

• $ cat SQLiteVersion.py

  #!/usr/bin/python3
  import sqlite3
  def ConnectToDB(sdb):
   return sqlite3.connect(sdb)
  print(sqlite3.sqlite_version)
  print(sqlite3.__path__)
  DB = ":memory:"
  con = ConnectToDB(DB)
  cur = con.cursor()
  cur.execute("SELECT sqlite_version(),sqlite_source_id();")
  for row in cur:
   print(row[0] + '\r\n' + row[1])
  con.close()
  

The output should be something like this:

$ ./SQLiteVersion.py
3.41.0
['/usr/lib/python3.9/sqlite3']
3.41.0
2023年02月13日 12:46:22 d35de3ad3fac6b30d3f266cbe4b1e9923eb31a1eff4a869205bbc3ba122eeec5

Answer 4

There's unofficial Python wheel distribution of APSW which itself doesn't release on PyPI:

APSW is not available at the Python Package Index (pypi) and hence cannot be installed from it. (A random person has put an outdated poor quality upload under the APSW name. It has nothing useful to do with this project.) The reason for this is [...]

And I assume the reason predates Python wheels.

The wheel distribution package is called apsw-wheels (and GitHub repo). It worked just fine for me except for PyPy wheels.