Let's use different versions of SQLite3 from Python3!

Introduction

In Python3, I had to use various versions of SQLite3 and SQLite3 that I improved by myself, so I summarized it as a memorandum.

environment

The OS is CentOS 7.7. You can access the outside by setting Proxy etc.

$ cat /etc/redhat-release
CentOS Linux release 7.7.1908 (Core)

First, install Python3. The version of Python3 is 3.6.8.

$ sudo yum install python3
$ python3 --version
Python 3.6.8

Check the version of SQLite3 with the SQLite3 command and Python3.

$ sqlite3 --version
3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668
$ python3
Python 3.6.8 (default, Aug  7 2019, 17:28:10) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.7.17'

I have SQLite 3.7.17 installed and Python3 is also using SQLite 3.7.17.

Using SQLite 3.31.1 with Python3

Get the latest version of SQLite3 (3.31.1). After getting it with wget and extracting it, change the directory name to sqlite-3.31.01.

$ wget https://www.sqlite.org/2020/sqlite-autoconf-3310100.tar.gz
$ tar zxvf sqlite-autoconf-3310100.tar.gz
$ mv sqlite-autoconf-3310100 sqlite-3.31.01

Build SQLite3.

$ cd sqlite-3.31.01
$ ./configure
$ make
$ ./sqlite3 --version
3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837bb4d6

Check the file. The SQLite3 library is located in the directory .libs.

$ ls -a
.            Makefile.fallback  config.guess   install-sh     sqlite3-shell.o    sqlite3.pc
..           Makefile.in        config.log     libsqlite3.la  sqlite3-sqlite3.o  sqlite3.pc.in
.deps        Makefile.msc       config.status  libtool        sqlite3.1          sqlite3.rc
.libs        README.txt         config.sub     ltmain.sh      sqlite3.c          sqlite3ext.h
INSTALL      Replace.cs         configure      missing        sqlite3.h          tea
Makefile     aclocal.m4         configure.ac   shell.c        sqlite3.lo
Makefile.am  compile            depcomp        sqlite3        sqlite3.o
$ ls -a .libs
.   libsqlite3.a   libsqlite3.lai  libsqlite3.so.0      sqlite3.o
..  libsqlite3.la  libsqlite3.so   libsqlite3.so.0.8.6

In the LD_LIBRARY_PATH environment variable, specify the absolute path of .libs.

$ export LD_LIBRARY_PATH=$HOME/sqlite-3.31.01/.libs
$ echo $LD_LIBRARY_PATH
/home/mototaka/sqlite-3.31.01/.libs

Let's check the version of SQLite3 with Python3.

$ python3
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.31.1'

From Python3, we have confirmed that the latest version of SQLite, 3.31.1, is available. Let's also check with the SQLite native sqlite_version () function.

$ python3
>>> import sqlite3
>>> con = sqlite3.connect(":memory:")
>>> cur = con.cursor()
>>> cur.execute("SELECT sqlite_version()")
>>> cur.fetchone()
('3.31.1',)

Similarly, from Python3, we have confirmed that the latest version of SQLite, 3.31.1, is available.

Using SQLite 3.20.1 with Python3

Get SQLite3 3.20.1 and build it.

$ wget https://www.sqlite.org/2017/sqlite-autoconf-3200100.tar.gz 
$ tar zxvf sqlite-autoconf-3200100.tar.gz
$ mv sqlite-autoconf-3200100 sqlite-3.20.01
$ cd sqlite-3.20.01/
$ ./configure
$ make
$ ./sqlite3 --version
3.20.1 2017-08-24 16:21:36 8d3a7ea6c5690d6b7c3767558f4f01b511c55463e3f9e64506801fe9b74dce34

Now specify the relative path of .libs in the environment variable LD_LIBRARY_PATH.

$ export LD_LIBRARY_PATH=./libs

Let's check the version of SQLite3 with Python3.

$ python3
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.20.1'

From Python3, I was able to confirm that the built SQLite 3.20.1 can be used. By specifying the relative path of .libs in LD_LIBRARY_PATH, it is possible to use various SQLite from Python3 by changing the directory in the following environment.

$ ls
sqlite3.00.08  sqlite3.06.23.1  sqlite3.12.02  sqlite3.18.02  sqlite3.23.00  sqlite3.29.00
sqlite3.01.06  sqlite3.07.17    sqlite3.13.00  sqlite3.19.03  sqlite3.24.00  sqlite3.30.01
sqlite3.02.07  sqlite3.08.11.1  sqlite3.14.02  sqlite3.2.8    sqlite3.25.03  sqlite3.31.01
sqlite3.03.17  sqlite3.09.03    sqlite3.15.02  sqlite3.20.01  sqlite3.26.00
sqlite3.04.02  sqlite3.10.02    sqlite3.16.02  sqlite3.21.00  sqlite3.27.02
sqlite3.05.09  sqlite3.11.01    sqlite3.17.02  sqlite3.22.00  sqlite3.28.00

Summary

In the case of SQLite, unlike other databases, it is difficult to change the database to connect with driver and connection information, and I thought that I had to improve the source code of Python3 at worst, but I use the environment variable LD_LIBRARY_PATH. This made it surprisingly easy to use different versions of SQLite3. Also, since I haven't used root privileges other than installing Pyhton3 this time, I think it's a good idea to easily try out the operation check with various versions.

If you find any mistakes or concerns about the description, we would appreciate it if you could give us feedback in the edit request or comment.