Upgrade Python SQLite driver to latest version on Windows

SQLite has an extension called JSON1 that allows working with JSON. It's really useful for storing data that wouldn't necessarily belong to a separate column, like metadata.

Unfortunately on Windows, SQLite driver for Python doesn't come bundled with this extension. We need to upgrade the driver to a newer version to get it.

You can find the compiled binaries for Windows on SQLite website. Here are the links, scraped directly from the page:

Extract the ZIP file and place the DLL into $PYTHON_DIR/DLLs.

You can find the exact location using PowerShell:

start (Join-Path (Split-Path (Get-Command python).Path) "dlls")
# C:\Python39\dlls

or use cmd directly:

powershell -command "start (Join-Path (Split-Path (Get-Command python).Path) "dlls")"

SQLite compile options

Run python, and fetch the list of compilation options to verify if it has succeeded:

import sqlite3
options = sqlite3.connect(':memory:').execute('pragma compile_options').fetchall()
for o in options: print(o[0])

COMPILER=msvc-1500
ENABLE_BYTECODE_VTAB
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS4
ENABLE_FTS5
ENABLE_GEOPOLY
ENABLE_JSON1 <-- IT WORKS! 🎉
ENABLE_RTREE
ENABLE_STMTVTAB
MAX_TRIGGER_DEPTH=100
TEMP_STORE=1
THREADSAFE=1

Thanks to the new driver, we also have access to FTS5 extension to work with full text search indices.