第 23 章 · SQLite

一句话：把整个关系数据库放进一个文件的库，程序直接读写这个文件，不用起服务、不用建用户。 用它还是用别的：单机程序、命令行小工具、测试用的库、拿 SQL 分析一份 CSV，用 SQLite；多台机器要同时连、写入并发高、数据要放在网络另一端，用 PostgreSQL（第 21 章）。 核对版本：SQLite 3.45（WSL Ubuntu 的 sqlite3、Python 3.12 自带的 sqlite3 模块），winget 上为 3.53，2026-10

23.1 安装与版本

SQLite 没有服务要起，要装的只是命令行程序 sqlite3：

winget install SQLite.SQLite

brew install sqlite
sudo apt install sqlite3

macOS 系统自带 /usr/bin/sqlite3，brew install sqlite 装的较新版本不会自动进 PATH，按 brew info sqlite 的提示加进去。程序里用不着装这个：Python 自带 sqlite3 模块，import sqlite3 即可；Node 22.13 起自带实验性的 node:sqlite，其他语言的驱动多数把 SQLite 一起编译进去了。

查版本，前一条是命令行程序，后一条是 Python 里那份，两者可以不同：

sqlite3 --version
python -c "import sqlite3; print(sqlite3.sqlite_version)"

23.2 五分钟概念

• 数据库就是一个文件：sqlite3 app.db 打开（不存在就新建）app.db，表、索引、数据全在里面。复制、发邮件、放进测试夹具都按文件处理，但正在写的库不能直接复制，见 23.4。
• 没有服务器，也没有用户：读写在你的进程里完成，没有端口、口令和权限，能读写这个文件的进程就能改全部数据。几个进程同时打开同一个文件时，靠操作系统的文件锁协调。
• 类型亲和性（Type Affinity）：列声明的类型只是倾向，不是约束。INTEGER 列存进 'abc' 不报错，VARCHAR(3) 不限长度；.import 自动建的表每列都是 TEXT，数字按文本排序，9 排在 100 前面。要严格检查类型，建表时在末尾加 STRICT。
• WAL 模式（Write-Ahead Logging）：默认模式下提交时读被挡住，有人在读也提交不了；开了 WAL，新写入先追加到旁边的 app.db-wal，读的人照常读，见下图。同一时刻仍然只能有一个连接在写，第二个写的人要等，等不到就报 database is locked。WAL 依赖进程间共享内存，所以库要放在本机磁盘上。
• 什么时候够用：一台机器上的程序、每天十万次以下访问的网站、桌面软件的数据文件、测试和数据分析都够用，单个库上限约 281 TB。程序和数据隔着网络、很多进程同时写、要多台服务器分担时，换 PostgreSQL 或 MySQL。

开了 WAL 之后，写连接把新的页追加到 -wal 文件，读连接先在 -shm 里查某页有没有更新的版本，有就从 -wal 读，没有就读主文件；-wal 攒到 1000 页左右，检查点把它们写回主文件：

23.3 常用命令

以 . 开头的是 sqlite3 命令行自己的命令，一行一条，不用分号；PRAGMA 和 VACUUM 是 SQL，要分号。通用的 SQL 见第 20 章 20.3。

任务	命令	说明
打开与查看
打开或新建库	.open app.db	也可以启动时写 sqlite3 app.db；不带文件名时是内存库，退出就没
列出表	.tables	表和视图都列出
看建表语句	.schema orders	不写表名列出全部
输出带列名	.headers on	配合 csv、list 模式用
换输出格式	.mode box	画成带边框的表格；.mode csv 输出 CSV
退出	.quit	也可以按 Ctrl+D（Windows 上 Ctrl+Z 回车）
导入导出
导入 CSV	.import --csv --skip 1 orders.csv orders	表已存在时 --skip 1 跳过表头；表不存在时第一行当列名，列都是 TEXT
结果写进文件	.output top.csv	之后的查询结果都写进文件，.output stdout 改回屏幕
导出成 SQL	.dump	建表和 INSERT 语句；sqlite3 app.db .dump > app.sql 存成文件
执行 SQL 文件	.read app.sql	用 .dump 的文件重建库就靠它
在线备份	.backup app-2026-10-08.db	库正在被别的程序写也能用，得到一致的副本
设置与维护
开 WAL	PRAGMA journal_mode=WAL;	返回 wal；写进文件，以后每次打开都是 WAL
开外键检查	PRAGMA foreign_keys=ON;	每个连接默认关，每次连接都要执行
整理压缩	VACUUM;	删了大量数据后文件不会自动变小，执行它才回收；期间锁住整个库
压缩成新文件	VACUUM INTO 'app-small.db';	得到整理过的副本，也可当在线备份用

23.4 典型工作流

从 CSV 导入并查询

先按自己的类型建表再导入，数字列才按数字比较和求和：

1. 打开或新建库：sqlite3 shop.db。
2. 建表，数字列声明成 INTEGER：CREATE TABLE orders (id INTEGER PRIMARY KEY, name TEXT, city TEXT, amount INTEGER);。
3. 导入，跳过 CSV 的表头行：.import --csv --skip 1 orders.csv orders。
4. 结果画成表格：.mode box。
5. 按城市汇总，Beijing 一行的 total 是 109：SELECT city, COUNT(*) AS n, SUM(amount) AS total FROM orders GROUP BY city ORDER BY total DESC;。
6. 切到 CSV 输出并带列名：.mode csv，再 .headers on。
7. 之后的结果写进文件：.output beijing.csv。
8. 查出要导出的行：SELECT * FROM orders WHERE city = 'Beijing';。
9. 改回屏幕输出并退出：.output stdout，再 .quit。

