Beziehungen und Joins

Verknüpfte Tabellen

# Author 1 --- many --- Book
# A foreign key on Book points back to Author
print('One-to-many is the most common link')

Fremdschlüssel

from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, mapped_column
# author_id: Mapped[int] = mapped_column(ForeignKey('authors.id'))
print('ForeignKey links child to parent')

Das übergeordnete Modell definieren

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from typing import List
class Base(DeclarativeBase):
    pass
class Author(Base):
    __tablename__ = 'authors'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    books: Mapped[List['Book']] = relationship(back_populates='author')
print('Author.books holds related Book objects')

Das untergeordnete Modell definieren

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
class Base(DeclarativeBase):
    pass
class Book(Base):
    __tablename__ = 'books'
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey('authors.id'))
    author: Mapped['Author'] = relationship(back_populates='books')
print('Book.author points to its parent')

back_populates

# author.books.append(book) also sets book.author = author
# Both sides stay in sync automatically
print('back_populates keeps both sides consistent')

Beziehungen durchlaufen

# for book in author.books:
#     print(book.title)
# print(book.author.name)
print('Navigate with plain attribute access')

Einen Objektgraphen aufbauen

# a = Author(name='Rowling')
# a.books.append(Book(title='Book 1'))
# session.add(a)
# session.commit()  # author and book both saved
print('Cascade saves the whole object graph')

Implizite Joins

# Accessing book.author runs a SELECT if not loaded yet
# This is lazy loading
print('Lazy loading fetches related rows on access')

Explizite Joins

from sqlalchemy import select
# stmt = select(Book).join(Author).where(Author.name == 'Rowling')
# books = session.scalars(stmt).all()
print('join(Author) filters across the relationship')

Eager Loading

from sqlalchemy import select
from sqlalchemy.orm import selectinload
# stmt = select(Author).options(selectinload(Author.books))
print('selectinload preloads children in one extra query')

Viele-zu-viele-Beziehungen

# students <-> courses via a 'enrollments' association table
# courses: Mapped[List['Course']] = relationship(secondary=enrollments)
print('secondary= models many-to-many')

Kurzer Test

Zusammenfassung