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

Simon Willison is researching methods to map SQLite query result columns back to their source table and column names, aiming to enhance Datasette's ability to render additional metadata for arbitrary SQL queries. He tasked Claude Code with finding solutions, which identified approaches using the apsw library, ctypes to access SQLite's internal C function, and analysis of EXPLAIN output.

Research: Mapping SQLite result columns back to their source table.column https://github.com/simonw/research/tree/main/sqlite-column-provenance readme It would be neat if arbitrary SQL queries in Datasette https://datasette.io/ 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 https://simonwillison.net/2026/Jun/13/us-government-directive-to-suspend-access/ on the problem. It found several promising solutions - one using apsw https://github.com/rogerbinns/apsw , another that uses ctypes to access the SQLite sqlite3 column table name C function https://sqlite.org/c3ref/column database name.html which is not otherwise exposed to Python , and one using clever interrogation of the output of EXPLAIN .