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 - Usage of Group By Clause

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.

main.py

from peewee import *

db = SqliteDatabase('mydatabase.db')

class BaseModel(Model):
   class Meta:
      database = db
	  
class Contacts(BaseModel):
   RollNo = IntegerField()
   Name = TextField()
   City = TextField()
   class Meta:
      database = db

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)
   
db.close()

Output

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"', [])

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