在线备份一个正在用的库

程序开着库、随时在写，也不用停：

1. 用备份接口复制出一个一致的副本：sqlite3 app.db ".backup app-2026-10-08.db"。
2. 或者整理压缩成新文件，副本更小，日志模式回到默认：sqlite3 app.db "VACUUM INTO 'app-2026-10-08-small.db'"。
3. 检查副本完好，输出 ok：sqlite3 app-2026-10-08.db "PRAGMA integrity_check;"。
4. 复制一份到另一台机器（scp 见第 6 章）：scp app-2026-10-08.db backup-host:/backups/。
5. 要恢复时先停掉程序，再把副本写回原库：sqlite3 app.db ".restore app-2026-10-08.db"。

不要用 cp 覆盖 app.db：旁边残留的 app.db-wal 会被套用到新文件上。定期备份和恢复演练的做法见第 20 章 20.4。

在 Python 里打开、建表、查询

1. 新建 app.py，内容见下：code app.py。
2. 运行，打印 (1, 'Tom')，同目录下多出 app.db：python app.py。
3. 再运行一次，多打印一行 (2, 'Tom')，说明数据存进了文件：python app.py。

import sqlite3

con = sqlite3.connect("app.db", timeout=10)  # 被锁时最多等 10 秒，默认 5 秒
con.execute("PRAGMA foreign_keys = ON")  # 外键检查每个连接都要开
con.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
with con:  # 块结束时自动 COMMIT，出错自动 ROLLBACK
    con.execute("INSERT INTO users (name) VALUES (?)", ("Tom",))  # 用 ? 传参，不拼字符串
for row in con.execute("SELECT id, name FROM users"):
    print(row)
con.close()

23.5 配置要点

SQLite 没有配置文件，设置分两处：命令行程序 sqlite3 启动时读用户目录下的 .sqliterc，按顺序执行里面的点命令和 SQL；程序里的设置靠连上后执行 PRAGMA，.sqliterc 对程序不起作用。

配置项	位置	说明
.sqliterc（Linux、macOS）	~/.sqliterc	只影响 sqlite3 命令行
.sqliterc（Windows）	%USERPROFILE%\.sqliterc	同上
临时换一个	sqlite3 -init my.rc app.db	用指定文件代替 .sqliterc

值得在程序里设的 PRAGMA。除 journal_mode 写进文件外，其余只对当前连接有效：

配置项	建议值	效果
journal_mode	WAL	读写互不阻塞；默认 delete；设一次就记在文件里
synchronous	NORMAL	配合 WAL，提交时不再每次刷盘，写入快得多；断电可能丢最后几次提交，但库不会坏；默认 FULL
foreign_keys	ON	检查外键；默认 OFF，外键写了也不管
busy_timeout	5000	被锁时最多等 5 秒再报错；默认 0，立刻报 database is locked；Python 的 sqlite3 模块默认已等 5 秒
cache_size	-20000	每个连接的页缓存，负数单位是 KB，这里约 20 MB；默认 -2000

一个 .sqliterc，以 -- 开头的是注释行：

-- 查询结果画成带边框的表格，带列名
.mode box
-- 库被别的连接锁住时最多等 5 秒，不立刻报 database is locked
.timeout 5000
-- 检查外键（每个连接默认关）
PRAGMA foreign_keys = ON;

23.6 注意事项

• 写入报 database is locked（命令行显示 Error: stepping, database is locked (5)，前缀因版本而异）。 原因：另一个连接正在写或开着事务没提交，同一时刻只能有一个写的人。处理：PRAGMA journal_mode=WAL;，设 busy_timeout，事务写完尽快 COMMIT。
• 订单的 user_id 指向不存在的用户，插入却没报错。 原因：外键检查每个连接默认关闭。处理：每次连接后执行 PRAGMA foreign_keys = ON;，之后同样的插入报 FOREIGN KEY constraint failed。
• ORDER BY amount DESC 排出 9、20、100，或 INTEGER 列里存进了 abc。 原因：类型亲和性只是倾向，.import 建的列又都是 TEXT。处理：先建表再 .import --skip 1；要严格检查的表末尾加 STRICT，存错类型报 cannot store TEXT value in INTEGER column。
• 复制出来的库报 database disk image is malformed，或少了最近的数据。 原因：复制时程序正在写，或只复制了 app.db，没带上 app.db-wal 里还没写回的页。处理：用 sqlite3 app.db ".backup 副本.db" 或 VACUUM INTO，见 23.4。
• 库放在网络共享盘或 NFS 上，偶发 database is locked、disk I/O error，甚至库损坏。 原因：网络文件系统的文件锁不可靠，WAL 要的共享内存跨机器也做不到。处理：库放本机磁盘；多台机器要访问同一份数据就换 PostgreSQL（第 21 章）。

23.7 深入阅读

• SQLite 文档：官方文档目录，SQL 语法、数据类型、文件格式、C 接口和各项限制都从这里进。
• sqlite3 命令行：全部点命令、各种输出模式、导入导出 CSV、.backup 和 .dump 的用法。
• 何时使用 SQLite（Appropriate Uses）：哪些场合 SQLite 够用，哪些场合该换成客户端/服务器式的数据库。
• WAL 模式：WAL 的工作方式、检查点、-wal 和 -shm 文件，以及为什么不能用在网络文件系统上。
• PRAGMA 语句：journal_mode、synchronous、foreign_keys、busy_timeout 等每个 PRAGMA 的取值和默认值。