Отношения с SQLAlchemy

Отношения с SQLAlchemy

Одна из лучших вещей в использовании ORM — это использование отношений, которые они обеспечивают. SQLAlchemy имеет много возможностей для значительного облегчения жизни вашей базы данных. Основные моменты, на которые будет направлена ​​эта статья:

• Что такое отношения?
• Автоматическое отслеживание базы данных: внешние ключи и наследование сеансов
• Запросы и нетерпеливая загрузка
• Локальный кеш результирующего набора

Начиная

Эта статья будет основываться на инициализации и настройке моделей в разделе Начало работы с SQLAlchemy. Если вы новичок в SQLAlchemy, я рекомендую ознакомиться с этой статьей, прежде чем продолжить. Определенная базовая структура таблицы состоит из Students, связанных с Activitys через таблицу Enrollment:

Student(id, name)
Activity(id, name)
Enrollment(id_student, id_activity, start_date, end_date)

Что такое отношения?

Отношение, по сути, позволяет вам перемещаться по отношениям внешнего ключа через атрибуты вашей модели. Отношения можно использовать для упрощения управления сеансами, упрощения запросов и условий соединения, ускоренной загрузки и, что наиболее важно, для простой навигации по внешнему ключу. Пока внешние ключи и отношения правильно определены, это просто вопрос связывания экземпляров моделей вместе через их отношения. Простое действие:

Student(
    name='Test Student',
    enrollments=[
        Enrollment(
            activity=Activity(name='Test Activity'),
            start_date=date.today(),
        )
    ]
)

создаст 3 записи в 3 таблицах и будет управлять первичными ключами для вас.

Пример отслеживания данных и базы данных

Для приведенных выше моделей необходимы некоторые образцы данных. Отличное время для демонстрации настройки и поддержания состояния базы данных!

from datetime import date, timedelta
import random
students = [
    Student(name=name)
    for name in ['Abby', 'Bob', 'Chris', 'Darlene', 'Edgar', 'Francine', 'Ginny']
]
activities = [
    Activity(name=name)
    for name in ['Kick Ball', 'Baseball', 'Dancing', 'Art', 'Programming']
]
today = date.today()
for student in students:
    potential_activities = random.sample(
        activities,
        k=random.randint(1, len(activities))
    )
    for activity in potential_activities:
        if random.randint(0, 1):
            end_date = today - timedelta(days=random.randint(1, 5))
        else:
            end_date = None
        Enrollment(
            student=student,
            activity=activity,
            start_date=today - timedelta(days=random.randint(6, 9)),
            end_date=end_date
        )
        print(f"Welcome {student.name} to {activity.name}!")

На данный момент сеанс не знает данных, и идентификаторы не были назначены:

student = students[0]
activity = student.enrollments[0]
print(student in session, activity in session, student.id, activity.id_student)
> False False None None

Добавляя студентов в сеанс, SQLAlchemy неявно прокручивает все объекты, по которым можно перемещаться, через отношения и также добавляет их в сеанс. Однако присутствие в сеансе не означает, что база данных знает о них, поэтому идентификаторы по-прежнему равны None:

session.add_all(students)
print(student in session, activity in session, student.id, activity.id_student)
> True True None None

Затем явный сброс отправит данные в базу данных, а затем отправит обратно и назначит все идентификаторы:

session.flush()
print(student in session, activity in session, student.id, activity.id_student)
> True True 1 1

Что действительно круто. Отслеживание объектов в сеансе и идентификаторов — утомительное занятие, о котором я рад не беспокоиться!

Запросы и жадная загрузка

На данный момент все данные находятся в нашем локальном кеше. Для демонстрации мы будем очищать кеш сеанса для демонстрационных запросов с помощью session.expire_all().

В этой статье основное внимание будет уделено 4 отдельным способам запроса взаимосвязей: lazy (по умолчанию), subqueryload,joinedload,и contains_eager.

Ленивый

Ленивая загрузка используется по умолчанию и часто этого достаточно. Запрос вернет именно то, что вы запрашиваете, и доступ к любой связи сначала проверит кэш идентификаторов сеанса на наличие объекта. Если его там нет, будет выдан запрос для заполнения отношения только для этого объекта.

session.expire_all()
query = session.query(Student)
for student in query:
    student.enrollments[0].activity

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

