pypika：SQL 语句生成器，让你再也不用为拼接 SQL 语句而发愁

楔子

日常工作中难免会和数据库打交道，而为了操作数据库我们会构建相应的 SQL 语句，而 pypika 的存在可以让我们更轻松地实现这一点。

pypika 是一个第三方库，我们首先要安装它，直接 pip install pypika 即可。

注：Python 还有一个第三库叫做 pika，是专门用来连接 RabbitMQ 的，这两个名字虽然很像，但是之间没有任何关系。

简单的 select

构建 select 语句的入口点是 pypika.Query，而查询数据的话必然要有两个关键信息：查询的表和指定的字段。

from pypika import Query


sql = Query.from_("t").select("id", "name", "age")
print(sql)  # SELECT "id","name","age" FROM "t"
# 返回的是一个 QueryBuilder 对象
print(sql.__class__)  # <class 'pypika.queries.QueryBuilder'>
# 直接转成字符串即可
print(str(sql))  # SELECT "id","name","age" FROM "t"
# 或者调用 get_sql 方法
print(sql.get_sql())  # SELECT "id","name","age" FROM "t"

如果指定了 select 但是没有指定表名，那么会报错：

from pypika import Query

try:
    sql = Query.select("id", "name", "age")
except Exception as e:
    print(e)  # Cannot select id, no FROM table specified.

Table、Column、Schema、Database

如果是简单的 select 语句，就像上面的栗子那样，那么 "表" 和 "字段" 直接通过字符串指定即可。但是对于更复杂的栗子来说，我们应该使用 pypika.Table，我们还以上面为例：

from pypika import Query, Table

table = Table("t")
sql = Query.from_(table).select(table.id, table.name, table.age)
# 得到的结果是一样的
print(str(sql))  # SELECT "id","name","age" FROM "t"

当然对于表而言，我们也是可以指定别名的。

from pypika import Query, Table

table = Table("t").as_("t1")
sql = Query.from_(table).select(table.id, table.name, table.age)

# 一旦起了别名，那么 pypika 就会认为你可能会进行连表操作，于是在选择字段的时候会带上别名
print(str(sql))  # SELECT "t1"."id","t1"."name","t1"."age" FROM "t" "t1"

对于 PostgreSQL 而言，它是具有 schema 的，那么我们要如何指定呢？可能有人会这么做？

from pypika import Query, Table, Schema

table = Table("my_schema.t")
sql = Query.from_(table).select(table.id, table.name, table.age)

# 这样是不行的，因为这表示从一张名为 my_schema.t 的表中筛选数据
# 显然这是不符合规范的，表名不应该包含 . 这种特殊字符
print(str(sql))  # SELECT "id","name","age" FROM "my_schema.t"
# 真正的格式应该是 "my_schema"."t" 而不是 "my_schema.t"


# 所以我们需要导入 Schema 这个类
sch = Schema("my_schema")
# 对 Schema 对象直接通过属性引用的方式即可指定表名，比如 sch.t 就表示从 my_schema 下寻找名称为 t 的表
sql = Query.from_(sch.t).select(sch.t.id, sch.t.name, sch.t.age)
print(str(sql))  # SELECT "id","name","age" FROM "my_schema"."t"

# 如果表名包含中文或者特殊字符的话该怎么办呢？比如有一张表叫 "<girl>"
# 显然不可能通过 sch.<girl> 的方式引用，于是这便涉及到 Python 基础了，我们可以使用反射的方式实现
print(
    str(Query.from_(t := getattr(sch, "<girl>")).select(t.id, t.name, t.age))
)  # SELECT "id","name","age" FROM "my_schema"."<girl>"

# 或者更简单的做法
table = Table("<girl>", schema="my_schema")
sql = Query.from_(table).select(table.id, table.name, table.age)
print(sql)  # SELECT "id","name","age" FROM "my_schema"."<girl>"

可以指定 table、schema，那么必然逃不掉 database。

from pypika import Query, Database

db = Database("my_db")
# 从 my_db 这个数据库下寻找名字为 my_schema 的 schema，在 my_schema 下寻找名字为 t 的表
table = db.my_schema.t
sql = Query.from_(table).select(table.id)
print(sql)  # SELECT "id" FROM "my_db"."my_schema"."t"

