Upgrading SQLite in Colab

Question

I have been doing some data analysis through a local jupyter notebook, using sqlite, pandas and plotly. I want to move that notebook on the colab website to allow others to use it but it is reporting SQLite version 3.22 instead of 3.30.

I am using some window functions that have become available only in SQLite 3.28 and wanted to upgrade the SQLite, I have tried

!apt-get update
!apt-get upgrade sqlite3

But this tells me I have the latest version of SQLite (ie 3.22). Any ideas how this might be resolved?

EDIT1: run ```!apt-cache policy sqlite3`` and the result was:

sqlite3:
  Installed: 3.22.0-1ubuntu0.2
  Candidate: 3.22.0-1ubuntu0.2
  Version table:
 *** 3.22.0-1ubuntu0.2 500
        500 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages
        500 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages
        100 /var/lib/dpkg/status
     3.22.0-1 500
        500 http://archive.ubuntu.com/ubuntu bionic/main amd64 Packages

Answer 1

Here's how to upgrade to the latest version

!curl https://www.sqlite.org/src/tarball/sqlite.tar.gz?r=release | tar xz
%cd sqlite/
!./configure
!make sqlite3.c
%cd /content
!npx degit coleifer/pysqlite3 -f
!cp sqlite/sqlite3.[ch] .
!python setup.py build_static build
!cp build/lib.linux-x86_64-3.7/pysqlite3/_sqlite3.cpython-37m-x86_64-linux-gnu.so \
     /usr/lib/python3.7/lib-dynload/
# then MENU: Runtime > Restart runtime ...
import sqlite3
sqlite3.sqlite_version  # 3.36.0

Here's an example notebook updated for Python 3.7

A faster(pre-compiled) version from my GDrive.

!gdown 1BSHIKQ7rFw5BpTq5nw1UZfjPK_7Mpnbi -O /usr/lib/python3.7/lib-dynload/
# MENU: Runtime > Restart runtime
import sqlite3
sqlite3.sqlite_version  # '3.38.0'

Answer 2

Problem 1: Installed sqlite3 is too old.

We can capitalize on google deploying the latest Ubuntu LTS in codelab as shown below by accessing Ubuntu repositories. Dqlite team, funded by Canonical, maintains a ppa for dqlite which has a dependency for the latest stable sqlite3. We can upgrade sqlite3 with three lines.

!sudo add-apt-repository -y ppa:dqlite/stable
!sudo apt update
!sudo apt-get install -y sqlite3

Codelab environment

!lsb_release -a 
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 18.04.3 LTS
Release:    18.04
Codename:   bionic

https://dqlite.io/docs/faq https://launchpad.net/~dqlite/+archive/ubuntu/stable

Problem 2: Codelab has already loaded sqlite3 into memory

!lsof -p `ps -ef | grep ipykernel_launcher | head -n 1 | awk '{print $2}'`  | grep sql
python3 131 root  mem       REG                7,0          2359698 /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6 (path dev=0,46)
python3 131 root  mem       REG                7,0          5772741 /usr/lib/python3.6/lib-dynload/_sqlite3.cpython-36m-x86_64-linux-gnu.so (path dev=0,46)

As shown here, libsqlite3 is loaded into memory. Python3 interpreter will not be able use the newly installed sqlite3 unless the python process is restarted

Method 1: Kill the Python. Crash the runtime kernel and juypter notebooks will restart it.

!sudo add-apt-repository -y ppa:dqlite/stable
!sudo apt update
!sudo apt-get install -y sqlite3
!sqlite3 --version
import sqlite3
print(sqlite3.sqlite_version_info)
### 
!kill `ps -ef | grep ipykernel_launcher | head -n 1 | awk '{print $2}'; /usr/bin/python3 -m ipykernel_launcher -f /root/.local/share/jupyter/runtime/kernel-*.json`
###
import sqlite3
sqlite3.sqlite_version_info

Method 2: Exit the kernel - best solution

!sudo add-apt-repository -y ppa:dqlite/stable
!sudo apt update
!sudo apt-get install -y sqlite3
!sqlite3 --version
import sqlite3, os
print(sqlite3.sqlite_version_info)
os._exit(00)
import sqlite3
print(sqlite3.sqlite_version_info)

Restart ipython Kernel with a command from a cell