Peewee 简明教程
Peewee - SQL Functions
美国国家标准协会 (ANSI) 结构化查询语言 (SQL) 标准定义了多种 SQL 函数。
American National Standards Institute (ANSI) Structured Query Language (SQL) standard defines many SQL functions.
以下之类的聚合函数在 Peewee 中很有用。
Aggregate functions like the following are useful in Peewee.
-
AVG() - Returns the average value.
-
COUNT() - Returns the number of rows.
-
FIRST() - Returns the first value.
-
LAST() - Returns the last value.
-
MAX() - Returns the largest value.
-
MIN() - Returns the smallest value.
-
SUM() - Returns the sum.
为了实现这些 SQL 函数，Peewee 有一个 SQL 辅助函数 fn()。上面示例中，我们用它来查找每个城市的记录数。
In order to implement these SQL functions, Peewee has a SQL helper function fn(). In above example, we used it to find count of records for each city.
以下示例构建了一个采用 SUM() 函数的 SELECT 查询。
Following example builds a SELECT query that employs SUM() function.
使用前面定义的模型中的 Bill 和 Item 表，我们将显示如 Bill 表中输入的每一项的数量总和。
Using Bill and Item tables from models defined earlier, we shall display sum of quantity of each item as entered in Bill table.
Item table
带有数据的 item 表如下所示 −
The item table with the data is given below −
Id |
Item Name |
Price |
1 |
Laptop |
25000 |
2 |
Printer |
12000 |
3 |
Router |
4000 |
Bill table
bill 表如下：
The bill table is as follows −
Id |
Item_id |
Brand_id |
Quantity |
1 |
1 |
3 |
5 |
2 |
2 |
2 |
2 |
3 |
3 |
4 |
5 |
4 |
2 |
2 |
6 |
5 |
3 |
4 |
3 |
6 |
1 |
3 |
1 |
Example
我们创建 Bill 和 Item 表之间的连接，从 Item 表中选择项目名称，从 Bill 表中选择数量总和。
We create a join between Bill and Item table, select item name from Item table and sum of quantity from Bill table.
from peewee import *
db = SqliteDatabase('mydatabase.db')
class BaseModel(Model):
class Meta:
database = db
class Item(BaseModel):
itemname = TextField()
price = IntegerField()
class Brand(BaseModel):
brandname = TextField()
item = ForeignKeyField(Item, backref='brands')
class Bill(BaseModel):
item = ForeignKeyField(Item, backref='bills')
brand = ForeignKeyField(Brand, backref='bills')
qty = DecimalField()
db.create_tables([Item, Brand, Bill])
qs=Bill.select(Item.itemname, fn.SUM(Bill.qty).alias('Sum'))
.join(Item).group_by(Item.itemname)
print (qs)
for q in qs:
print ("Item: {} sum: {}".format(q.item.itemname, q.Sum))
db.close()以上的脚本执行了以下 SELECT 查询 −
Above script executes the following SELECT query −
SELECT "t1"."itemname", SUM("t2"."qty") AS "Sum" FROM "bill" AS "t2"
INNER JOIN "item" AS "t1" ON ("t2"."item_id" = "t1"."id") GROUP BY "t1"."itemname"