Peewee is a simple and lightweight Python ORM (Object-Relational Mapping) library. It provides a convenient way to interact with databases using Python code. In this blog post, we will explore how to use the GROUP BY clause and aggregate functions in Peewee.

Grouping Data with Group by

The GROUP BY clause is used to group rows that have the same values in specified columns. It allows us to perform aggregate functions on each group of data. Let’s look at an example:

from peewee import *

# Declare and connect to the database
database = SqliteDatabase('my_database.db')

# Define a simple model
class Person(Model):
    name = CharField()
    age = IntegerField()

    class Meta:
        database = database

# Create the Person table if it doesn't exist
Person.create_table()

# Insert some sample data
Person.create(name='John', age=25)
Person.create(name='Jane', age=30)
Person.create(name='Michael', age=25)
Person.create(name='Alice', age=30)

# Group the data by age and count the number of persons in each age group
query = Person.select(Person.age, fn.COUNT().alias('count')).group_by(Person.age)
results = query.execute()

# Print the results
for row in results:
    print(f"Age: {row.age}, Count: {row.count}")

In this example, we have a Person model with name and age fields. We insert some sample data into the Person table. Then, we use the select method to query the data, using the Person.age column and the COUNT aggregate function. Finally, we iterate over the results and print each age group and the count.

The output of the above code will be:

Age: 25, Count: 2
Age: 30, Count: 2

As you can see, the data is grouped by age and the count of persons in each age group is displayed.

Aggregating Data with Aggregate Functions

Peewee provides a set of built-in aggregate functions that can be used in combination with the GROUP BY clause to perform different calculations on the grouped data. Let’s see some examples:

# Calculate the average age of persons
query = Person.select(fn.AVG(Person.age))
average_age = query.scalar()
print(f"Average Age: {average_age}")

# Calculate the maximum and minimum age of persons
query = Person.select(fn.MAX(Person.age), fn.MIN(Person.age))
result = query.tuples().get()
max_age, min_age = result
print(f"Max Age: {max_age}, Min Age: {min_age}")

In the first example, we use the AVG aggregate function to calculate the average age of persons. The scalar method is used to retrieve the single result value. In the second example, we use the MAX and MIN aggregate functions to calculate the maximum and minimum age of persons, respectively. We retrieve the results as tuples using the tuples method and extract the values with the get method.

Conclusion

In this blog post, we learned how to use the GROUP BY clause and aggregate functions in Peewee. We saw how to group data by a specific column and perform aggregate calculations such as counting, averaging, and finding maximum and minimum values. This allows us to analyze and summarize data efficiently using Peewee in Python.

Peewee is a powerful tool that simplifies database interactions and makes it easy to work with data in a Pythonic way. I hope this blog post has given you a good introduction to the group by and aggregate functions in Peewee. Happy coding!