SELECT student.id AS student_id, student.name AS student_name
FROM student
SELECT enrollment.id_student AS enrollment_id_student, enrollment.id_class AS enrollment_id_class, enrollment.start_date AS enrollment_start_date, enrollment.end_date AS enrollment_end_date
FROM enrollment
WHERE 1 = enrollment.id_student
SELECT activity.id AS activity_id, activity.name AS activity_name
FROM activity
WHERE activity.id = 1
SELECT enrollment.id_student AS enrollment_id_student, enrollment.id_class AS enrollment_id_class, enrollment.start_date AS enrollment_start_date, enrollment.end_date AS enrollment_end_date
FROM enrollment
WHERE 2 = enrollment.id_student
SELECT enrollment.id_student AS enrollment_id_student, enrollment.id_class AS enrollment_id_class, enrollment.start_date AS enrollment_start_date, enrollment.end_date AS enrollment_end_date
FROM enrollment
WHERE 3 = enrollment.id_student
SELECT enrollment.id_student AS enrollment_id_student, enrollment.id_class AS enrollment_id_class, enrollment.start_date AS enrollment_start_date, enrollment.end_date AS enrollment_end_date
FROM enrollment
WHERE 4 = enrollment.id_student
SELECT activity.id AS activity_id, activity.name AS activity_name
FROM activity
WHERE activity.id = 2
SELECT enrollment.id_student AS enrollment_id_student, enrollment.id_class AS enrollment_id_class, enrollment.start_date AS enrollment_start_date, enrollment.end_date AS enrollment_end_date
FROM enrollment
WHERE 5 = enrollment.id_student
SELECT enrollment.id_student AS enrollment_id_student, enrollment.id_class AS enrollment_id_class, enrollment.start_date AS enrollment_start_date, enrollment.end_date AS enrollment_end_date
FROM enrollment
WHERE 6 = enrollment.id_student
SELECT activity.id AS activity_id, activity.name AS activity_name
FROM activity
WHERE activity.id = 4
SELECT enrollment.id_student AS enrollment_id_student, enrollment.id_class AS enrollment_id_class, enrollment.start_date AS enrollment_start_date, enrollment.end_date AS enrollment_end_date
FROM enrollment
WHERE 7 = enrollment.id_student

объединенная нагрузка

joinedload добавит к запросу дополнительное соединение, предназначенное для отношения, чтобы заполнить отношение результатами.

from sqlalchemy.orm import joinedload
session.expire_all()
query = session.query(Student) \
    .options(
        joinedload(Student.enrollments)
            .joinedload(Enrollment.activity)
    )
for student in query:
    student.enrollments[0].activity

Когда это возможно, я всегда использую точный атрибут отношения из родительской модели. Явность всегда побеждает неявность, и гораздо проще понять, откуда берутся ваши данные и куда они поступают.

Создает один запрос для получения студента, его зачислений и всех действий:

SELECT student.id AS student_id, student.name AS student_name, activity_1.id AS activityStudentid, activity_1.name AS activityStudentname, enrollment_1.id_student AS enrollmentStudentid_student, enrollment_1.id_class AS enrollmentStudentid_class, enrollment_1.start_date AS enrollmentStudentstart_date, enrollment_1.end_date AS enrollmentStudentend_date
FROM student
LEFT OUTER JOIN enrollment AS enrollment_1 ON student.id = enrollment_1.id_student
LEFT OUTER JOIN activity AS activity_1 ON activity_1.id = enrollment_1.id_class

подзапрос

subqueryload загрузит отношение, используя отдельный запрос, а не соединение.

from sqlalchemy.orm import subqueryload
session.expire_all()
query = session.query(Student) \
    .options(
        subqueryload(Student.enrollments)
            .subqueryload(Enrollment.activity)
    )
for student in query:
    student.enrollments[0].activity

Выдает 3 запроса. Исходный запрос и один запрос на подзапрос:

SELECT student.id AS student_id, student.name AS student_name
FROM student
SELECT enrollment.id_student AS enrollment_id_student, enrollment.id_class AS enrollment_id_class, enrollment.start_date AS enrollment_start_date, enrollment.end_date AS enrollment_end_date, anon_1.student_id AS anonStudentstudent_id
FROM (SELECT student.id AS student_id
FROM student) AS anon_1 JOIN enrollment ON anon_1.student_id = enrollment.id_student ORDER BY anon_1.student_id
SELECT activity.id AS activity_id, activity.name AS activity_name, enrollment_1.id_class AS enrollmentStudentid_class
FROM (SELECT student.id AS student_id
FROM student) AS anon_1 JOIN enrollment AS enrollment_1 ON anon_1.student_id = enrollment_1.id_student JOIN activity ON activity.id = enrollment_1.id_class ORDER BY enrollment_1.id_class

contains_eager

contains_eager будет использовать результаты явного соединения в запросе для заполнения связи. Для этого есть два отличных варианта использования:

• Отношения уже являются частью запроса в соединении — так что продолжайте и заполните его.
• Вы хотите, чтобы отфильтрованное подмножество данных было заполнено отношением. Поскольку связь заполняется тем, что возвращается в запросе, если запрос отфильтровывает результаты связи, связь будет знать только то, что возвращается в запросе.

from sqlalchemy.orm import contains_eager
session.expire_all()
query = session.query(Student) \
    .join(Student.enrollments) \
    .join(Enrollment.activity) \
    .options(
        contains_eager(Student.enrollments)
            .contains_eager(Enrollment.activity)
    )
for student in query:
    student.enrollments[0].activity

Что создает простейший сгенерированный запрос:

