Peewee is a lightweight and expressive Object Relational Mapper (ORM) library for Python. It provides an intuitive and powerful API for interacting with databases, including advanced support for many-to-many relationships.

In this blog post, we will explore the different ways to model and query many-to-many relationships in Peewee, using its advanced features and patterns.

Setting up the environment

Before we begin, let’s make sure we have Peewee installed:

pip install peewee

And import the necessary modules:

from peewee import *
from playhouse.sqlite_ext import SqliteExtDatabase

The Problem: Students and Courses

Let’s consider a scenario where we have a database of students and courses. A student can be enrolled in multiple courses, and a course can have multiple students.

We will model this relationship using a many-to-many pattern.

The Solution: Intermediate Table

One way to implement a many-to-many relationship is by using an intermediate table that links the two main tables. This intermediate table stores the foreign keys of each table, effectively creating a bridge between them.

Let’s define the models for our problem:

database = SqliteExtDatabase('students.db')

class BaseModel(Model):
    class Meta:
        database = database

class Student(BaseModel):
    name = CharField(unique=True)

class Course(BaseModel):
    name = CharField(unique=True)

class StudentCourse(BaseModel):
    student = ForeignKeyField(Student)
    course = ForeignKeyField(Course)

In the above code, we define three models: Student, Course, and StudentCourse. The StudentCourse model represents the intermediate table and holds the foreign keys for both Student and Course.

By default, Peewee assumes the primary key column to be id in the database tables. However, you can specify a different primary key by using the primary_key=True argument when defining the fields.

Defining the Many-to-Many Relationship

To define the many-to-many relationship between Student and Course, we need to add some fields to the models.

In the Student model, we will add a courses field which is a ManyToManyField:

class Student(BaseModel):
    name = CharField(unique=True)
    courses = ManyToManyField(Course, backref='students')

Similarly, in the Course model, we will add a students field:

class Course(BaseModel):
    name = CharField(unique=True)
    students = ManyToManyField(Student, backref='courses')

The backref argument creates a reverse relationship from Course to Student and vice versa. This allows us to easily access the related objects from either end of the relationship.

Creating and Querying Many-to-Many Relationships

Let’s create some students and courses in the database and establish the many-to-many relationships between them:

database.create_tables([Student, Course, StudentCourse])
database.close()

with database.connect():
    database.execute_sql('PRAGMA foreign_keys = ON;')  # Enable foreign key constraints

with database:
    math = Course.create(name='Math')
    science = Course.create(name='Science')
    history = Course.create(name='History')

    alice = Student.create(name='Alice')
    bob = Student.create(name='Bob')
    charlie = Student.create(name='Charlie')

    alice.courses.add([math, science])
    bob.courses.add([math, history])
    charlie.courses.add([science, history])

To query the many-to-many relationships, we can use the students and courses fields of the models:

# Get all courses of a student
alice_courses = alice.courses

# Get all students enrolled in a course
math_students = math.students

We can also perform complex queries using the many-to-many relationships, such as finding students enrolled in multiple courses:

# Get students enrolled in both Math and Science
students = Student.select().join(StudentCourse).join(Course).where(
    (Course.name == 'Math') | (Course.name == 'Science')
).group_by(Student).having(fn.COUNT(StudentCourse.course) == 2)

for student in students:
    print(student.name)

Conclusion

Peewee provides advanced support for modeling and querying many-to-many relationships in Python. By using an intermediate table and the ManyToManyField field, we can easily establish and manage complex relationships between objects.

In this blog post, we explored the many-to-many pattern in Peewee, defined the models, and performed queries to fetch related objects. With this knowledge, you can now confidently handle complex many-to-many scenarios in your projects using Peewee. Happy coding!