在查询到结果集之后，我们还可以进行排序：

from pypika import Query, Order

sql = Query.from_("t").select("id", "name").orderby("id", order=Order.desc)
print(sql)  # SELECT "id","name" FROM "t" ORDER BY "id" DESC

# 如果是多个字段的话
sql = Query.from_("t").select("id", "name").orderby("age", "id")
print(sql)  # SELECT "id","name" FROM "t" ORDER BY "age","id"

sql = Query.from_("t").select("id", "name").orderby("age", "id", order=Order.desc)
print(sql)  # SELECT "id","name" FROM "t" ORDER BY "age" DESC,"id" DESC

# 如果是一个字段升序、一个字段降序怎么办？答案是调用两次 orderby 即可
sql = Query.from_("t").select("id", "name").orderby("age", order=Order.desc).orderby("id")
print(sql)  # SELECT "id","name" FROM "t" ORDER BY "age" DESC,"id"

四则运算

执行 select 查询的时候，字段是可以进行运算的。

from pypika import Query, Field, Table

table = Table("t")
print(
    Query.from_(table).select(table.id, table.age + 1)
)  # SELECT "id","age"+1 FROM "t"

# 我们看一下 table.id 是什么类型
print(table.id.__class__)  # <class 'pypika.terms.Field'>

# 所以我们也可以直接根据 Field 进行构建
print(
    Query.from_(table).select(Field("id"), Field("age") + 1, Field("first") + Field("last"))
)  # SELECT "id","age"+1,"first"+"last" FROM "t"

# 补充一下：对于 Database 而言，进行属性查找会得到 Schema
db = Database("my_db")
print(db.my_schema.__class__)  # <class 'pypika.queries.Schema'>
# 对于 Schema 而言，进行属性查找会得到 Table
print(db.my_schema.t.__class__)  # <class 'pypika.queries.Table'>
# 对于 Table 而言，进行属性查找会得到 Field
print(db.my_schema.t.id.__class__)  # <class 'pypika.terms.Field'>

当然我们也可以进行更加复杂的运算，以及给字段起别名：

from pypika import Query, Field, Table

table = Table("t")

sql = Query.from_(table).select(Field("id").as_("ID"),
                                ((Field("count") + 2000) * Field("price")).as_("amount"))
# 字段起别名可以使用 as、也可以不使用
print(sql)  # SELECT "id" "ID",("count"+2000)*"price" "amount" FROM "t"

可以看到还是比较强大的，特别是会自动给你加上引号，这样可以防止关键字冲突。

where 语句

我们在获取数据的时候，很少会全部获取，绝大多数情况只会获取指定的数据，这个时候就需要使用 where 语句。

from pypika import Query, Field, Table

girl = Table("girl")

# 注意：where 里面不可以写 "age > 18"
sql = Query.from_(girl).select("*").where(Field("age") >= 18)
print(sql)  # SELECT * FROM "girl" WHERE "age">=18

# 如果是多个 where 条件怎么办呢？答案是调用多次 where 即可
# 就像我们之前使用的 orderby 一样，其实除了 orderby 之外，像 select、where、groupby都是可以多次调用的
sql = (Query.from_(girl).select("id").select(girl.name).select(Field("age"))
       .where(Field("age") >= 18).where(girl.age <= 30).where(Field("length") < 165))
print(sql)  # SELECT "id","name","age" FROM "girl" WHERE "age">=18 AND "age"<=30 AND "length"<165

还有 in 和 between 也是支持的，举个例子：

from pypika import Query, Field, Table, Database

girl = Table("girl")

sql = Query.from_(girl).select("*").where(Field("age").between(18, 30))
print(sql)  # SELECT * FROM "girl" WHERE "age" BETWEEN 18 AND 30

# between 还可以使用切片的形式，并且切片除了数字之外、时间也是可以的，因为数据库是支持对时间的 between 语法的
sql = Query.from_(girl).select("*").where(Field("age")[18: 30]).where(Field("length").isin([155, 156, 157]))
print(sql)  # SELECT * FROM "girl" WHERE "age" BETWEEN 18 AND 30 AND "length" IN (155,156,157)

