Peewee is a lightweight and expressive Python ORM (Object Relational Mapping) library that simplifies database operations. It provides a simple and intuitive API to interact with relational databases.

One of the key features of Peewee is its ability to handle relationships between database tables. In this blog post, we will explore how to perform relationship queries using Peewee.

Setup

Before we dive into the details, let’s set up a basic database structure to work with. For this example, we will use SQLite as our backend database.

First, we need to install Peewee and SQLite:

pip install peewee

Next, let’s create a simple database model with two tables - Author and Book. Each book will have an author associated with it.

from peewee import *

# Create a SQLite database
db = SqliteDatabase('library.db')

# Define Author model
class Author(Model):
    name = CharField()

    class Meta:
        database = db

# Define Book model
class Book(Model):
    title = CharField()
    author = ForeignKeyField(Author, backref='books')

    class Meta:
        database = db

# Create tables
db.create_tables([Author, Book])

Querying Relationships

Now that we have our database set up, let’s see how we can query the relationships between our tables using Peewee.

One-to-Many Relationship

In our example, there is a one-to-many relationship between Author and Book. An author can have multiple books, and each book is associated with only one author.

To retrieve all the books written by an author, we can use the Author model’s books attribute, which we defined as a backref in the Book model.

# Query all books written by an author
author = Author.get(Author.id == 1)
books = author.books

for book in books:
    print(book.title)

Many-to-One Relationship

In a many-to-one relationship, multiple records from one table can be associated with a single record in another table. In our example, multiple books can have the same author.

To retrieve the author of a book, we can use the Book model’s author attribute.

# Query the author of a book
book = Book.get(Book.id == 1)
author = book.author

print(author.name)

Conclusion

Peewee makes it simple to perform relationship queries in Python. Whether it’s a one-to-many or many-to-one relationship, Peewee provides a clean and intuitive way to work with related tables.

In this blog post, we explored how to perform relationship queries using Peewee. We set up a basic database structure, defined models, and demonstrated how to query relationships between tables.

Peewee is a powerful ORM that offers many more features like many-to-many relationships, filtering, and sorting. If you’re working with Python and need to interact with a relational database, Peewee is definitely worth exploring.

Happy coding!