楔子
作为一名后端开发，日常工作中难免要和数据库打交道，而想要操作数据库，必然要构建 SQL 语句。你可以手动编写原生 SQL，也可以借助现有的第三方模块，比如 pypika。
本篇文章就来介绍一下 pypika 的用法，由于是第三方库，需要先安装，直接 pip install pypika 即可。
注：Python 还有一个第三库叫 pika，是专门用来连接 RabbitMQ 的，这两个名字虽然很像，但是之间没有任何关系。
简单的 SELECT 查询
构建 SELECT 语句的入口点是 pypika.Query，而查询数据的话必然要有两个关键信息：表和字段。
from pypika import Query
query = Query.from_("people").select("id", "name", "age")
print(query)
"""
SELECT "id","name","age" FROM "people"
"""
# 返回的是一个 QueryBuilder 对象
print(query.__class__)
"""
<class 'pypika.queries.QueryBuilder'>
"""
# 直接转成字符串即可
print(str(query))
"""
SELECT "id","name","age" FROM "people"
"""
# 或者调用 get_sql 方法
print(query.get_sql())
"""
SELECT "id","name","age" FROM "people"
"""
以上我们就构建了最简单的 SQL 语句，当然表和字段都是以字符串形式指定的，我们还可以使用对象。
from pypika import Query, Table, Field
table = Table("people")
fields = [Field("id"), Field("name"), Field("age")]
query = Query.from_(table).select(*fields)
print(query)
"""
SELECT "id","name","age" FROM "people"
"""
# 有了对象之后，我们就可以指定别名了
table = Table("data").as_("d")
fields = [Field("max_count").as_("max_cnt")]
query = Query.from_(table).select(*fields)
print(query)
"""
SELECT "max_count" "max_cnt" FROM "data" "d"
"""
当然目前只指定了表，如果希望在指定表的同时还指定数据库，要怎么做呢？
from pypika import Query, Table, Database
database = Database("fruits")
# 或者直接写成 Table("apple", "fruits") 也可以
table = Table("apple", database)
query = Query.from_(table).select("name", "price")
print(query)
"""
SELECT "name","price" FROM "fruits"."apple"
"""
# 注意：我们不要写成 Table("fruits.apple")，这是不对的
# 因为这表示从一张名为 "fruits.apple" 的表中获取数据
# 显然这是不符合规范的，表名不应该包含 . 这种特殊字符
table = Table("fruits.apple")
query = Query.from_(table).select("name", "price")
print(query)
"""
SELECT "name","price" FROM "fruits.apple"
"""
# 注意 from 子句，结果是 "fruits.apple"
# 真正的格式应该是 "fruits"."apple"，而不是 "fruits.apple"
如果是 MySQL，那么层级是数据库、表，但如果是 PostgreSQL，那么还会多一层 schema。如果想指定 schema，该怎么做呢？
from pypika import Query, Table, Database, Schema
# 数据库
database = Database("fruits")
# schema，隶属于指定的 database
schema = Schema("default", database)
# 表，隶属于指定的 schema
table = Table("apple", schema)
# 构建查询
query = Query.from_(table).select("name", "price")
print(query)
"""
SELECT "name","price" FROM "fruits"."default"."apple"
"""
然后在筛选字段的时候，还可以对字段做操作。
from pypika import Query, Field
# 如果想对字段做操作，那么需要使用 Field 对象
query = Query.from_("t").select(
Field("id") + 1,
Field("first") + Field("last"),
(Field("count") + 200) * Field("price")
)
print(query)
"""
SELECT "id"+1,"first"+"last",("count"+200)*"price" FROM "t"
"""
可以看到还是比较强大的，特别是会自动给你加上引号，这样可以防止关键字冲突。
WHERE 条件过滤
在获取数据的时候，很少会全量获取，绝大多数都是获取满足指定条件的数据，这个时候就需要使用 WHERE 语句。
from pypika import Query, Field
query = Query.from_("t").select("*").where(
(Field("salary") >= 10000) &
(Field("age").between(18, 30)) &
(Field("name").like("张%")) &
(Field("department").isin(["销售", "财务"]))
)
print(query)
"""
SELECT * FROM "t"
WHERE "salary">=10000 AND
"age" BETWEEN 18 AND 30 AND
"name" LIKE '张%' AND
"department" IN ('销售','财务')
"""
你在数据库中可以使用的语法，比如 IS NULL、NOT IN、IS NOT NULL 等等，在 Field 对象中都有指定的方法对应，并且这些方法都见名知意，可以自己试一下。
然后如果有多个条件，那么之间可以用 & 和 | 进行组合，等价于 AND 和 OR。
分组和聚合
接下来说一说 GROUP BY，既然提到它，那就必须要先了解如何在 pypika 中指定聚合函数。
from pypika import functions as fn, Field
fn.Count(Field("id"))
fn.Concat(Field("first_name"), "-", Field("last_name"))
fn.Substring(Field("name"), 3, 9)
所有的聚合函数，都可以在 functions 模块中找到。
from pypika import Field, Query
from pypika import functions as fn
query = Query.from_("people").select(
"age", fn.Count(Field("id"))
).where(
Field("age")[18: 30] & (Field("length") < 160)
).groupby("age")
print(query)
"""
SELECT "age",COUNT("id") FROM "people"
WHERE "age" BETWEEN 18 AND 30 AND "length"<160
GROUP BY "age"
"""
在指定字段的时候，可以直接传一个字符串，也可以传一个 Field 对象。有时候为了方便，当不需要对字段做操作的时候，我们会直接传一个字符串。
但对于 fn.Count 等聚合函数来说，里面一定要传 Field 对象，至于原因我们测试一下就知道了。
from pypika import Field, Query
from pypika import functions as fn
query1 = Query.from_("t").select(fn.Substring("name", 1, 5))
query2 = Query.from_("t").select(fn.Substring(Field("name"), 1, 5))
# SUBSTRING 里面的第一个参数表示长度为 4 的字符串
print(query1)
"""
SELECT SUBSTRING('name',1,5) FROM "t"
"""
# SUBSTRING 里面的第一个参数表示字段 name
print(query2)
"""
SELECT SUBSTRING("name",1,5) FROM "t"
"""
这就是两者的区别，当然有人会觉得这是 PostgreSQL 的语法吧，MySQL 应该是反引号才对，没错，后面的话我们会说如何适配数据库。因为数据库的种类不同，语法也会稍有不同，而目前没有任何信息表明我们使用的到底是哪一种数据库。
当执行了 GROUP BY 之后，还可以继续执行 HAVING。
from pypika import Field, Query
from pypika import functions as fn
query = Query.from_("people").select(
"age", fn.Count(Field("id"))
).groupby("age").having(fn.Count(Field("id")) > 30)
print(query)
"""
SELECT "age",COUNT("id") FROM "people"
GROUP BY "age" HAVING COUNT("id")>30
"""
以上就是分组和聚合。
两表 JOIN
如果是两张表需要 JOIN 的话，该怎么做呢？
from pypika import Query, Table
t1 = Table("t1")
t2 = Table("t2")
query = Query.from_(t1).select(
t1.name, t2.age
).left_join(t2).using("id")
print(query)
"""
SELECT "t1"."name","t2"."age" FROM "t1"
LEFT JOIN "t2" USING ("id")
"""
由于涉及到多张表，那么当字段出现重叠的时候，需要同时指定表名，可以直接通过获取 Table 对象属性的方式指定。但如果表的字段名恰好和 Table 对象的某个属性名冲突，就不行了，我们举个例子。
from pypika import Query, Table
t1 = Table("t1")
t2 = Table("t2")
# 比如 Table 对象有一个属性叫 field
# 而表中也有一个字段叫 field
query = Query.from_(t1).select(
t2.field
).left_join(t2).using("id")
print(query)
"""
SELECT <bound method Selectable.field of Table('t2')>
FROM "t1" LEFT JOIN "t2" USING ("id")
"""
print(t2.field)
"""
<bound method Selectable.field of Table('t2')>
"""
显然这个时候就比较尴尬了，那我们应该怎么做呢？
from pypika import Query, Table, Field
t1 = Table("t1")
t2 = Table("t2")
query = Query.from_(t1).select(
Field("field", table=t2)
).left_join(t2).using("id")
print(query)
"""
SELECT "t2"."field" FROM "t1" LEFT JOIN "t2" USING ("id")
"""
这样就没问题了，Field 类还可以接收一个 table 参数，指定字段来自于哪张表，当然如果是单表，那么该字段就无需指定了。
当然除了 LEFT JOIN 之外，其它 JOIN 也是支持的。
这些方法内部都调用了 join 方法。
如果两张表要连接的字段的名字相同、并且是等值连接，那么可以使用 using。但还有一种情况是：两个名字不同的字段进行等值连接，比如一张表的 uid 等于另一张表的 tid 等等。
from pypika import Query, Table
t1 = Table("t1")
t2 = Table("t2")
# Field("uid", table=t1) 还可以写成 t1.field("uid")
# 这两者是完全等价的，但 t1.field("uid") 写起来更方便
query = Query.from_(t1).select(t2.age, t1.name) \
.left_join(t2) \
.on(t1.field("uid") == t2.field("tid")) \
.where(t1.age > 18)
print(query)
"""
SELECT "t2"."age","t1"."name" FROM "t1"
LEFT JOIN "t2" ON "t1"."uid"="t2"."tid"
WHERE "t1"."age">18
"""
以上就是 JOIN 相关的内容，至于三表连接，你可以自己试一下。
嵌套子查询
再来看看嵌套子查询：
from pypika import Query, Table, functions as fn
t1 = Table("t1")
t2 = Table("t2")
sub_query = Query.from_(t1).select(fn.Avg(t2.age).as_("avg")) \
.left_join(t2).using("id").where(t1.age > 18)
print(sub_query)
"""
SELECT AVG("t2"."age") "avg" FROM "t1"
LEFT JOIN "t2" USING ("id") WHERE "t1"."age">18
"""
# 子查询完全可以当成一张表来操作
query = Query.from_(t1).select("age", "name").where(
t1.field("age") > Query.from_(sub_query).select("avg")
)
print(query)
"""
SELECT "age","name" FROM "t1"
WHERE "age">(
SELECT "sq0"."avg" FROM (
SELECT AVG("t2"."age") "avg"
FROM "t1" LEFT JOIN "t2" USING ("id") WHERE "t1"."age">18
) "sq0"
)
"""
集合运算
两个结果集之间是可以合并的，比如 UNION 和 UNION ALL，至于 UNION DISTINCE 是 UNION 的同义词，所以 pypika 没有设置专门的函数。另外 UNION 虽然可以用来合并多个结果集，但前提是它们要有相同的列。
from pypika import Query, Table
t1 = Table("t1")
t2 = Table("t2")
query1 = Query.from_(t1).select("name", "salary")
query2 = Query.from_(t2).select("name", "salary")
print(query1.union(query2))
print(query2.union(query1))
"""
(SELECT "name","salary" FROM "t1") UNION (SELECT "name","salary" FROM "t2")
(SELECT "name","salary" FROM "t2") UNION (SELECT "name","salary" FROM "t1")
"""
# union 可以使用 + 代替
print(str(query1 + query2) == str(query1.union(query2))) # True
print(str(query2 + query1) == str(query2.union(query1))) # True
# union_all 可以使用 * 代替
print(query1.union_all(query2))
print(query2.union_all(query1))
"""
(SELECT "name","salary" FROM "t1") UNION ALL (SELECT "name","salary" FROM "t2")
(SELECT "name","salary" FROM "t2") UNION ALL (SELECT "name","salary" FROM "t1")
"""
print(str(query1 * query2) == str(query1.union_all(query2))) # True
print(str(query2 * query1) == str(query2.union_all(query1))) # True
此外还有交集、差集、对称差集。
from pypika import Query, Table
t1 = Table("t1")
t2 = Table("t2")
query1 = Query.from_(t1).select("name", "salary")
query2 = Query.from_(t2).select("name", "salary")
# 交集，没有提供专门的操作符
print(query1.intersect(query2))
"""
(SELECT "name","salary" FROM "t1")
INTERSECT
(SELECT "name","salary" FROM "t2")
"""
# 差集，可以使用减号替代
print(query1.minus(query2))
"""
(SELECT "name","salary" FROM "t1")
MINUS
(SELECT "name","salary" FROM "t2")
"""
# 对称差集，没有提供专门的操作符
print(query1.except_of(query2))
"""
(SELECT "name","salary" FROM "t1")
EXCEPT
(SELECT "name","salary" FROM "t2")
"""
时间间隔
有时我们查找数据需要查找 7 天以内的，或者 1 个月以内的，这时候该怎么做呢？
from pypika import Query, Table, functions as fn, Interval
t = Table('fruits')
query = Query.from_(t).select(t.id, t.name) \
.where(t.harvest_date + Interval(months=1) < fn.Now())
print(query)
"""
SELECT "id","name" FROM "fruits"
WHERE "harvest_date"+INTERVAL '1 MONTH'<NOW()
"""
多值比较
SQL 有一个非常有用的特性，假设一张表中有 year、month 这两个字段，然后我想找出 year、month 组合起来之后大于 2020 年 7 月的记录。比如 year = 2021、month = 2 这条记录就是合法的，因为 year 是大于 2020 的。而 year = 2020、month = 8 也是合法的。
显然这个时候就有些不好搞了，我们无法通过 year > 2020 and month > 7 这种形式，但是数据库提供了多值比较：
select * from t where (year, month) > (2020, 7)
是不是很像元组呢？会先比较 year，如果满足 year > 2020，直接成立。year < 2020，直接不成立，后面就不用比了。如果 year = 2020，那么再比较 month。
from pypika import Query, Table, Tuple
t = Table("t")
query = Query.from_(t).select(t.salary).where(
Tuple(t.year, t.month) >= (2020, 7))
print(query)
"""
SELECT "salary" FROM "t" WHERE ("year","month")>=(2020,7)
"""
对于 in 字句也是同样的道理：
from pypika import Query, Table, Tuple
t = Table("t")
query = Query.from_(t).select(t.salary).where(
Tuple(t.year, t.month
).isin([(2020, 7), (2020, 8), (2020, 9)]))
print(query)
"""
SELECT "salary" FROM "t"
WHERE ("year","month") IN ((2020,7),(2020,8),(2020,9))
"""
CASE WHEN
然后看看 CASE WHEN，SQL 层面上的就不说了，我们只看怎么用 pypika 实现。
from pypika import Table, Query, Case
t = Table("t")
query = Query.from_(t).select(
t.name,
Case().when(t.age < 18, "未成年").when(t.age < 30, "成年")
.when(t.age < 50, "中年").else_("老年").as_("age")
)
print(query)
"""
SELECT "name",
CASE WHEN "age"<18 THEN '未成年'
WHEN "age"<30 THEN '成年'
WHEN "age"<50 THEN '中年'
ELSE '老年' END "age"
FROM "t"
"""
WITH 语句
WITH 语句就是给子查询指定一个名字，然后在其它地方可以直接使用该名字，就像访问一张已存在的表一样。
from pypika import Table, Query, AliasedQuery
t = Table("t")
sub_query = Query.from_(t).select("*")
query = Query.with_(sub_query, "alias").from_(
AliasedQuery("alias")).select("*")
print(query)
"""
WITH alias AS (SELECT * FROM "t") SELECT * FROM alias
"""
DISTINCT
如果我们想对结果集进行去重的话，要怎么做呢？
from pypika import Query, Table
t = Table("t")
# 只需要在 select 之前调用一次 distinct 即可
query = Query.from_(t).distinct().select(t.id, t.age)
print(query)
"""
SELECT DISTINCT "id","age" FROM "t"
"""
ORDER BY 排序
在查询到结果集之后，也可以进行排序。
from pypika import Query, Order
query = Query.from_("t").select("id", "name") \
.orderby("id", order=Order.desc)
print(query)
"""
SELECT "id","name" FROM "t" ORDER BY "id" DESC
"""
# 如果是多个字段的话
query = Query.from_("t").select("id", "name") \
.orderby("age", "id")
print(query)
"""
SELECT "id","name" FROM "t" ORDER BY "age","id"
"""
query = Query.from_("t").select("id", "name") \
.orderby("age", "id", order=Order.desc)
print(query)
"""
SELECT "id","name" FROM "t" ORDER BY "age" DESC,"id" DESC
"""
# 如果是一个字段升序、一个字段降序怎么办？很简单，调用两次 orderby 即可
query = Query.from_("t").select("id", "name") \
.orderby("age", order=Order.desc).orderby("id")
print(query)
"""
SELECT "id","name" FROM "t" ORDER BY "age" DESC,"id"
"""
LIMIT 和 OFFSET
获取到结果集之后，可以选择指定的条数，比如实现分页功能。
from pypika import Table, Query, Field
from pypika import functions as fn, Order
table = Table("t")
query = Query.from_(table) \
.select(fn.Count(Field("id")).as_("count"), "age", "length") \
.where(table.field("age") > 18) \
.groupby("age", "length") \
.having(fn.Count("id") > 10) \
.orderby("count", order=Order.desc) \
.orderby("age", order=Order.asc) \
.limit(10).offset(5)
print(query)
"""
SELECT COUNT("id") "count","age","length"
FROM "t" WHERE "age">18
GROUP BY "age","length"
HAVING COUNT('id')>10
ORDER BY "count" DESC,"age" ASC
LIMIT 10 OFFSET 5
"""
这里我们将所有子句都演示了一遍，算是做一个总结。
插入数据
以上说的都是查询数据，那么插入数据要怎么实现呢？
from pypika import Table, Query
t = Table("t")
# 查询是 Query.from_，插入数据是 Query.into
query = Query.into(t).insert(1, "古明地觉", 16, "东方地灵殿")
print(query)
"""
INSERT INTO "t" VALUES (1,'古明地觉',16,'东方地灵殿')
"""
# 如果存在 None 值，会自动处理
query = Query.into(t).insert(1, "古明地觉", None, "东方地灵殿")
print(query)
"""
INSERT INTO "t" VALUES (1,'古明地觉',NULL,'东方地灵殿')
"""
如果表中存在 JSON，那么直接对字典 dumps 一下传进去即可。
当然上面是单条插入，如果我想同时插入多条数据，该怎么做呢？
from pypika import Table, Query
table = Table("t")
query = Query.into(table) \
.insert(1, "古明地觉", 16, "东方地灵殿") \
.insert(2, "古明地恋", 15, "东方地灵殿")
print(query)
"""
INSERT INTO "t"
VALUES (1,'古明地觉',16,'东方地灵殿'),
(2,'古明地恋',15,'东方地灵殿')
"""
# 或者
query = Query.into(table).insert((1, "古明地觉", 16, "东方地灵殿"),
(2, "古明地恋", 15, "东方地灵殿"))
print(query)
"""
INSERT INTO "t"
VALUES (1,'古明地觉',16,'东方地灵殿'),
(2,'古明地恋',15,'东方地灵殿')
"""
在插入数据的时候，也可以选择指定的部分字段。
from pypika import Table, Query, Field
table = Table("t")
query = Query.into(table).columns(
"id", table.field("name"), table.age, Field("place")
).insert(1, "古明地觉", 16, "东方地灵殿")
print(query)
"""
INSERT INTO "t" ("id","name","age","place")
VALUES (1,'古明地觉',16,'东方地灵殿')
"""
当然也可以将一张表的记录插入到另一张表中。
from pypika import Table, Query, Field
t1 = Table("t1")
t2 = Table("t2")
query = Query.into(t1).columns("id", "name", "age") \
.from_(t2).select("id", "name", "age") \
.where(Field("age") > 18)
print(query)
"""
INSERT INTO "t1" ("id","name","age")
SELECT "id","name","age" FROM "t2" WHERE "age">18
"""
两个表 JOIN 之后的结果也可以插入到新表中，不过在 Python 中拼接 SQL 语句的时候，很少会遇到这种需求。
更新数据
再来看看更新数据怎么做？
from pypika import Table, Query
t = Table("t")
# 更新是 update
query = Query.update(t).set(t.name, "古明地恋")
print(query)
"""
UPDATE "t" SET "name"='古明地恋'
"""
query = Query.update(t).set(t.name, "古明地恋").where(t.id == 1)
print(query)
"""
UPDATE "t" SET "name"='古明地恋' WHERE "id"=1
"""
query = Query.update(t).set(t.name, "古明地恋").set(t.age, 16)
print(query)
"""
UPDATE "t" SET "name"='古明地恋',"age"=16
"""
用另一张表的数据更新当前也是一种比较常见的操作，比如 t1 有 uid、name 两个字段，t2 有 tid、name 两个字段。如果 t1 的 uid 在 t2 的 tid 中存在，那么就用 t2 的 name 更新掉 t1 的 name。
from pypika import Table, Query
t1 = Table("t1")
t2 = Table("t2")
query = Query.update(t1).join(t2).on(
t1.uid == t2.tid
).set(t1.name, t2.name).where(t1.uid > 10)
print(query)
"""
UPDATE "t1" JOIN "t2" ON "t1"."uid"="t2"."tid"
SET "name"="t2"."name" WHERE "t1"."uid">10
"""
数据库适配
不同数据库的 SQL 语法会有略微不同，最大的一个不同就是包裹字段所用的符号，MySQL 用的是反引号、PostgreSQL 用的是双引号。而 pypika 不知道你的数据库种类，所以默认用的是双引号。如果想适配 MySQL 的话，那么应该告诉 pypika，我们要适配 MySQL。
from pypika import (
MySQLQuery,
PostgreSQLQuery,
OracleQuery,
MSSQLQuery,
SQLLiteQuery,
ClickHouseQuery,
VerticaQuery
)
# pypika 提供多种数据库的适配，我们以 MySQL 为例
# 之前用的是 Query 这个类，而以上这些类都继承 Query
# 所以语法和之前是一样的
from pypika import Table, MySQLQuery, PostgreSQLQuery
t = Table("t")
print(
MySQLQuery.from_(t).select(t.id, t.age)
) # SELECT `id`,`age` FROM `t`
print(
PostgreSQLQuery.from_(t).select(t.id, t.age)
) # SELECT "id","age" FROM "t"
要操作哪一种数据库，直接选择对应的 Query 即可。
小结
以上就是 pypika 的相关内容，总的来说还是很方便的，在面对一些不复杂的 SQL 时，使用该模块会非常方便。
当然 pypika 还支持更多高级用法，比如窗口函数，有兴趣可以查看官网。