SELECT student.id AS student_id, student.name AS student_name, activity.id AS activity_id, activity.name AS activity_name, enrollment.id_student AS enrollment_id_student, enrollment.id_class AS enrollment_id_class, enrollment.start_date AS enrollment_start_date, enrollment.end_date AS enrollment_end_date
FROM student
JOIN enrollment ON student.id = enrollment.id_student
JOIN activity ON activity.id = enrollment.id_class

Когда и почему вас это волнует?

Что хорошо во всех этих стратегиях, так это то, что если у SQLAlchemy нет необходимых данных, он запросит их. Во всех 4 примерах цикл for и доступ к активности первого зачисления для каждого учащегося абсолютно одинаковы. Это позволяет чрезвычайно легко настраивать и оптимизировать ваши стратегии загрузки. В духе «Предварительная оптимизация — корень всех зол», я предпочитаю оставлять вещи на ленивой загрузке, если только они не окажутся проблемой, или если я заранее не знаю, что это будет проблемой.

joinedload, subqueryload и contains_eager

Оба они достигают основной цели: сокращают количество запросов. joinedload может показаться очевидным выбором. Он работает полностью в рамках одного запроса! Однако по мере того, как ваш запрос становится все более сложным, легко быстро превратиться в огромные наборы результатов. Каждое объединение, которое вы добавляете к своему запросу, означает, что каждая строка должна представлять каждую комбинацию данных во всех сопоставленных таблицах. С другой стороны, хотя subqueryload может выдать дополнительный запрос, у него не будет такого же потенциала для возврата тех же огромных наборов данных. Как правило, с большими запросами рассмотрите возможность использования joinedload с отношениями один к одному и subqueryload с отношениями один ко многим. Используйте contains_eager , если вы уже присоединяетесь к связи в запросе или если вам нужно загрузить связь с отфильтрованным набором результатов.

Смешивать и сочетать!

В примерах вы, возможно, заметили, что параметры загрузки связаны друг с другом, чтобы загрузить последующие отношения. Для каждого из них вы можете использовать любую из стратегий загрузки. Я часто subqueryload первые отношения и joinedload остальные отношения:

from sqlalchemy.orm import subqueryload, joinedload
session.expire_all()
query = session.query(Student) \
    .options(
        subqueryload(Student.enrollments)
            .joinedload(Enrollment.activity)
    )
for student in query:
    student.enrollments[0].activity

Производит:

SELECT student.id AS student_id, student.name AS student_name
FROM student
SELECT enrollment.id_student AS enrollment_id_student, enrollment.id_class AS enrollment_id_class, enrollment.start_date AS enrollment_start_date, enrollment.end_date AS enrollment_end_date, anon_1.student_id AS anonStudentstudent_id, activity_1.id AS activityStudentid, activity_1.name AS activityStudentname
FROM (SELECT student.id AS student_id
FROM student) AS anon_1 JOIN enrollment ON anon_1.student_id = enrollment.id_student LEFT OUTER JOIN activity AS activity_1 ON activity_1.id = enrollment.id_class ORDER BY anon_1.student_id

Использование локального кеша

Поскольку отношения в основном загружаются один раз, а затем повторно используются, они являются идеальными кандидатами для расширения с помощью свойств, чтобы обеспечить лучший доступ. Может показаться заманчивым определить два отдельных отношения, использующих secondary для доступа к текущим и прошлым действиям:

class Student(Base):
    ...
    current_activities = relationship(
        lambda: Activity,
        primaryjoin=lambda: and_(
            Student.id == Enrollment.id_student,
            Enrollment.end_date == None
        ),
        secondary=lambda: Enrollment.__table__,
        backref='current_students'
    )
    past_activities = relationship(
        lambda: Activity,
        primaryjoin=lambda: and_(
            Student.id == Enrollment.id_student,
            Enrollment.end_date != None
        ),
        secondary=lambda: Enrollment.__table__,
        backref='past_students'
    )

Однако это имеет несколько проблем. Это усложняет любую нетерпеливую стратегию загрузки. Его сложнее поддерживать. Для загрузки похожих наборов данных требуется несколько запросов. И не очевидно, как использовать отношения при добавлении новых данных.

С другой стороны, использование @propertys или даже @hybrid_propertys позволяет использовать одно отношение, а затем создавать разные представления этих данных. Вот current_activities и past_activities как свойства:

class Student(Base):
    ...
    @property
    def current_activities(self):
        for enrollment in self.enrollments:
            if not enrollment.end_date:
                yield enrollment.activity
    @property
    def past_activities(self):
        for enrollment in self.enrollments:
            if enrollment.end_date:
                yield enrollment.activity

Это обеспечивает большую гибкость и делает очень очевидным, как вы будете выполнять любую нетерпеливую загрузку или добавлять новые записи — просто используйте основные отношения!

Как и во всем в SQLAlchemy, это лишь малая часть того, что возможно. Не упоминаются самореферентные отношения, множественные внешние ключи и отношения к одной и той же таблице или управление сиротами через каскад.

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