2026-06-13原文1 min readUpdated: 2026-06-14

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

Determining the source table.column for each result column in arbitrary SQLite queries is feasible because SQLite computes this internally and exposes it via its column-metadata API when compiled with SQLITE_ENABLE_COLUMN_METADATA. While Python's standard sqlite3 module doesn't surface this information, robust methods exist: using the third-party apsw library provides direct access with cursor.description_full, or a pure-stdlib ctypes bridge (column_provenance.py) can retrieve the C function sqlite3_column_table_name(), and another approach relies on parsing EXPLAIN output.

SourceSimon Willison's Weblog

Research: Mapping SQLite result columns back to their source table.column

Simon Willison’s Weblog

13th June 2026

Research

Mapping SQLite result columns back to their source table.column — Determining the source table.column for each result column in arbitrary SQLite queries is feasible because SQLite computes this internally and exposes it via its column-metadata API when compiled with SQLITE_ENABLE_COLUMN_METADATA. While Python’s standard sqlite3 module doesn’t surface this information, robust methods exist: using the third-party apsw library provides direct access with cursor.description_full, or a pure-stdlib ctypes bridge (column_provenance.py) can retrieve the…

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.

To build that, we would need to be able to look at a SQL query like select users.name, orders.total from users join orders on orders.user_id = users.id and programmatically identify the table.column for each result - navigating not just joins but also more complex syntax like CTEs.

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.