Работа со связанными объектами¶

Каскадирование объектов в сессию¶

Теперь у нас есть объект User и два объекта Address, которые связаны в двунаправленной структуре в памяти, но, как отмечалось ранее в Вставка строк с помощью ORM, эти объекты находятся в состоянии transient до тех пор, пока они не будут связаны с объектом Session.

Мы используем Session, который все еще продолжается, и заметим, что когда мы применяем метод Session.add() к ведущему объекту User, связанный объект Address также добавляется к этому же Session:

>>> session.add(u1)
>>> u1 in session
True
>>> a1 in session
True
>>> a2 in session
True

Описанное выше поведение, когда Session получает объект User и следует вдоль отношения User.addresses, чтобы найти связанный объект Address, известно как каскад сохранения и обновления и подробно рассматривается в справочной документации ORM по адресу Каскады.

Три объекта сейчас находятся в состоянии pending; это означает, что они готовы стать предметом операции INSERT, но она еще не началась; у всех трех объектов еще не назначен первичный ключ, и, кроме того, у объектов a1 и a2 есть атрибут user_id, который ссылается на Column, который имеет ForeignKeyConstraint, ссылающийся на колонку user_account.id; это также None, поскольку объекты еще не связаны с реальной строкой базы данных: :

>>> print(u1.id)
None
>>> print(a1.user_id)
None

Именно на этом этапе мы можем увидеть очень большую полезность, которую обеспечивает процесс единицы работы; вспомните в разделе INSERT обычно генерирует предложение «значения» автоматически, строки были вставлены в таблицы user_account и address с использованием некоторых сложных синтаксисов, чтобы автоматически связать столбцы address.user_id со столбцами строк user_account. Кроме того, было необходимо, чтобы мы выдавали INSERT для строк user_account сначала, до строк address, поскольку строки address зависимы от родительской строки user_account для значения в их столбце user_id.

При использовании Session все эти утомительные действия выполняются за нас, и даже самый закоренелый пурист SQL может извлечь пользу из автоматизации операторов INSERT, UPDATE и DELETE. Когда мы Session.commit() выполняем транзакцию, все шаги выполняются в правильном порядке, и, кроме того, вновь созданный первичный ключ строки user_account соответствующим образом применяется к столбцу address.user_id:

>>> session.commit()
INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] ('pkrabs', 'Pearl Krabs')
INSERT INTO address (email_address, user_id) VALUES (?, ?)
[...] ('pearl.krabs@gmail.com', 6)
INSERT INTO address (email_address, user_id) VALUES (?, ?)
[...] ('pearl@aol.com', 6)
COMMIT

Использование отношений для присоединения¶

В разделах Явные предложения FROM и JOIN и Установка положения о включении было представлено использование методов Select.join() и Select.join_from() для составления предложений SQL JOIN. Для того чтобы описать, как соединить таблицы, эти методы либо инфертируют предложение ON на основе наличия единственного однозначного объекта ForeignKeyConstraint в структуре метаданных таблицы, который связывает две таблицы, либо мы можем предоставить явную конструкцию SQL Expression, которая указывает на конкретное предложение ON.

При использовании сущностей ORM доступен дополнительный механизм, помогающий нам установить ON-параметр объединения, который заключается в использовании объектов relationship(), которые мы установили в нашем пользовательском отображении, как было показано в Объявление сопоставленных классов. Атрибут class-bound, соответствующий relationship(), может быть передан в качестве единственного аргумента в Select.join(), где он служит для указания как правой части соединения, так и пункта ON одновременно:

>>> print(select(Address.email_address).select_from(User).join(User.addresses))
SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

Наличие ORM relationship() на отображении не используется Select.join() или Select.join_from(), если мы его не указываем; оно не используется для вывода предложения ON. Это означает, что если мы соединяем из User в Address без предложения ON, это работает из-за ForeignKeyConstraint между двумя сопоставленными Table объектами, а не из-за relationship() объектов на User и Address классах:

>>> print(select(Address.email_address).join_from(User, Address))
SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