当然还有其它的方法，比如：contains、isnull、notnull、notin、not_like 等等，可以自己试一下。

另外可能有人觉得每一个条件都要调用一次 where 太麻烦了，而且默认都是 AND，如果是 OR 的话怎么办呢？于是我们就引出了 &、｜、^ 三个符号。

AND

from pypika import Query, Field, Table

girl = Table("girl")

sql = Query.from_(girl).select("*").where((Field("age") > 18) & Field("length") < 160)
print(sql)  # SELECT * FROM "girl" WHERE "age">18 AND "length"<160

OR

from pypika import Query, Field, Table

girl = Table("girl")

sql = Query.from_(girl).select("*").where((Field("age") > 18) | Field("length") < 160)
print(sql)  # SELECT * FROM "girl" WHERE "age">18 OR "length"<160

XOR

from pypika import Query, Field, Table

girl = Table("girl")

sql = Query.from_(girl).select("*").where((Field("age") > 18) ^ Field("length") < 160)
print(sql)  # SELECT * FROM "girl" WHERE "age">18 XOR "length"<160

对于 AND 和 OR，pypika 提供了专门的类来实现链式的 AND、OR 表达式。

from pypika import Query, Field, Table, Criterion

girl = Table("girl")

sql = Query.from_(girl).select("*").where(
    Criterion.all([Field("id") > 10,
                   Field("age") > 18,
                   Field("length").isin([155, 156, 157])])
)
print(sql)  # SELECT * FROM "girl" WHERE "id">10 AND "age">18 AND "length" IN (155,156,157)


sql = Query.from_(girl).select("*").where(
    Criterion.any([Field("id").between(10, 50),
                   Field("age") > 18,
                   Field("length").isin([155, 156, 157])])
)
print(sql)  # SELECT * FROM "girl" WHERE "id" BETWEEN 10 AND 50 OR "age">18 OR "length" IN (155,156,157)

分组和聚合

然后是 group by，我们看看如何实现：

from pypika import Query, Field, Table, functions as fn

girl = Table("girl")

# fn 是一个模块，里面包含了很多的类，每个类对应一个聚合函数
# 但是注意：往类里面（比如下面的 fn.Count）传入字段的时候，不能直接传递字符串，需要传递一个 Field 对象
sql = Query.from_(girl).select("age", fn.Count(Field("id"))).where(
    Field("age")[18: 30] & (Field("length") < 160)
).groupby(Field("age"))
print(sql)  # SELECT "age",COUNT("id") FROM "girl" WHERE "age" BETWEEN 18 AND 30 AND "length"<160 GROUP BY "age"

可以看到拼接的 SQL 语句是正确的，如果我们传递的是 fn.Count("id") 的话，那么拼接之后得到的就是 Count('id')。pypika 给你变成了单引号，显然这是不合法的，因为在数据库中单引号表示字符串。如果传递字符串，那么只能传递 "*"，会得到 Count(*)，这是个特例。当然有人会说这是 PostgreSQL 的语法吧，MySQL 应该是反引号才对，没错，后面的话我们会说如何适配数据库。因为数据库的种类不同，语法也会稍有不同，而目前我们没有任何信息表明我们使用的到底是哪一种数据库。

除了 fn.Count 表示计算次数之外，还有很多其它的函数，比如：fn.Sum 表示计算总和；fn.Avg 表示计算平均值等等。除此之外还有很多其它的函数，比如取绝对值、对数、类型转换等等，在 SQL 里面有的都可以去里面找。关于 fn 里面的功能后面会提及，我们当前的重点是 groupby，当 groupby 之后我们就可以接 having 了。

from pypika import Query, Field, Table, functions as fn

girl = Table("girl")

sql = Query.from_(girl).select("age", fn.Count(Field("id"))).where(
    Field("age")[18: 30] & (Field("length") < 160)
).groupby(Field("age")).having(fn.Count(Field("id")) > 30)
print(sql)  
# SELECT "age",COUNT("id") FROM "girl" WHERE "age" BETWEEN 18 AND 30 AND "length"<160 GROUP BY "age" HAVING COUNT("id")>30

