Mapping SQLite result columns back to their source `table.column`

Simon Willison · Simon Willison · 2026-06-13

Simon Willison uses Claude Code to research methods for mapping SQLite query result columns back to their source table.column identifiers, discovering three viable approaches including ctypes access to the SQLite C API and EXPLAIN output analysis, as groundwork for richer Datasette query rendering.

Open original ↗

Appears in

Simon Willison's AI-Augmented Datasette Ecosystem: Agent, Apps, and Plugins

Extraction

Topics: sqlitedatasettesql-analysisai-assisted-developmentpython

Claims

SQLite's sqlite3_column_table_name() C function, which exposes column provenance, is not accessible through Python's standard sqlite3 module.
Claude Code identified three methods to retrieve source table.column mappings from arbitrary SQLite queries: via the apsw library, via ctypes calling the SQLite C API directly, and via analysis of EXPLAIN output.
Column provenance mapping must handle complex SQL including CTEs and multi-table JOINs, not just simple column references.
Willison intends to use this research to render Datasette query results with additional metadata derived from column source information.
Claude Opus 4.8, referred to by its internal name 'Fable', is currently banned by the US government.

Key quotes

It would be neat if arbitrary SQL queries in Datasette could be rendered with additional information based on which columns from which tables were included in the results.

I decided to set Claude Code (Opus 4.8, since Fable is currently banned by the US government) on the problem.

It found several promising solutions - one using apsw, another that uses ctypes to access the SQLite sqlite3_column_table_name() C function (which is not otherwise exposed to Python), and one using clever interrogation of the output of EXPLAIN.