Peewee is a lightweight Object-Relational Mapping (ORM) library for Python that simplifies database interaction. It provides a convenient way to interact with databases using Python code, without having to write complex SQL queries.

In this blog post, we will explore how to write complex queries using Peewee. We will cover various scenarios where you might need to write advanced queries and demonstrate how Peewee can handle them efficiently.

Basic Querying

Before diving into complex queries, let’s quickly review basic querying in Peewee. To query records from a table, you can use the select() method on the model class. For example, to retrieve all records from a User table, you can do:

users = User.select()

You can also apply filters to the query using methods like where() or filter(). For example, to retrieve all users with the name “John”, you can do:

users = User.select().where(User.name == 'John')

Complex Querying

Joins

One common scenario in complex queries is joining multiple tables. Peewee provides an elegant way to perform joins using the join() method. For example, to retrieve all orders along with the related customer information, you can do:

orders = Order.select().join(Customer)

Subqueries

Sometimes, you may need to use subqueries to perform more advanced filtering or calculations. Peewee supports subqueries through the select() method. Let’s say we want to retrieve all users who have placed more than 10 orders. We can achieve this using a subquery:

subquery = Order.select(fn.COUNT(Order.id)).where(Order.user == User.id)

users = User.select().where(subquery > 10)

Aggregations

Peewee also supports aggregations like COUNT(), SUM(), or AVG(). You can use these functions with the annotate() method to perform calculations on groups of records. For example, to retrieve the total number of orders for each customer, you can do:

query = Order.select(fn.COUNT(Order.id).alias('total_orders'), Customer).join(Customer).group_by(Customer)

results = query.dicts()

Raw Queries

In some cases, you may need to execute raw SQL queries that are not supported by Peewee’s ORM methods. Peewee allows you to execute raw queries using the execute_sql() method. For example, if you have a complex join query that cannot be expressed using Peewee’s join() method, you can execute it directly:

query = '''
SELECT * FROM orders 
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'USA'
'''

results = Order.execute_sql(query)

Conclusion

Peewee is a powerful ORM library that allows you to write complex queries in a simplified manner. In this blog post, we explored various scenarios where you might need to write advanced queries and demonstrated how Peewee can handle them efficiently. By leveraging Peewee’s capabilities, you can streamline your database interactions and make your code more readable and maintainable.

Peewee documentation: https://docs.peewee-orm.com