两表 join

多表 join 的话可以直接使用 Query.join 来实现，并且后面需要跟 using 或 on 字句。

from pypika import Query, Table


table1 = Table("t1")
table2 = Table("t2")

sql = Query.from_(table1).select(table2.age, table1.name).left_join(table2).using("id")
print(sql)  # SELECT "t2"."age","t1"."name" FROM "t1" LEFT JOIN "t2" USING ("id")

# 此时查询字段的时候就不能只写字段名了，因为涉及到了两张表
# 假设我们指定的是 "age"，那么结果默认是 "t1"."age"，但我们不一定就是要 t1 里面的 age
# 假设我们指定的是 Field("age")，那么结果得到的就是 "age"，如果两张表里面都要 age 字段，那么数据库是无法区分我们到底选择哪张表里的 age 字段的
# 可能有人觉得 Field("t2.age") 不就行了，答案是不行的，原因跟我们之前说的一样，该结果得到的是 "t2.age"，而结果应该是 "t2"."age"

# 所以我们需要通过 table2.age 的方式，但是这样也隐藏一个问题，其实这个问题在之前就应该说的
# 我们说通过 "Table对象.字段" 的方式可以筛选指定的字段，但是 Table对象有没有自己的方法呢？
print(table1.as_)  # <bound method builder.<locals>._copy of Table('t1')>

# 显然 as_ 就是一个方法，用于起别名，我们之前用过
# 但是问题来了，如果数据库表中就有一个字段叫做 as_ 该咋办
sql = Query.from_(table1).select(table2.as_).left_join(table2).using("id")
print(sql)  # SELECT <bound method builder.<locals>._copy of Table('t2')> FROM "t1" LEFT JOIN "t2" USING ("id")

# 我们看到结果不是我们想要的，所以还有最后一个做法
sql = Query.from_(table1).select(table2.field("age")).left_join(table2).using("id")
print(sql)  # SELECT "t2"."age" FROM "t1" LEFT JOIN "t2" USING ("id")

# 因此我们看到选择字段的方式还是很多的，总结一下：
"""

+---------------------+----------------------------------------------+
|         方式        |                     缺点                     |
+---------------------+----------------------------------------------+
|        "字段"       |               不能是多表 join                |
|    Field("字段")    |               不能是多表 join                |
|      table.字段     | 可以多表 join，但是不能和 Table 内部方法重名 |
| table.field("字段") |              没有限制，终极选择              |
+---------------------+----------------------------------------------+

"""

join 的几种方式

left_join
right_join
inner_join
outer_join
cross_join
hash_join

以上在内部都调用了 join 方法，看部分源码：

    def inner_join(self, item: Union[Table, "QueryBuilder", AliasedQuery]) -> "Joiner":
        return self.join(item, JoinType.inner)

    def left_join(self, item: Union[Table, "QueryBuilder", AliasedQuery]) -> "Joiner":
        return self.join(item, JoinType.left)

    def right_join(self, item: Union[Table, "QueryBuilder", AliasedQuery]) -> "Joiner":
        return self.join(item, JoinType.right)

    def outer_join(self, item: Union[Table, "QueryBuilder", AliasedQuery]) -> "Joiner":
        return self.join(item, JoinType.outer)

    def cross_join(self, item: Union[Table, "QueryBuilder", AliasedQuery]) -> "Joiner":
        return self.join(item, JoinType.cross)

    def hash_join(self, item: Union[Table, "QueryBuilder", AliasedQuery]) -> "Joiner":
        return self.join(item, JoinType.hash)

使用 on 进行连接

当两张表要连接的字段的名字相同、并且是等值连接，那么可以使用 using。但是这种情况还是不多的，最常见的情况是：两个名字不同的字段进行等值连接，比如一张表的 uid 等于另一张表的 tid 等等。

from pypika import Query, Table


table1 = Table("t1")
table2 = Table("t2")

sql = Query.from_(table1).select(table2.age, table1.name).left_join(table2)\
    .on(table1.field("uid") == table2.field("tid"))
