将 SQLite 结果列映射回其源 `table.column`Mapping SQLite result columns back to their source `table.column`
作者探索如何在 Datasette 中，将任意 SQL 查询返回的结果列准确映射回其原始的 `table.column` 来源。实现这一功能可以让查询结果展示更多基于源表的附加上下文信息，从而提升数据呈现的可读性。这需要深入解析复杂的 SQL 语句（如多表连接和别名），以提取每个返回列在数据库结构中的真实出处。
Simon Willison
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.
需要完整排版与评论请前往来源站点阅读。