第十章 使用Python操作数据库
目录
第十章 使用Python操作数据库
│
├── 1. 数据库编程接口 → 为什么用数据库 / PEP 249 / 七步流程 / 占位符 / 注入 / 异常
├── 2. 使用 SQLite → 连接 / 建表 / 增删改查 / 取值方式 / 事务 / 备份 / 什么时候该换
├── 3. 使用 MySQL → pymysql / 连接参数 / 完整 CRUD / 跟 SQLite 的五处差别
├── 4. 使用 PostgreSQL → psycopg / 连接 / RETURNING / ON CONFLICT / JSONB
├── 5. 使用 MongoDB → 文档型 / pymongo / 增删改查 / 聚合 / 什么时候该用
├── 6. 使用 Redis → 五种结构 / 过期 / 管道 / 缓存套路
├── 7. 实战 → 图书借阅系统 / 给热门榜加缓存
└── 8. 小结 → 知识清单 + 避坑清单 + 5 道题 + 下一章预告
照旧建个目录第十章`，本章示例都放里面跑。
>>> import os
>>> import sqlite3
>>> os.makedirs("chapter10", exist_ok=True)
>>> os.chdir("chapter10")
>>> os.getcwd().endswith("chapter10")
True
1. 数据库编程接口
1.1 文件不行在哪
先说清楚为什么要用数据库，不然学完只会觉得它麻烦。
| 文件（csv / json） | 数据库 | |
|---|---|---|
| 查一条数据 | 整个文件读进来，自己循环找 | 交给引擎，有索引时毫秒级 |
| 改一个字段 | 读全部、改一处、整体写回 | 一条 UPDATE 只动那一行 |
| 两个人同时写 | 互相覆盖，或者写坏文件 | 事务 + 锁，按规则排队 |
| 写到一半断电 | 文件半截，内容损坏 | 事务回滚到上一个一致状态 |
| 约束 | 靠自己在代码里判断 | 建表时声明，引擎兜底 |
| 数据量 | 几千行开始吃力 | 几百万行照常 |
最后两条是我最看重的。文件方案里，"库存不能为负""手机号不能重复"这些规矩全靠你记得写判断，漏一处就等着出事。数据库里写成 CHECK (stock >= 0) 和 UNIQUE，引擎替你看着，谁都绕不过去。
代价是多了一个要装、要连、要维护的东西。个人脚本、配置文件、一次性导出，用文件就够了，别为了用数据库而用数据库。
1.2 DB-API 是什么
Python 连数据库有套统一规矩，写在 PEP 249 里，大家叫它 DB-API 2.0。它规定了两件事，驱动作者该提供哪些对象和方法，以及这些方法该长什么样。
你的 Python 代码
│
│ 统一的一套调用：connect / cursor / execute / fetch / commit
▼
驱动程序（sqlite3、pymysql、psycopg…）
│
│ 各自的网络协议
▼
数据库（SQLite 文件、MySQL 服务、PostgreSQL 服务…）
好处很直接，学会一套写法，换数据库只要改驱动和 SQL 方言。坏处也很直接，这套规矩只管到"关系型数据库"，MongoDB 和 Redis 不在里面，它们各有各的客户端风格，第 5、6 节单独讲。
规矩里三个模块级变量，连上之前可以先看一眼。
>>> sqlite3.apilevel
'2.0'
>>> sqlite3.paramstyle
'qmark'
>>> sqlite3.threadsafety
3
apilevel 是这套规矩的版本，现在都是 2.0。paramstyle 最要紧，它决定占位符怎么写，下一小节专门讲。threadsafety 是线程安全等级，0 到 3，数字越大越安全，sqlite3 的 3 表示连接对象可以在线程之间共享。
1.3 七步流程
不管哪个数据库，DB-API 的流程都是这七步。先用 SQLite 走一遍。
① connect() 建立连接，拿到 Connection
② cursor() 从连接上开一个游标 Cursor
③ execute() 把 SQL 交给游标去执行
④ fetch*() 查询语句取回结果
⑤ commit() 写操作提交，不提交等于没做
⑥ rollback() 出错了回滚
⑦ close() 关游标、关连接
>>> con = sqlite3.connect("shop.db")
>>> cur = con.cursor()
>>> _ = cur.execute("DROP TABLE IF EXISTS goods")
>>> _ = cur.execute("""CREATE TABLE IF NOT EXISTS goods (
... id INTEGER PRIMARY KEY AUTOINCREMENT,
... name TEXT NOT NULL,
... price REAL DEFAULT 0,
... stock INTEGER DEFAULT 0)""")
>>> _ = cur.execute("INSERT INTO goods (name, price, stock) VALUES (?, ?, ?)", ("笔记本", 5.5, 100))
>>> con.commit()
>>> _ = cur.execute("SELECT id, name, price, stock FROM goods")
>>> cur.fetchall()
[(1, '笔记本', 5.5, 100)]
>>> cur.close()
>>> con.close()
这里有三处要停下来看。
第一，execute 的第二个参数是元组，哪怕只有一个值也要写成 ("笔记本", 5.5, 100) 这种形式。少写逗号，Python 就不把它当元组。
第二，_ = 的作用是把返回值接住。交互环境会把每条语句的返回值打出来，execute 返回游标自己，你会看到一串 <sqlite3.Cursor object at 0x...>，没什么用还挡视线。用 _ 接一下就干净了。
第三，commit() 一定别忘。SQLite 的写操作在提交之前只存在于当前连接里，别的连接看不到，程序一退出就全没了。我见过太多"明明插入成功，重新打开数据库却是空的"，八成是漏了这一行。
1.4 占位符的方言差异
把值拼进 SQL 有两种做法，差别大到值得单独开一节。
先说结论，永远用占位符，永远不要拼字符串。原因是 SQL 注入，1.5 节会演示。
麻烦在于占位符长什么样，各家不一样。
| 驱动 | paramstyle | 写法 | 用在哪个库 |
|---|---|---|---|
sqlite3 | qmark | WHERE id = ? | SQLite |
pymysql | format | WHERE id = %s | MySQL |
mysql-connector-python | pyformat | WHERE id = %(uid)s | MySQL |
psycopg2 / psycopg | pyformat | WHERE id = %s | PostgreSQL |
oracledb | named | WHERE id = :uid | Oracle |
pyformat 支持两种写法，%s 配元组，%(名字)s 配字典。psycopg2 里 %(name)s 也能用，但日常写 %s 更多。
>>> cur_dict = {"uid": 1}
>>> "SELECT * FROM goods WHERE id = %(uid)s" % cur_dict
'SELECT * FROM goods WHERE id = 1'
上面这行只是让你看清 pyformat 的样子，真代码里不许这么写，% 拼接和 f-string 是同一个性质的问题。
换数据库时最容易踩的就是这个。SQLite 写惯了 ?，换成 MySQL 忘改成 %s，报错信息还特别难懂。写个小抄贴在旁边，能省不少时间。
1.5 一次真实的注入
光说"有风险"没感觉，直接演一遍。建个用户表，两条数据。
>>> con = sqlite3.connect(":memory:")
>>> cur = con.cursor()
>>> _ = cur.execute("CREATE TABLE user (id INTEGER PRIMARY KEY, name TEXT, pwd TEXT)")
>>> _ = cur.executemany("INSERT INTO user (name, pwd) VALUES (?, ?)",
... [("admin", "a1b2c3"), ("张三", "123456")])
>>> con.commit()
现在有个登录函数，把用户名和密码拼进 SQL。
>>> name = "admin"
>>> pwd = "' OR '1'='1"
>>> sql = "SELECT * FROM user WHERE name='%s' AND pwd='%s'" % (name, pwd)
>>> print(sql)
SELECT * FROM user WHERE name='admin' AND pwd='' OR '1'='1'
'1'='1' 永远成立，于是整句变成"查 admin 并且密码是空，或者 1 等于 1"。后半截恒真，前面的密码检查被彻底架空。
>>> _ = cur.execute(sql)
>>> cur.fetchall()
[(1, 'admin', 'a1b2c3'), (2, '张三', '123456')]
两条记录全出来了，攻击者一条密码都没猜对就拿到了整张表。换成参数化，同样的输入什么也查不到。
>>> _ = cur.execute("SELECT * FROM user WHERE name=? AND pwd=?", (name, pwd))
>>> cur.fetchall()
[]
>>> _ = cur.execute("SELECT * FROM user WHERE name=? AND pwd=?", ("admin", "a1b2c3"))
>>> cur.fetchall()
[(1, 'admin', 'a1b2c3')]
区别在哪。字符串拼接时，你交给数据库的是"一句话"，数据库照着念。参数化时，你交给数据库的是"一个模板加一组值"，数据库先解析模板定好结构，再把值填进去，值永远是值，不会被当成 SQL 的一部分重新解析。'1'='1' 到了里面就是一个普通的字符串，跟密码字段比，比不上就是比不上。
还有一个限制要知道，占位符只能替值，不能替表名和列名。
>>> _ = cur.execute("SELECT * FROM ?", ("user",))
OperationalError: near "?": syntax error
这条报错按全书约定只留最后一行。想动态指定表名，只能用白名单过滤后拼接，别把用户输入直接放进去。
>>> allowed = {"user", "goods"}
>>> table = "user" if "user" in allowed else "goods"
>>> _ = cur.execute("SELECT count(*) FROM %s" % table)
>>> cur.fetchone()
(2,)
1.6 出错时抛什么
DB-API 规定了一组异常，各驱动都从这些类派生。层次是这样的。
Exception
├── Warning 非致命警告，比如插入时数据被截断
└── Error
├── InterfaceError 驱动本身的问题，跟数据库无关
└── DatabaseError 数据库那边出的问题
├── DataError 数据有问题，比如类型不对、除零
├── OperationalError 操作层面，连不上、表不存在、锁超时
├── IntegrityError 违反了约束，主键重复、外键对不上
├── InternalError 数据库内部错误
├── ProgrammingError SQL 写错、表已存在、参数个数不对
└── NotSupportedError 用了数据库不支持的功能
实跑确认一下这些类的继承关系。
>>> for n in ("DataError", "OperationalError", "IntegrityError"):
... print(n, "->", getattr(sqlite3, n).__mro__[1].__name__)
...
DataError -> DatabaseError
OperationalError -> DatabaseError
IntegrityError -> DatabaseError
平时最常碰上的是这三个。
>>> _ = con.execute("CREATE TABLE u (id INTEGER PRIMARY KEY, name TEXT UNIQUE)")
>>> _ = con.execute("INSERT INTO u (name) VALUES ('张三')")
>>> try:
... con.execute("INSERT INTO u (name) VALUES ('张三')")
... except sqlite3.IntegrityError as e:
... print("违反唯一约束:", e)
...
违反唯一约束: UNIQUE constraint failed: u.name
>>> try:
... con.execute("SELECT * FROM 不存在的表")
... except sqlite3.OperationalError as e:
... print("表不存在:", e)
...
表不存在: no such table: 不存在的表
写代码的时候别一股脑 except Exception，把 IntegrityError 单独拎出来能干很多事。比如注册时发现手机号重复，你可以给用户回一句"这个号码注册过了"，而不是丢一个 500 错误页。
2. 使用 SQLite
2.1 它是什么
SQLite 是个有点特殊的数据库，它不是一个服务，就是一个文件。
普通数据库 SQLite
┌──────────┐ ┌─────────────┐
│ 你的程序 │ │ 你的程序 │
└────┬─────┘ └──────┬──────┘
│ 网络 │ 直接读写文件
┌────┴─────┐ ┌──────┴──────┐
│ 数据库 │ │ app.db │
│ 服务进程 │ │ 单个文件 │
└──────────┘ └────────────┘
要装、要启动、要配账号 拷走就走，不用装
没有端口、没有用户名密码、没有"服务起没起来"这回事。Python 标准库自带 sqlite3 模块，import 就能用。手机 App、桌面软件、浏览器存数据用的基本都是它，全球部署量大概是所有数据库里最大的。
2.2 连上
>>> con = sqlite3.connect("demo.db")
>>> con.close()
>>> mem = sqlite3.connect(":memory:")
>>> mem.close()
两种连法。给个文件名就是文件数据库，文件不存在会自动创建；给 ":memory:" 是内存数据库，速度快，程序结束就没了，适合做测试和临时计算。
连接还有几个常用参数。
>>> con = sqlite3.connect("demo2.db", timeout=5.0, isolation_level=None)
>>> con.close()
timeout 是遇到锁时的等待秒数，默认 5 秒，超时抛 OperationalError: database is locked。isolation_level=None 是打开自动提交模式，每条语句立刻生效，不用手动 commit。
2.3 建表和它的类型
先建一张书目表。
>>> con = sqlite3.connect("demo.db")
>>> _ = con.execute("DROP TABLE IF EXISTS book")
>>> _ = con.execute("""CREATE TABLE book (
... id INTEGER PRIMARY KEY AUTOINCREMENT,
... isbn TEXT UNIQUE,
... title TEXT NOT NULL,
... price REAL DEFAULT 0,
... stock INTEGER DEFAULT 0 CHECK (stock >= 0))""")
>>> con.commit()
几个关键词。INTEGER PRIMARY KEY 在 SQLite 里就是自增主键，AUTOINCREMENT 可以省略，加上它保证 id 只增不复用。UNIQUE 是不许重复，NOT NULL 是不许为空，CHECK 是自定义条件，DEFAULT 是缺省值。
SQLite 的类型系统有点特别，它只有五个存储类型。
| 存储类型 | 能放什么 | 对应的 Python 类型 |
|---|---|---|
| NULL | 空 | None |
| INTEGER | 整数，按大小占 1 到 8 字节 | int |
| REAL | 浮点数 | float |
| TEXT | 文本，按编码存 | str |
| BLOB | 原始字节 | bytes、bytearray、memoryview |
写 VARCHAR(50) 也能建表，SQLite 会按类型亲和规则把它归到 TEXT。这是它宽容的地方，也是容易栽的地方，声明了长度它照样不检查。
>>> _ = con.execute("INSERT INTO book (isbn, title, price, stock) VALUES (?,?,?,?)", ("978-1", "Python入门", 59.9, 30))
>>> _ = con.execute("INSERT INTO book (isbn, title, price, stock) VALUES (?,?,?,?)", ("978-2", "Flask实战", 69.0, 12))
>>> con.commit()
>>> row = con.execute("SELECT isbn, title, price, stock FROM book WHERE id = 1").fetchone()
>>> [type(v).__name__ for v in row]
['str', 'str', 'float', 'int']
日期没有专门的类型。常见做法是按 ISO 8601 字符串存，写成 "2026-10-08"，排序和比较都正确。
>>> con.execute("SELECT '2026-09-20' < '2026-10-08'").fetchone()[0]
1
字符串比日期之所以对得上，是因为 ISO 格式从大到小排列，字典序恰好等于时间序。换成 "09/20/2026" 这种写法就全乱了。
顺带一句，Python 3.12 开始 sqlite3 默认的日期适配器已经标记弃用，直接往里塞 datetime 对象会看到 DeprecationWarning。自己转成字符串存最省心。
2.4 增
>>> cur = con.cursor()
>>> _ = cur.execute("INSERT INTO book (isbn, title, price, stock) VALUES (?,?,?,?)", ("978-3", "数据分析", 75.0, 20))
>>> cur.lastrowid
3
>>> cur.rowcount
1
>>> con.commit()
lastrowid 是刚插入那行的 id，只在自增主键上有意义。rowcount 是受影响的行数，插入时是 1，看 UPDATE 时它更有用。
一次插很多行用 executemany，别在循环里调 execute。
>>> batch = [("978-4", "算法图解", 45.0, 8),
... ("978-5", "网络爬虫", 52.0, 15),
... ("978-6", "Django实战", 88.0, 6)]
>>> _ = cur.executemany("INSERT INTO book (isbn, title, price, stock) VALUES (?,?,?,?)", batch)
>>> cur.rowcount
3
>>> con.commit()
>>> con.execute("SELECT COUNT(*) FROM book").fetchone()[0]
6
executemany 把整批数据一次交给引擎，省掉反复解析 SQL 的开销。数据量越大差得越多，几千行往上能差出一个数量级。
2.5 查
>>> con.execute("SELECT id, title, price FROM book WHERE price >= 60 ORDER BY price DESC").fetchall()
[(6, 'Django实战', 88.0), (3, '数据分析', 75.0), (2, 'Flask实战', 69.0)]
取结果有三种方式，游标像一根指针，取完就往下走。
>>> _ = cur.execute("SELECT title, price FROM book ORDER BY id")
>>> cur.fetchone()
('Python入门', 59.9)
>>> cur.fetchmany(2)
[('Flask实战', 69.0), ('数据分析', 75.0)]
>>> cur.fetchall()
[('算法图解', 45.0), ('网络爬虫', 52.0), ('Django实战', 88.0)]
>>> cur.fetchone() is None
True
fetchone 取一行并往下挪一格，fetchmany(n) 取 n 行，fetchall 取剩下的全部。取完再取就是 None 或空列表，不会自己回到开头。想重来一遍，重新 execute 一次。
条件、排序、分页、聚合，这些都是 SQL 本身的活。
>>> cur.execute("SELECT title FROM book WHERE title LIKE ? ORDER BY id", ("%实战%",)).fetchall()
[('Flask实战',), ('Django实战',)]
>>> cur.execute("SELECT title, price FROM book ORDER BY price LIMIT 2 OFFSET 1").fetchall()
[('网络爬虫', 52.0), ('Python入门', 59.9)]
>>> cur.execute("SELECT COUNT(*), ROUND(AVG(price), 2), MAX(stock) FROM book").fetchone()
(6, 64.82, 30)
注意每行末尾那个逗号，('Flask实战',) 是只有一个元素的元组，不是字符串。这个细节在处理结果时经常让人卡住。
LIMIT 加 OFFSET 是分页的标准写法，第一页 LIMIT 20 OFFSET 0，第二页 OFFSET 20。数据量大到一定程度 OFFSET 会越来越慢，那时候改成用上一页最后一条的 id 做条件。
2.6 结果行怎么变成字典
默认每一行是元组，取字段得数位置。id 是第 0 个，title 是第 1 个，表结构一改，代码里的下标就全错了。
设一下 row_factory 能解决。
>>> con_row = sqlite3.connect("demo.db")
>>> con_row.row_factory = sqlite3.Row
>>> row = con_row.execute("SELECT id, title, price FROM book WHERE id = 1").fetchone()
>>> row["title"]
'Python入门'
>>> row[1]
'Python入门'
>>> tuple(row)
(1, 'Python入门', 59.9)
>>> list(row.keys())
['id', 'title', 'price']
>>> dict(row)
{'id': 1, 'title': 'Python入门', 'price': 59.9}
sqlite3.Row 既支持下标也支持字段名，转成 dict 之后能直接喂给 json.dumps。写查询时 row["title"] 比 row[1] 好懂得多。
有一点要提醒，print(row) 打出来的是 <sqlite3.Row object at 0x...> 这种对象地址，看不见内容，Row 没有实现好看的 repr。想看内容就转 dict 或 tuple，上面代码里就是这么做的。
row_factory 要在取游标之前设好，一般紧跟在 connect 后面写一行。
2.7 改和删
>>> con.execute("UPDATE book SET price = 39.9, stock = stock - 3 WHERE id = 4").rowcount
1
>>> con.execute("UPDATE book SET price = price * 0.9 WHERE stock > 10").rowcount
4
>>> con.execute("SELECT title, price FROM book WHERE id = 4").fetchone()
('算法图解', 39.9)
>>> con.execute("DELETE FROM book WHERE id = 6").rowcount
1
>>> con.execute("SELECT COUNT(*) FROM book").fetchone()[0]
5
>>> con.commit()
UPDATE 和 DELETE 一定要带 WHERE。我见过 UPDATE user SET vip = 1 这种漏了条件的语句，执行完整个表都变了。写这类语句时先写 WHERE 再写 SET，手滑了顶多是语法错，不会误伤数据。
价格打九折那一条改了 4 行，因为第一个 UPDATE 已经把算法图解的库存降到 5，它退出了 stock > 10 的范围。写多条 UPDATE 时留意顺序，第二条看到的是第一条改完之后的数据。
2.8 事务
事务的核心就一句话，一组操作要么全成，要么全不做。转账是最典型的例子。
>>> con3 = sqlite3.connect("demo.db")
>>> _ = con3.execute("DROP TABLE IF EXISTS acct")
>>> _ = con3.execute("CREATE TABLE acct (name TEXT PRIMARY KEY, bal INTEGER)")
>>> _ = con3.executemany("INSERT INTO acct VALUES (?,?)", [("张三", 1000), ("李四", 500)])
>>> con3.commit()
>>> def transfer(a, b, amount):
... con3.execute("UPDATE acct SET bal = bal - ? WHERE name = ?", (amount, a))
... con3.execute("UPDATE acct SET bal = bal + ? WHERE name = ?", (amount, b))
... bal = con3.execute("SELECT bal FROM acct WHERE name = ?", (a,)).fetchone()[0]
... if bal < 0:
... raise ValueError("余额不足，转出方会变负数")
... con3.commit()
... return "转了 %d 元" % amount
...
>>> transfer("张三", "李四", 200)
'转了 200 元'
>>> con3.execute("SELECT name, bal FROM acct ORDER BY name").fetchall()
[('张三', 800), ('李四', 700)]
张三在前是因为排序按 Unicode 码位，张 是 U+5F20，李 是 U+674E，张小。这个细节在前面章节讲字符串比较时提过。
现在转一笔超额的钱。
>>> try:
... transfer("张三", "李四", 5000)
... except ValueError as e:
... con3.rollback()
... print("失败:", e)
...
失败: 余额不足，转出方会变负数
>>> con3.execute("SELECT name, bal FROM acct ORDER BY name").fetchall()
[('张三', 800), ('李四', 700)]
钱没少，因为 rollback() 把那两条 UPDATE 撤销了。漏掉这句的话，张三的余额会停在 -4200，钱凭空消失。
with 语句能替你管这件事。
>>> with con3:
... _ = con3.execute("UPDATE acct SET bal = bal + 100 WHERE name = '张三'")
...
>>> con3.execute("SELECT bal FROM acct WHERE name = '张三'").fetchone()[0]
900
>>> try:
... with con3:
... _ = con3.execute("UPDATE acct SET bal = bal + 100 WHERE name = '张三'")
... raise RuntimeError("后面的步骤炸了")
... except RuntimeError as e:
... print("异常:", e)
...
异常: 后面的步骤炸了
>>> con3.execute("SELECT bal FROM acct WHERE name = '张三'").fetchone()[0]
900
with con3: 正常走完自动 commit，中途抛异常自动 rollback。第二次明明执行了加 100，退出时回滚，余额还是 900。这个写法我现在几乎不手动写 commit 了，省心。
它有个坑，with 只管事务不管连接，退出 with 之后连接还开着，最后记得 con.close()。
2.9 看表结构和做备份
库里有哪些表，表里有哪些列，SQLite 存在一张系统表里。
>>> con3.execute("SELECT type, name FROM sqlite_master ORDER BY type, name").fetchall()
[('index', 'sqlite_autoindex_acct_1'), ('index', 'sqlite_autoindex_book_1'), ('table', 'acct'), ('table', 'book'), ('table', 'sqlite_sequence')]
>>> con3.execute("PRAGMA table_info(book)").fetchall()
[(0, 'id', 'INTEGER', 0, None, 1), (1, 'isbn', 'TEXT', 0, None, 0), (2, 'title', 'TEXT', 1, None, 0), (3, 'price', 'REAL', 0, '0', 0), (4, 'stock', 'INTEGER', 0, '0', 0)]
sqlite_master 是全库对象的清单，PRAGMA table_info(表名) 是某张表的列定义，六个字段依次是序号、列名、类型、是否非空、默认值、是否主键。写数据库管理工具时就用这两个。
清单里那两个 sqlite_autoindex 是 UNIQUE 约束自动建的索引，sqlite_sequence 是 AUTOINCREMENT 用来记数的表，都不是你自己建的，看见别奇怪。
备份别复制文件，用 backup() 更稳。
>>> import os
>>> bak = sqlite3.connect("demo_backup.db")
>>> con3.backup(bak)
>>> bak.close()
>>> os.path.exists("demo_backup.db")
True
>>> check = sqlite3.connect("demo_backup.db")
>>> check.execute("SELECT COUNT(*) FROM acct").fetchone()[0]
2
>>> check.execute("SELECT bal FROM acct WHERE name = '张三'").fetchone()[0]
900
>>> check.close()
直接 shutil.copy 数据库文件有个前提，得保证这会儿没有人正在写。有人写的时候拷，拷出来的文件可能是上半截旧数据加下半截新数据。backup() 走引擎内部的备份接口，边写边备也不会出问题。
2.10 什么时候该换掉它
SQLite 好用，但它的边界很清楚。
| 场景 | 判断 |
|---|---|
| 单机程序、桌面软件、手机 App | 正合适 |
| 小网站，日访问几千 | 够用 |
| 数据量几百万行以内 | 够用 |
| 多个进程同时写 | 吃力，它写的时候锁整库 |
| 需要跨机器访问 | 不行，它只能本机读写文件 |
| 需要用户权限管理 | 没有，拿到文件就能读全部 |
| 数据量上千万、要分库分表 | 换 MySQL 或 PostgreSQL |
判断标准其实就一条，有没有多个进程要同时写。有，就换。
3. 使用 MySQL
SQLite 那套写法学会之后，换 MySQL 主要改三处，驱动名、连接参数、占位符。剩下的大部分代码原样搬。
3.1 装哪个驱动
MySQL 有好几个 Python 驱动，挑一个就行。
| 驱动 | 安装命令 | 特点 |
|---|---|---|
| PyMySQL | pip install PyMySQL | 纯 Python，装起来最省事，社区用得多 |
| mysqlclient | pip install mysqlclient | C 扩展，速度最快，Windows 上可能要装编译工具 |
| mysql-connector-python | pip install mysql-connector-python | Oracle 官方维护，纯 Python |
我一般用 PyMySQL，不为别的，就是 pip install 从没失败过。要性能的场景换 mysqlclient，接口基本兼容。
后面的代码以 PyMySQL 为例。
3.2 连上
# mysql_demo.py
import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="123456",
database="shop",
charset="utf8mb4",
cursorclass=DictCursor, # 每行返回 dict，等价于 sqlite3.Row
autocommit=False, # 关掉自动提交，自己控制事务
)
print(conn.server_version)
conn.close()
跟 SQLite 比，connect 的参数从"一个文件名"变成"一整套地址加账号"。另外 cursorclass=DictCursor 这一句值得加，不加的话每行是元组，加了就是字典，取字段写 row["title"]。
3.3 增删改查
下面这份是完整能跑的，建表、插数据、查、改、删都在里面。
# mysql_crud.py
import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(host="127.0.0.1", user="root", password="123456",
database="shop", charset="utf8mb4",
cursorclass=DictCursor, autocommit=False)
CREATE_SQL = """
CREATE TABLE IF NOT EXISTS goods (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL DEFAULT 0,
stock INT NOT NULL DEFAULT 0,
KEY idx_title (title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
try:
with conn.cursor() as cur:
cur.execute(CREATE_SQL)
cur.execute("INSERT INTO goods (title, price, stock) VALUES (%s, %s, %s)",
("Python入门", "59.90", 30))
print("新记录 id =", cur.lastrowid)
cur.executemany("INSERT INTO goods (title, price, stock) VALUES (%s, %s, %s)",
[("Flask实战", "69.00", 12), ("数据分析", "75.00", 20)])
cur.execute("SELECT id, title, price FROM goods WHERE stock > %s ORDER BY price DESC", (10,))
for row in cur.fetchall():
print(row)
cur.execute("UPDATE goods SET stock = stock - %s WHERE title = %s", (5, "Python入门"))
print("改动行数 =", cur.rowcount)
cur.execute("DELETE FROM goods WHERE stock <= %s", (0,))
conn.commit()
except Exception as e:
conn.rollback()
raise
finally:
conn.close()
with conn.cursor() as cur 会自动关游标，跟 with open() 一个道理。事务还是得自己 commit。
3.4 跟 SQLite 不一样的五处
这五处是我换过去时逐个踩过的。
一，占位符是 %s。SQLite 的 ? 在 MySQL 里不认，报的错还跟占位符一点关系都没有，很难往这边想。
二，字符集写 utf8mb4，别写 utf8。MySQL 里的 utf8 是残缺的，最多三个字节，存不了 emoji 和部分生僻汉字，插入时直接报 Incorrect string value。真 UTF-8 得写 utf8mb4。
三，金额用 DECIMAL，别用 FLOAT。0.1 加 0.2 在二进制浮点里等于 0.30000000000000004，账目上迟早出问题。Python 侧传字符串进去，取出来 PyMySQL 会转成 Decimal 对象，用 decimal.Decimal 做加减。
四，连接会自己断。MySQL 默认 8 小时没动静就掐掉连接，第二天早上第一条 SQL 会报 2006, MySQL server has gone away。查之前先探一下。
# mysql_ping.py
def get_cursor(conn):
conn.ping(reconnect=True) # 断了就重连，没断就什么都不做
return conn.cursor()
生产环境一般用连接池，比如 DBUtils 或者 SQLAlchemy 自带的池，让池子去管重连和回收。
五，MySQL 的 DDL 会隐式提交。CREATE TABLE、ALTER TABLE 执行完，前面没提交的事务直接就提交了，没法回滚。SQLite 的 DDL 在事务里是能回滚的，这一点差异写迁移脚本时会咬人。
3.5 几个常见报错
| 报错 | 意思 | 怎么办 |
|---|---|---|
2003, Can't connect to MySQL server | 连不上，服务没起或者端口不通 | 看服务有没有起，防火墙有没有放行 |
1045, Access denied for user | 账号密码错，或者没授权从这台机器连 | 检查用户权限，root 默认只允许 localhost |
1049, Unknown database | 库不存在 | 先 CREATE DATABASE，或者改 database 参数 |
2006, MySQL server has gone away | 连接超时被掐，或者包太大 | 用 ping(reconnect=True)，大字段调 max_allowed_packet |
1062, Duplicate entry | 撞了唯一约束 | 对应 IntegrityError，按业务逻辑处理 |
1213, Deadlock found | 死锁，两个事务互相等 | 重试一次，一般就过了 |
2013, Lost connection during query | 查询执行太久被断 | 优化 SQL，或者调 wait_timeout |
4. 使用 PostgreSQL
PostgreSQL 这两年用得越来越多，原因大概是它对 SQL 标准的支持更完整，且有些功能别家真没有。Python 这边连它，主流选择是 psycopg。
4.1 装驱动
pip install "psycopg[binary]"
[binary] 这个方括号是 pip 的"额外依赖"写法，装的是打包好二进制的版本，不用自己编译 C 库。老项目里常见的是 psycopg2-binary，接口几乎一样，新项目直接用 psycopg 3。
4.2 连上
# pg_demo.py
import psycopg
conn = psycopg.connect(
host="127.0.0.1",
port=5432,
dbname="shop",
user="postgres",
password="123456",
)
cur = conn.execute("SELECT version()")
print(cur.fetchone()[0])
conn.close()
psycopg 3 有个贴心的地方，连接对象自带 execute，不用先 cursor()，简单查询少写一行。
参数名跟 PyMySQL 略有不同，库名是 dbname 不是 database，用户名是 user。我第一次写的时候少了个字母，报的错是密码错误，查了半天。
4.3 增删改查
# pg_crud.py
import psycopg
conn = psycopg.connect("dbname=shop user=postgres password=123456 host=127.0.0.1")
conn.execute("""
CREATE TABLE IF NOT EXISTS goods (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
price NUMERIC(10, 2) NOT NULL DEFAULT 0,
stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0)
)
""")
conn.execute("INSERT INTO goods (title, price, stock) VALUES (%s, %s, %s)",
("Python入门", "59.90", 30))
cur = conn.execute("SELECT id, title, price FROM goods WHERE stock > %s ORDER BY price", (10,))
print(cur.fetchall())
conn.execute("UPDATE goods SET stock = stock - %s WHERE title = %s", (5, "Python入门"))
conn.commit()
conn.close()
上面第二行那个字符串是 libpq 的连接串格式，psycopg 支持，写配置文件时挺方便。
占位符同样是 %s，跟 PyMySQL 一致，从 MySQL 迁过来这一处不用改。
4.4 RETURNING，一条语句拿到新行
插入之后想立刻知道新行的 id，SQLite 里有 lastrowid。每家数据库都支持各自的取法，PostgreSQL 给的是一个更通用的写法。
>>> con4 = sqlite3.connect(":memory:")
>>> _ = con4.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT, score REAL)")
>>> _ = con4.executemany("INSERT INTO t (name, score) VALUES (?,?)", [("张三", 88.5), ("李四", 92.0)])
>>> con4.execute("INSERT INTO t (name, score) VALUES (?,?) RETURNING *", ("王五", 80.0)).fetchall()
[(3, '王五', 80.0)]
这段代码用的是 SQLite，因为 SQLite 从 3.35 开始也支持 RETURNING，你现在就能敲一遍。PostgreSQL 上写法完全一样，只是占位符换成 %s。
RETURNING 后面可以跟 *，也可以指定列，UPDATE 和 DELETE 也能用。
>>> con4.execute("UPDATE t SET score = score + 5 WHERE name = '王五' RETURNING id, score").fetchone()
(3, 85.0)
>>> con4.execute("DELETE FROM t WHERE id = 3 RETURNING name, score").fetchone()
('王五', 85.0)
最后一句拿到的正是被删掉那行的内容。做"删除并返回"这种需求时特别顺手，不用先 SELECT 再 DELETE，也就不会有中间被别人改掉的风险。
4.5 ON CONFLICT，存在就改不存在就插
这个需求太常见了，计数、累加、初始化配置都会碰到。以前的写法是先查有没有，有就 UPDATE，没有就 INSERT，三步。
>>> _ = con4.execute("CREATE TABLE u (email TEXT UNIQUE, hits INTEGER DEFAULT 0)")
>>> _ = con4.execute("INSERT INTO u (email) VALUES (?)", ("a@x.com",))
>>> _ = con4.execute("""INSERT INTO u (email, hits) VALUES (?, ?)
... ON CONFLICT(email) DO UPDATE SET hits = hits + 1""", ("a@x.com", 0))
>>> con4.execute("SELECT * FROM u").fetchall()
[('a@x.com', 1)]
第一次插入时没有冲突，hits 是 0。再来一次同样的语句，冲突触发，hits 变成 1。
>>> _ = con4.execute("INSERT INTO u (email) VALUES (?) ON CONFLICT(email) DO NOTHING", ("a@x.com",))
>>> con4.execute("SELECT * FROM u").fetchall()
[('a@x.com', 1)]
DO NOTHING 是冲突了就什么都不做，也不报错。初始化数据时最省心，跑多少遍结果都一样。
MySQL 里对应的写法是 INSERT ... ON DUPLICATE KEY UPDATE，或者 MySQL 8 之后也支持 INSERT ... ON DUPLICATE，语法不一样但思路相同。SQLite 3.24 以上支持上面这套，所以能本地验证。
4.6 JSONB，能索引的 JSON
PostgreSQL 有个 JSONB 类型，存进去是二进制 JSON，能在里面建索引、按字段查。存配置、存结构不固定的扩展字段，用它比建一堆空列干净。
# pg_jsonb.py
import psycopg
conn = psycopg.connect("dbname=shop user=postgres password=123456 host=127.0.0.1")
conn.execute("""
CREATE TABLE IF NOT EXISTS device (
id BIGSERIAL PRIMARY KEY,
payload JSONB NOT NULL
)
""")
conn.execute("INSERT INTO device (payload) VALUES (%s)",
('{"cpu": 8, "os": "linux", "tags": ["x86", "ssd"]}',))
cur = conn.execute("SELECT payload->>'os', payload->'cpu' FROM device")
print(cur.fetchall()) # [('linux', 8)]
cur = conn.execute("SELECT id FROM device WHERE payload->>'os' = %s", ("linux",))
print(cur.fetchall()) # [(1,)]
conn.execute("CREATE INDEX IF NOT EXISTS idx_device_os ON device ((payload->>'os'))")
conn.commit()
conn.close()
->> 取出的是文本，-> 取出的是 JSON 值，这两个箭头记牢就行。最后那条给 JSON 内部字段建索引的写法，是 JSONB 相比普通 TEXT 存 JSON 最大的优势。
SQLite 也有 JSON 函数，能用但没这么强。它把 JSON 当文本存，查询用 json_extract。
>>> _ = con4.execute("CREATE TABLE cfg (id INTEGER, payload TEXT)")
>>> _ = con4.execute("INSERT INTO cfg VALUES (1, ?)", ('{"cpu": 8, "os": "linux"}',))
>>> con4.execute("SELECT json_extract(payload, '$.cpu') FROM cfg").fetchone()
(8,)
>>> con4.execute("SELECT json_extract(payload, '$.os') FROM cfg").fetchone()
('linux',)
4.7 跟 MySQL 怎么选
这个问题没有标准答案，我把自己做选择时看的几条列出来。
| 看什么 | 选 MySQL | 选 PostgreSQL |
|---|---|---|
| 团队熟悉哪个 | 熟 MySQL 就 MySQL | 熟 PG 就 PG |
| 需要 JSONB、数组、自定义类型 | 这类需求 PG 强一截 | |
| 复杂查询、窗口函数、CTE | 8.0 之后也都有了 | 支持更早更完整 |
| 全文检索中文 | 要配分词插件 | 内置能力更强，中文仍需插件 |
| 生态和云服务 | 到处都是，资料最多 | 云厂商支持也很全了 |
| 简单读写为主 | 用的人多，招人容易 |
如果是新项目且没有历史包袱，我倾向 PostgreSQL。如果团队里已经有 MySQL 的运维经验，那就别折腾，迁移成本比功能差异重要得多。
5. 使用 MongoDB
前面四个都是关系型数据库，得先定好表结构才能存数据。MongoDB 是文档型的，它反过来，你来什么它存什么。
5.1 文档跟表有什么不一样
拿一条书目数据对比一下就清楚了。
关系型（MySQL / PostgreSQL / SQLite） 文档型（MongoDB）
表 books 集合 books
┌────┬──────────┬──────┬────────┐ ┌────────────────────────────┐
│ id │ title │ price│ author │ │ { │
├────┼──────────┼──────┼────────┤ │ _id: ObjectId(...), │
│ 1 │Python入门│ 59.9 │ 李明 │ │ title: "Python入门", │
│ 2 │Flask实战 │ 69.0 │ 王芳 │ │ price: 59.9, │
└────┴──────────┴──────┴────────┘ │ author: "李明", │
│ tags: ["编程", "入门"], │
每行列数固定，改结构要 ALTER TABLE │ addr: {city: "杭州"} │
│ } │
└────────────────────────────┘
每条文档字段可以不一样，
想加字段直接加
术语对应一下。
| 关系型 | MongoDB | 说明 |
|---|---|---|
| 库 database | 库 database | 一样 |
| 表 table | 集合 collection | |
| 行 row | 文档 document | |
| 列 column | 字段 field | |
| 主键 id | _id | MongoDB 自动生成 |
| SQL | 查询条件（字典） | 不用写 SQL，传字典 |
文档可以嵌套，一条书里能直接带上标签数组和地址对象。这在关系型里得拆成三张表再 JOIN，MongoDB 里一次取出来。
5.2 装和连
pip install pymongo
# mongo_demo.py
from pymongo import MongoClient
client = MongoClient("mongodb://localhost:27017/")
db = client["shop"] # 库
books = db["books"] # 集合
print(client.server_info()["version"])
print(books.count_documents({}))
client.close()
有个容易懵的地方，库和集合都不用提前建，第一次往里写数据就自动有了。db["books"] 写错了名字不会报错，只会安安静静建一个新的空集合。我第一次用的时候往 db["book"] 里插了两万条，查了半天为什么 db["books"] 是空的。
5.3 增删改查
# mongo_crud.py
from pymongo import MongoClient, ASCENDING
client = MongoClient("mongodb://localhost:27017/")
books = client["shop"]["books"]
books.insert_many([
{"title": "Python入门", "price": 59.9, "stock": 30,
"tags": ["编程", "入门"], "addr": {"city": "杭州", "shop": "A"}},
{"title": "Flask实战", "price": 69.0, "stock": 12,
"tags": ["编程", "Web"], "addr": {"city": "上海", "shop": "B"}},
{"title": "数据分析", "price": 75.0, "stock": 20,
"tags": ["数据"], "addr": {"city": "杭州", "shop": "A"}},
])
print(books.count_documents({})) # 3
print(books.find_one({"title": "Python入门"}))
cur = books.find({"price": {"$gte": 60}}, {"title": 1, "price": 1}).sort("price", ASCENDING)
for doc in cur:
print(doc)
books.update_one({"title": "Flask实战"}, {"$set": {"price": 39.9}, "$inc": {"sold": 1}})
books.delete_many({"stock": {"$lt": 15}})
books.create_index([("title", ASCENDING)], unique=True)
client.close()
几个要点。查询条件是一个字典，{"price": {"$gte": 60}} 里的 $gte 是大于等于，类似的还有 $gt $lt $lte $ne $in。第二个参数是投影，{"title": 1, "price": 1} 表示只要这两个字段，_id 默认会带回来，不想要就写 {"_id": 0}。
find 返回的是游标，不是列表。它一条一条往外吐，数据量大时不会一次性占满内存。要当列表用就 list(cur)。
5.4 本地没有 MongoDB 也能练
替身是这么搭的，一个列表存文档，查询条件照着 pymongo 的样子解析。整段复制过去能跑，能看清每个查询返回的东西长什么样。等你装好了真 MongoDB，把 Collection 换成 db["books"]，条件写法一模一样。
# -*- coding: utf-8 -*-
"""用列表 + 字典模拟一个 MongoDB 集合，看清文档查询到底在比什么。
真正的写法见本节后面的 pymongo 示例，查询条件长得一模一样。
"""
OPS = {"gte": lambda a, b: a >= b, "gt": lambda a, b: a > b,
"lte": lambda a, b: a <= b, "lt": lambda a, b: a < b,
"ne": lambda a, b: a != b,
"in": lambda a, b: a in b, "nin": lambda a, b: a not in b}
class Collection:
def __init__(self, name):
self.name = name
self.docs = []
self._seq = 0
def _new_id(self):
self._seq += 1
return "ObjectId(%024d)" % self._seq
def insert_many(self, docs):
for d in docs:
d = dict(d)
d["_id"] = self._new_id()
self.docs.append(d)
return [d["_id"] for d in self.docs[-len(docs):]]
def find(self, cond=None, projection=None):
"""cond 形如 {"age": {"$gte": 18}, "addr.city": "杭州"}"""
cond = cond or {}
out = []
for d in self.docs:
if all(self._match(d, k, v) for k, v in cond.items()):
out.append(self._project(d, projection))
return Cursor(out)
def _value_of(self, doc, path):
"""把 "addr.city" / "$addr.city" 这样的路径取成值"""
cur = doc
for part in path.lstrip("$").split("."):
if not isinstance(cur, dict) or part not in cur:
return None
cur = cur[part]
return cur
def _match(self, doc, key, want):
cur = doc
for part in key.split("."):
if not isinstance(cur, dict) or part not in cur:
return False
cur = cur[part]
if isinstance(want, dict): # {"$gte": 18} 这类运算符
return all(OPS[op.lstrip("$")](cur, val) for op, val in want.items())
return cur == want
def _project(self, doc, projection):
if not projection:
return dict(doc)
keep = [k for k, v in projection.items() if v]
if not keep: # {"_id": 0} 这种只排除的写法
keep = [k for k in doc if k not in projection]
return {k: doc[k] for k in keep if k in doc}
def update_one(self, cond, update):
hit = self.find(cond).to_list()
if not hit:
return 0
target = hit[0]
for d in self.docs:
if d["_id"] == target["_id"]:
self._apply(d, update)
return 1
return 0
def update_many(self, cond, update):
ids = {d["_id"] for d in self.find(cond).to_list()}
for d in self.docs:
if d["_id"] in ids:
self._apply(d, update)
return len(ids)
def _apply(self, doc, update):
for op, fields in update.items():
if op == "$set":
doc.update(fields)
elif op == "$inc":
for k, v in fields.items():
doc[k] = doc.get(k, 0) + v
elif op == "$push":
for k, v in fields.items():
doc.setdefault(k, []).append(v)
def delete_many(self, cond):
ids = {d["_id"] for d in self.find(cond).to_list()}
self.docs = [d for d in self.docs if d["_id"] not in ids]
return len(ids)
def count_documents(self, cond=None):
return len(self.find(cond).to_list())
def aggregate(self, pipeline):
"""只支持 $match + $group + $sort 三段，够演示用"""
rows = [dict(d) for d in self.docs]
for stage in pipeline:
for op, spec in stage.items():
if op == "$match":
rows = [d for d in rows
if all(self._match(d, k, v) for k, v in spec.items())]
elif op == "$group":
buckets = {}
for d in rows:
key = self._value_of(d, spec["_id"])
acc = buckets.setdefault(key, {"_id": key, "cnt": 0, "sum": 0})
acc["cnt"] += 1
acc["sum"] = round(acc["sum"] + d.get("price", 0), 2)
rows = list(buckets.values())
elif op == "$sort":
for field, direction in spec.items():
rows.sort(key=lambda d: d[field], reverse=direction < 0)
return Cursor(rows)
class Cursor:
def __init__(self, rows):
self.rows = rows
def sort(self, key, direction=1):
self.rows.sort(key=lambda d: d[key], reverse=direction < 0)
return self
def limit(self, n):
self.rows = self.rows[:n]
return self
def to_list(self):
return self.rows
def __iter__(self):
return iter(self.rows)
def __repr__(self):
return "<Cursor %d docs>" % len(self.rows)
books = Collection("books")
books.insert_many([
{"title": "Python入门", "price": 59.9, "stock": 30, "tags": ["编程", "入门"],
"addr": {"city": "杭州", "shop": "A"}},
{"title": "Flask实战", "price": 69.0, "stock": 12, "tags": ["编程", "Web"],
"addr": {"city": "上海", "shop": "B"}},
{"title": "数据分析", "price": 75.0, "stock": 20, "tags": ["数据"],
"addr": {"city": "杭州", "shop": "A"}},
{"title": "算法图解", "price": 45.0, "stock": 8, "tags": ["编程", "算法"],
"addr": {"city": "北京", "shop": "C"}},
])
print("插入了", books.count_documents(), "条")
print(books.find())
print("价格不低于 60 的：")
print(books.find({"price": {"$gte": 60}}, {"title": 1, "price": 1}).to_list())
print("杭州的书（嵌套字段用点号）：")
print(books.find({"addr.city": "杭州"}, {"title": 1, "price": 1}).to_list())
print("库存最少的三本：")
print(books.find({}, {"title": 1, "stock": 1}).sort("stock", 1).limit(3).to_list())
print("先挑出编程类，再按城市统计数量和总价：")
print(books.aggregate([
{"$match": {"price": {"$gte": 40}}},
{"$group": {"_id": "$addr.city"}},
{"$sort": {"sum": -1}},
]).to_list())
print("更新：")
print(books.update_one({"title": "算法图解"}, {"$set": {"price": 39.9}, "$inc": {"sold": 1}}))
print(books.find({"title": "算法图解"}, {"title": 1, "price": 1, "sold": 1}).to_list())
print("删除：")
print(books.delete_many({"addr.city": "北京"}), "剩余", books.count_documents())
跑出来的结果。
插入了 4 条
<Cursor 4 docs>
价格不低于 60 的：
[{'title': 'Flask实战', 'price': 69.0}, {'title': '数据分析', 'price': 75.0}]
杭州的书（嵌套字段用点号）：
[{'title': 'Python入门', 'price': 59.9}, {'title': '数据分析', 'price': 75.0}]
库存最少的三本：
[{'title': '算法图解', 'stock': 8}, {'title': 'Flask实战', 'stock': 12}, {'title': '数据分析', 'stock': 20}]
先挑出编程类，再按城市统计数量和总价：
[{'_id': '杭州', 'cnt': 2, 'sum': 134.9}, {'_id': '上海', 'cnt': 1, 'sum': 69.0}, {'_id': '北京', 'cnt': 1, 'sum': 45.0}]
更新：
1
[{'title': '算法图解', 'price': 39.9, 'sold': 1}]
删除：
1 剩余 3
看几个细节。find() 打印出来是 <Cursor 4 docs>，它不直接给你数据，要 to_list() 或者迭代。真 pymongo 的游标也是这个脾气。
嵌套字段用点号访问，{"addr.city": "杭州"} 这样写就能查到城市。这是 MongoDB 里最方便的特性之一，关系型里得 JOIN 才能做同样的事。
聚合管道是一串阶段，前一阶段的输出喂给后一阶段。上面那段先 $match 过滤，再 $group 按城市分组，最后 $sort 排序。$group 的 _id 是分组依据，写 "$addr.city" 表示取这个字段的值。
5.5 什么时候用它
| 场景 | 判断 |
|---|---|
| 数据结构老在变，字段不固定 | 合适，不用改表 |
| 一条数据里天然带嵌套，比如商品带多个规格 | 合适，一次取完 |
| 需要跨表事务、强一致 | 不合适，虽然新版本支持了事务，但别拿它当主账本 |
| 报表、复杂关联查询 | 不合适，关系型的 SQL 更顺手 |
| 日志、埋点、内容管理 | 合适，很常见的用法 |
我的经验是，结构稳定、要算钱，用关系型；结构多变、读多写少，用 MongoDB。两者混着用也很常见，订单放 MySQL，订单的扩展属性放 MongoDB。
6. 使用 Redis
Redis 跟前面几个不是一类东西。它把数据放在内存里，读写都是微秒级，通常不当主存储，当缓存、当队列、当计数器。
6.1 它存的是五种结构
Redis 的 key 都是字符串，值可以是五种类型。
key value 类型 样子 常见用途
───────────────────────────────────────────────────────────────────────
"site" → String → "python.org" 缓存单个值、计数器
数字也能存，可以 INCR
"queue:tasks" → List → ["urgent","t1","t2"] 队列、最新列表
两头都能进能出
"user:1001" → Hash → {"name":"张三","city":"杭州"}
存对象的字段，能单独改一个字段
"article:7:lk" → Set → {"u1","u2","u3"} 去重、共同好友、点赞
无序，元素唯一
"rank:score" → ZSet → {"李四":95,"张三":91}
带分数的集合，自动排好序，排行榜
选哪种结构决定了后面好不好写。比如存用户信息，用 String 存一整个 JSON 是最简单的，但想改其中一个字段就得整体读出来改完再写回去；用 Hash 可以直接 hincrby 改一个字段。
6.2 装和连
pip install redis
# redis_demo.py
import redis
r = redis.Redis(host="localhost", port=6379, db=0, decode_responses=True)
r.set("site", "python.org")
print(r.get("site"))
print(r.ttl("site"))
decode_responses=True 这一句我每次都加。不加的话取出来是 bytes，中文会变成 b'\xe5\xbc\xa0\xe4\xb8\x89' 这种，还得自己 decode。
db=0 是 Redis 的库编号，默认 16 个库，编号 0 到 15，互相隔离。当缓存用的话一般就用 0。
6.3 没装 Redis 也能先把命令敲熟
替身只有一个类，方法名照抄 redis-py，数据存在字典里。整段复制过去能跑，每个命令返回什么看得清清楚楚。装好真 Redis 之后，把 r = FakeRedis(decode_responses=True) 换成 r = redis.Redis(host="localhost", port=6379, db=0, decode_responses=True)，下面所有调用一行都不用改。
# -*- coding: utf-8 -*-
"""Redis 命令的本地替身：方法名跟 redis-py 一致，数据存在内存字典里。
真装了 Redis 之后，把 r = FakeRedis() 换成
r = redis.Redis(host="localhost", port=6379, db=0, decode_responses=True)，
下面所有调用一行都不用改。
"""
class FakeRedis:
"""decode_responses 的意思跟真客户端一样：True 返回字符串，False 返回字节。"""
def __init__(self, decode_responses=False):
self._data = {}
self._ttl = {}
self.dec = decode_responses
def _out(self, v):
if self.dec:
return v
if isinstance(v, str):
return v.encode("utf-8")
if isinstance(v, list):
return [x.encode("utf-8") if isinstance(x, str) else x for x in v]
if isinstance(v, set):
return {x.encode("utf-8") for x in v}
if isinstance(v, dict):
return {(k.encode("utf-8") if isinstance(k, str) else k):
(x.encode("utf-8") if isinstance(x, str) else x)
for k, x in v.items()}
if isinstance(v, tuple):
return tuple(self._out(list(v)))
return v
# ---- String ----
def set(self, key, value, ex=None):
self._data[key] = value
if ex is not None:
self._ttl[key] = ex
return True
def get(self, key):
return self._out(self._data.get(key))
def mget(self, keys):
return [self._out(self._data.get(k)) for k in keys]
def incr(self, key, amount=1):
new = int(self._data.get(key, "0")) + amount
self._data[key] = str(new)
return new
def expire(self, key, seconds):
if key in self._data:
self._ttl[key] = seconds
return True
return False
def ttl(self, key):
return self._ttl.get(key, -1)
def delete(self, *keys):
n = 0
for k in keys:
if self._data.pop(k, None) is not None:
n += 1
return n
def exists(self, *keys):
return sum(1 for k in keys if k in self._data)
# ---- List ----
def rpush(self, key, *values):
lst = self._data.setdefault(key, [])
lst.extend(values)
return len(lst)
def lpush(self, key, *values):
lst = self._data.setdefault(key, [])
lst[:0] = list(reversed(values))
return len(lst)
def lrange(self, key, start, end):
lst = self._data.get(key, [])
if end == -1:
end = len(lst) - 1
return self._out(lst[start:end + 1])
def llen(self, key):
return len(self._data.get(key, []))
# ---- Hash ----
def hset(self, key, field=None, value=None, mapping=None):
h = self._data.setdefault(key, {})
if mapping:
h.update(mapping)
return len(mapping)
h[field] = value
return 1
def hget(self, key, field):
return self._out(self._data.get(key, {}).get(field))
def hgetall(self, key):
return self._out(self._data.get(key, {}))
def hincrby(self, key, field, amount=1):
h = self._data.setdefault(key, {})
new = int(h.get(field, "0")) + amount
h[field] = str(new)
return new
# ---- Set ----
def sadd(self, key, *members):
s = self._data.setdefault(key, set())
before = len(s)
s.update(members)
return len(s) - before
def smembers(self, key):
return self._out(self._data.get(key, set()))
def sismember(self, key, member):
return member in self._data.get(key, set())
def scard(self, key):
return len(self._data.get(key, set()))
# ---- Sorted Set ----
def zadd(self, key, mapping):
z = self._data.setdefault(key, {})
new = sum(1 for k in mapping if k not in z)
z.update({k: float(v) for k, v in mapping.items()})
return new
def zrange(self, key, start, end, withscores=False):
items = sorted(self._data.get(key, {}).items(), key=lambda kv: kv[1])
return self._pick(items, start, end, withscores)
def zrevrange(self, key, start, end, withscores=False):
items = sorted(self._data.get(key, {}).items(),
key=lambda kv: kv[1], reverse=True)
return self._pick(items, start, end, withscores)
def _pick(self, items, start, end, withscores):
if end == -1:
end = len(items) - 1
picked = items[start:end + 1]
return self._out(picked) if withscores else self._out([k for k, _ in picked])
# ---- Pipeline ----
def pipeline(self):
return _Pipeline(self)
class _Pipeline:
def __init__(self, r):
self._r = r
self._calls = []
def __getattr__(self, name):
def wrapper(*a, **kw):
self._calls.append((name, a, kw))
return self
return wrapper
def execute(self):
return [getattr(self._r, n)(*a, **kw) for n, a, kw in self._calls]
r = FakeRedis(decode_responses=True) # 换成 redis.Redis(...) 即可连真库
print("--- String ---")
print(r.set("site", "python.org"))
print(r.get("site"))
print(r.set("verify:13800138000", "482913", ex=60))
print(r.get("verify:13800138000"), "ttl =", r.ttl("verify:13800138000"))
r.set("pv:2026-10-08", 0)
print(r.incr("pv:2026-10-08"), r.incr("pv:2026-10-08"), r.incr("pv:2026-10-08"))
print(r.mget(["site", "pv:2026-10-08", "nope"]))
print(r.exists("site", "nope"), r.delete("pv:2026-10-08"), r.exists("pv:2026-10-08"))
print("--- List ---")
print(r.rpush("queue:tasks", "task1", "task2"))
print(r.lpush("queue:tasks", "urgent"))
print(r.lrange("queue:tasks", 0, -1), "len =", r.llen("queue:tasks"))
print("--- Hash ---")
print(r.hset("user:1001", mapping={"name": "张三", "city": "杭州", "vip": "0"}))
print(r.hincrby("user:1001", "vip"))
print(r.hget("user:1001", "name"))
print(r.hgetall("user:1001"))
print("--- Set ---")
print(r.sadd("article:7:likes", "u1", "u2", "u3"))
print(r.sadd("article:7:likes", "u1"))
print(r.sismember("article:7:likes", "u1"), r.sismember("article:7:likes", "u9"))
print(sorted(r.smembers("article:7:likes")), r.scard("article:7:likes"))
print("--- Sorted Set ---")
print(r.zadd("rank:score", {"张三": 88, "李四": 95, "王五": 76}))
print(r.zadd("rank:score", {"张三": 91}))
print(r.zrange("rank:score", 0, -1, withscores=True))
print(r.zrevrange("rank:score", 0, 2, withscores=True))
print("--- Pipeline ---")
p = r.pipeline()
p.set("cache:a", "1")
p.incr("cache:a")
p.get("cache:a")
print(p.execute())
print("--- 返回字节的样子 ---")
rb = FakeRedis() # 不传 decode_responses，跟 redis-py 默认一致
rb.set("site", "python.org")
rb.hset("u", mapping={"name": "张三"})
print(rb.get("site"))
print(rb.hgetall("u"))
输出长这样。
--- String ---
True
python.org
True
482913 ttl = 60
1 2 3
['python.org', '3', None]
1 1 0
--- List ---
2
3
['urgent', 'task1', 'task2'] len = 3
--- Hash ---
3
1
张三
{'name': '张三', 'city': '杭州', 'vip': '1'}
--- Set ---
3
0
True False
['u1', 'u2', 'u3'] 3
--- Sorted Set ---
3
0
[('王五', 76.0), ('张三', 91.0), ('李四', 95.0)]
[('李四', 95.0), ('张三', 91.0), ('王五', 76.0)]
--- Pipeline ---
[True, 2, '2']
--- 返回字节的样子 ---
b'python.org'
{b'name': b'\xe5\xbc\xa0\xe4\xb8\x89'}
6.4 几个高频用法
计数器。INCR 是原子操作，多个人同时加也不会算错，这是它比自己在 Python 里读出来加一再写回去强的地方。
# redis_counter.py
r.incr("pv:2026-10-08") # 页面浏览量加一
r.incr("user:1001:login", amount=1)
r.hincrby("user:1001", "points", 10) # 积分加十
限流。一分钟内同一个手机号只能发一次验证码。
# redis_limit.py
import redis
r = redis.Redis(decode_responses=True)
def can_send(phone):
key = "sms:%s" % phone
if r.exists(key):
return False, r.ttl(key)
r.set(key, "1", ex=60) # 60 秒后自动消失
return True, 60
print(can_send("13800138000")) # (True, 60)
print(can_send("13800138000")) # (False, 59)
ex=60 让这个 key 六十秒后自动删除，不用自己写清理任务。这是 Redis 最实用的特性之一。
排行榜。ZSet 天然有序，插入即排序。
# redis_rank.py
r.zadd("rank:score", {"张三": 88, "李四": 95})
r.zincrby("rank:score", 3, "张三") # 加三分
print(r.zrevrange("rank:score", 0, 9, withscores=True)) # 前十名
print(r.zrevrank("rank:score", "张三")) # 名次，从 0 开始
管道。多条命令一次发过去，省掉往返的网络时间。
# redis_pipe.py
pipe = r.pipeline()
for i in range(1000):
pipe.set("k%d" % i, i)
pipe.execute() # 一次网络往返，而不是一千次
上面替身脚本最后那段演示的就是管道，execute() 返回的是每条命令的结果组成的列表。
6.5 别把它当数据库
Redis 的数据在内存里，掉电就没。它有两种持久化方式，RDB 定时快照和 AOF 记录每条写命令，都能把数据落到磁盘上，但都不保证一条不丢。
所以规矩是，Redis 里的数据丢了不能出事。缓存丢了去数据库再查一次就好，计数器丢了大不了重新数，但订单只存在 Redis 里就是给自己找麻烦。
7. 实战
7.1 图书借阅系统
把这一章的东西攒成一个能用的小系统。三张表，书目、读者、借阅记录，借书和还书都走事务，再配上查询和备份。
存成 library.py 放在 chapter10 目录里，直接 python library.py 就能跑。为了让复制过去就能运行，库路径写的是相对路径，放到真实项目里记得按第九章说的，改成基于 __file__ 拼出来的绝对路径。
# -*- coding: utf-8 -*-
"""图书借阅系统：SQLite 版。可以反复运行，每次都从空库重建。"""
import os
import sqlite3
import unicodedata
from datetime import date, timedelta
DB = "library.db" # 真实项目里换成基于 __file__ 拼出来的绝对路径
TODAY = "2026-10-08" # 固定住今天，保证每次运行输出一致
SCHEMA = """
CREATE TABLE book (
id INTEGER PRIMARY KEY AUTOINCREMENT,
isbn TEXT UNIQUE NOT NULL,
title TEXT NOT NULL,
author TEXT,
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);
CREATE TABLE member (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
phone TEXT
);
CREATE TABLE borrow (
id INTEGER PRIMARY KEY AUTOINCREMENT,
book_id INTEGER NOT NULL REFERENCES book(id),
member_id INTEGER NOT NULL REFERENCES member(id),
borrow_date TEXT NOT NULL,
due_date TEXT NOT NULL,
return_date TEXT
);
CREATE INDEX idx_borrow_open ON borrow(book_id, return_date);
"""
def pad(text, width):
"""按终端实际占位宽度补齐，中文算两格"""
shown = sum(2 if unicodedata.east_asian_width(c) in ("W", "F") else 1
for c in str(text))
return str(text) + " " * max(0, width - shown)
def connect():
con = sqlite3.connect(DB)
con.row_factory = sqlite3.Row
con.execute("PRAGMA foreign_keys = ON") # SQLite 的外键默认是关的
return con
def init(con):
con.executescript(SCHEMA)
con.executemany(
"INSERT INTO book (isbn, title, author, stock) VALUES (?,?,?,?)",
[("9787111000011", "Python入门", "李明", 3),
("9787111000028", "Flask实战", "王芳", 1),
("9787111000035", "数据分析", "陈晨", 2),
("9787111000042", "算法图解", "赵磊", 0)],
)
con.executemany("INSERT INTO member (name, phone) VALUES (?,?)",
[("张三", "13800000001"), ("李四", "13800000002")])
con.commit()
def borrow(con, book_id, member_id, today=TODAY):
"""借书：扣库存 + 写记录，两步要么都成要么都不成"""
cur = con.cursor()
try:
with con:
cur.execute("SELECT title, stock FROM book WHERE id = ?", (book_id,))
row = cur.fetchone()
if row is None:
raise ValueError("编号 %s 的书不存在" % book_id)
if row["stock"] <= 0:
raise ValueError("《%s》库存不足" % row["title"])
cur.execute("UPDATE book SET stock = stock - 1 WHERE id = ?", (book_id,))
due = date.fromisoformat(today) + timedelta(days=30)
cur.execute(
"INSERT INTO borrow (book_id, member_id, borrow_date, due_date)"
" VALUES (?,?,?,?)",
(book_id, member_id, today, due.isoformat()))
return "借出成功，应还日期 %s" % due.isoformat()
except (ValueError, sqlite3.Error) as e:
return "失败：%s" % e
def give_back(con, borrow_id, today=TODAY):
cur = con.cursor()
with con:
cur.execute("SELECT book_id, return_date FROM borrow WHERE id = ?", (borrow_id,))
row = cur.fetchone()
if row is None:
return "失败：没有这条借阅记录"
if row["return_date"]:
return "失败：这本书已经还过了"
cur.execute("UPDATE borrow SET return_date = ? WHERE id = ?", (today, borrow_id))
cur.execute("UPDATE book SET stock = stock + 1 WHERE id = ?", (row["book_id"],))
return "归还成功"
def unreturned(con):
sql = """SELECT m.name, b.title, r.borrow_date, r.due_date
FROM borrow r
JOIN book b ON b.id = r.book_id
JOIN member m ON m.id = r.member_id
WHERE r.return_date IS NULL
ORDER BY r.due_date"""
return [dict(x) for x in con.execute(sql)]
def overdue(con, today=TODAY):
sql = """SELECT m.name, b.title, r.due_date,
julianday(?) - julianday(r.due_date) AS days
FROM borrow r
JOIN book b ON b.id = r.book_id
JOIN member m ON m.id = r.member_id
WHERE r.return_date IS NULL AND r.due_date < ?
ORDER BY days DESC"""
return [dict(x) for x in con.execute(sql, (today, today))]
def hot_books(con, top=3):
sql = """SELECT b.title, COUNT(*) AS times
FROM borrow r JOIN book b ON b.id = r.book_id
GROUP BY b.id ORDER BY times DESC, b.id LIMIT ?"""
return [dict(x) for x in con.execute(sql, (top,))]
def search(con, keyword):
"""参数化模糊查询"""
sql = "SELECT id, title, stock FROM book WHERE title LIKE ? ORDER BY id"
return [dict(x) for x in con.execute(sql, ("%" + keyword + "%",))]
def main():
if os.path.exists(DB):
os.remove(DB)
con = connect()
init(con)
print("书目共 %d 种，读者 %d 人"
% (con.execute("SELECT COUNT(*) FROM book").fetchone()[0],
con.execute("SELECT COUNT(*) FROM member").fetchone()[0]))
print("--- 借书 ---")
print(borrow(con, 1, 1)) # 张三借 Python入门
print(borrow(con, 1, 2)) # 李四借 Python入门
print(borrow(con, 2, 1)) # 张三借 Flask实战（只有 1 本）
print(borrow(con, 3, 2)) # 李四借数据分析
print(borrow(con, 2, 2)) # 李四再借，没了
print(borrow(con, 4, 1)) # 算法图解库存 0
print(borrow(con, 99, 1)) # 不存在的书
print("--- 库存 ---")
for r in con.execute("SELECT title, stock FROM book ORDER BY id"):
print(" %s %d" % (pad(r["title"], 10), r["stock"]))
print("--- 未还记录 ---")
for r in unreturned(con):
print(" %s 借《%s》 %s 借，%s 到期" % (r["name"], r["title"], r["borrow_date"], r["due_date"]))
print("--- 还书 ---")
print(give_back(con, 1))
print(give_back(con, 1))
print("还完之后《Python入门》库存 =",
con.execute("SELECT stock FROM book WHERE id=1").fetchone()["stock"])
print("--- 逾期（今天 %s） ---" % TODAY)
con.execute("UPDATE borrow SET due_date = '2026-09-20' WHERE id = 2")
con.commit()
for r in overdue(con):
print(" %s 的《%s》逾期 %.0f 天" % (r["name"], r["title"], r["days"]))
print(" 逾期共 %d 条" % len(overdue(con)))
print("--- 热门排行 ---")
for i, r in enumerate(hot_books(con), 1):
print(" %d. %s 借出 %d 次" % (i, r["title"], r["times"]))
print("--- 搜索 ---")
print(" 关键词 Python:", search(con, "Python"))
print(" 注入尝试 :", search(con, "' OR '1'='1"))
print("--- 备份 ---")
bak = DB + ".bak"
dst = sqlite3.connect(bak)
con.backup(dst)
dst.close()
print(" 库大小 %d 字节，备份 %d 字节"
% (os.path.getsize(DB), os.path.getsize(bak)))
con.close()
if __name__ == "__main__":
main()
跑一遍看结果。
书目共 4 种，读者 2 人
--- 借书 ---
借出成功，应还日期 2026-11-07
借出成功，应还日期 2026-11-07
借出成功，应还日期 2026-11-07
借出成功，应还日期 2026-11-07
失败：《Flask实战》库存不足
失败：《算法图解》库存不足
失败：编号 99 的书不存在
--- 库存 ---
Python入门 1
Flask实战 0
数据分析 1
算法图解 0
--- 未还记录 ---
张三 借《Python入门》 2026-10-08 借，2026-11-07 到期
李四 借《Python入门》 2026-10-08 借，2026-11-07 到期
张三 借《Flask实战》 2026-10-08 借，2026-11-07 到期
李四 借《数据分析》 2026-10-08 借，2026-11-07 到期
--- 还书 ---
归还成功
失败：这本书已经还过了
还完之后《Python入门》库存 = 2
--- 逾期（今天 2026-10-08） ---
李四 的《Python入门》逾期 18 天
逾期共 1 条
--- 热门排行 ---
1. Python入门 借出 2 次
2. Flask实战 借出 1 次
3. 数据分析 借出 1 次
--- 搜索 ---
关键词 Python: [{'id': 1, 'title': 'Python入门', 'stock': 2}]
注入尝试 : []
--- 备份 ---
库大小 28672 字节，备份 28672 字节
几处值得说的设计。
PRAGMA foreign_keys = ON 这一句不能省。SQLite 的外键约束默认是关着的，你写了 REFERENCES book(id) 它也不检查，删掉一本还有借阅记录的书照样成功。想要外键真起作用，每次连上都要执行这条。
借书函数里用的是 with con:。扣库存和写记录必须绑在一起，任何一步失败就回滚。这里有个细节，抛异常的那句在 with 里面，异常往外传的时候 with 会替你回滚，所以外面只需要 catch 一下转成提示语。
CHECK (stock >= 0) 是最后一道防线。业务代码里已经判断过库存，数据库再兜一层，谁都绕不过去。
搜索那一段，关键词是 ' OR '1'='1，结果是空列表。参数化查询把它当成了普通字符串，库里没有书名包含这串字符的书。
逾期天数用的是 julianday(到期日) - julianday(今天)，SQLite 的 julianday 把日期转成天数，两个相减就是天数差。日期按 ISO 格式存的好处在这儿体现出来了。
7.2 给热门榜加一层缓存
热门榜是典型的算起来慢、变得不快、看的人多的数据，很适合缓存。这段把完整套路走一遍，顺带把最容易踩的那个坑也踩一遍给你看。
# -*- coding: utf-8 -*-
"""给热门榜加一层缓存。TinyCache 的接口照着 redis-py 写，
把 cache = TinyCache() 换成 cache = redis.Redis(decode_responses=True) 就能连真 Redis。
"""
import json
import sqlite3
class TinyCache:
"""本地替身：方法名 get / set / delete / ttl 跟 redis 客户端一致"""
def __init__(self):
self._data = {}
self._expire = {}
self.now = 0 # 逻辑时钟，方便演示过期，不依赖真实时间
def set(self, key, value, ex=None):
# 真 Redis 只能存字符串，所以这里也用 json 转一下
self._data[key] = json.dumps(value, ensure_ascii=False)
self._expire[key] = None if ex is None else self.now + ex
return True
def get(self, key):
raw = self._data.get(key)
if raw is None:
return None
exp = self._expire.get(key)
if exp is not None and exp <= self.now:
del self._data[key]
return None
return json.loads(raw)
def delete(self, *keys):
n = 0
for k in keys:
if self._data.pop(k, None) is not None:
n += 1
return n
def ttl(self, key):
# 真 Redis：key 不存在返回 -2，永不过期返回 -1，其余返回剩余秒数
if key not in self._data or key not in self._expire:
return -2
exp = self._expire[key]
if exp is not None and exp <= self.now:
return -2
return -1 if exp is None else exp - self.now
def build_db():
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE book (id INTEGER PRIMARY KEY, title TEXT)")
con.execute("CREATE TABLE borrow (id INTEGER PRIMARY KEY, book_id INTEGER)")
con.executemany("INSERT INTO book (id, title) VALUES (?,?)",
[(1, "Python入门"), (2, "Flask实战"), (3, "数据分析"),
(4, "算法图解"), (5, "网络爬虫")])
rows = []
for book_id, times in [(1, 12), (2, 8), (3, 5), (4, 3), (5, 1)]:
rows.extend([(book_id,)] * times)
con.executemany("INSERT INTO borrow (book_id) VALUES (?)", rows)
con.commit()
return con
def hot_books(con, top=3):
sql = """SELECT b.title, COUNT(*) AS times
FROM borrow r JOIN book b ON b.id = r.book_id
GROUP BY b.id ORDER BY times DESC, b.id LIMIT ?"""
return [(r[0], r[1]) for r in con.execute(sql, (top,))]
def get_hot(con, cache, top=3):
key = "rank:hot:%d" % top
data = cache.get(key)
if data is not None:
return data, "缓存命中"
data = hot_books(con, top)
cache.set(key, data, ex=300) # 300 秒后失效
return data, "查库并写入缓存"
con = build_db()
cache = TinyCache()
for i in range(1, 3):
data, how = get_hot(con, cache)
print("第 %d 次 | %s | %s" % (i, how, data))
print("（有人又借走一本《Flask实战》）")
con.execute("INSERT INTO borrow (book_id) VALUES (2)")
con.commit()
data, how = get_hot(con, cache)
print("第 3 次 | %s | %s | 库里其实是 %s" % (how, data, hot_books(con)))
cache.delete("rank:hot:3")
data, how = get_hot(con, cache)
print("第 4 次 | %s | %s" % (how, data))
print("（过了 301 秒）")
cache.now += 301
print("ttl =", cache.ttl("rank:hot:3"))
data, how = get_hot(con, cache)
print("第 5 次 | %s | %s" % (how, data))
运行结果。
第 1 次 | 查库并写入缓存 | [('Python入门', 12), ('Flask实战', 8), ('数据分析', 5)]
第 2 次 | 缓存命中 | [['Python入门', 12], ['Flask实战', 8], ['数据分析', 5]]
（有人又借走一本《Flask实战》）
第 3 次 | 缓存命中 | [['Python入门', 12], ['Flask实战', 8], ['数据分析', 5]] | 库里其实是 [('Python入门', 12), ('Flask实战', 9), ('数据分析', 5)]
第 4 次 | 查库并写入缓存 | [('Python入门', 12), ('Flask实战', 9), ('数据分析', 5)]
（过了 301 秒）
ttl = -2
第 5 次 | 查库并写入缓存 | [('Python入门', 12), ('Flask实战', 9), ('数据分析', 5)]
第 3 次那个结果要好好看。库里的《Flask实战》已经是 9 次了，缓存里还是 8 次。这就是缓存的经典问题，数据改了，缓存没跟着改，读到的就是过期数据。
处理办法有三种，任选其一。
一是更新数据时顺手把缓存删掉，下次读自然重新查库。这叫缓存失效，最常用。
二是设短一点的过期时间，比如 60 秒，容忍一分钟的误差。
三是不用缓存，榜单实时算。数据量小的时候这是最省心的选择，别为了用 Redis 而用 Redis。
还有一个小细节，第 1 次结果里是元组，第 2 次从缓存取出来变成了列表。Redis 只能存字符串，我用 JSON 转了一下，JSON 不认识元组，转回来就成列表了。要保住类型的话，自己约定好取出来再 tuple() 转回去。
8. 小结
8.1 这一章学了什么
数据库编程接口
├── PEP 249（DB-API 2.0），connect / cursor / execute / fetch / commit / close
├── paramstyle 决定占位符长相：sqlite3 用 ?，pymysql 和 psycopg 用 %s
├── 参数化查询防注入，占位符只能替值不能替表名
└── 异常分两层，IntegrityError 和 OperationalError 最常碰上
SQLite
├── 标准库自带，一个文件就是一个库，零配置
├── 五种存储类型，日期按 ISO 字符串存
├── executemany 批量插入，row_factory 控制每行长什么样
├── 事务用 with con，正常提交异常回滚
└── 边界是"多进程同时写"，碰到就换
MySQL / PostgreSQL
├── PyMySQL 和 psycopg 是最常用的两个驱动
├── 占位符都是 %s，字符集 MySQL 要用 utf8mb4
├── 金额用 DECIMAL 或 NUMERIC，别用浮点
└── RETURNING 和 ON CONFLICT 能把多条语句压成一条
MongoDB
├── 文档型，集合里放文档，字段可以不固定
├── 查询条件是字典，$gte 这类运算符写在值里
├── 嵌套字段用点号访问，find 返回游标
└── 结构多变、读多写少的场景合适
Redis
├── 内存型数据库，五种结构 String / List / Hash / Set / ZSet
├── decode_responses=True 让返回值是字符串而不是字节
├── ex 参数设过期时间，pipeline 批量发命令
└── 数据可以丢，别拿它当唯一存储
8.2 避坑清单
写着写着攒下来的这些，每一条都对应一个具体的翻车现场。
1. 忘了 commit，数据看起来插进去了，重开程序发现是空的
2. 用 f-string 拼 SQL，等着被注入
3. 占位符写错方言，SQLite 的 ? 搬到 MySQL 上不认
4. UPDATE 和 DELETE 漏了 WHERE，整表遭殃
5. 循环里调 execute 插几千行，慢得莫名其妙
6. 没设 row_factory，代码里全是 row[1] row[2] 这种下标
7. 事务里抛了异常没 rollback，钱就对不上了
8. SQLite 忘了开 PRAGMA foreign_keys，外键约束形同虚设
9. MySQL 字符集写成 utf8，存 emoji 直接报错
10. 金额用 FLOAT 存，对账的时候差几分钱
11. MongoDB 集合名拼错，安安静静建了个新集合
12. Redis 取出来是 bytes，中文变成一坨转义
13. 缓存更新了数据不删，用户一直看到旧值
14. 直接 shutil.copy 数据库文件，拷出来个半截的
15. 连接开了不关，跑久了报 too many connections
第 1 条和第 4 条是新手前两个月最常犯的，第 13 条是用上缓存之后迟早会碰到的。
8.3 五道题
第一题，建一张订单表，插五条数据，按城市统计总金额，按金额从高到低排。
>>> q1 = sqlite3.connect(":memory:")
>>> _ = q1.execute("CREATE TABLE ord (id INTEGER PRIMARY KEY, city TEXT, amount REAL)")
>>> _ = q1.executemany("INSERT INTO ord (city, amount) VALUES (?,?)",
... [("杭州", 120.5), ("上海", 80.0), ("杭州", 45.5), ("北京", 200.0), ("上海", 19.5)])
>>> q1.execute("SELECT city, ROUND(SUM(amount), 2) FROM ord GROUP BY city ORDER BY 2 DESC").fetchall()
[('北京', 200.0), ('杭州', 166.0), ('上海', 99.5)]
ORDER BY 2 表示按查询结果的第二列排序，写聚合函数进去也行，这里是图省事。
第二题，写个卖货函数，库存不足要回滚，不能让库存变负数。
>>> q2 = sqlite3.connect(":memory:")
>>> _ = q2.execute("CREATE TABLE sku (name TEXT PRIMARY KEY, stock INTEGER CHECK (stock >= 0))")
>>> _ = q2.executemany("INSERT INTO sku VALUES (?,?)", [("键盘", 2), ("鼠标", 0)])
>>> def sell(name, n):
... row = q2.execute("SELECT stock FROM sku WHERE name = ?", (name,)).fetchone()
... if row is None:
... raise ValueError("没有这个商品")
... if row[0] < n:
... raise ValueError("库存不足")
... q2.execute("UPDATE sku SET stock = stock - ? WHERE name = ?", (n, name))
... q2.commit()
... return "卖出 %d 个" % n
...
>>> sell("键盘", 1)
'卖出 1 个'
>>> try:
... sell("鼠标", 1)
... except ValueError as e:
... q2.rollback()
... print("失败:", e)
...
失败: 库存不足
>>> q2.execute("SELECT name, stock FROM sku ORDER BY name").fetchall()
[('键盘', 1), ('鼠标', 0)]
这里 raise 发生在 commit 之前，事务还没提交，rollback 是保险起见。更省事的做法是 with q2: 包住整个函数体。
第三题，把查询结果转成字典列表，再序列化成 JSON 字符串。
>>> import json
>>> q3 = sqlite3.connect(":memory:")
>>> q3.row_factory = sqlite3.Row
>>> _ = q3.execute("CREATE TABLE p (id INTEGER, name TEXT, age INTEGER)")
>>> _ = q3.executemany("INSERT INTO p VALUES (?,?,?)", [(1, "张三", 18), (2, "李四", 20)])
>>> rows = [dict(r) for r in q3.execute("SELECT id, name, age FROM p")]
>>> rows
[{'id': 1, 'name': '张三', 'age': 18}, {'id': 2, 'name': '李四', 'age': 20}]
>>> json.dumps(rows, ensure_ascii=False)
'[{"id": 1, "name": "张三", "age": 18}, {"id": 2, "name": "李四", "age": 20}]'
这基本上就是写接口时最常用的一段，row_factory 加列表推导，两行搞定。
第四题，下面这个查询函数有注入漏洞，先复现，再改对。
>>> q4 = sqlite3.connect(":memory:")
>>> _ = q4.execute("CREATE TABLE people (id INTEGER, name TEXT)")
>>> _ = q4.executemany("INSERT INTO people VALUES (?,?)", [(1, "张三"), (2, "李四")])
>>> def find_bad(keyword):
... return q4.execute("SELECT * FROM people WHERE name = '%s'" % keyword).fetchall()
...
>>> find_bad("张三")
[(1, '张三')]
>>> find_bad("' OR '1'='1")
[(1, '张三'), (2, '李四')]
>>> def find_ok(keyword):
... return q4.execute("SELECT * FROM people WHERE name = ?", (keyword,)).fetchall()
...
>>> find_ok("' OR '1'='1")
[]
>>> find_ok("张三")
[(1, '张三')]
同样是查，拼接版把整张表交出去了，参数化版什么也查不到。
第五题，日志表有五条记录，写分页查询，每页两条，再统计各级别的数量。
>>> q5 = sqlite3.connect(":memory:")
>>> _ = q5.execute("CREATE TABLE log (id INTEGER PRIMARY KEY, level TEXT, msg TEXT)")
>>> _ = q5.executemany("INSERT INTO log (level, msg) VALUES (?,?)",
... [("ERROR", "a"), ("WARN", "b"), ("ERROR", "c"), ("INFO", "d"), ("ERROR", "e")])
>>> q5.execute("SELECT id, level FROM log WHERE level = 'ERROR' LIMIT 2 OFFSET 0").fetchall()
[(1, 'ERROR'), (3, 'ERROR')]
>>> q5.execute("SELECT id, level FROM log WHERE level = 'ERROR' LIMIT 2 OFFSET 2").fetchall()
[(5, 'ERROR')]
>>> q5.execute("SELECT level, COUNT(*) FROM log GROUP BY level ORDER BY COUNT(*) DESC, level").fetchall()
[('ERROR', 3), ('INFO', 1), ('WARN', 1)]
OFFSET 是跳过的条数，第一页跳 0 条，第二页跳 2 条。分组那里加了 level 做二级排序，不然数量相同的两组顺序是不保证的。
