【免费下载链接】peewee
a small, expressive orm -- supports postgresql, mysql, sqlite, now with asyncio
导读
本文是 Peewee（a small, expressive ORM，支持 PostgreSQL、MySQL、SQLite 及 asyncio）官方文档中 docs/peewee/querying.rst 的完整技术解读。它系统覆盖了关系型数据库中最常用的增删改查（CRUD）操作，以及批量写入、原子更新、Upsert、聚合、窗口函数、RETURNING 子句和公共表表达式（CTE）等进阶查询能力。读完本文，你将掌握 Peewee 中从 Model.create 到递归 CTE 的全部查询 API 用法、底层行为（如缓存、事务、主键回填）与性能优化手段，并能直接照搬到自己的项目中使用。
一、概述：CRUD 操作全景
Peewee 的查询 API 以 Model 类方法为入口，覆盖四种基本 SQL 语句：
| 操作 | 对应方法 | SQL 语句 |
|---|---|---|
| 创建记录 | Model.create、Model.save、Model.insert、Model.insert_many、Model.bulk_create | INSERT |
| 更新记录 | Model.save、Model.update、Model.bulk_update | UPDATE |
| 删除记录 | Model.delete_instance、Model.delete | DELETE |
| 查询记录 | Model.select、Model.get、Model.get_by_id | SELECT |
此外，官方还提供了大量取自 PostgreSQL Exercises；运算符的完整清单参见 docs/peewee/query_operators.rst。
二、创建新记录
2.1 使用 Model.create
Model.create 接收关键字参数，参数名对应模型的字段名。执行后返回一个新的模型实例，同时向表中插入一行：
>>> User.create(username='Charlie')
<__main__.User object at 0x2529350>
该操作会执行一条 INSERT，并且新行的主键会被自动检索并回填到返回的实例上。
从源码看，create 的实现非常直接（见 peewee.py）：
@classmethod
def create(cls, **query):
inst = cls(**query)
inst.save(force_insert=True)
return inst
它本质上是"构造实例 + 强制 INSERT 的 save()"两步的语法糖。
2.2 先构造实例再 save()
你也可以用程序化方式构造模型实例，然后调用 save()。save() 返回受影响的行数：
>>> user = User(username='Charlie')
>>> user.save() # save() 返回被修改的行数。
1
>>> user.id
1
>>> huey = User()
>>> huey.username = 'Huey'
>>> huey.save()
1
>>> huey.id
2
save() 的完整行为见 peewee.py：当主键为 None 且未强制插入时执行 INSERT 并回填自增主键；当主键已存在时执行 UPDATE。
2.3 外键赋值
当模型包含外键字段时，可以直接把模型实例赋给外键字段：
>>> tweet = Tweet.create(user=huey, message='Hello!')
也可以直接使用关联对象的主键值：
>>> tweet = Tweet.create(user=2, message='Hello again!')
2.4 纯插入：Model.insert
如果只是想插入数据而不需要模型实例，可以使用 Model.insert，执行后返回新行的主键：
>>> User.insert(username='Mickey').execute()
3
注意：如果你需要提升批量插入的速度，请参考本文"批量插入"一节。Peewee 也提供
Model.replace等插入变体，见"Upsert"一节。
三、批量插入（Bulk Inserts）
3.1 最朴素（也是最慢）的方式
在循环里逐个调用 Model.create：
data_source = [
{'field1': 'val1-1', 'field2': 'val1-2'},
{'field1': 'val2-1', 'field2': 'val2-2'},
# ...
]
for data_dict in data_source:
MyModel.create(**data_dict)
这种方式慢的原因有四：
- 如果循环没有包在事务里，每次
create都是独立事务，代价极高； - 大量 Python 逻辑介入，且每条
InsertQuery都要生成并解析为 SQL； - 发送给数据库解析的 SQL 原始字节数巨大；
- 需要获取 last insert id，某些情况下会额外执行一次查询。
3.2 第一层优化：包进事务
使用 db.atomic() 把循环包进单个事务，能获得显著的性能提升：
# 这样快很多。
with db.atomic():
for data_dict in data_source:
MyModel.create(**data_dict)
不过上面仍然存在第 2、3、4 条问题。
3.3 第二层优化：insert_many
insert_many 接受一个字典列表（或元组列表），用单条查询插入多行：
data_source = [
{'field1': 'val1-1', 'field2': 'val1-2'},
{'field1': 'val2-1', 'field2': 'val2-2'},
# ...
]
# 批量 INSERT 多行最快的写法。
MyModel.insert_many(data_source).execute()
若传入的是元组列表，则必须同时指定 fields 以说明每个值对应的字段：
# 我们也可以 INSERT 元组……
data = [('val1-1', 'val1-2'),
('val2-1', 'val2-2'),
('val3-1', 'val3-2')]
# 但需要指明这些值对应哪些字段。
MyModel.insert_many(data, fields=[MyModel.field1, MyModel.field2]).execute()
同样建议把批量插入包进事务：
# 当然，也可以包进一个事务：
with db.atomic():
MyModel.insert_many(data, fields=fields).execute()
从源码看，insert_many(rows, fields=None) 只是把数据委托给 ModelInsert 构造器（见 peewee.py），最终仍走 execute() 执行。
SQLite 注意事项：SQLite 用户使用批量插入 API 时，SQLite 版本必须是 3.7.11.0 或更新。此外，SQLite 默认限制单条 SQL 查询的绑定变量数量：3.32.0（2020-05-22）之前的版本上限为 999，之后的版本为 32766。
3.4 分批插入（Batch Inserts）
当数据量很大时，需要把数据拆分成多个批次。SQLite 尤其受绑定变量数量限制（999 或 32766），因此批次大小大约是 999 // 行长 或 32766 // 行长。
可以手写循环分批（强烈建议包进事务）：
# 每次插入 100 行。
with db.atomic():
for idx in range(0, len(data_source), 100):
MyModel.insert_many(data_source[idx:idx+100]).execute()
Peewee 自带 chunked 辅助函数，可以高效地把任意可迭代对象切成一批批的小迭代器：
from peewee import chunked
# 每次插入 100 行。
with db.atomic():
for batch in chunked(data_source, 100):
MyModel.insert_many(batch).execute()
chunked 的实现位于 peewee.py，它基于 izip_longest 把迭代器按固定长度分组：
def chunked(it, n):
marker = object()
for group in (list(g) for g in izip_longest(*[iter(it)] * n,
fillvalue=marker)):
if group[-1] is marker:
del group[group.index(marker):]
yield group
3.5 替代方案：bulk_create 与 bulk_update
Model.bulk_create 与 insert_many 行为类似，但它接受的是未保存的模型实例列表，并且可选 batch_size 参数：
# 例如，从一个文件中读取用户名列表。
with open('user_list.txt') as fh:
# 构造一批未保存的 User 实例。
users = [User(username=line.strip()) for line in fh.readlines()]
# 包进事务，每 100 个一批执行 INSERT。
with db.atomic():
User.bulk_create(users, batch_size=100)
PostgreSQL 专属福利：PostgreSQL 支持
RETURNING子句，因此使用bulk_create后，之前未保存的模型实例会自动获得新的主键值。这一逻辑在 peewee.py 的bulk_create实现中清晰可见：当数据库支持returning_clause且模型有主键时，会用返回的pk_fields逐行setattr回填实例主键。
Model.bulk_update 则可以高效地批量更新一个模型列表中的一个或多个列：
# 先创建 3 个用户 u1、u2、u3。
u1, u2, u3 = [User.create(username='u%s' % i) for i in (1, 2, 3)]
# 现在修改这些实例。
u1.username = 'u1-x'
u2.username = 'u2-y'
u3.username = 'u3-z'
# 用一条 UPDATE 更新全部三个用户。
User.bulk_update([u1, u2, u3], fields=[User.username])
生成的 SQL 大致如下：
UPDATE "users" SET "username" = CASE "users"."id"
WHEN 1 THEN "u1-x"
WHEN 2 THEN "u2-y"
WHEN 3 THEN "u3-z" END
WHERE "users"."id" IN (1, 2, 3);
对于超长列表，建议指定合理的
batch_size并用Database.atomic()包裹：with database.atomic(): User.bulk_update(list_of_users, fields=['username'], batch_size=50)
警告：
bulk_update可能不是更新大量记录最高效的方式——它的实现是为所有被更新行构造"主键 → 字段值"的映射，并使用 SQLCASE语句完成（见 peewee.py）。若涉及大量数据，请评估其 SQL 体积。
另一个辅助工具是 Database.batch_commit，它以 batch 大小的事务逐块处理行数据，同时也为非 PostgreSQL 数据库获取新建行主键提供了变通方案：
# 待插入的行数据列表。
row_data = [{'username': 'u1'}, {'username': 'u2'}, ...]
# 假设 row_data 有 789 项，下面的代码会产生 8 个事务（7x100 行 + 1x89 行）。
for row in db.batch_commit(row_data, 100):
User.create(**row)
其实现见 peewee.py：先 chunked 分组，再为每个分组开启 atomic() 事务并逐个 yield。
3.6 从另一张表批量装载：insert_from
如果数据已存在于另一张表，可以构造 源为 SELECT 查询 的 INSERT 语句，使用 Model.insert_from：
res = (TweetArchive
.insert_from(
Tweet.select(Tweet.user, Tweet.message),
fields=[TweetArchive.user, TweetArchive.message])
.execute())
等价 SQL：
INSERT INTO "tweet_archive" ("user_id", "message")
SELECT "user_id", "message" FROM "tweet";
四、更新已有记录
4.1 实例的 save() 更新语义
一旦模型实例拥有了主键，后续调用 save() 将执行 UPDATE 而非再次 INSERT，且主键保持不变：
>>> user.save() # save() 返回被修改的行数。
1
>>> user.id
1
>>> user.save()
>>> user.id
1
>>> huey.save()
1
>>> huey.id
2
4.2 批量更新：Model.update
要一次更新多条记录，使用 Model.update。下面的例子会把创建日期早于今天的 Tweet 全部标记为已发布。update 同样接收关键字参数，键对应字段名：
>>> today = datetime.today()
>>> query = Tweet.update(is_published=True).where(Tweet.creation_date < today)
>>> query.execute() # 返回被更新的行数。
4
详细说明可参考 Model.update、Update 与 Model.bulk_update 的文档。若需要原子更新（如对列自增），见下文。
五、原子更新（Atomic Updates）
Peewee 支持原子更新。假设需要更新某些计数器，朴素写法是这样的：
>>> for stat in Stat.select().where(Stat.url == request.url):
... stat.counter += 1
... stat.save()
千万不要这样做！ 它不仅慢，而且在多进程同时更新计数器时存在竞态条件。
正确做法是使用 Model.update 在数据库端完成原子操作：
>>> query = Stat.update(counter=Stat.counter + 1).where(Stat.url == request.url)
>>> query.execute()
更新语句可以写得非常复杂。比如给所有员工发放奖金：原有奖金加上工资的 10%：
>>> query = Employee.update(bonus=(Employee.bonus + (Employee.salary * .1)))
>>> query.execute() # 给每个人发奖金！
甚至可以用子查询更新列的值。假设 User 上有一个反规范化列 num_tweets 存储用户推文数，需要定期刷新：
>>> subquery = Tweet.select(fn.COUNT(Tweet.id)).where(Tweet.user == User.id)
>>> update = User.update(num_tweets=subquery)
>>> update.execute()
5.1 Upsert（插入或更新）
Peewee 提供多种 upsert 支持：
- SQLite（3.24.0 之前）与 MySQL：支持
Model.replace——插入记录，若发生约束冲突则替换已有记录。 - SQLite 3.24+ 与 PostgreSQL：完整支持
ON CONFLICT查询。
Model.replace 与 Insert.on_conflict_replace 的等价用法：
class User(Model):
username = TextField(unique=True)
last_login = DateTimeField(null=True)
# 插入或更新用户。无论用户此前是否存在，"last_login" 都会被更新。
user_id = (User
.replace(username='the-user', last_login=datetime.now())
.execute())
# 下面的查询与之等价：
user_id = (User
.insert(username='the-user', last_login=datetime.now())
.on_conflict_replace()
.execute())
此外，SQLite、MySQL 和 PostgreSQL 都支持 ignore 动作（见
Insert.on_conflict_ignore）：只想插入并忽略潜在的约束冲突时使用。源码层面，replace()等价于insert(...).on_conflict('REPLACE')（见 peewee.py）。
MySQL 通过 ON DUPLICATE KEY UPDATE 子句支持 upsert：
class User(Model):
username = TextField(unique=True)
last_login = DateTimeField(null=True)
login_count = IntegerField()
# 插入一个新用户。
User.create(username='huey', login_count=0)
# 模拟用户登录。登录次数和时间戳会被正确创建或更新。
now = datetime.now()
rowid = (User
.insert(username='huey', last_login=now, login_count=1)
.on_conflict(
preserve=[User.last_login], # 使用我们本要插入的值。
update={User.login_count: User.login_count + 1})
.execute())
上面这个 upsert 查询可以安全地反复执行：登录计数被原子地自增、最后登录时间被更新、不会产生重复行。
PostgreSQL 与 SQLite（3.24.0 及以上） 提供了不同语法，允许更精细地控制：指定哪个约束冲突触发解决逻辑、哪些值应更新或保留：
class User(Model):
username = TextField(unique=True)
last_login = DateTimeField(null=True)
login_count = IntegerField()
# 插入一个新用户。
User.create(username='huey', login_count=0)
now = datetime.now()
rowid = (User
.insert(username='huey', last_login=now, login_count=1)
.on_conflict(
conflict_target=[User.username], # 哪个约束？
preserve=[User.last_login], # 使用我们本要插入的值。
update={User.login_count: User.login_count + 1})
.execute())
MySQL 与 PostgreSQL/SQLite 的主要区别在于：PostgreSQL 和 SQLite 必须指定
conflict_target。
再看一个使用 EXCLUDED 命名空间的进阶（略带构造色彩）例子。EXCLUDED 允许我们引用发生冲突的数据中的值。假设有一个把唯一键（字符串）映射为值（整数）的表：
class KV(Model):
key = CharField(unique=True)
value = IntegerField()
# 创建一行。
KV.create(key='k1', value=1)
# 演示 EXCLUDED 的用法。
# 这里我们尝试为某个 key 插入新值。如果该 key 已存在，
# 则用"原值 + 尝试插入的值"更新——前提是新值大于原值。
query = (KV.insert(key='k1', value=10)
.on_conflict(conflict_target=[KV.key],
update={KV.value: KV.value + EXCLUDED.value},
where=(EXCLUDED.value > KV.value)))
# 执行后 "kv" 表中的数据将是：
# (key='k1', value=11)
query.execute()
# 如果再次执行该查询，则不会有任何更新，
# 因为新值（10）已经小于原行中的值（11）。
理解 ON CONFLICT 需要掌握四个核心概念：
conflict_target=：哪一列（或哪些列）有 UNIQUE 约束。对于用户表，可能是用户邮箱。preserve=：发生冲突时，从新数据中取出哪些值用于更新。update=：发生冲突时，应用到已存在行上的数据映射。EXCLUDED：这个"魔法"命名空间允许你引用本应被插入、但因约束失败而未插入的新数据。
完整示例——一个登录函数，同时演示以上四个概念：
class User(Model):
email = CharField(unique=True) # 用户唯一标识。
last_login = DateTimeField()
login_count = IntegerField(default=0)
ip_log = TextField(default='')
# 演示上述 4 个概念。
def login(email, ip):
rowid = (User
.insert({User.email: email,
User.last_login: datetime.now(),
User.login_count: 1,
User.ip_log: ip})
.on_conflict(
# 如果 INSERT 因邮箱约束冲突失败，则执行 UPDATE。
conflict_target=[User.email],
# 把 "last_login" 设为我们本要插入的值（datetime.now()）。
preserve=[User.last_login],
# 登录次数 +1，并把新 IP 拼到 ip 历史的最前面。
update={User.login_count: User.login_count + 1,
User.ip_log: fn.CONCAT(EXCLUDED.ip_log, ',', User.ip_log)})
.execute())
return rowid
# 插入初始行，返回新行 id（1）。
print(login('test@example.com', '127.1'))
# 因为 test@example.com 已存在，将触发 UPSERT。返回上面的行 id（1）。
print(login('test@example.com', '127.2'))
u = User.get()
print(u.login_count, u.ip_log)
# 输出 "2 127.2,127.1"
更详细的说明参见 Insert.on_conflict 与 OnConflict 的文档。
六、删除记录
6.1 删除单个实例：delete_instance
Model.delete_instance 删除指定模型实例，并可通过 recursive=True 递归删除依赖对象：
>>> user = User.get(User.id == 1)
>>> user.delete_instance() # 返回被删除的行数。
1
>>> User.get(User.id == 1)
UserDoesNotExist: instance matching query does not exist:
SQL: SELECT t1."id", t1."username" FROM "user" AS t1 WHERE t1."id" = ?
PARAMS: [1]
源码实现见 peewee.py：当 recursive=True 时，通过 dependencies() 遍历所有反向引用；对于可空外键且未指定 delete_nullable 时，会把外键置空而非删除子行。
6.2 批量删除：Model.delete
删除任意一组行时，使用 DELETE 查询。下面的语句删除所有创建时间超过一年的 Tweet：
>>> query = Tweet.delete().where(Tweet.creation_date < one_year_ago)
>>> query.execute() # 返回被删除的行数。
7
相关文档：Model.delete_instance、Model.delete、DeleteQuery。
七、查询单条记录
7.1 Model.get、get_by_id 与下标访问
Model.get 用于检索符合给定查询的单条实例；主键查询可用快捷方法 Model.get_by_id。get 是"先 select 再限制为单行"的快捷方式，且若无匹配则抛出 DoesNotExist 异常：
>>> User.get(User.id == 1)
<__main__.User object at 0x25294d0>
>>> User.get_by_id(1) # 同上。
<__main__.User object at 0x252df10>
>>> User[1] # 也同上。
<__main__.User object at 0x252dd10>
>>> User.get(User.id == 1).username
u'Charlie'
>>> User.get(User.username == 'Charlie')
<__main__.User object at 0x2529410>
>>> User.get(User.username == 'nobody')
UserDoesNotExist: instance matching query does not exist:
SQL: SELECT t1."id", t1."username" FROM "user" AS t1 WHERE t1."username" = ?
PARAMS: ['nobody']
从源码看（peewee.py），get() 支持同时传多个条件或 Django 风格的过滤关键字；get_by_id 只是 get(primary_key == pk) 的封装（peewee.py）。
7.2 更高级的查询：SelectBase.get
对于更复杂的需求，可以用 SelectBase.get。下面查询名为 charlie 的用户发布的最新推文：
>>> (Tweet
... .select()
... .join(User)
... .where(User.username == 'charlie')
... .order_by(Tweet.created_date.desc())
... .get())
<__main__.Tweet object at 0x2623410>
相关 API 一览：
Model.getModel.get_by_idModel.get_or_none—— 无匹配时返回None（实现见 peewee.py）Model.selectSelectBase.getSelectBase.first—— 返回结果集第一条，无则返回None
八、Create-or-Get 模式
Peewee 提供 Model.get_or_create：先尝试检索匹配行，失败则创建新行。
对于"先创建、失败再获取"（create-or-get）的逻辑，通常依赖 unique 约束或主键来防止重复。以注册新用户为例，User 模型对 username 有唯一约束，因此可依靠数据库的完整性保证：
try:
with db.atomic():
return User.create(username=username)
except peewee.IntegrityError:
# `username` 是唯一列，所以这个用户名一定已存在，
# 此时安全地调用 .get()。
return User.get(User.username == username)
你可以把这种逻辑封装为自定义 Model 类上的 classmethod。
上面的例子是"先尝试创建，再回退检索"。如果你更倾向于先检索，则用 Model.get_or_create。它与 Django 同名函数实现思路一致，支持 Django 风格的关键字过滤参数，返回一个 2 元组：实例和是否新建的布尔值。
用户注册用 get_or_create 可以这样写：
user, created = User.get_or_create(username=username)
假设有一个 Person 模型，检索时只关心 first name 和 last name，但如果最终需要新建记录，还要写入出生日期和最喜欢的颜色：
person, created = Person.get_or_create(
first_name=first_name,
last_name=last_name,
defaults={'dob': dob, 'favorite_color': 'green'})
传给 get_or_create 的所有关键字参数都会用于 get() 部分，唯独 defaults 字典例外——它只用于填充新建实例的值。
从实现看（peewee.py），get_or_create 会先构造 WHERE 查询尝试 get()；失败后开启 atomic() 事务执行 create()，若此时再遇 IntegrityError（并发场景下他人已插入），则回退再查一次。
九、查询多条记录
9.1 迭代、索引与切片
用 Model.select 从表中检索行。Peewee 允许迭代结果，也支持索引和切片：
>>> query = User.select()
>>> [user.username for user in query]
['Charlie', 'Huey', 'Peewee']
>>> query[1]
<__main__.User at 0x7f83e80f5550>
>>> query[1].username
'Huey'
>>> query[:2]
[<__main__.User at 0x7f83e80f53a8>, <__main__.User at 0x7f83e80f5550>]
Select 查询很"聪明"：你可以多次迭代、索引、切片，但查询只会执行一次——因为结果被缓存了。
最简单的用法是直接迭代，返回 User 表所有行：
>>> for user in User.select():
... print(user.username)
...
Charlie
Huey
Peewee
注意：同一查询的后续迭代不会再次访问数据库（结果已缓存）。如果为了降低内存占用而想禁用缓存，迭代时改用
Select.iterator。
9.2 外键与反向引用（backref）
迭代包含外键的模型时，注意访问关联模型值的方式——意外解析外键或迭代反向引用可能引发 N+1 查询问题（详见 docs/peewee/querying.rst 中关于 N+1 的说明）。
创建外键（如 Tweet.user）时，可以用 backref 建立反向引用（User.tweets）。反向引用以 Select 实例形式暴露：
>>> tweet = Tweet.get()
>>> tweet.user # 访问外键返回关联模型。
<tw.User at 0x7f3ceb017f50>
>>> user = User.get()
>>> user.tweets # 访问反向引用返回一个查询。
<peewee.ModelSelect at 0x7f73db3bafd0>
user.tweets 可以像任何 Select 一样迭代：
>>> for tweet in user.tweets:
... print(tweet.message)
...
hello world
this is fun
look at this picture of my food
9.3 行类型：字典、元组与 namedtuple
除了返回模型实例，Select 查询还可以返回字典、元组和 namedtuple。某些场景下用字典处理行数据更方便：
>>> query = User.select().dicts()
>>> for row in query:
... print(row)
{'id': 1, 'username': 'Charlie'}
{'id': 2, 'username': 'Huey'}
{'id': 3, 'username': 'Peewee'}
更多信息参见 BaseQuery.namedtuples、BaseQuery.tuples、BaseQuery.dicts。
9.4 迭代大型结果集
默认情况下 Peewee 迭代 Select 查询时会缓存所有行，这是为了支持多次迭代和索引切片而不额外发查询。但当你计划迭代海量行时，缓存反而会成为问题。
使用 iterator 方法可降低迭代大结果集时的内存占用——它逐行产出模型而不缓存：
# 假设我们有 1000 万条 stat 对象要导出到 csv 文件。
stats = Stat.select()
# 我们想象中的序列化器类。
serializer = CSVSerializer()
# 遍历所有 stats 并序列化。
for stat in stats.iterator():
serializer.serialize_object(stat)
对于简单查询，把行转成字典、namedtuple 或元组还能进一步提速。以下方法可以应用在任意 Select 查询上以改变结果行类型：
BaseQuery.dictsBaseQuery.namedtuplesBaseQuery.tuples
别忘了链式调用 iterator 以同时降低内存。例如：
# 假设我们有 1000 万条 stat 对象要导出到 csv 文件。
stats = Stat.select()
# 我们的序列化器类。
serializer = CSVSerializer()
# 遍历所有 stats（以元组形式返回、不缓存）并序列化。
for stat_tuple in stats.tuples().iterator():
serializer.serialize_tuple(stat_tuple)
当迭代包含多表列的大量行时，Peewee 会为每一行重建模型图，复杂图结构下该操作可能较慢。例如同时选取推文、推文作者的 username 和 avatar 时，每行要创建两个对象（tweet 和 user）。除了上述行类型外，还有第四种方法 BaseQuery.objects：它把行作为模型实例返回，但不尝试解析模型图：
query = (Tweet
.select(Tweet, User) # 同时选择 tweet 和 user 数据。
.join(User))
# 注意：user 列存放在独立的 User 实例中，可通过 tweet.user 访问：
for tweet in query:
print(tweet.user.username, tweet.content)
# 使用 ".objects()" 不会创建 tweet.user 对象，而是把所有
# user 属性直接赋给 tweet 实例：
for tweet in query.objects():
print(tweet.username, tweet.content)
提示：
objects()还接受任意构造函数，会用行元组调用它（见 peewee.py 附近与本文"行元组/字典/namedtuple"一节）。
为追求最大性能，可以直接执行查询并用底层数据库游标迭代结果。Database.execute 接收查询对象，执行后返回 DB-API 2.0 的 Cursor 对象，游标返回原始行元组：
query = Tweet.select(Tweet.content, User.username).join(User)
cursor = database.execute(query)
for (content, username) in cursor:
print(username, '->', content)
十、过滤记录
10.1 使用 Python 运算符
可以用普通 Python 运算符过滤记录。Peewee 支持种类繁多的查询运算符（完整表格见 docs/peewee/query_operators.rst）：
>>> user = User.get(User.username == 'Charlie')
>>> for tweet in Tweet.select().where(Tweet.user == user, Tweet.is_published == True):
... print(tweet.user.username, '->', tweet.message)
...
Charlie -> hello world
Charlie -> this is fun
>>> for tweet in Tweet.select().where(Tweet.created_date < datetime.datetime(2011, 1, 1)):
... print(tweet.message, tweet.created_date)
...
Really old tweet 2010-01-01 00:00:00
10.2 跨连接过滤
>>> for tweet in Tweet.select().join(User).where(User.username == 'Charlie'):
... print(tweet.message)
hello world
this is fun
look at this picture of my food
10.3 复杂条件：位运算符
表达复杂查询时，用括号配合 Python 的位运算 or 和 and 运算符：
>>> Tweet.select().join(User).where(
... (User.username == 'Charlie') |
... (User.username == 'Peewee Herman'))
注意：Peewee 使用位运算符（
&和|）而非逻辑运算符（and和or）。原因是 Python 会把逻辑运算的返回值强转为布尔值。这也是为什么 "IN" 查询必须用.in_()而不能用in运算符。
where 子句里可以放很多有趣的东西，例如：
- 字段表达式，如
User.username == 'Charlie' - 函数表达式，如
fn.Lower(fn.Substr(User.username, 1, 1)) == 'a' - 列与列的比较，如
Employee.salary < (Employee.tenure * 1000) + 40000
还可以嵌套查询。例如，找出用户名以 "a" 开头的用户的推文：
# 获取用户名以 "a" 开头的用户。
a_users = User.select().where(fn.Lower(fn.Substr(User.username, 1, 1)) == 'a')
# ".in_()" 方法表示一个 "IN" 查询。
a_user_tweets = Tweet.select().where(Tweet.user.in_(a_users))
10.4 更多查询示例
更多示例参见 docs/peewee/query_examples.rst，该文档演示了如何用 Peewee 实现 PostgreSQL Exercises 网站上的练习。下面是一些常用模式：
获取活跃用户：
User.select().where(User.active == True)
获取 staff 或 superuser：
User.select().where(
(User.is_staff == True) | (User.is_superuser == True))
获取名为 "charlie" 的用户的推文：
Tweet.select().join(User).where(User.username == 'charlie')
获取 staff 或 superuser 的推文（假设存在外键关系）：
Tweet.select().join(User).where(
(User.is_staff == True) | (User.is_superuser == True))
用子查询获取 staff 或 superuser 的推文：
staff_super = User.select(User.id).where(
(User.is_staff == True) | (User.is_superuser == True))
Tweet.select().where(Tweet.user.in_(staff_super))
十一、排序记录
用 Query.order_by 让结果有序：
>>> for t in Tweet.select().order_by(Tweet.created_date):
... print(t.pub_date)
...
2010-01-01 00:00:00
2011-06-07 14:08:48
2011-06-07 14:12:57
>>> for t in Tweet.select().order_by(Tweet.created_date.desc()):
... print(t.pub_date)
...
2011-06-07 14:12:57
2011-06-07 14:08:48
2010-01-01 00:00:00
还可以用 + 和 - 前缀运算符表达排序方向：
# 下面这些查询是等价的：
Tweet.select().order_by(Tweet.created_date.desc())
Tweet.select().order_by(-Tweet.created_date) # 注意 "-" 前缀。
# 类似地，可以用 "+" 表示升序；未指定排序时升序也是默认值。
User.select().order_by(+User.username)
也可以跨连接排序。假设希望先按作者用户名、再按创建时间倒序排列推文：
query = (Tweet
.select()
.join(User)
.order_by(User.username, Tweet.created_date.desc()))
等价 SQL：
SELECT t1."id", t1."user_id", t1."message", t1."is_published", t1."created_date"
FROM "tweet" AS t1
INNER JOIN "user" AS t2
ON t1."user_id" = t2."id"
ORDER BY t2."username", t1."created_date" DESC
按计算值排序时，可以引入所需的 SQL 表达式，也可以引用分配给该值的别名。先看基础查询：获取所有用户名及其推文数，并按推文数从多到少排序：
query = (User
.select(User.username, fn.COUNT(Tweet.id).alias('num_tweets'))
.join(Tweet, JOIN.LEFT_OUTER)
.group_by(User.username))
方式一：复用 select 子句中的 COUNT 表达式，按推文数倒序：
query = (User
.select(User.username, fn.COUNT(Tweet.id).alias('num_tweets'))
.join(Tweet, JOIN.LEFT_OUTER)
.group_by(User.username)
.order_by(fn.COUNT(Tweet.id).desc()))
方式二：引用 select 子句中计算值的别名。注意不是直接引用别名，而是用 SQL 辅助类包装：
query = (User
.select(User.username, fn.COUNT(Tweet.id).alias('num_tweets'))
.join(Tweet, JOIN.LEFT_OUTER)
.group_by(User.username)
.order_by(SQL('num_tweets').desc()))
方式三：更"Peewee 风格"的做法——先把表达式存成变量再复用：
ntweets = fn.COUNT(Tweet.id)
query = (User
.select(User.username, ntweets.alias('num_tweets'))
.join(Tweet, JOIN.LEFT_OUTER)
.group_by(User.username)
.order_by(ntweets.desc())
十二、随机记录
偶尔需要从数据库随机取一条记录。可以通过按 random 或 rand 函数排序实现（取决于所用数据库）。
PostgreSQL 和 SQLite 使用 Random 函数：
# 抽取 5 位幸运儿：
LotteryNumber.select().order_by(fn.Random()).limit(5)
MySQL 使用 Rand：
# 抽取 5 位幸运儿：
LotteryNumber.select().order_by(fn.Rand()).limit(5)
十三、分页记录
Query.paginate 让"取一页记录"变得简单，接收两个参数：page_number 和 items_per_page。
注意：页码从 1 开始，第一页是第 1 页。
>>> for tweet in Tweet.select().order_by(Tweet.id).paginate(2, 10):
... print(tweet.message)
...
tweet 10
tweet 11
tweet 12
tweet 13
tweet 14
tweet 15
tweet 16
tweet 17
tweet 18
tweet 19
实现上，paginate 会把 1-based 页码换算为 LIMIT/OFFSET（见 peewee.py）：
@Node.copy
def paginate(self, page, paginate_by=20):
if page > 0:
page -= 1
self._limit = paginate_by
self._offset = page * paginate_by
若需要更细粒度控制，可以随时使用 Query.limit 和 Query.offset。
十四、计数记录
任何 select 查询都可以计数：
>>> Tweet.select().count()
100
>>> Tweet.select().where(Tweet.id > 50).count()
50
Peewee 会把查询包进一个外层计数查询，生成类似 SQL：
SELECT COUNT(1) FROM ( ... your query ... );
源码实现见 peewee.py：它去掉排序、以别名 _wrapped 包裹原查询，再对 SELECT 1 的结果执行 COUNT(1) 标量查询。
十五、聚合记录
15.1 分组计数
假设想列出所有用户及其各自的推文数：
query = (User
.select(User, fn.Count(Tweet.id).alias('count'))
.join(Tweet, JOIN.LEFT_OUTER)
.group_by(User))
查询结果返回带全部正常属性的 User 对象，另外多出一个属性 count 表示每个用户的推文数。使用左外连接（LEFT OUTER JOIN）以包含没有推文的用户。
15.2 带 HAVING 的聚合
假设有一个打标签的应用，想找出关联对象数达到某个阈值的标签。以多对多配置为例：
class Photo(Model):
image = CharField()
class Tag(Model):
name = CharField()
class PhotoTag(Model):
photo = ForeignKeyField(Photo)
tag = ForeignKeyField(Tag)
找出至少关联 5 张照片的标签：
query = (Tag
.select()
.join(PhotoTag)
.join(Photo)
.group_by(Tag)
.having(fn.Count(Photo.id) > 5))
等价 SQL：
SELECT t1."id", t1."name"
FROM "tag" AS t1
INNER JOIN "phototag" AS t2 ON t1."id" = t2."tag_id"
INNER JOIN "photo" AS t3 ON t2."photo_id" = t3."id"
GROUP BY t1."id", t1."name"
HAVING Count(t3."id") > 5
如果还想把关联计数取出来存到标签对象上：
query = (Tag
.select(Tag, fn.Count(Photo.id).alias('count'))
.join(PhotoTag)
.join(Photo)
.group_by(Tag)
.having(fn.Count(Photo.id) > 5))
十六、检索标量值
调用 Query.scalar 可以检索标量值：
>>> PageView.select(fn.Count(fn.Distinct(PageView.url))).scalar()
100
传入 as_tuple=True 可以一次检索多个标量：
>>> Employee.select(
... fn.Min(Employee.salary), fn.Max(Employee.salary)
... ).scalar(as_tuple=True)
(30000, 50000)
实现上 scalar() 默认取第一行的第一个值，as_tuple=True 时返回整行（peewee.py）。
十七、窗口函数（Window Functions）
窗口函数是指作用于 SELECT 查询处理过程中滑动数据窗口的聚合函数。它支持：
- 对结果集子集执行聚合；
- 计算累计和（running total）；
- 结果排名；
- 把某行值与前面（或后面！）行的值进行比较。
Peewee 通过调用 Function.over 并传入分区或排序参数来创建窗口函数（见 peewee.py）。
下面示例统一使用如下模型与样例数据：
class Sample(Model):
counter = IntegerField()
value = FloatField()
data = [(1, 10),
(1, 20),
(2, 1),
(2, 3),
(3, 100)]
Sample.insert_many(data, fields=[Sample.counter, Sample.value]).execute()
样例表现在包含：
| id | counter | value |
|---|---|---|
| 1 | 1 | 10.0 |
| 2 | 1 | 20.0 |
| 3 | 2 | 1.0 |
| 4 | 2 | 3.0 |
| 5 | 3 | 100.0 |
17.1 有序窗口（Ordered Windows）
计算 value 的累计和。为了让它是"累计"的，需要按 id 排序：
query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).over(order_by=[Sample.id]).alias('total'))
for sample in query:
print(sample.counter, sample.value, sample.total)
# 1 10. 10.
# 1 20. 30.
# 2 1. 31.
# 2 3. 34.
# 3 100 134.
再举一例：计算当前值与上一个值（按 id 排序）的差：
difference = Sample.value - fn.LAG(Sample.value, 1).over(order_by=[Sample.id])
query = Sample.select(
Sample.counter,
Sample.value,
difference.alias('diff'))
for sample in query:
print(sample.counter, sample.value, sample.diff)
# 1 10. NULL
# 1 20. 10. -- (20 - 10)
# 2 1. -19. -- (1 - 20)
# 2 3. 2. -- (3 - 1)
# 3 100 97. -- (100 - 3)
17.2 分区窗口（Partitioned Windows）
计算每个不同 "counter" 值的平均 value。counter 有 1、2、3 三种可能取值，可以按 counter 分区计算 AVG()：
query = Sample.select(
Sample.counter,
Sample.value,
fn.AVG(Sample.value).over(partition_by=[Sample.counter]).alias('cavg'))
for sample in query:
print(sample.counter, sample.value, sample.cavg)
# 1 10. 15.
# 1 20. 15.
# 2 1. 2.
# 2 3. 2.
# 3 100 100.
同时指定 order_by 与 partition_by 可以在分区内排序。例如在每个 counter 组内按 value 排名：
query = Sample.select(
Sample.counter,
Sample.value,
fn.RANK().over(
order_by=[Sample.value],
partition_by=[Sample.counter]).alias('rank'))
for sample in query:
print(sample.counter, sample.value, sample.rank)
# 1 10. 1
# 1 20. 2
# 2 1. 1
# 2 3. 2
# 3 100 1
17.3 有界窗口（Bounded Windows）
默认情况下，窗口函数以 unbounded preceding（无界前驱）为起点、当前行 为终点进行评估。可以通过在 Function.over 中指定 start 和/或 end 改变窗口边界。Peewee 还在 Window 对象上提供了辅助方法生成合适的边界引用（Window 类定义见 peewee.py 起）：
Window.CURRENT_ROW—— 引用当前行的属性。Window.preceding—— 指定前几行；省略数字表示所有前驱行。Window.following—— 指定后几行；省略数字表示所有后续行。
下面计算 value 的累计和（按 id 排序），但只看当前行及其前两行的累计：
query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).over(
order_by=[Sample.id],
start=Window.preceding(2),
end=Window.CURRENT_ROW).alias('rsum'))
for sample in query:
print(sample.counter, sample.value, sample.rsum)
# 1 10. 10.
# 1 20. 30. -- (20 + 10)
# 2 1. 31. -- (1 + 20 + 10)
# 2 3. 24. -- (3 + 1 + 20)
# 3 100 104. -- (100 + 3 + 1)
严格来说无需显式写
end=Window.CURRENT，因为那是默认值，这里仅为演示。
再看一个例子：计算累计和的"反向"——所有值的总和按 id 顺序逐步减去当前样本值。做法是计算从当前行到最后一行的和：
query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).over(
order_by=[Sample.id],
start=Window.CURRENT_ROW,
end=Window.following()).alias('rsum'))
# 1 10. 134. -- (10 + 20 + 1 + 3 + 100)
# 1 20. 124. -- (20 + 1 + 3 + 100)
# 2 1. 104. -- (1 + 3 + 100)
# 2 3. 103. -- (3 + 100)
# 3 100 100. -- (100)
17.4 过滤聚合（Filtered Aggregates）
聚合函数还支持 filter 函数（PostgreSQL 和 SQLite 3.25+），会被翻译成 FILTER (WHERE...) 子句。过滤表达式用 Function.filter 方法附加到聚合函数上。
例如计算 value 的累计和（按 id），但排除 counter=2 的样本：
query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).filter(Sample.counter != 2).over(
order_by=[Sample.id]).alias('csum'))
for sample in query:
print(sample.counter, sample.value, sample.csum)
# 1 10. 10.
# 1 20. 30.
# 2 1. 30.
# 2 3. 30.
# 3 100 130.
注意：
filter调用必须先于over调用。
17.5 复用窗口定义
如果多个聚合要共用同一个窗口定义，可以创建 Window 对象。Window 接收与 Function.over 相同的参数，可以代替单个参数传给 over()。
这里声明一个按 id 排序的窗口，并用该定义调用多个窗口函数：
win = Window(order_by=[Sample.id])
query = Sample.select(
Sample.counter,
Sample.value,
fn.LEAD(Sample.value).over(win),
fn.LAG(Sample.value).over(win),
fn.SUM(Sample.value).over(win)
).window(win) # 在查询中包含我们的窗口定义。
for row in query.tuples():
print(row)
# counter value lead() lag() sum()
# 1 10. 20. NULL 10.
# 1 20. 1. 10. 30.
# 2 1. 3. 20. 31.
# 2 3. 100. 1. 34.
# 3 100. NULL 3. 134.
17.6 多个窗口定义
上一个例子演示了声明一个 Window 定义并复用于多个聚合。你可以在查询中包含任意数量的窗口定义，但必须保证每个窗口有唯一别名：
w1 = Window(order_by=[Sample.id]).alias('w1')
w2 = Window(partition_by=[Sample.counter]).alias('w2')
query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).over(w1).alias('rsum'), # 累计和。
fn.AVG(Sample.value).over(w2).alias('cavg') # 每类均值。
).window(w1, w2) # 包含我们的窗口定义。
for sample in query:
print(sample.counter, sample.value, sample.rsum, sample.cavg)
# counter value rsum cavg
# 1 10. 10. 15.
# 1 20. 30. 15.
# 2 1. 31. 2.
# 2 3. 34. 2.
# 3 100 134. 100.
类似地，若多个窗口定义有相似部分，可以扩展已定义的窗口。例如先按 counter 分区，再定义第二个窗口在分区基础上追加排序子句：
w1 = Window(partition_by=[Sample.counter]).alias('w1')
# 通过扩展 w1，这个窗口定义同样按 "counter" 分区。
w2 = Window(extends=w1, order_by=[Sample.value.desc()]).alias('w2')
query = (Sample
.select(Sample.counter, Sample.value,
fn.SUM(Sample.value).over(w1).alias('group_sum'),
fn.RANK().over(w2).alias('revrank'))
.window(w1, w2)
.order_by(Sample.id))
for sample in query:
print(sample.counter, sample.value, sample.group_sum, sample.revrank)
# counter value group_sum revrank
# 1 10. 30. 2
# 1 20. 30. 1
# 2 1. 4. 2
# 2 3. 4. 1
# 3 100. 100. 1
17.7 帧类型：RANGE vs ROWS vs GROUPS
帧类型不同，数据库处理有序分组的方式也不同。先额外插入两行 Sample 数据来观察差异：
>>> Sample.create(counter=1, value=20.)
<Sample 6>
>>> Sample.create(counter=2, value=1.)
<Sample 7>
表内现在有：
| id | counter | value |
|---|---|---|
| 1 | 1 | 10.0 |
| 2 | 1 | 20.0 |
| 3 | 2 | 1.0 |
| 4 | 2 | 3.0 |
| 5 | 3 | 100.0 |
| 6 | 1 | 20.0 |
| 7 | 2 | 1.0 |
按 counter 和 value 排序，计算样本的"累计和"来观察帧类型差异。指定帧类型可用：
Window.RANGEWindow.ROWSWindow.GROUPS
RANGE 在存在逻辑重复值时可能产生意外结果：
query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).over(
order_by=[Sample.counter, Sample.value],
frame_type=Window.RANGE).alias('rsum'))
for sample in query.order_by(Sample.counter, Sample.value):
print(sample.counter, sample.value, sample.rsum)
# counter value rsum
# 1 10. 10.
# 1 20. 50.
# 1 20. 50.
# 2 1. 52.
# 2 1. 52.
# 2 3. 55.
# 3 100 155.
新增行后，部分行拥有重复的 category 和 value 值。RANGE 帧类型会把这些重复值一并评估而非分别评估。
更符合直觉的结果用 ROWS 帧类型获得：
query = Sample.select(
Sample.counter,
Sample.value,
fn.SUM(Sample.value).over(
order_by=[Sample.counter, Sample.value],
frame_type=Window.ROWS).alias('rsum'))
for sample in query.order_by(Sample.counter, Sample.value):
print(sample.counter, sample.value, sample.rsum)
# counter value rsum
# 1 10. 10.
# 1 20. 30.
# 1 20. 50.
# 2 1. 51.
# 2 1. 52.
# 2 3. 55.
# 3 100 155.
Peewee 选择帧类型的规则：
- 若用户指定了
frame_type，则使用该帧类型； - 若指定了
start和/或end边界，Peewee 默认使用ROWS； - 若用户未指定帧类型或边界，则使用数据库默认值
RANGE。
GROUPS 帧类型根据排序项，按行组来理解窗口范围。用 GROUPS 可以定义覆盖不同行分组的帧。看示例：
query = (Sample
.select(Sample.counter, Sample.value,
fn.SUM(Sample.value).over(
order_by=[Sample.counter, Sample.value],
frame_type=Window.GROUPS,
start=Window.preceding(1)).alias('gsum'))
.order_by(Sample.counter, Sample.value))
for sample in query:
print(sample.counter, sample.value, sample.gsum)
# counter value gsum
# 1 10 10
# 1 20 50
# 1 20 50 (10) + (20+0)
# 2 1 42
# 2 1 42 (20+20) + (1+1)
# 2 3 5 (1+1) + 3
# 3 100 103 (3) + 100
可以推断：窗口按排序项 (counter, value) 分组，这里观察的是上一个组到当前组之间的窗口。
关于窗口函数 API 的更多信息，参见
Function.over、Function.filter与Window的文档；窗口函数通用背景知识可参考 PostgreSQL 的窗口函数教程及其官方文档、SQLite 的窗口函数文档。
十八、检索行元组 / 字典 / namedtuple
有时你并不需要创建模型实例的开销，只想迭代行数据。此时使用：
BaseQuery.dictsBaseQuery.namedtuplesBaseQuery.tuplesBaseQuery.objects—— 接受任意构造函数，用行元组调用它。
示例——按 URL 分组统计：
stats = (Stat
.select(Stat.url, fn.Count(Stat.url))
.group_by(Stat.url)
.tuples())
# 迭代一组 2 元组，包含 url 和计数。
for stat_url, stat_count in stats:
print(stat_url, stat_count)
用 dicts() 把游标行返回为字典：
stats = (Stat
.select(Stat.url, fn.Count(Stat.url).alias('ct'))
.group_by(Stat.url)
.dicts())
# 迭代一组字典，包含 url 和计数。
for stat in stats:
print(stat['url'], stat['ct'])
十九、RETURNING 子句
PostgresqlDatabase 支持在 UPDATE、INSERT 和 DELETE 查询上使用 RETURNING 子句。指定 RETURNING 后，可以迭代查询所访问的行。
默认情况下，各类查询执行后的返回值为：
INSERT—— 新插入行的自增主键值。当未使用自增主键时，Postgres 会返回新行的主键，而 SQLite 和 MySQL 不会；UPDATE—— 被修改的行数；DELETE—— 被删除的行数。
一旦使用 RETURNING 子句，执行查询的返回值就变成可迭代的游标对象。
PostgreSQL 允许通过 RETURNING 返回被插入或被修改行的数据。例如：有一个 Update 会把注册过期的所有用户账户停用，停用后要给每位用户发邮件通知。与其写 SELECT + UPDATE 两条查询，不如用带 RETURNING 的单条 UPDATE：
query = (User
.update(is_active=False)
.where(User.registration_expired == True)
.returning(User))
# 给每个被停用的用户发邮件。
for deactivate_user in query.execute():
send_deactivation_email(deactivated_user.email)
RETURNING 子句同样可用于 Insert 和 Delete：用于 INSERT 时返回新建行，用于 DELETE 时返回被删除的行。
RETURNING 的唯一限制是：它只能由查询 FROM 子句中出现的表的列组成。要选择某张表的全部列，直接传入 Model 类即可。
再举一例：添加用户并把创建时间设为服务端生成的当前时间戳，然后在一条查询里取回新用户的 ID、Email 和创建时间戳：
query = (User
.insert(email='foo@bar.com', created=fn.now())
.returning(User)) # 表示 User 上的所有列。
# 使用 RETURNING 时，execute() 返回一个游标。
cursor = query.execute()
# 获取刚插入的 user 对象并记录数据：
user = cursor[0]
logger.info('Created user %s (id=%s) at %s', user.email, user.id, user.created)
默认游标返回 Model 实例，但你可以指定其他行类型：
data = [{'name': 'charlie'}, {'name': 'huey'}, {'name': 'mickey'}]
query = (User
.insert_many(data)
.returning(User.id, User.username)
.dicts())
for new_user in query.execute():
print('Added user "%s", id=%s' % (new_user['username'], new_user['id']))
与 Select 查询一样，你可以指定各种结果行类型（见上文"检索行元组/字典/namedtuple"）。
二十、公共表表达式（CTE）
Peewee 支持在所有类型查询中包含公共表表达式（CTE）。CTE 的典型用途：
- 抽出公共子查询；
- 按 CTE 结果集中派生的列分组或过滤；
- 编写递归查询。
要把 Select 查询声明为 CTE，使用 SelectQuery.cte 方法，它会把查询包装进 CTE 对象；要让 CTE 成为查询的一部分，使用 Query.with_cte 方法并传入 CTE 对象列表（源码见 peewee.py 与 peewee.py）。
20.1 简单示例
假设有一批由 key 和浮点值组成的数据点：
class Sample(Model):
key = TextField()
value = FloatField()
data = (
('a', (1.25, 1.5, 1.75)),
('b', (2.1, 2.3, 2.5, 2.7, 2.9)),
('c', (3.5, 3.5)))
# 填充数据。
for key, values in data:
Sample.insert_many([(key, value) for value in values],
fields=[Sample.key, Sample.value]).execute()
用 CTE 计算：对每个 key，哪些值高于该 key 的平均值：
# 先声明用作 CTE 的查询：计算每个 key 的平均值。
cte = (Sample
.select(Sample.key, fn.AVG(Sample.value).alias('avg_value'))
.group_by(Sample.key)
.cte('key_avgs', columns=('key', 'avg_value')))
# 现在查询 sample 表，用 CTE 找出 value 超过该 key 平均值的行，
# 并计算超出平均值多少。
query = (Sample
.select(Sample.key, Sample.value)
.join(cte, on=(Sample.key == cte.c.key))
.where(Sample.value > cte.c.avg_value)
.order_by(Sample.value)
.with_cte(cte))
迭代查询结果，即可看到各组中高于平均值的样本：
>>> for sample in query:
... print(sample.key, sample.value)
# 'a', 1.75
# 'b', 2.7
# 'b', 2.9
20.2 复杂示例
再看一个更完整的例子：用多个 CTE 找出顶级销售区域中每个产品的销售额汇总。模型如下：
class Order(Model):
region = TextField()
amount = FloatField()
product = TextField()
quantity = IntegerField()
先给出 SQL 版本（出自 PostgreSQL 官方文档的经典示例）：
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales) / 10 FROM regional_sales)
)
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
用 Peewee 书写等价逻辑：
reg_sales = (Order
.select(Order.region,
fn.SUM(Order.amount).alias('total_sales'))
.group_by(Order.region)
.cte('regional_sales'))
top_regions = (reg_sales
.select(reg_sales.c.region)
.where(reg_sales.c.total_sales > (
reg_sales.select(fn.SUM(reg_sales.c.total_sales) / 10)))
.cte('top_regions'))
query = (Order
.select(Order.region,
Order.product,
fn.SUM(Order.quantity).alias('product_units'),
fn.SUM(Order.amount).alias('product_sales'))
.where(Order.region.in_(top_regions.select(top_regions.c.region)))
.group_by(Order.region, Order.product)
.with_cte(reg_sales, top_regions))
注意通过 cte.c.列名 访问 CTE 的列，以及 reg_sales.select(...) 在 CTE 之上继续构造子查询的用法。
20.3 递归 CTE
Peewee 支持递归 CTE，在处理由父外键表示的树形结构时非常有用。例如在线书店的分类层级，希望生成一张表展示所有分类及其绝对深度、从根到该分类的路径。
假设每个分类有指向直接父分类的外键：
class Category(Model):
name = TextField()
parent = ForeignKeyField('self', backref='children', null=True)
用递归 CTE 列出所有分类及其深度与父级路径：
# 定义递归 CTE 的基准情形：parent 外键为空的分类。
Base = Category.alias()
level = Value(1).alias('level')
path = Base.name.alias('path')
base_case = (Base
.select(Base.id, Base.name, Base.parent, level, path)
.where(Base.parent.is_null())
.cte('base', recursive=True))
# 定义递归项。
RTerm = Category.alias()
rlevel = (base_case.c.level + 1).alias('level')
rpath = base_case.c.path.concat('->').concat(RTerm.name).alias('path')
recursive = (RTerm
.select(RTerm.id, RTerm.name, RTerm.parent, rlevel, rpath)
.join(base_case, on=(RTerm.parent == base_case.c.id)))
# 递归 CTE 由基准情形与递归项 UNION ALL 而成。
cte = base_case.union_all(recursive)
# 现在从 CTE 中查询分类、层级和路径。
query = (cte
.select_from(cte.c.name, cte.c.level, cte.c.path)
.order_by(cte.c.path))
# 迭代所有分类，打印名称、绝对层级与从根到分类的路径。
for category in query:
print(category.name, category.level, category.path)
# 示例输出：
# root, 1, root
# p1, 2, root->p1
# c1-1, 3, root->p1->c1-1
# c1-2, 3, root->p1->c1-2
# p2, 2, root->p2
# c2-1, 3, root->p2->c2-1
要点解析：
Category.alias()为同一张表创建别名以便自连接；base_case选取根分类（parent IS NULL），并给初始level（Value(1)）和path（自身名字）；- 递归项通过
RTerm.parent == base_case.c.id连接基准，逐层level + 1、path.concat('->').concat(name)； - 最终用
cte.select_from(...)从 CTE 结果中查询。
20.4 数据修改型 CTE
Peewee 还支持数据修改型 CTE。下面用单条查询把数据从一张表移动到归档表：
class Event(Model):
name = CharField()
timestamp = DateTimeField()
class Archive(Model):
name = CharField()
timestamp = DateTimeField()
# 把 Event 表中 24 小时之前的行移动到 Archive。
cte = (Event
.delete()
.where(Event.timestamp < (datetime.now() - timedelta(days=1)))
.returning(Event)
.cte('moved_rows'))
# 创建一个简单的 SELECT 从 CTE 获取结果行。
src = Select((cte,), (cte.c.id, cte.c.name, cte.c.timestamp))
# 把 DELETE 返回的数据插入归档表。
res = (Archive
.insert_from(src, (Archive.id, Archive.name, Archive.timestamp))
.with_cte(cte)
.execute())
大致对应如下 SQL：
WITH "moved_rows" AS (
DELETE FROM "event" WHERE ("timestamp" < XXXX-XX-XXTXX:XX:XX)
RETURNING "id", "name", "timestamp")
INSERT INTO "archive" ("id", "name", "timestamp")
SELECT "moved_rows"."id", "moved_rows"."name", "moved_rows"."timestamp"
FROM "moved_rows";
更多示例可参考仓库中的测试文件 tests/models.py 与 tests/sql.py。
二十一、相关文档
- 外键与连接（Foreign Keys and Joins）：本文原稿中的该部分已独立成篇；
- 查询运算符表：
==、<、.in_()、&、|等全部支持的运算符； - 查询示例：取自 PostgreSQL Exercises 的大量真实查询；
- 模型定义：本文示例中的
User/Tweet等模型定义与字段类型； - 数据库与事务：
atomic()、batch_commit()等数据库级 API 的详细说明。
【免费下载链接】peewee
a small, expressive orm -- supports postgresql, mysql, sqlite, now with asyncio
创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
