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

# Research: Mapping SQLite result columns back to their source `table.column` Source: [https://simonwillison.net/2026/Jun/13/sqlite-column-provenance/](https://simonwillison.net/2026/Jun/13/sqlite-column-provenance/) [Research](https://simonwillison.net/elsewhere/research/)[Mapping SQLite result columns back to their source \`table\.column\`](https://github.com/simonw/research/tree/main/sqlite-column-provenance#readme)— 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](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`\.