Python Friday #83: Relationship Patterns in SQLAlchemy ORM

SQLAlchemy ORM offers us the same basic relationship patterns as the Core part. However, with a little bit more code we can create navigation properties that makes working with our objects much simpler.

This post is part of my journey to learn Python. You can find the other parts of this series here. You find the code for this post in my PythonFriday repository on GitHub.

 

Back to the bookstore

As with SQLAlchemy Core, we create a little bookstore that allows us to persist books and explore how related tables work in the ORM part. I reuse the setup code from Foundation for SQLAlchemy ORM and ignore the Employee table for this post.

At the end of this post we have a database with these related tables:

The classes we create in this post go into the data/ folder next to our existing employee.py class.

 

One to many

A book has a publisher and a publisher has one or more books to promote, print and sell. The one to many pattern allows us to represent this relation between the Publisher and the Book table.

For a simpler workflow later on, I like to create a bidirectional relationship between Publisher and Book. That will allow us to access books from the publisher side and the publisher from the book.

To achieve that, we create a books property in the Publisher class that represents the relationship with the Book class:

In the Book class we use the two properties published_by and publisher. Only published_by is stored in the database, while publisher is not persisted and only works inside SQLAlchemy ORM (and our Python code):

In the argument back_populates we need to set the property from the other end of the relationship (in book we use (Publisher.)books and in Publisher we use (Book.)publisher).

 

One to one

The one to one pattern is great if we want to have additional data to a table but do not want to work with them all the time. For our Book class we can create a BookDetail class and store things like the cover or a more detailed description. Since every row in the BookDetail table matches exactly one row in the Book table, we can use the same Id for both primary keys.

We need to extend the book class with a details property when we want to navigate from a book to its bookdetail:

 

Many to many

An author writes one or more books and a book can have one or more authors. For this relationship we can use the many to many pattern with the mapping-table BookAuthor to link Book with Author:

We want only one row that connects a specific author to a specific book. To enforce that, we tell both properties that they are the primary key.

The Author class has a property books that uses the secondary table BookAuthor to create the relationship with the Book class:

We need to extend our Book class with the property authors to create the mapping in the opposite direction:

 

Update __all_models.py

Before we can use our new models, we need to update the __all_models.py file:

 

Create tables for your models

SQLAlchemy ORM creates our tables as soon as we use our global_init() method through the session factory. This allows us to write this little script to create all our tables:

 

Next

We now have the tables and their relations in place. Next week we persist some objects and look how we can work with relational data in SQLAlchemy ORM.