alchemy-utils 0.1a0 发布

英文标题：alchemy-utils 0.1a0

开发者发布了 alchemy-utils 0.1a0，这是一个基于 SQLAlchemy 的实验性 Python 库和 CLI 工具，旨在为 sqlite-utils 提供数据库无关的替代方案。该原型由 Codex 和 GPT-5.6 Sol Ultra 协助构建，支持与 sqlite-utils 类似的插入、upsert、批量操作、建表、更新及表结构检查 API。项目已针对 PostgreSQL、SQLite 和 DuckDB 进行测试，并可通过命令行读取 PostgreSQL 表数据或将 CSV 自动导入 DuckDB。针对旧金山树木数据集的 DuckDB 导入性能经过优化后，首次运行时间从接近一小时降至约 35 秒。

打开原文相关 6 · 机会 2

中文

我长期以来一直在思考：如果把我的 sqlite-utils Python 库和 CLI 工具做成数据库无关的版本，会是什么样子。今天早上（这确实是一个洗澡时想到的项目），我让 Codex 和 GPT-5.6 Sol Ultra 构建了一个原型：

进行一次探索性研究，评估构建一个与 SQLite-utils 具有相同核心 API 的库需要什么——尤其是 insert、upsert、insert_all、upsert_all、create 和 update 方法，以及表结构检查相关功能——但后端基于 SQLalchemy，使其能够适用于多个数据库引擎

针对 PostgreSQL、SQLite 和 duckdb 进行测试

以 ~/dev/sqlite-utils 为参考

为此创建一个 git 仓库，并尽早且频繁地提交——使用 uv init 初始化项目——采用 red/green TDD 和 pytest；可参考 ~/dev/django-sql-dashboard，了解 PostgreSQL 测试的一种实现思路

只用了极少的后续提示，就将这个项目完善到了足以作为 alpha 版本发布的状态。

下面是一条命令，我可以用它列出本地 PostgreSQL 中、我的博客数据库副本里某张表的行：

uvx --with 'alchemy-utils[postgresql]' alchemy-utils rows 'postgresql+psycopg://simon@localhost:5432/simonwillisonblog' redirects_redirect

其输出开头如下：

[
  {
    "id": 2328,
    "domain": "simonwillison.net",
    "path": "2020/May/21/apple-photos-sqlite/",
    "target": "/2020/May/21/dogsheep-photos/",
    "created": "2020-05-21T13:03:46.591692-07:00"
  },
  {
    "id": 3,
    "domain": "feeds.simonwillison.net",
    "path": "swn-links",
    "target": "https://simonwillison.net/atom/links/",
    "created": "2017-10-01T14:12:54.820729-07:00"
  }

或者，如果你想创建一个包含旧金山每一棵树的 DuckDB 数据库，并自动创建与该文件匹配的表结构：

curl 'https://raw.githubusercontent.com/simonw/sf-tree-history/refs/heads/main/Street_Tree_List.csv' | uvx --with 'alchemy-utils[duckdb]' alchemy-utils insert 'duckdb:////tmp/trees.db' trees - --csv

（我第一次运行这条命令时花了将近一小时，因此我让 Codex 对其进行了优化，将耗时降至约 35 秒。）

English / 原文

I've long pondered what a database agnostic version of my sqlite-utils Python library and CLI utility might look like. This morning (literally a shower project) I tasked Codex and GPT-5.6 Sol Ultra with building a prototype:

Do a research spike to see what it would take to build a library with the same core API as SQLite-utils - in particular the insert and upsert and insert_all and upsert_all and create and update methods, and the table introspection stuff - but backed by SQLalchemy so it works for multiple database engines

Test against PostgreSQL and SQLite and duckdb

Use ~/dev/sqlite-utils for reference

Create a git repo for this and commit and early and often - use uv init to start the project - use red/green TDD and pytest, see ~/dev/django-sql-dashboard for one idea as to how the PostgreSQL tests could work

It took very few follow-up prompts to produce this project in a state good enough to release as an alpha.

Here's a one-liner I can use to list the rows in a table in my local PostgreSQL copy of my blog's database:

uvx --with 'alchemy-utils[postgresql]' alchemy-utils rows 'postgresql+psycopg://simon@localhost:5432/simonwillisonblog' redirects_redirect

The output from that starts like this:

[
  {
    "id": 2328,
    "domain": "simonwillison.net",
    "path": "2020/May/21/apple-photos-sqlite/",
    "target": "/2020/May/21/dogsheep-photos/",
    "created": "2020-05-21T13:03:46.591692-07:00"
  },
  {
    "id": 3,
    "domain": "feeds.simonwillison.net",
    "path": "swn-links",
    "target": "https://simonwillison.net/atom/links/",
    "created": "2017-10-01T14:12:54.820729-07:00"
  }

Or if you'd like a DuckDB database with every tree in San Francisco, schema created automatically to match the file:

(That one took nearly an hour the first time I ran it, so I had Codex optimize it and got it down to around 35 seconds.)