Peewee - SQL Functions

American National Standards Institute (ANSI) Structured Query Language (SQL) standard defines many SQL functions.

Aggregate functions pke 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.

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.

Following example builds a SELECT query that employs SUM() function.

Using Bill and Item tables from models defined earper, we shall display sum of quantity of each item as entered in Bill table.

Item table

The item table with the data is given below −

Bill table

The bill table is as follows −

Example

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 = SqpteDatabase( 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).apas( Sum ))
   .join(Item).group_by(Item.itemname)
print (qs)
for q in qs:
   print ("Item: {} sum: {}".format(q.item.itemname, q.Sum))

db.close()

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"

Output

Accordingly, the output is as follows −

Item: Laptop sum: 6
Item: Printer sum: 8
Item: Router sum: 8