（十三）连接 SQLite 并完成增删改查

1. 连接、语句与游标

sqlite3 随 Python 提供，数据库通常保存在一个文件中；写 ":memory:" 则建立这次连接使用的内存数据库，适合下面的独立小例子。

# closing 在离开时调用 close；with connection 则负责事务提交或回滚。
from contextlib import closing
import sqlite3

with closing(sqlite3.connect(":memory:", autocommit=False)) as connection:
    with connection:
        connection.execute("CREATE TABLE books(id INTEGER PRIMARY KEY, title TEXT UNIQUE, finished INTEGER)")
        cursor = connection.execute(
            "INSERT INTO books(title, finished) VALUES (?, ?)", ("Python", 0)
        )
        print("新编号", cursor.lastrowid)
        connection.executemany(
            "INSERT INTO books(title, finished) VALUES (?, ?)",
            [("NumPy", 1), ("Pygame", 0)],
        )
        connection.execute("UPDATE books SET finished = ? WHERE title = ?", (1, "Python"))
        cursor = connection.execute("DELETE FROM books WHERE title = ?", ("Pygame",))
        print("删除行数", cursor.rowcount)
    rows = connection.execute("SELECT title, finished FROM books ORDER BY id").fetchall()
    print(rows)

输出“新编号 1”“删除行数 1”和 [('Python', 1), ('NumPy', 1)]。四种动作分别是 INSERT 加入、SELECT 查询、UPDATE 修改、DELETE 删除。SQL 后面的 WHERE 选择受影响的行，没有它就会对整张表执行对应修改或删除。

execute 返回游标，它提供这次语句的结果和读取位置：fetchone() 取得下一行，没有行时返回 None；fetchall() 收集全部剩余行；也可以直接遍历游标。executemany 用同一条语句依次执行多组参数，仍需要给整组操作安排事务。

lastrowid 适合读取这次普通插入得到的编号；后面“遇到同名书则更新”的操作，要按唯一标题查询已有编号，不拿上一次插入编号猜它。

2. 参数绑定与按名称读取字段

# ? 只负责绑定值，标题中的引号仍是普通数据，不成为 SQL 结构。
from contextlib import closing
import sqlite3

with closing(sqlite3.connect(":memory:", autocommit=False)) as connection:
    connection.row_factory = sqlite3.Row
    with connection:
        connection.execute("CREATE TABLE books(id INTEGER PRIMARY KEY, title TEXT)")
        title = "A'); DROP TABLE books; --"
        connection.execute("INSERT INTO books(title) VALUES (?)", (title,))
    row = connection.execute("SELECT id, title FROM books WHERE title = ?", (title,)).fetchone()
    print(row["id"], row["title"] == title)

输出 1 True。单个参数也写成 (title,) 这个一元组；sqlite3.Row 让结果同时支持列名与位置访问。还可以使用 :title 形式的命名占位符，再传 {"title": title} 这样的参数字典。

占位符接收数据值，表名、列名和排序方向属于 SQL 结构，需要写在语句里。需要切换排序方式时，可以从程序定义好的几种 SQL 片段中选择，再把筛选值通过参数交入。Python SQLite 接口

3. 列类型与实际保存的值

SQLite 普通表会根据列的类型亲和性尝试转换输入。直接观察同一 INTEGER 列接收两份文本：

# typeof 取得 SQLite 实际保存的类型；普通 INTEGER 声明本身不完成业务检查。
from contextlib import closing
import sqlite3

with closing(sqlite3.connect(":memory:", autocommit=False)) as connection:
    with connection:
        connection.execute("CREATE TABLE values_demo(value INTEGER)")
        connection.executemany("INSERT INTO values_demo VALUES (?)", [("35",), ("很多",)])
    print(connection.execute("SELECT value, typeof(value) FROM values_demo ORDER BY rowid").fetchall())

得到 [(35, 'integer'), ('很多', 'text')]。第一项转成整数，第二项仍保存文本。前面日志表用 typeof(minutes) = 'integer' 与非负判断组成 CHECK，让写入阶段检查真实保存的类型；入口仍负责把输入转换成实际的 Python 整数，并给出对应说明。SQLite 类型与转换