Объединение между смежными целями¶

В разделе Псевдонимы сущностей ORM мы представили конструкцию aliased(), которая используется для применения псевдонима SQL к сущности ORM. При использовании relationship() для построения SQL JOIN, в случае, когда целью соединения является aliased(), можно использовать модификатор PropComparator.of_type(). Для демонстрации мы построим то же самое соединение, показанное в Псевдонимы сущностей ORM, используя вместо него атрибуты relationship() для соединения:

>>> print(
...     select(User)
...     .join(User.addresses.of_type(address_alias_1))
...     .where(address_alias_1.email_address == "patrick@aol.com")
...     .join(User.addresses.of_type(address_alias_2))
...     .where(address_alias_2.email_address == "patrick@gmail.com")
... )
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
WHERE address_1.email_address = :email_address_1
AND address_2.email_address = :email_address_2

Чтобы использовать relationship() для построения соединения из псевдослучайной сущности, атрибут доступен непосредственно из конструкции aliased():

>>> user_alias_1 = aliased(User)
>>> print(select(user_alias_1.name).join(user_alias_1.addresses))
SELECT user_account_1.name
FROM user_account AS user_account_1
JOIN address ON user_account_1.id = address.user_id

Дополнение критериев ОН¶

Предложение ON, создаваемое конструкцией relationship(), также может быть дополнено дополнительными критериями. Это полезно как для быстрых способов ограничения области действия конкретного соединения по пути отношения, так и для таких случаев, как настройка стратегий загрузчика, представленных ниже в Стратегии работы погрузчика. Метод PropComparator.and_() позиционно принимает серию SQL-выражений, которые будут присоединены к пункту ON JOIN через AND. Например, если мы хотим выполнить JOIN из User в Address, но при этом ограничить критерий ON только определенными адресами электронной почты:

>>> stmt = select(User.fullname).join(
...     User.addresses.and_(Address.email_address == "pearl.krabs@gmail.com")
... )
>>> session.execute(stmt).all()
SELECT user_account.fullname
FROM user_account
JOIN address ON user_account.id = address.user_id AND address.email_address = ?
[...] ('pearl.krabs@gmail.com',)
[('Pearl Krabs',)]

Формы EXISTS: has() / any()¶

В разделе подзапросы EXISTS мы представили объект Exists, который обеспечивает работу ключевого слова SQL EXISTS в сочетании со скалярным подзапросом. Конструкция relationship() предусматривает некоторые вспомогательные методы, которые могут быть использованы для генерации некоторых распространенных стилей запросов EXISTS в терминах отношения.

Для отношения «один ко многим», например User.addresses, EXISTS для таблицы address, которая коррелирует с таблицей user_account, может быть получена с помощью PropComparator.any(). Этот метод принимает необязательный критерий WHERE для ограничения строк, сопоставленных подзапросом:

>>> stmt = select(User.fullname).where(
...     User.addresses.any(Address.email_address == "pearl.krabs@gmail.com")
... )
>>> session.execute(stmt).all()
SELECT user_account.fullname
FROM user_account
WHERE EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id AND address.email_address = ?)
[...] ('pearl.krabs@gmail.com',)
[('Pearl Krabs',)]

Поскольку EXISTS имеет тенденцию быть более эффективным для отрицательного поиска, распространенным запросом является поиск сущностей, в которых нет связанных сущностей. Это можно сделать с помощью такой фразы, как ~User.addresses.any(), чтобы выбрать для User сущностей, у которых нет связанных строк Address:

>>> stmt = select(User.fullname).where(~User.addresses.any())
>>> session.execute(stmt).all()
SELECT user_account.fullname
FROM user_account
WHERE NOT (EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id))
[...] ()
[('Patrick McStar',), ('Squidward Tentacles',), ('Eugene H. Krabs',)]

Метод PropComparator.has() работает в основном так же, как PropComparator.any(), за исключением того, что он используется для отношений «многие-к-одному», например, если бы мы хотели найти все объекты Address, принадлежащие «pearl»:

>>> stmt = select(Address.email_address).where(Address.user.has(User.name == "pkrabs"))
>>> session.execute(stmt).all()
SELECT address.email_address
FROM address
WHERE EXISTS (SELECT 1
FROM user_account
WHERE user_account.id = address.user_id AND user_account.name = ?)
[...] ('pkrabs',)
[('pearl.krabs@gmail.com',), ('pearl@aol.com',)]

Общие операторы отношений¶

Существует несколько дополнительных разновидностей помощников генерации SQL, которые поставляются с relationship(), включая:

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

  >>> print(select(Address).where(Address.user == u1))
  SELECT address.id, address.email_address, address.user_id
  FROM address
  WHERE :param_1 = address.user_id
  

• сравнение многих с одним не равно - оператор not equals также может быть использован:

  >>> print(select(Address).where(Address.user != u1))
  SELECT address.id, address.email_address, address.user_id
  FROM address
  WHERE address.user_id != :user_id_1 OR address.user_id IS NULL
  

• объект содержится в коллекции «один-ко-многим « - по сути, это версия сравнения «равно», выберите строки, в которых первичный ключ равен значению внешнего ключа в связанном объекте:

  >>> print(select(User).where(User.addresses.contains(a1)))
  SELECT user_account.id, user_account.name, user_account.fullname
  FROM user_account
  WHERE user_account.id = :param_1
  

• Объект имеет определенного родителя с точки зрения один-ко-многим - функция with_parent() производит сравнение, которое возвращает строки, на которые ссылается данный родитель, это по сути то же самое, что использовать оператор == со стороной многие-ко-многим:

  >>> from sqlalchemy.orm import with_parent
  >>> print(select(Address).where(with_parent(u1, User.addresses)))
  SELECT address.id, address.email_address, address.user_id
  FROM address
  WHERE :param_1 = address.user_id
  

Селектиновая нагрузка¶

Наиболее полезным загрузчиком в современной SQLAlchemy является опция загрузчика selectinload(). Эта опция решает наиболее распространенную форму проблемы «N плюс один», которая заключается в том, что набор объектов ссылается на связанные коллекции. selectinload() гарантирует, что определенная коллекция для всей серии объектов будет загружена заранее с помощью одного запроса. Для этого используется форма SELECT, которая в большинстве случаев может быть выполнена только для связанной таблицы, без введения JOIN или подзапросов, и только для тех родительских объектов, для которых коллекция еще не загружена. Ниже мы иллюстрируем selectinload(), загружая все объекты User и все связанные с ними объекты Address; хотя мы вызываем Session.execute() только один раз, учитывая конструкцию select(), при обращении к базе данных фактически выполняется два оператора SELECT, второй из которых предназначен для получения связанных объектов Address:

>>> from sqlalchemy.orm import selectinload
>>> stmt = select(User).options(selectinload(User.addresses)).order_by(User.id)
>>> for row in session.execute(stmt):
...     print(
...         f"{row.User.name}  ({', '.join(a.email_address for a in row.User.addresses)})"
...     )
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()
SELECT address.user_id AS address_user_id, address.id AS address_id,
address.email_address AS address_email_address
FROM address
WHERE address.user_id IN (?, ?, ?, ?, ?, ?)
[...] (1, 2, 3, 4, 5, 6)
spongebob  (spongebob@sqlalchemy.org)
sandy  (sandy@sqlalchemy.org, sandy@squirrelpower.org)
patrick  ()
squidward  ()
ehkrabs  ()
pkrabs  (pearl.krabs@gmail.com, pearl@aol.com)

Присоединившийся груз¶

Стратегия ускоренной загрузки joinedload() является самым старым механизмом ускоренной загрузки в SQLAlchemy, который дополняет оператор SELECT, передаваемый в базу данных, JOIN (который может быть внешним или внутренним соединением в зависимости от опций), который затем может загружать связанные объекты.

