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
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
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)
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.