模块 12: 数据库

关系模式描述表、列与约束。主键标识行，外键引用另一表键。作者表及 book.author_id 避免到处重复作者名。NOT NULL 拒绝必需值缺失，UNIQUE 拒绝重复，CHECK 约束局部谓词。表不是有序序列，需 ORDER BY。SQLite 每个连接需开启外键检查才能依赖它。

SELECT 选列，WHERE 筛行，JOIN 按关系组合。图书作者按 ID 连接，而非碰巧相同的名字。COUNT 计行，GROUP BY 聚合前分组。NULL 表示缺失或未知，应用 IS NULL。多匹配可使连接行数增加，需检查键基数。参数占位符绑定数据值，不能任意替代表或列名。

重复事实导致更新异常，多个图书行的作者名可能不一致。作者事实保存一次并引用。图书与作者多对多需带复合键连接表，而非逗号字段。版本与实体副本身份不同，应分别建模。规范化帮助一致性，实测需要时可反规范化，但更新策略须保持正确。先从事实与依赖出发。

索引是随表维护的查询结构，能减少精确或范围查询工作，但占空间并增加更新成本。数据库根据结构与统计选择计划，不能假定每个谓词都用索引。EXPLAIN QUERY PLAN 显示策略。书名索引支持精确查询，前导通配子串可能仍扫描。增加索引前需测量负载并计写成本。

事务让修改一起提交或回滚。原子性避免借阅与库存不一致，隔离管理并发交互，持久性关注已提交数据在数据库保证下抗故障。条件 UPDATE 仅在库存正数时扣减，插入借阅属同一事务。用户值用 ? 绑定，保持为数据而非 SQL 语法。输入验证与参数化互补，都不能代替授权。

下载脚本，在终端中使用 Python 3.11 或更新版本运行：python m12_database.py. Windows 也可使用 py -3；部分系统使用 python3。实验仅用标准库。先预测结果，再运行并完成变体。不要使用 -O，以保留断言。下方输出由构建器实际运行捕获，两种语言使用相同代码与输出。

建作者图书模式、连接、检查索引，重新打开文件确认跨连接保存。

下载 m12_database.py

"""Persist records, join relations and inspect an indexed query."""
import sqlite3, tempfile
from pathlib import Path
with tempfile.TemporaryDirectory() as folder:
    path = Path(folder) / "catalogue.sqlite"
    db = sqlite3.connect(path)
    try:
        db.execute("PRAGMA foreign_keys = ON")
        db.executescript("""
            CREATE TABLE authors(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
            CREATE TABLE books(id INTEGER PRIMARY KEY, title TEXT NOT NULL, author_id INTEGER NOT NULL REFERENCES authors(id));
            CREATE INDEX book_title ON books(title);
        """)
        with db:
            db.execute("INSERT INTO authors VALUES (?, ?)", (1, "Frank Herbert"))
            db.execute("INSERT INTO books VALUES (?, ?, ?)", (1, "Dune", 1))
        query = "SELECT books.title, authors.name FROM books JOIN authors ON authors.id=books.author_id WHERE books.title=?"
        print("joined:", db.execute(query, ("Dune",)).fetchall())
        plan = db.execute("EXPLAIN QUERY PLAN SELECT id FROM books WHERE title=?", ("Dune",)).fetchall()
        indexed = any("INDEX" in row[3] for row in plan)
        print("index used:", indexed); assert indexed
        attack = "' OR 1=1 --"
        assert db.execute("SELECT id FROM books WHERE title=?", (attack,)).fetchall() == []
    finally:
        db.close()
    reopened = sqlite3.connect(path)
    try:
        print("reopened rows:", reopened.execute("SELECT COUNT(*) FROM books").fetchone()[0])
        assert reopened.execute("SELECT COUNT(*) FROM books").fetchone()[0] == 1
    finally:
        reopened.close()

joined: [('Dune', 'Frank Herbert')]
index used: True
reopened rows: 1

1. 加入同作者另一图书。
2. 尝试无效作者 ID。
3. 按作者统计书数。

重复请求在尝试扣减后失败，扣减须回滚。本实验拒绝重复，模块 14 将实现幂等重放。

下载 m12_transactions.py

"""A stock decrement and loan creation commit together or roll back together."""
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys=ON")
db.executescript("""
 CREATE TABLE books(id INTEGER PRIMARY KEY, copies INTEGER NOT NULL CHECK(copies>=0));
 CREATE TABLE loans(request_id TEXT PRIMARY KEY, book_id INTEGER REFERENCES books(id));
 INSERT INTO books VALUES(1, 2);
""")
def borrow(request_id, book_id):
    with db:
        changed = db.execute("UPDATE books SET copies=copies-1 WHERE id=? AND copies>0", (book_id,)).rowcount
        if changed != 1: raise ValueError("unavailable")
        db.execute("INSERT INTO loans VALUES (?, ?)", (request_id, book_id))
try:
    borrow("request-a", 1)
    try:
        borrow("request-a", 1)
    except sqlite3.IntegrityError:
        print("duplicate request rolled back")
    assert db.execute("SELECT copies FROM books").fetchone()[0] == 1
    borrow("request-b", 1)
    try:
        borrow("request-c", 1)
    except ValueError:
        print("out of stock rejected")
    print("copies:", db.execute("SELECT copies FROM books").fetchone()[0], "loans:", db.execute("SELECT COUNT(*) FROM loans").fetchone()[0])
    assert db.execute("SELECT COUNT(*) FROM loans").fetchone()[0] == 2
finally:
    db.close()

duplicate request rolled back
out of stock rejected
copies: 0 loans: 2

1. 移除事务分组，指出破坏不变式。
2. 测试未知图书。
3. 解释条件更新为何优于未保护先查再扣。

先尝试，再展开解答。★ 应用概念；★★ 结合概念；★★★ 进行设计或证明。

选择答案查看反馈，重置后可重做。无需 JavaScript 也可阅读答案表。

• SQLite 事务 — 阅读提交回滚与写事务，描述部分更新失败。
• Python sqlite3 教程 — 阅读占位符与连接管理，区分关闭与回滚。

画作者图书模式，解释失败借阅事务。模块 13 将把程序本身作为结构化输入，并探究计算边界。