Стратегия joinedload() лучше всего подходит для загрузки связанных объектов «многие-к-одному», так как в этом случае требуется только добавить дополнительные столбцы к строке первичной сущности, которая будет получена в любом случае. Для большей эффективности она также принимает опцию joinedload.innerjoin, так что внутреннее соединение вместо внешнего может быть использовано в случае, подобном приведенному ниже, когда мы знаем, что все объекты Address имеют связанные объекты User:

>>> from sqlalchemy.orm import joinedload
>>> stmt = (
...     select(Address)
...     .options(joinedload(Address.user, innerjoin=True))
...     .order_by(Address.id)
... )
>>> for row in session.execute(stmt):
...     print(f"{row.Address.email_address} {row.Address.user.name}")
SELECT address.id, address.email_address, address.user_id, user_account_1.id AS id_1,
user_account_1.name, user_account_1.fullname
FROM address
JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id
ORDER BY address.id
[...] ()
spongebob@sqlalchemy.org spongebob
sandy@sqlalchemy.org sandy
sandy@squirrelpower.org sandy
pearl.krabs@gmail.com pkrabs
pearl@aol.com pkrabs

joinedload() также работает для коллекций, то есть для отношений «один ко многим», однако он имеет эффект рекурсивного умножения первичных строк для каждого связанного элемента, что увеличивает объем передаваемых данных для набора результатов на порядки для вложенных коллекций и/или больших коллекций, поэтому его использование по сравнению с другим вариантом, таким как selectinload(), должно оцениваться в каждом конкретном случае.

Важно отметить, что критерии WHERE и ORDER BY заключающего оператора Select не нацелены на таблицу, выводимую с помощью joinedload(). Выше в SQL видно, что к таблице user_account применяется анонимный псевдоним, который не может быть напрямую использован в запросе. Более подробно эта концепция рассматривается в разделе Дзен присоединенной загрузки.

На клаузулу ON, отображаемую joinedload(), можно воздействовать непосредственно, используя метод PropComparator.and_(), описанный ранее в Дополнение критериев ОН; примеры этой техники со стратегиями загрузчика приведены ниже в Расширение путей стратегии погрузчика. Однако, в более общем случае, «объединенная нетерпеливая загрузка» может быть применена к Select, использующему Select.join(), используя подход, описанный в следующем разделе Явное присоединение + нетерпеливая нагрузка.

Явное присоединение + нетерпеливая нагрузка¶

Если бы мы загружали строки Address при присоединении к таблице user_account, используя такой метод, как Select.join() для отображения JOIN, мы могли бы также использовать этот JOIN для того, чтобы нетерпеливо загружать содержимое атрибута Address.user для каждого возвращаемого объекта Address. По сути, это означает, что мы используем «объединенную загрузку с нетерпением», но рендерим JOIN самостоятельно. Этот распространенный вариант использования достигается с помощью опции contains_eager(). Эта опция очень похожа на joinedload(), за исключением того, что она предполагает, что мы сами настроили JOIN, и вместо этого только указывает, что дополнительные столбцы в предложении COLUMNS должны быть загружены в связанные атрибуты на каждом возвращаемом объекте, например:

>>> from sqlalchemy.orm import contains_eager
>>> stmt = (
...     select(Address)
...     .join(Address.user)
...     .where(User.name == "pkrabs")
...     .options(contains_eager(Address.user))
...     .order_by(Address.id)
... )
>>> for row in session.execute(stmt):
...     print(f"{row.Address.email_address} {row.Address.user.name}")
SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.email_address, address.user_id
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = ? ORDER BY address.id
[...] ('pkrabs',)
pearl.krabs@gmail.com pkrabs
pearl@aol.com pkrabs

Выше мы отфильтровали строки по user_account.name, а также загрузили строки из user_account в атрибут Address.user возвращаемых строк. Если бы мы применили joinedload() отдельно, то получили бы SQL-запрос, который без необходимости соединяет дважды:

