Python 教程 / 综合练习:收集 API 数据并写入 SQLite

API 收集程序应先验证整批响应,再按稳定的外部 ID 存入数据库。这个综合练习把 JSON 请求与 SQLite 事务结合:已有 ID 更新名称和金额,重复运行不会不断新增同一笔记录。

先定义标识与重复运行政策

只有外部服务保证相同 id 始终代表同一交易,UPSERT 才有明确意义。不能仅凭名称和金额相同就合并两笔交易。本例在同一批里遇到重复 ID 时,后面的有效值覆盖前面的值;若业务要求重复即报错,需要在写入前另加检查。

所有记录先通过验证,之后才打开数据库并开始写入。因此最后一条无效时,前面的有效记录也不会先写进去。数据库写入在一个本地事务中完成,但网络请求和本地提交并不会因此变成分布式事务。

请求与数据验证基础参见HTTP API 与 JSON 响应,连接与事务规则参见sqlite3 数据保存。本例要求 Python 3.12 以上,以使用显式 autocommit=False;接口见Python 官方 sqlite3 文档。

准备本机 JSON 响应

创建独立练习目录,将以下内容以 UTF-8 保存为 expenses.json,不要在含有凭据或私人文件的目录启动文件服务器:

[{"id":"e1","name":"coffee","amount":3500},{"id":"e2","name":"lunch","amount":8000}]

第一个终端进入该目录,选择项目解释器并启动:

python -m http.server 8000 --bind 127.0.0.1

127.0.0.1 只供本机访问,8000 为端口。如果端口已占用,选择空闲端口,并同步修改客户端地址。保持这个终端运行,在第二个终端操作客户端;如果实际命令名是 python3,两个终端一致替换。服务器会提供当前目录的文件访问,因此始终使用专用目录,并在实验后停止。

执行完整收集程序

在相同练习目录中,把以下完整 Python 代码保存为 collect.py:

import json
import sqlite3
import sys
from urllib.request import urlopen

with urlopen(sys.argv[1], timeout=5) as response:
    if response.status != 200:
        raise ValueError("unexpected status")
    raw = response.read(1_000_001)
    if len(raw) > 1_000_000:
        raise ValueError("response too large")
    data = json.loads(raw.decode("utf-8"))
if not isinstance(data, list) or len(data) > 10000:
    raise ValueError("invalid batch")
MAX_AMOUNT = 1_000_000_000_000
records = []
for row in data:
    if not isinstance(row, dict):
        raise ValueError("invalid row")
    key, name, amount = row.get("id"), row.get("name"), row.get("amount")
    if not isinstance(key, str) or not key.strip() or not isinstance(name, str) or not name.strip():
        raise ValueError("invalid identity")
    if type(amount) is not int or not 0 <= amount <= MAX_AMOUNT:
        raise ValueError("invalid amount")
    records.append((key, name, amount))
con = sqlite3.connect("collected.db", autocommit=False)
try:
    with con:
        con.execute("CREATE TABLE IF NOT EXISTS expenses (id TEXT PRIMARY KEY, name TEXT NOT NULL, amount INTEGER NOT NULL CHECK(amount BETWEEN 0 AND 1000000000000))")
        con.executemany("INSERT INTO expenses VALUES (?, ?, ?) ON CONFLICT(id) DO UPDATE SET name=excluded.name, amount=excluded.amount", records)
    count = con.execute("SELECT COUNT(*) FROM expenses").fetchone()[0]
    total = sum(row[0] for row in con.execute("SELECT amount FROM expenses"))
    print("stored:", count, "total:", total)
finally:
    con.close()

第二个终端选用相同项目环境,在该目录执行:

python collect.py http://127.0.0.1:8000/expenses.json

第一次应输出 stored: 2 total: 11500。相同响应再执行一次,仍为 2 条、11500,不会变成 4 条。collected.db 相对于客户端的工作目录创建;从另一目录启动就可能得到另一份数据库,不能仅凭文件名判断使用的是同一个文件。

客户端要求状态 200、响应不超过 1,000,000 字节、列表不超过 10,000 项。每项必须是字典,id 与 name 为非空白字符串,amount 必须恰为 int 且在 0 至 1,000,000,000,000 之间,bool 被排除。这里用 strip 判断空白,但保存的是原始字符串;"e1" 与 " e1 " 并不会自动合并。

问号参数绑定把值与 SQL 结构分开。executemany 按记录顺序执行 UPSERT,所以同一 ID 的后值获胜;空列表不会删除原有记录。这个工具是更新收集器,不是与远端全量镜像同步的删除程序。

查询金额后使用 Python sum,可用精确整数累计。SQLite 的整数 SUM 在累计时可能超出 64 位范围,而改用浮点 total() 会引入另一种精度语义;相关条件见SQLite 官方聚合函数说明。现有表的约束不会因 CREATE TABLE IF NOT EXISTS 自动更新,真实数据结构需独立迁移。

验证更新、失败和收集完整性

  1. 对同一响应运行两次,确认记录数与合计不变。
  2. 只把 expenses.json 中 e2 的金额改成 9000,重新执行,预期 2 条、12500。
  3. 把一个金额改成字符串,程序应在写入前报 invalid amount。重新查询数据库,前一次有效内容应仍保留。
  4. 恢复原 JSON,再确认 2 条、11500。最后在服务器终端按 Ctrl+C 停止服务。

请求、UTF-8 解码、JSON 解析或字段检查失败都会显示异常并以失败状态结束;不要继续运行依赖完整数据的后续任务,也不要把所有失败转换成空列表。超时参数限制阻塞等待,并不等于整个批处理总共最多五秒,见urlopen 官方说明。

普通 SQLite 表的 INTEGER 声明和范围 CHECK 并不是严格整数类型检查,其他写入路径仍需遵守一致政策。本例经过 Python 类型验证后再绑定,并在 finally 关闭连接;with con 管理提交与回滚,本身不会代替 close。

真实外部服务还涉及 HTTPS、认证、分页、请求配额、重试和删除政策。只成功取得一页不能自动声称全部收集完成。增加重试前应限制次数并确认可重复性,记录实际完成范围,不能用无限重试或空结果隐藏丢失数据。