SQLAlchemy Relationships

Introduction

Define relationships between models using SQLAlchemy's relationship functions.

One-to-Many

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Author(Base):
    __tablename__ = "authors"
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    books = relationship("Book", back_populates="author")

class Book(Base):
    __tablename__ = "books"
    id = Column(Integer, primary_key=True)
    title = Column(String(100))
    author_id = Column(Integer, ForeignKey("authors.id"))
    author = relationship("Author", back_populates="books")

Accessing Related Data

session = Session()

# Get author's books
author = session.query(Author).first()
print(author.books)

# Get book author
book = session.query(Book).first()
print(book.author.name)

Many-to-Many

book_tags = Table("book_tags", Base.metadata,
    Column("book_id", Integer, ForeignKey("books.id")),
    Column("tag_id", Integer, ForeignKey("tags.id"))
)

class Book(Base):
    __tablename__ = "books"
    tags = relationship("Tag", secondary=book_tags)

Practice Problems

1. Create one-to-many relationship
2. Create many-to-many relationship
3. Query with joinedload
4. Delete with cascade
5. Use backref for convenience