>>> stmt = (
...     select(Address)
...     .join(Address.user)
...     .where(User.name == "pkrabs")
...     .options(joinedload(Address.user))
...     .order_by(Address.id)
... )
>>> print(stmt)  # SELECT has a JOIN and LEFT OUTER JOIN unnecessarily
SELECT address.id, address.email_address, address.user_id,
user_account_1.id AS id_1, user_account_1.name, user_account_1.fullname
FROM address JOIN user_account ON user_account.id = address.user_id
LEFT OUTER JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id
WHERE user_account.name = :name_1 ORDER BY address.id

Расширение путей стратегии погрузчика¶

В Дополнение критериев ОН мы проиллюстрировали, как добавить произвольные критерии в JOIN, созданный с помощью relationship(), чтобы также включить дополнительные критерии в предложение ON. Метод PropComparator.and_() фактически является общедоступным для большинства вариантов загрузчика. Например, если мы хотим повторно загрузить имена пользователей и их адреса электронной почты, но опуская адреса электронной почты с доменом sqlalchemy.org, мы можем применить PropComparator.and_() к аргументу, передаваемому в selectinload(), чтобы ограничить этот критерий:

>>> from sqlalchemy.orm import selectinload
>>> stmt = (
...     select(User)
...     .options(
...         selectinload(
...             User.addresses.and_(~Address.email_address.endswith("sqlalchemy.org"))
...         )
...     )
...     .order_by(User.id)
...     .execution_options(populate_existing=True)
... )
>>> for row in session.execute(stmt):
...     print(
...         f"{row.User.name}  ({', '.join(a.email_address for a in row.User.addresses)})"
...     )
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()
SELECT address.user_id AS address_user_id, address.id AS address_id,
address.email_address AS address_email_address
FROM address
WHERE address.user_id IN (?, ?, ?, ?, ?, ?)
AND (address.email_address NOT LIKE '%' || ?)
[...] (1, 2, 3, 4, 5, 6, 'sqlalchemy.org')
spongebob  ()
sandy  (sandy@squirrelpower.org)
patrick  ()
squidward  ()
ehkrabs  ()
pkrabs  (pearl.krabs@gmail.com, pearl@aol.com)

Очень важным моментом, который следует отметить выше, является то, что специальная опция добавляется с .execution_options(populate_existing=True). Эта опция, которая вступает в силу при получении строк, указывает, что используемый нами загрузчик должен заменить существующее содержимое коллекций на объектах, если они уже загружены. Поскольку мы работаем с одним Session неоднократно, объекты, которые мы видим загруженными выше, являются теми же экземплярами Python, что и те, которые были впервые сохранены в начале раздела ORM этого учебника.

Raiseload¶

Еще одна дополнительная стратегия загрузчика, о которой стоит упомянуть, это raiseload(). Эта опция используется для полной блокировки приложения от возникновения проблемы N plus one, заставляя то, что обычно было бы ленивой загрузкой, вызывать ошибку. Она имеет два варианта, которые управляются с помощью опции raiseload.sql_only для блокирования либо ленивой загрузки, требующей SQL, либо всех операций «загрузки», включая те, которые требуют только обращения к текущему Session.

Один из способов использования raiseload() - настроить его на самом relationship(), установив relationship.lazy в значение "raise_on_sql", чтобы для определенного отображения определенное отношение никогда не пыталось выдать SQL:

class User(Base):
    __tablename__ = "user_account"

    # ... Column mappings

    addresses = relationship("Address", back_populates="user", lazy="raise_on_sql")


class Address(Base):
    __tablename__ = "address"

    # ... Column mappings

    user = relationship("User", back_populates="addresses", lazy="raise_on_sql")

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

u1 = s.execute(select(User)).scalars().first()
u1.addresses
sqlalchemy.exc.InvalidRequestError: 'User.addresses' is not available due to lazy='raise_on_sql'

Исключение указывает на то, что эта коллекция должна быть загружена вперед:

u1 = s.execute(select(User).options(selectinload(User.addresses))).scalars().first()

Опция lazy="raise_on_sql" также пытается быть умной в отношении отношений «многие-к-одному»; выше, если атрибут Address.user объекта Address не был загружен, но этот объект User локально присутствовал в том же Session, стратегия «raiseload» не вызовет ошибки.