cysqlite - a new sqlite driver (https://charlesleifer.com/blog/cysqlite---a-new-sqlite-driver/)

Charles Leifer has been maintaining pysqlite3 (https://github.com/coleifer/pysqlite3) - a fork of the Python standard library’s sqlite3 module that makes it much easier to run upgraded SQLite versions - since 2018.

He’s been working on a ground-up Cython (https://cython.org/) rewrite called cysqlite (https://github.com/coleifer/cysqlite) for almost as long, but it’s finally at a stage where it’s ready for people to try out.

The biggest change from the sqlite3 module involves transactions. Charles explains his discomfort with the sqlite3 at length - the library provides two different variants neither of which exactly match the autocommit mechanism in SQLite itself.

I’m particularly excited about the support for custom virtual tables (https://cysqlite.readthedocs.io/en/latest/api.html#tablefunction), a feature I’d love to see in sqlite3 itself.

cysqlite provides a Python extension compiled from C, which means it normally wouldn’t be available in Pyodide. I set Claude Code on it (https://github.com/simonw/research/tree/main/cysqlite-wasm-wheel) and it built me cysqlite-0.1.4-cp311-cp311-emscripten_3_1_46_wasm32.whl (https://github.com/simonw/research/blob/main/cysqlite-wasm-wheel/cysqlite-0.1.4-cp311-cp311-emscripten_3_1_46_wasm32.whl), a 688KB wheel file with a WASM build of the library that can be loaded into Pyodide like this:

import micropip await micropip.install( “https://simonw.github.io/research/cysqlite-wasm-wheel/cysqlite-0.1.4-cp311-cp311-emscripten_3_1_46_wasm32.whl” ) import cysqlite print(cysqlite.connect(“:memory:“).execute( “select sqlite_version()” ).fetchone())

(I also learned that wheels like this have to be built for the emscripten version used by that edition of Pyodide - my experimental wheel loads in Pyodide 0.25.1 but fails in 0.27.5 with a Wheel was built with Emscripten v3.1.46 but Pyodide was built with Emscripten v3.1.58 error.)

You can try my wheel in this new Pyodide REPL (https://7ebbff98.tools-b1q.pages.dev/pyodide-repl) i had Claude build as a mobile-friendly alternative to Pyodide’s own hosted console (https://pyodide.org/en/stable/console.html).

I also had Claude build this demo page (https://simonw.github.io/research/cysqlite-wasm-wheel/demo.html) that executes the original test suite in the browser and displays the results:

Via lobste.rs (https://lobste.rs/s/gipvta/cysqlite_new_sqlite_driver)

Tags: python (https://simonwillison.net/tags/python), sqlite (https://simonwillison.net/tags/sqlite), charles-leifer (https://simonwillison.net/tags/charles-leifer), webassembly (https://simonwillison.net/tags/webassembly), pyodide (https://simonwillison.net/tags/pyodide), ai-assisted-programming (https://simonwillison.net/tags/ai-assisted-programming), claude-code (https://simonwillison.net/tags/claude-code)