print(sql)  # SELECT "t2"."age","t1"."name" FROM "t1" LEFT JOIN "t2" ON "t1"."uid"="t2"."tid"

当然我们可以在 join 之前使用 where，先把不必要的数据过滤掉。

from pypika import Query, Table


table1 = Table("t1")
table2 = Table("t2")

sql = Query.from_(table1).select(table2.age, table1.name).left_join(table2).using("id")\
    .where(table1.age > 18)
print(sql)  # SELECT "t2"."age","t1"."name" FROM "t1" LEFT JOIN "t2" USING ("id") WHERE "t1"."age">18

嵌套子查询

再来看看嵌套子查询：

from pypika import Query, Table, functions as fn


table1 = Table("t1")
table2 = Table("t2")

sub_query = Query.from_(table1).select(fn.Avg(table2.age).as_("avg")).left_join(table2).using("id")\
    .where(table1.age > 18)
print(sub_query)  # SELECT AVG("t2"."age") "avg" FROM "t1" LEFT JOIN "t2" USING ("id") WHERE "t1"."age">18

# 子查询完全可以当成一张表来操作
sql = Query.from_(table1).select("age", "name").where(table1.field("age") > Query.from_(sub_query).select("avg"))
print(sql)
"""
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 distinct 它是 union 的同义词，所以 pypika 没有设置专门的函数。union 虽然可以用来合并多个结果集，但前提是它们要有相同的列。

from pypika import Query, Table

table1 = Table("t1")
table2 = Table("t2")

sql1 = Query.from_(table1).select("name", "age", "salary")
sql2 = Query.from_(table2).select("name", "age", "salary")

print(sql1.union(sql2))  # (SELECT "name","age","salary" FROM "t1") UNION (SELECT "name","age","salary" FROM "t2")
print(sql2.union(sql1))  # (SELECT "name","age","salary" FROM "t2") UNION (SELECT "name","age","salary" FROM "t1")

# union 可以使用 + 代替
print(str(sql1 + sql2) == str(sql1.union(sql2)))  # True
print(str(sql2 + sql1) == str(sql2.union(sql1)))  # True

# union_all 可以使用 * 代替
print(sql1.union_all(sql2))  # (SELECT "name","age","salary" FROM "t1") UNION ALL (SELECT "name","age","salary" FROM "t2")
print(sql2.union_all(sql1))  # (SELECT "name","age","salary" FROM "t2") UNION ALL (SELECT "name","age","salary" FROM "t1")
print(str(sql1 * sql2) == str(sql1.union_all(sql2)))  # True
print(str(sql2 * sql1) == str(sql2.union_all(sql1)))  # True

此外还有交集、差集、对称差集。

from pypika import Query, Table

table1 = Table("t1")
table2 = Table("t2")

sql1 = Query.from_(table1).select("name", "age", "salary")
sql2 = Query.from_(table2).select("name", "age", "salary")

# 交集，没有提供专门的操作符
print(sql1.intersect(sql2))  # (SELECT "name","age","salary" FROM "t1") INTERSECT (SELECT "name","age","salary" FROM "t2")
# 差集，可以使用减号替代
print(sql1.minus(sql2))  # (SELECT "name","age","salary" FROM "t1") MINUS (SELECT "name","age","salary" FROM "t2")
# 对称差集，没有提供专门的操作符
print(sql1.except_of(sql2))  # (SELECT "name","age","salary" FROM "t1") EXCEPT (SELECT "name","age","salary" FROM "t2")

Date, Time, and Intervals

非常简单，直接看栗子即可：

from pypika import Query, Table, functions as fn, Interval

fruits = Table('fruits')

sql = Query.from_(fruits) \
    .select(fruits.id, fruits.name) \
    .where(fruits.harvest_date + Interval(months=1) < fn.Now())
print(sql)  # 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

table = Table("t")

sql = Query.from_(table).select(table.salary).where(Tuple(table.year, table.month) >= (2020, 7))
print(sql)  # SELECT "salary" FROM "t" WHERE ("year","month")>=(2020,7)

对于 in 字句也是同样的道理：

from pypika import Query, Table, Tuple

table = Table("t")

sql = Query.from_(table).select(table.salary).where(
    Tuple(table.year, table.month
          ).isin([(2020, 7), (2020, 8), (2020, 9)]))
print(sql)  # SELECT "salary" FROM "t" WHERE ("year","month") IN ((2020,7),(2020,8),(2020,9))

字符串函数

字符串操作也是非常常用的，下面我们来看一下：

from pypika import Query, Table

table = Table("t")

sql = Query.from_(table).select(
    "name", "age", "salary"
).where(
    table.field("name").like("古明地%") &
    table.field("place").ilike("ja%")  # ilike 不区分大小写
)
print(sql)  # SELECT "name","age","salary" FROM "t" WHERE "name" LIKE '古明地%' AND "place" ILIKE 'ja%'

正则也是可以的：

from pypika import Query, Table

table = Table("t")

sql = Query.from_(table).select(
    "name", "age", "salary"
).where(
    table.field("xxx").regex(r"^[abc][a-zA-Z]+&")
)
print(sql)  # SELECT "name","age","salary" FROM "t" WHERE "xxx" REGEX '^[abc][a-zA-Z]+&'

还有字符串之间的 concat、大小写转换等等：

from pypika import Query, Table, functions as fn

table = Table("t")

sql = Query.from_(table).select(
    fn.Concat(table.field("name"), "-", table.field("age")),
    fn.Upper(table.field("name")),
    fn.Lower(table.field("name")),
    fn.Length(table.field("name"))
)
print(sql)  # SELECT CONCAT("name",'-',"age"),UPPER("name"),LOWER("name"),LENGTH("name") FROM "t"

自定义函数

尽管 pypika 的 fn 模块支持大部分的 SQL 函数，但还是有少部分没有覆盖到，这个时候我们自定义函数的实现，举个例子：

from pypika import Table, Query, CustomFunction

table = Table("t")
# 我们自定义实现数据库的 LENGTH 函数，该函数可以接收一个参数来查看长度
my_length = CustomFunction("LENGTH", ["col"])
# 我们自定义实现数据库的 POWER 函数，该函数可以接收两个参数来计算幂
my_power = CustomFunction("POWER", ["col1", "col2"])

sql = Query.from_(table).select(my_length(table.field("name")))
print(sql)  # SELECT LENGTH("name") FROM "t"

sql = Query.from_(table).select(my_power(table.field("num"), table.field("base")))
print(sql)  # SELECT POWER("num","base") FROM "t"
sql = Query.from_(table).select(my_power(table.field("num"), 3))
print(sql)  # SELECT POWER("num",3) FROM "t"

case when

然后看看 case when，SQL 层面上的就不说了，我们只看怎么用 pypika 实现。

from pypika import Table, Query, Case

table = Table("t")

sql = Query.from_(table).select(
    table.name,
    Case().when(table.age < 18, "未成年").when(table.age < 30, "成年")
        .when(table.age < 50, "中年").else_("老年").as_("age")
)
print(sql)
"""
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

