Peewee - Counting and Aggregation

We can find number of records reported in any SELECT query by attaching count() method. For example, following statement returns number of rows in Contacts table with City=’Nasik’.

qry=Contacts.select().where (Contacts.City=='Nasik').count()
print (qry)

Example

SQL has GROUP BY clause in SELECT query. Peewee supports it in the form of group_by() method. Following code returns city wise count of names in Contacts table.

from peewee import *

db = SqliteDatabase('mydatabase.db')
class Contacts(BaseModel):
   RollNo = IntegerField()
   Name = TextField()
   City = TextField()
   class Meta:
      database = db

db.create_tables([Contacts])

qry=Contacts.select(Contacts.City, fn.Count(Contacts.City).alias('count')).group_by(Contacts.City)
print (qry.sql())
for q in qry:
   print (q.City, q.count)

The SELECT query emitted by Peewee will be as follows −

('SELECT "t1"."City", Count("t1"."City") AS "count" FROM "contacts" AS "t1" GROUP BY "t1"."City"', [])

Output

As per sample data in Contacts table, following output is displayed −

Chennai 1
Delhi   2
Indore  1
Mumbai  1
Nagpur  1
Nasik   3
Pune    1