Skip to content

#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.

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:

ER Model for tables Book, Author, Publisher, BookDetail and BookAuthor

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
import sqlalchemy as sa
from data.modelbase import ModelBase

class Publisher(ModelBase):
    __tablename__ = 'Publisher'

    id = sa.Column('Id', sa.Integer, primary_key=True, autoincrement=True)
    name = sa.Column('Name', sa.String, nullable=False)
    books = sa.orm.relationship("Book", back_populates="publisher")

    def __repr__(self):
        return f'<Publisher {self.id} ({self.name})>'

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):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
import sqlalchemy as sa
from data.modelbase import ModelBase

class Book(ModelBase):
    __tablename__ = 'Book'

    id = sa.Column('Id', sa.Integer, primary_key=True, autoincrement=True)
    title = sa.Column('Title', sa.String, nullable=False)
    isbn = sa.Column('ISBN', sa.String(13), nullable=False)
    pages = sa.Column('Pages', sa.Integer)
    published_by = sa.Column('PublishedBy', sa.Integer, sa.ForeignKey('Publisher.Id'), nullable=False) 
    publisher = sa.orm.relation("Publisher", back_populates="books")

    def __repr__(self):
        return f'<Book {self.id} ({self.title} {self.isbn}) {self.pages}>'

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
import sqlalchemy as sa
from data.modelbase import ModelBase

class BookDetails(ModelBase):
    __tablename__ = 'BookDetail'

    book_id = sa.Column('Id', sa.Integer, sa.ForeignKey('Book.Id'), primary_key=True)
    cover = sa.Column('Cover', sa.String)
    book = sa.orm.relationship("Book", back_populates="details")


    def __repr__(self):
        return f'<BookDetails {self.book_id} ({self.cover})>'

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
import sqlalchemy as sa
from data.modelbase import ModelBase

class Book(ModelBase):
    __tablename__ = 'Book'

    id = sa.Column('Id', sa.Integer, primary_key=True, autoincrement=True)
    title = sa.Column('Title', sa.String, nullable=False)
    isbn = sa.Column('ISBN', sa.String(13), nullable=False)
    pages = sa.Column('Pages', sa.Integer)
    published_by = sa.Column('PublishedBy', sa.Integer, sa.ForeignKey('Publisher.Id'), nullable=False) 
    publisher = sa.orm.relation("Publisher", back_populates="books")
    details = sa.orm.relation("BookDetails", uselist=False, back_populates="book")

    def __repr__(self):
        return f'<Book {self.id} ({self.title} {self.isbn}) {self.pages}>'

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
import sqlalchemy as sa
from data.modelbase import ModelBase

class BookAuthor(ModelBase):
    __tablename__ = 'BookAuthor'

    book_id = sa.Column('BookId', sa.Integer, sa.ForeignKey('Book.Id'), primary_key=True)
    author_id = sa.Column('AuthorId', sa.Integer, sa.ForeignKey('Author.Id'), primary_key=True)

    def __repr__(self):
        return f'<BookAuthor {self.book_id} {self.author_id}>'

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
import sqlalchemy as sa
from data.modelbase import ModelBase

class Author(ModelBase):
    __tablename__ = 'Author'

    id = sa.Column('Id', sa.Integer, primary_key=True, autoincrement=True)
    first_name = sa.Column('FirstName', sa.String, nullable=False)
    last_name = sa.Column('LastName', sa.String, nullable=False)
    books = sa.orm.relation("Book", secondary='BookAuthor', back_populates="authors")

    def __repr__(self):
        return f'<Author {self.id} ({self.first_name} {self.last_name})>'

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
import sqlalchemy as sa
from data.modelbase import ModelBase

class Book(ModelBase):
    __tablename__ = 'Book'

    id = sa.Column('Id', sa.Integer, primary_key=True, autoincrement=True)
    title = sa.Column('Title', sa.String, nullable=False)
    isbn = sa.Column('ISBN', sa.String(13), nullable=False)
    pages = sa.Column('Pages', sa.Integer)
    published_by = sa.Column('PublishedBy', sa.Integer, sa.ForeignKey('Publisher.Id'), nullable=False) 
    publisher = sa.orm.relation("Publisher", back_populates="books")
    details = sa.orm.relation("BookDetails", uselist=False, back_populates="book")
    authors = sa.orm.relation("Author", secondary='BookAuthor', back_populates="books")

    def __repr__(self):
        return f'<Book {self.id} ({self.title} {self.isbn}) {self.pages}>'

Update __all_models.py

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# Add all your SQLAlchemy models here. 
# This allows you to import just this file
# whenever you need to work with your models 
# (like creating tables or for migrations)

from data.employee import Employee
from data.publisher import Publisher
from data.book_author import BookAuthor
from data.book import Book
from data.author import Author
from data.book_details import BookDetails

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
import os

from sqlalchemy.orm import joinedload
import data.db_session as db_session
from data.__all_models import Book, Publisher, Author, BookDetails

def setup_db():
    db_file = os.path.join(
        os.path.dirname(__file__),
        'db',
        'Bookstore.sqlite')
    db_session.global_init(db_file)

if __name__ == '__main__':
    print("\n--- setup_db() ---\n")
    setup_db()

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.