table = Table("t")

sub_query = Query.from_(table).select("*")
sql = Query.with_(sub_query, "alias").from_(AliasedQuery("alias")).select("*")
print(sql)  # WITH alias AS (SELECT * FROM "t") SELECT * FROM alias

distinct

如果我们相对结果集进行去重的话，要怎么做呢？

from pypika import Query, Table

table = Table("t")
print(
    # 只需要在 select 之前调用一次 distinct 即可
    Query.from_(table).distinct().select(table.id, table.age)
)  # SELECT DISTINCT "id","age" FROM "t"

limit 与 offset

这两个就没有什么好说的了，直接看语法吧。

from pypika import Table, Query, functions as fn, Order

table = Table("t")
sql = Query.from_(table).select(fn.Count("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(sql)
"""
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

table = Table("t")
# 查询是 Query.from_，插入数据是 Query.into
sql = Query.into(table).insert(1, "古明地觉", 16, "东方地灵殿")
print(sql)  # INSERT INTO "t" VALUES (1,'古明地觉',16,'东方地灵殿')

以上就是简单的插入数据，但是有需要注意的地方，比如：None 值的处理。

from pypika import Table, Query

table = Table("t")

sql = Query.into(table).insert(1, "古明地觉", None, "东方地灵殿")
# 自动帮你换成了 NULL，如果是我们自己手动拼接的话，那么还需要额外处理一下
# 而 pypika 内部已经帮你做好了，如果不把 None 换成 NULL，交给数据库执行是会报错的
print(sql)  # INSERT INTO "t" VALUES (1,'古明地觉',NULL,'东方地灵殿')

# 假设第二个字段在数据库中是一个 json
sql = Query.into(table).insert(1, {"name": "古明地觉", "age": 16, "where": None})
print(sql)  # INSERT INTO "t" VALUES (1,{'name': '古明地觉', 'age': 16, 'where': None})
# 我们看到这不是我们期望的结果，我们应该手动将值先变成 json 才行

import json
sql = Query.into(table).insert(1, json.dumps({"name": "古明地觉", "age": 16, "where": None}, ensure_ascii=False))
print(sql)  # INSERT INTO "t" VALUES (1,'{"name": "古明地觉", "age": 16, "where": null}')
# 以上就没有问题了

上面是插入一条数据，如果是插入多条呢？有两种方式：

from pypika import Table, Query

table = Table("t")

sql = Query.into(table).insert(1, "古明地觉", 16, "东方地灵殿")\
    .insert(2, "古明地恋", 15, "东方地灵殿")
print(sql)  # INSERT INTO "t" VALUES (1,'古明地觉',16,'东方地灵殿'),(2,'古明地恋',15,'东方地灵殿')

# 或者
sql = Query.into(table).insert((1, "古明地觉", 16, "东方地灵殿"), (2, "古明地恋", 15, "东方地灵殿"))
print(sql)  # INSERT INTO "t" VALUES (1,'古明地觉',16,'东方地灵殿'),(2,'古明地恋',15,'东方地灵殿')

指定字段插入

很多时候我们可以只指定部分字段的值：

from pypika import Table, Query, Field

table = Table("t")

sql = Query.into(table).columns(
    "id", table.field("name"), table.age, Field("place")
).insert(1, "古明地觉", 16, "东方地灵殿")
print(str(sql))  # INSERT INTO "t" ("id","name","age","place") VALUES (1,'古明地觉',16,'东方地灵殿')

将一张表的记录插入到另一张表中

如果要将一张表的部分记录插入到另一张表中要怎么办呢？

from pypika import Table, Query, Field

table1 = Table("t1")
table2 = Table("t2")

sql = Query.into(table1).columns(
    "id", "name", "age").from_(table2).select("id", "name", "age").where(Field("age") > 18)
print(sql)  # INSERT INTO "t1" ("id","name","age") SELECT "id","name","age" FROM "t2" WHERE "age">18

将两个表 join 之后的数据插入到新表中也是可以的：

from pypika import Table, Query, Field

table1 = Table("t1")
table2 = Table("t2")
table3 = Table("t3")

sql = Query.into(table1).columns("id", "name", "age").from_(
    table2).left_join(table3).on(table2.id == table3.id).select(table2.id, table2.name, table3.age)
print(sql)
"""
INSERT INTO "t1" ("id","name","age") 
SELECT "t2"."id","t2"."name","t3"."age" FROM "t2" LEFT JOIN "t3" ON "t2"."id"="t3"."id"
"""

更新数据

然后是更新数据：

from pypika import Table, Query

table = Table("t")
# 更新是 update
sql = Query.update(table).set(table.name, "古明地恋")
print(sql)  # UPDATE "t" SET "name"='古明地恋'
sql = Query.update(table).set(table.name, "古明地恋").where(table.id == 1)
print(sql)  # UPDATE "t" SET "name"='古明地恋' WHERE "id"=1

sql = Query.update(table).set(table.name, "古明地恋").set(table.age, 16)
print(sql)  # UPDATE "t" SET "name"='古明地恋',"age"=16

用另一张表的数据更新当前也是一种比较常见的操作，比如 t1 有 id、name 两个字段，t2 有 id、name 两个字段。如果 t1 的 id 在 t2 中存在，那么就用 t2 的 name 更新掉 t1 的 name。

from pypika import Table, Query

table1 = Table("t1")
table2 = Table("t2")

sql = Query.update(table1).join(table2).on(
    table1.uid == table2.tid
).set(table1.name, table2.name).where(table1.uid > 10)
print(sql)  # UPDATE "t1" JOIN "t2" ON "t1"."uid"="t2"."tid" SET "name"="t2"."name" WHERE "t1"."uid">10

最后，更新字段还有另一种方式：

from pypika import Table

table1 = Table("t1")
table2 = Table("t2")

# Query.update(table1) 也可以写成 table1.update()，其它不变
sql = table1.update().join(table2).on(
    table1.uid == table2.tid
).set(table1.name, table2.name).where(table1.uid > 10)
# 可以看到打印的结果是一样的
print(sql)  # UPDATE "t1" JOIN "t2" ON "t1"."uid"="t2"."tid" SET "name"="t2"."name" WHERE "t1"."uid">10

占位符

相当于先构建好相应的 SQL 语句，然后驱动在执行的时候会对数据进行替换，举个栗子：

from pypika import Table, Query, Parameter

table = Table("t")
sql = Query.into(table).columns(
    "id", "name", "age").insert(Parameter(":1"), Parameter(":2"), Parameter(":3"))
print(sql)  # INSERT INTO "t" ("id","name","age") VALUES (:1,:2,:3)

# 我们看到 VALUES 后面只是相应的占位符，具体的值是多少需要驱动执行的时候传递
# 但是问题来了，不同的驱动的占位符是不同的。有的是 %s，有的是 $1、$2、$3 这种
# 所以需要根据驱动调整占位符，但是 SQLAlchemy 提供了一种通用的方式
from sqlalchemy import text, create_engine
sql = Query.into(table).columns(
    "id", "name", "age").insert(Parameter(":id"), Parameter(":name"), Parameter(":age"))
# 传递到 text 中
sql = text(str(sql))
print(sql)  # INSERT INTO "t" ("id","name","age") VALUES (:id,:name,:age)
engine = create_engine("")
engine.execute(sql, id=123, name="古明地觉", age=16)

一些高级用法

我们之前说不同数据库的 SQL 语法会有略微不同，最大的一个不同就是包裹字段所用的符号，MySQL 用的是反引号、PostgreSQL 用的是双引号。而 pypika 不知道要为你适配什么数据库，所以默认用的是双引号，如果想适配 MySQL 的话，那么我们应该告诉 pypika，我们要适配 MySQL。

至于做法也很简单，我们之前不是用 Query 这个类进行的增删改查吗？现在不用它了。

from pypika import Table, MySQLQuery, PostgreSQLQuery

table = Table("t")
print(
    MySQLQuery.from_(table).select(table.id, table.age)
)  # SELECT `id`,`age` FROM `t`

print(
    PostgreSQLQuery.from_(table).select(table.id, table.age)
)  # SELECT "id","age" FROM "t"

我们看到此时就实现了数据库的适配，除了 MySQL 和 PostgreSQL 之外，pypika 还可以适配其它的数据库：

MSSQLQuery: 微软的 SqlServer
OracleQuery: 甲骨文的 Oracle
VerticaQuery: 列式数据库 Vertica

当然还支持窗口函数：

from pypika import Table, Query, analytics as an

table = Table("t")
sql = Query.from_(table).select(
    an.Sum("amount").over("month").as_("amount_sum"),
    an.Avg("amount").over("month").as_("amount_avg")
)
print(sql)
"""
SELECT SUM('amount') OVER (PARTITION BY month) "amount_sum",
       AVG('amount') OVER (PARTITION BY month) "amount_avg"
FROM "t"
"""

更详细内容可以去 https://pypika.readthedocs.io/en/latest/3_advanced.html#window-frames 进行查看。

总的来说，pypika 还是很方便的，如果需要手动拼接 SQL 的话，不妨试试 pypika。

posted @ 2021-04-29 01:35 古明地盆阅读(8132) 评论(1) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

来自东方地灵殿的小提琴手

愿时间能带走痛苦