How To Update or Upgrade SQLite3 version in Python

This guide will show you how to upgrade the SQLite3 version on you Ubuntu Linux distribution and make Python use the upgraded version.

Introduction

You've discovered how powerful SQLite and want to use it's latest and greatest features in Python. But there's a problem. When you run your advanced queries on SQLite directly they run fine. But when you try running them using they Python sqlite3 library, you run in to errors.

Chances are that the version of SQLite being used by Python is older than the version of SQLite that has become your recent best friend.

Current State

I have a Python 3.97 environment that ships with SQLite3 version 3.34.1.

As of 23 June 2022, the latest version of SQLite3 available on their official download page is 3.38.5.

Build and Install the Latest Version of SQLite from Source

Download the latest version of the source from the official SQLite download page. Make sure you download the autoconf version.

cd ~
wget https://www.sqlite.org/2022/sqlite-autoconf-3380500.tar.gz
tar xvfz sqlite-autoconf-3380500.tar.gz
cd sqlite-autoconf-3380500

Then run the following commands which I have blatantly copied from Charles Leifer's excellent post on the same subject.

$ export CFLAGS="-DSQLITE_ENABLE_FTS3 \
    -DSQLITE_ENABLE_FTS3_PARENTHESIS \
    -DSQLITE_ENABLE_FTS4 \
    -DSQLITE_ENABLE_FTS5 \
    -DSQLITE_ENABLE_JSON1 \
    -DSQLITE_ENABLE_LOAD_EXTENSION \
    -DSQLITE_ENABLE_RTREE \
    -DSQLITE_ENABLE_STAT4 \
    -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \
    -DSQLITE_SOUNDEX \
    -DSQLITE_TEMP_STORE=3 \
    -DSQLITE_USE_URI \
    -O2 \
    -fPIC"
$ export PREFIX="/usr/local"
$ LIBS="-lm" ./configure --disable-tcl --enable-shared --enable-tempstore=always --prefix="$PREFIX"
$ make
$ sudo make install  # Install system-wide.

Side note: Support for arrow keys in WSL

After running the ./configure command, you'll see a lot of output. If you want to work with the sqlite3 command line tool, pay attention to the bits that refer the to the readline library.

If your WSL installation does not have the readline library installed, you'll encounter some frustration in the sqlite3 command line tool when you attempt to scroll up and down using arrow keys. There is a quick fix for this using rlwrap tool. But you may want to bake the support in to the compiled binary.

Check that the new version is installed on the system

sqlite3 --version

If all has gone according to plan, you should see something like this:

If not, try to close out your current terminal session and start a new one.

Making Python use the latest installed version of SQLite3

This is the tricky part. Despite having the latest SQLite version installed in the system, when you use sqlite3 in Python, you may still see the earlier version.

Make sure you have this problem before continuing though.

python3 -c "import sqlite3; print(sqlite3.sqlite_version)"

You can check the version of SQLite that is used by Python like this:

If the version matches the one you just installed, then everything is good to go. You can stop reading now and get back to solving your next challenge.

But if you're reading this, I can only assume the powers that be have not been gracious towards you. But do not fear. I've got the solution for you.

First find out where the sqlite library that is used by python is installed.

Run python3 -c "import sqlite3; print(sqlite3.__file__)" to determine the location where python is installed in your current environment. The highlight part is the path of interest.

Now run the following command to locate the sqlite library file installed in the path.

ldd `find /usr/lib/python3.8/ -name '_sqlite3*'` | grep sqlite

You'll see something like this:

Make a backup copy of the file, just in case we end up messing up everything. Then replace the file (in my case libsqlite3.so.0, also note my path is /lib/x86_64-linux-gnu), with the version of the file found in the /.libs directory in the build directory you extracted the source to earlier.

#Go to the .libs directory
cd ~/sqlite-autoconf-3380500/.libs

#Make a backup just in case
mkdir backup
cp /lib/x86_64-linux-gnu/libsqlite3.so.0 ./backup/libsqlite3.so.0

#Overwrite the old version with the new version
#The path you have to copy may be different than mine. Check previous step.
sudo cp libsqlite3.so.0 /lib/x86_64-linux-gnu/libsqlite3.so.0

That's it.

Restart a new terminal session if necessary and check that Python uses the latest version.