Перейти к содержимому

Связи таблиц в SQLAlchemy ORM

Связи таблиц в SQLAlchemy ORM

Проектирование One-to-Many, решение проблемы N+1 и продвинутая агрегация.

Содержание курса

1. Настройка и Проектирование

1.1 Структура проекта

В серьезных приложениях модели не хранят в одном файле. Мы разделим их логически, чтобы избежать путаницы и циклических импортов.

project/
├── models/
│   ├── __init__.py     # Экспорт всех моделей
│   ├── base.py         # Базовый класс (DeclarativeBase)
│   ├── user.py         # Модель пользователя
│   └── post.py         # Модель поста
├── main.py             # Запуск кода
└── docker-compose.yml  # База данных
Plain text

1.2 Описание моделей (One-to-Many)

Рассмотрим классическую связь: Один пользователь может написать Много постов.
Для этого нам понадобятся два инструмента:

  • ForeignKey (в базе данных) — физическое ограничение, ссылка на ID другой таблицы.
  • relationship (в Python) — "магическая" связь, позволяющая обращаться к связанным объектам как к атрибутам (например, user.posts).
models/user.py
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import relationship
from .base import Base

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    username = Column(String(32), nullable=False, unique=True)
    email = Column(String, nullable=True, unique=True)

    # Указываем, что у юзера есть список постов.
    # back_populates="author" связывает это поле с полем author в модели Post
    posts = relationship("Post", back_populates="author")

    def __repr__(self):
        return f"User(id={self.id}, username='{self.username}')"
Python
models/post.py
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from .base import Base

class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True)
    title = Column(String(100), nullable=False)

    # server_default=func.now() заставляет БД (Postgres) самой ставить время при вставке
    published_at = Column(DateTime, nullable=False, server_default=func.now())

    # ФИЗИЧЕСКАЯ СВЯЗЬ: колонка user_id ссылается на users.id
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)

    # ЛОГИЧЕСКАЯ СВЯЗЬ: позволяет получить объект User через post.author
    author = relationship("User", back_populates="posts")
Python

Почему back_populates? Раньше использовали backref, который неявно создавал поле в другой модели. Современный стиль SQLAlchemy требует явного определения полей в обоих классах с back_populates. Это делает код читаемым и понятным для IDE.

2. Проблема N+1 и Стратегии загрузки

2.1 Что такое N+1?

Это самая частая проблема производительности в ORM. Допустим, мы хотим вывести список заголовков постов и имена их авторов.

Плохой подход ❌

# 1. Делаем запрос на получение всех постов (1 запрос)
posts = session.scalars(select(Post)).all()

for post in posts:
    # 2. При обращении к post.author SQLAlchemy делает "ленивый" запрос в БД
    print(f"{post.title} написал {post.author.username}")
Python

Если у вас 100 постов, будет выполнен 1 запрос для списка + 100 запросов для каждого автора. Итого 101 запрос.

2.2 Решение: Оптимизация запросов

Нужно сказать SQLAlchemy: "Загрузи связанные данные сразу". Для этого есть две основные стратегии.

joinedload

Использует SQL JOIN.

Когда использовать: Связь "Многие-к-Одному" (Post -> Author). Загружает данные в том же запросе.

stmt = select(Post).options(
    joinedload(Post.author)
)
posts = session.scalars(stmt).all()
Python

selectinload

Использует второй запрос с WHERE IN (...).

Когда использовать: Связь "Один-ко-Многим" (User -> Posts). Избегает декартова произведения (дублирования строк).

stmt = select(User).options(
    selectinload(User.posts)
)
users = session.scalars(stmt).all()
Python

Что происходит под капотом при selectinload?

-- Запрос 1: Получаем юзеров
SELECT * FROM users;

-- Запрос 2: Получаем посты для этих юзеров
SELECT * FROM posts WHERE user_id IN (1, 2, 3, ...);

3. Аналитика: Фильтрация и Агрегация

3.1 Фильтрация через JOIN

Часто нужно найти родителя по свойствам ребенка (или наоборот). Например: "Найти всех пользователей, которые написали пост со словом 'Docker'".
Для этого мы явно соединяем таблицы через .join().

stmt = (
    select(User)
    .join(User.posts)  # Присоединяем таблицу постов
    .where(Post.title.ilike("%docker%"))  # Фильтруем по полю поста
    .options(selectinload(User.posts))    # Не забываем подгрузить посты для вывода
)
users = session.scalars(stmt).all()
Python

3.2 Группировка (GROUP BY) и HAVING

SQLAlchemy позволяет строить сложные аналитические запросы. Посчитаем, сколько постов у каждого пользователя.

from sqlalchemy import func

stmt = (
    # Выбираем ID, Имя и Количество постов
    select(User.id, User.username, func.count(Post.id).label("total_posts"))
    .join(User.posts, isouter=True)  # LEFT JOIN, чтобы учесть юзеров без постов
    .group_by(User.id, User.username) # Группируем по уникальному юзеру
    .order_by(User.id)
)

# Результат — это список кортежей (не объектов моделей!)
results = session.execute(stmt).all()
for user_id, name, count in results:
    print(f"{name}: {count} постов")
Python

Фильтрация после группировки (HAVING)

Если нам нужны только "активные" авторы (например, более 1 поста), мы не можем использовать where(), так как количество еще не посчитано. Используем having().

stmt = (
    select(User)
    .join(User.posts)
    .group_by(User.id)
    .having(func.count(Post.id) > 1)  # Оставляем только тех, у кого > 1 поста
    .options(selectinload(User.posts))
)
active_users = session.scalars(stmt).all()
Python

Запомните: WHERE фильтрует исходные строки ДО группировки. HAVING фильтрует результаты агрегатных функций ПОСЛЕ группировки.

Резюме

Мы научились связывать таблицы через ForeignKey и relationship. Главное правило производительности: всегда думайте о том, как и когда данные загружаются из БД. Используйте joinedload для одиночных связей и selectinload для коллекций.

↑ Наверх
Конспект:
Вторник, 27 января 2026
Связи таблиц в SQLAlchemy ORM