Написание операторов SELECT для отображаемых классов ORM¶
О данном документе
В данном разделе используются отображения ORM, впервые проиллюстрированные в разделе Унифицированный учебник по SQLAlchemy, показанные в разделе Объявление сопоставленных классов.
Операторы SELECT формируются функцией select()
, которая возвращает объект Select
. Сущности и/или SQL-выражения, которые необходимо вернуть (например, предложение «columns»), передаются в функцию позиционно. Далее для формирования полного оператора используются дополнительные методы, например, метод Select.where()
, показанный ниже:
>>> from sqlalchemy import select
>>> stmt = select(User).where(User.name == "spongebob")
При наличии заполненного объекта Select
для его выполнения в ORM с целью получения строк обратно, объект передается в Session.execute()
, где возвращается объект Result
:
>>> result = session.execute(stmt)
{execsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',){stop}
>>> for user_obj in result.scalars():
... print(f"{user_obj.name} {user_obj.fullname}")
spongebob Spongebob Squarepants
Выбор сущностей и атрибутов ORM¶
Конструкция select()
принимает сущности ORM, включая отображенные классы, а также атрибуты уровня класса, представляющие отображенные столбцы, которые при построении преобразуются в элементы ORM-annotated FromClause
и ColumnElement
.
Объект Select
, содержащий ORM-аннотированные сущности, обычно выполняется с использованием объекта Session
, а не объекта Connection
, чтобы могли действовать возможности, связанные с ORM, в том числе возвращаться экземпляры ORM-mapped объектов. При использовании Connection
непосредственно строки результата будут содержать данные только на уровне столбцов.
Выбор сущностей ORM¶
Ниже мы выбираем из сущности User
, создавая Select
, который выбирает из сопоставленного Table
, к которому сопоставлен User
:
>>> result = session.execute(select(User).order_by(User.id))
{execsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()
При выборе из сущностей ORM сама сущность возвращается в результат в виде строки с одним элементом, а не в виде ряда отдельных столбцов; например, выше Result
возвращает объекты Row
, имеющие только один элемент в строке, который держится на объекте User
:
>>> result.all()
[(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),),
(User(id=2, name='sandy', fullname='Sandy Cheeks'),),
(User(id=3, name='patrick', fullname='Patrick Star'),),
(User(id=4, name='squidward', fullname='Squidward Tentacles'),),
(User(id=5, name='ehkrabs', fullname='Eugene H. Krabs'),)]
При выборе списка одноэлементных строк, содержащих сущности ORM, типично пропустить генерацию объектов Row
и вместо этого получить непосредственно сущности ORM. Этого проще всего добиться, используя для выполнения метод Session.scalars()
, а не метод Session.execute()
, так что возвращается объект ScalarResult
, который дает отдельные элементы, а не строки:
>>> session.scalars(select(User).order_by(User.id)).all()
{execsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()
{stop}[User(id=1, name='spongebob', fullname='Spongebob Squarepants'),
User(id=2, name='sandy', fullname='Sandy Cheeks'),
User(id=3, name='patrick', fullname='Patrick Star'),
User(id=4, name='squidward', fullname='Squidward Tentacles'),
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')]
Вызов метода Session.scalars()
эквивалентен вызову метода Session.execute()
для получения объекта Result
, а затем вызову метода Result.scalars()
для получения объекта ScalarResult
.
Одновременный выбор нескольких сущностей ORM¶
Функция select()
принимает любое количество классов ORM и/или выражений столбцов одновременно, в том числе может быть запрошено несколько классов ORM. При SELECT из нескольких ORM-классов они именуются в каждой строке результатов в соответствии с именем класса. В приведенном ниже примере строки результатов для SELECT по отношению к User
и Address
будут ссылаться на них под именами User
и Address
:
>>> stmt = select(User, Address).join(User.addresses).order_by(User.id, Address.id)
>>> for row in session.execute(stmt):
... print(f"{row.User.name} {row.Address.email_address}")
{execsql}SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id
[...] (){stop}
spongebob spongebob@sqlalchemy.org
sandy sandy@sqlalchemy.org
sandy squirrel@squirrelpower.org
patrick pat999@aol.com
squidward stentcl@sqlalchemy.org
Если бы мы хотели присвоить этим сущностям в строках разные имена, то использовали бы конструкцию aliased()
с параметром aliased.name
для псевдонима с явным именем:
>>> from sqlalchemy.orm import aliased
>>> user_cls = aliased(User, name="user_cls")
>>> email_cls = aliased(Address, name="email")
>>> stmt = (
... select(user_cls, email_cls)
... .join(user_cls.addresses.of_type(email_cls))
... .order_by(user_cls.id, email_cls.id)
... )
>>> row = session.execute(stmt).first()
{execsql}SELECT user_cls.id, user_cls.name, user_cls.fullname,
email.id AS id_1, email.user_id, email.email_address
FROM user_account AS user_cls JOIN address AS email
ON user_cls.id = email.user_id ORDER BY user_cls.id, email.id
[...] ()
{stop}>>> print(f"{row.user_cls.name} {row.email.email_address}")
spongebob spongebob@sqlalchemy.org
Приведенная выше алиасная форма рассматривается далее в Использование Relationship для объединения смежных целей.
Существующая конструкция Select
также может быть дополнена ORM-классами и/или выражениями столбцов с помощью метода Select.add_columns()
. С помощью этой формы мы можем получить то же самое утверждение, что и выше:
>>> stmt = (
... select(User).join(User.addresses).add_columns(Address).order_by(User.id, Address.id)
... )
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id
Выбор отдельных атрибутов¶
Атрибуты сопоставленного класса, такие как User.name
и Address.email_address
, могут использоваться так же, как Column
или другие объекты SQL-выражений при передаче в select()
. При создании select()
, направленного против определенных столбцов, будут возвращаться объекты Row
, а не сущности, такие как User
или Address
. Каждый Row
будет иметь каждый столбец, представленный отдельно:
>>> result = session.execute(
... select(User.name, Address.email_address)
... .join(User.addresses)
... .order_by(User.id, Address.id)
... )
{execsql}SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id
[...] (){stop}
Приведенный выше оператор возвращает объекты Row
со столбцами name
и email_address
, как показано в приведенной ниже демонстрации времени выполнения:
>>> for row in result:
... print(f"{row.name} {row.email_address}")
spongebob spongebob@sqlalchemy.org
sandy sandy@sqlalchemy.org
sandy squirrel@squirrelpower.org
patrick pat999@aol.com
squidward stentcl@sqlalchemy.org
Группировка выбранных атрибутов с помощью пакетов¶
Конструкция Bundle
- это расширяемая ORM-конструкция, позволяющая группировать наборы выражений столбцов в строках результатов:
>>> from sqlalchemy.orm import Bundle
>>> stmt = select(
... Bundle("user", User.name, User.fullname),
... Bundle("email", Address.email_address),
... ).join_from(User, Address)
>>> for row in session.execute(stmt):
... print(f"{row.user.name} {row.user.fullname} {row.email.email_address}")
{execsql}SELECT user_account.name, user_account.fullname, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] (){stop}
spongebob Spongebob Squarepants spongebob@sqlalchemy.org
sandy Sandy Cheeks sandy@sqlalchemy.org
sandy Sandy Cheeks squirrel@squirrelpower.org
patrick Patrick Star pat999@aol.com
squidward Squidward Tentacles stentcl@sqlalchemy.org
Bundle
потенциально полезен для создания облегченных представлений и пользовательских группировок столбцов. Bundle
также может быть подклассифицирован для возврата альтернативных структур данных; пример см. в Bundle.create_row_processor()
.
Выбор псевдонимов ORM¶
Как уже говорилось в учебном пособии Использование псевдонимов, для создания SQL-псевдонима сущности ORM используется конструкция aliased()
относительно сопоставленного класса:
>>> from sqlalchemy.orm import aliased
>>> u1 = aliased(User)
>>> print(select(u1).order_by(u1.id))
{printsql}SELECT user_account_1.id, user_account_1.name, user_account_1.fullname
FROM user_account AS user_account_1 ORDER BY user_account_1.id
Как и в случае использования Table.alias()
, SQL-псевдоним имеет анонимное имя. Для случая выбора сущности из строки с явным именем можно передать и параметр aliased.name
:
>>> from sqlalchemy.orm import aliased
>>> u1 = aliased(User, name="u1")
>>> stmt = select(u1).order_by(u1.id)
>>> row = session.execute(stmt).first()
{execsql}SELECT u1.id, u1.name, u1.fullname
FROM user_account AS u1 ORDER BY u1.id
[...] (){stop}
>>> print(f"{row.u1.name}")
spongebob
См.также
Конструкция aliased
является центральной для нескольких вариантов использования, в том числе:
использование подзапросов в ORM; подробнее об этом говорится в разделах Выбор сущностей из подзапросов и Присоединение к подзапросам.
Управление именем сущности в наборе результатов; пример см. в Одновременный выбор нескольких сущностей ORM
Присоединение к одной и той же сущности ORM несколько раз; пример см. в Использование Relationship для объединения смежных целей.
Получение результатов ORM из текстовых высказываний¶
ORM поддерживает загрузку сущностей из операторов SELECT, поступающих из других источников. Типичным случаем является текстовый оператор SELECT, который в SQLAlchemy представлен с помощью конструкции text()
. Конструкция text()
может быть дополнена информацией о столбцах, отображаемых в ORM, которые будут загружены этим оператором; затем эта информация может быть связана с самой сущностью ORM, чтобы объекты ORM могли быть загружены на основе этого оператора.
Дан текстовый SQL-оператор, который мы хотим загрузить из:
>>> from sqlalchemy import text
>>> textual_sql = text("SELECT id, name, fullname FROM user_account ORDER BY id")
Мы можем добавить информацию о столбцах в оператор с помощью метода TextClause.columns()
; при его вызове объект TextClause
преобразуется в объект TextualSelect
, который принимает на себя роль, сравнимую с конструкцией Select
. Обычно методу TextClause.columns()
передаются объекты Column
или эквивалентные им, и в этом случае мы можем использовать ORM-атрибуты класса User
напрямую:
>>> textual_sql = textual_sql.columns(User.id, User.name, User.fullname)
Теперь у нас есть ORM-конфигурированная SQL-конструкция, которая, как указано, может загружать отдельно столбцы «id», «name» и «fullname». Чтобы использовать этот оператор SELECT в качестве источника полных сущностей User
, мы можем связать эти столбцы с обычной конструкцией Select
, поддерживающей ORM, используя метод Select.from_statement()
:
>>> orm_sql = select(User).from_statement(textual_sql)
>>> for user_obj in session.execute(orm_sql).scalars():
... print(user_obj)
{execsql}SELECT id, name, fullname FROM user_account ORDER BY id
[...] (){stop}
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')
Этот же объект TextualSelect
можно также преобразовать в подзапрос с помощью метода TextualSelect.subquery()
и связать с сущностью User
с помощью конструкции aliased()
, аналогично тому, как это рассматривается ниже в Выбор сущностей из подзапросов:
>>> orm_subquery = aliased(User, textual_sql.subquery())
>>> stmt = select(orm_subquery)
>>> for user_obj in session.execute(stmt).scalars():
... print(user_obj)
{execsql}SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT id, name, fullname FROM user_account ORDER BY id) AS anon_1
[...] (){stop}
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')
Разница между использованием TextualSelect
непосредственно с Select.from_statement()
и использованием aliased()
заключается в том, что в первом случае в результирующем SQL не создается подзапрос. В некоторых случаях это может быть выгодно с точки зрения производительности или сложности.
Выбор сущностей из подзапросов¶
Конструкция aliased()
, рассмотренная в предыдущем разделе, может быть использована с любой конструкцией Subuqery
, исходящей из метода типа Select.subquery()
, для связи сущностей ORM со столбцами, возвращаемыми этим подзапросом; между столбцами, получаемыми подзапросом, и столбцами, на которые сопоставлена сущность, должно быть отношение соответствия столбцов, то есть подзапрос должен быть в конечном итоге получен из этих сущностей, как в примере ниже:
>>> inner_stmt = select(User).where(User.id < 7).order_by(User.id)
>>> subq = inner_stmt.subquery()
>>> aliased_user = aliased(User, subq)
>>> stmt = select(aliased_user)
>>> for user_obj in session.execute(stmt).scalars():
... print(user_obj)
{execsql} SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id < ? ORDER BY user_account.id) AS anon_1
[generated in ...] (7,)
{stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')
Выбор сущностей из UNION и других операций над множествами¶
Функции union()
и union_all()
являются наиболее распространенными операциями над множествами, которые наряду с другими операциями над множествами, такими как except_()
, intersect()
и другими, дают объект, известный как CompoundSelect
, который состоит из нескольких конструкций Select
, объединенных ключевым словом set-operation. ORM-сущности могут быть выбраны из простых составных селектов с помощью метода Select.from_statement()
, проиллюстрированного ранее в Получение результатов ORM из текстовых высказываний. В этом методе оператор UNION является полным оператором, который будет выведен на экран, никакие дополнительные критерии не могут быть добавлены после использования Select.from_statement()
:
>>> from sqlalchemy import union_all
>>> u = union_all(
... select(User).where(User.id < 2), select(User).where(User.id == 3)
... ).order_by(User.id)
>>> stmt = select(User).from_statement(u)
>>> for user_obj in session.execute(stmt).scalars():
... print(user_obj)
{execsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id < ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id = ? ORDER BY id
[generated in ...] (2, 3)
{stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=3, name='patrick', fullname='Patrick Star')
Конструкция CompoundSelect
может быть более гибко использована в запросе, который в дальнейшем может быть модифицирован путем организации его в подзапрос и связывания с сущностью ORM с помощью aliased()
, как это было показано ранее в Выбор сущностей из подзапросов. В приведенном ниже примере мы сначала используем CompoundSelect.subquery()
для создания подзапроса оператора UNION ALL, затем упаковываем его в конструкцию aliased()
, где он может быть использован как любая другая сопоставленная сущность в конструкции select()
, в том числе мы можем добавить фильтрацию и порядок по критериям на основе его экспортированных столбцов:
>>> subq = union_all(
... select(User).where(User.id < 2), select(User).where(User.id == 3)
... ).subquery()
>>> user_alias = aliased(User, subq)
>>> stmt = select(user_alias).order_by(user_alias.id)
>>> for user_obj in session.execute(stmt).scalars():
... print(user_obj)
{execsql}SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id < ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id = ?) AS anon_1 ORDER BY anon_1.id
[generated in ...] (2, 3)
{stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=3, name='patrick', fullname='Patrick Star')
Присоединяйтесь к¶
Методы Select.join()
и Select.join_from()
используются для построения SQL JOIN на основе оператора SELECT.
В данном разделе будут подробно описаны случаи использования этих методов в ORM. Общий обзор их использования с точки зрения Core приведен в Явные предложения FROM и JOIN в Унифицированный учебник по SQLAlchemy.
Использование Select.join()
в контексте ORM для запросов 2.0 style в основном эквивалентно, за вычетом унаследованных случаев использования, использованию метода Query.join()
в запросах 1.x style.
Простые соединения отношений¶
Рассмотрим отображение между двумя классами User
и Address
, с отношением User.addresses
, представляющим собой коллекцию объектов Address
, связанных с каждым User
. Наиболее частое использование Select.join()
заключается в создании JOIN вдоль этого отношения, используя атрибут User.addresses
в качестве индикатора того, как это должно происходить:
>>> stmt = select(User).join(User.addresses)
Там, где выше, вызов Select.join()
вместе с User.addresses
приведет к SQL, примерно эквивалентному:
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id
В приведенном примере мы называем User.addresses
, переданный в Select.join()
, «клаузой включения», то есть он указывает, как должна быть построена часть JOIN «ON».
Совет
Обратите внимание, что использование Select.join()
для JOIN от одной сущности к другой влияет на предложение FROM оператора SELECT, но не на предложение columns; оператор SELECT в данном примере будет продолжать возвращать строки только из сущности User
. Для одновременного выбора столбцов/сущностей из User
и Address
необходимо, чтобы сущность Address
также была названа в функции select()
, либо добавлена в конструкцию Select
после этого с помощью метода Select.add_columns()
. Примеры обеих этих форм см. в разделе Одновременный выбор нескольких сущностей ORM.
Объединение нескольких соединений¶
Для построения цепочки соединений можно использовать несколько вызовов Select.join()
. Атрибут relationship-bound подразумевает сразу и левую, и правую сторону соединения. Рассмотрим дополнительные сущности Order
и Item
, где отношение User.orders
ссылается на сущность Order
, а отношение Order.items
ссылается на сущность Item
через ассоциативную таблицу order_items
. Два вызова Select.join()
приведут к тому, что будет создан JOIN сначала из User
в Order
, а затем из Order
в Item
. Однако, поскольку Order.items
является отношением many to many, оно приводит к двум отдельным элементам JOIN, что в общей сложности составляет три элемента JOIN в результирующем SQL:
>>> stmt = select(User).join(User.orders).join(Order.items)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id
Порядок обращения к методу Select.join()
важен лишь в той степени, в какой «левая» сторона того, к чему мы хотим присоединиться, должна присутствовать в списке FROM до того, как мы укажем новую цель. <<<Например, Select.join()
не будет знать, как правильно присоединиться, если мы укажем select(User).join(Order.items).join(User.orders)
, и выдаст ошибку. В правильной практике метод Select.join()
вызывается таким образом, чтобы соответствовать тому, как мы хотели бы видеть клаузулы JOIN в SQL, и каждый вызов должен представлять собой четкую связь с тем, что ему предшествует.
Все элементы, которые мы указали в предложении FROM, остаются доступными в качестве потенциальных точек для продолжения присоединения FROM. Мы можем продолжать добавлять другие элементы для присоединения к сущности FROM User
, например, добавить к нашей цепочке присоединений отношение User.addresses
:
>>> stmt = select(User).join(User.orders).join(Order.items).join(User.addresses)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id
JOIN address ON user_account.id = address.user_id
Присоединения к целевой сущности¶
Вторая форма Select.join()
позволяет использовать в качестве цели любую сопоставленную сущность или основную селектируемую конструкцию. В этом случае Select.join()
попытается включить предложение ON для JOIN, используя естественную связь по внешнему ключу между двумя сущностями:
>>> stmt = select(User).join(Address)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id
В приведенной форме вызова вызывается Select.join()
для автоматического вывода «on clause». Эта форма вызова в конечном итоге приведет к ошибке, если между двумя сопоставленными конструкциями Table
либо нет установки ForeignKeyConstraint
, либо между ними существует несколько связей ForeignKeyConstraint
, так что выбор подходящего ограничения оказывается неоднозначным.
Примечание
При использовании Select.join()
или Select.join_from()
без указания условия ON сконфигурированные в ORM конструкции relationship()
не принимаются во внимание. При попытке вывода условия ON для JOIN учитываются только сконфигурированные отношения ForeignKeyConstraint
между сущностями на уровне отображаемых объектов Table
.
Присоединения к цели с помощью предложения ON¶
Третья форма вызова позволяет передавать как целевую сущность, так и предложение ON в явном виде. Пример, включающий SQL-выражение в качестве условия ON, выглядит следующим образом:
>>> stmt = select(User).join(Address, User.id == Address.user_id)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id
Предложение ON, основанное на выражении, также может быть relationship()
-связанным атрибутом, подобно тому, как это используется в Простые соединения отношений:
>>> stmt = select(User).join(Address, User.addresses)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id
Приведенный пример кажется избыточным, поскольку указывает на цель Address
двумя разными способами; однако полезность этой формы становится очевидной при присоединении к алиасным сущностям; пример см. в разделе Использование Relationship для объединения смежных целей.
Объединение отношений с пользовательскими критериями ON¶
Предложение ON, создаваемое конструкцией relationship()
, может быть дополнено дополнительными критериями. Это полезно как для быстрого ограничения области действия конкретного соединения по пути отношения, так и для таких случаев, как настройка стратегий загрузчика, например joinedload()
и selectinload()
. Метод PropComparator.and_()
позиционно принимает серию SQL-выражений, которые будут присоединены к предложению ON JOIN через AND. Например, если мы хотим выполнить JOIN от User
до Address
, но при этом ограничить критерий ON только определенными адресами электронной почты:
>>> stmt = select(User.fullname).join(
... User.addresses.and_(Address.email_address == "squirrel@squirrelpower.org")
... )
>>> session.execute(stmt).all()
{execsql}SELECT user_account.fullname
FROM user_account
JOIN address ON user_account.id = address.user_id AND address.email_address = ?
[...] ('squirrel@squirrelpower.org',){stop}
[('Sandy Cheeks',)]
См.также
Метод PropComparator.and_()
также работает с такими стратегиями загрузчика, как joinedload()
и selectinload()
. См. раздел Добавление критериев к параметрам загрузчика.
Использование Relationship для объединения смежных целей¶
При построении объединений с использованием атрибутов relationship()
-bound для указания условия ON синтаксис с двумя аргументами, показанный в Присоединения к цели с помощью предложения ON, может быть расширен для работы с конструкцией aliased()
, для указания SQL-псевдонима в качестве цели соединения, при этом используя атрибут relationship()
-bound для указания условия ON, как в приведенном ниже примере, где сущность User
дважды присоединяется к двум различным конструкциям aliased()
против сущности Address
: :
>>> address_alias_1 = aliased(Address)
>>> address_alias_2 = aliased(Address)
>>> stmt = (
... select(User)
... .join(address_alias_1, User.addresses)
... .where(address_alias_1.email_address == "patrick@aol.com")
... .join(address_alias_2, User.addresses)
... .where(address_alias_2.email_address == "patrick@gmail.com")
... )
>>> print(stmt)
{printsql}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
Та же схема может быть выражена более лаконично с помощью модификатора PropComparator.of_type()
, который можно применить к атрибуту relationship()
-bound, передав ему целевую сущность, чтобы указать цель за один шаг. В приведенном ниже примере с помощью атрибута PropComparator.of_type()
создается такой же SQL-оператор, как и только что проиллюстрированный:
>>> 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")
... )
{printsql}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))
{printsql}SELECT user_account_1.name
FROM user_account AS user_account_1
JOIN address ON user_account_1.id = address.user_id
Присоединение к подзапросам¶
Целью объединения может быть любая «выбираемая» сущность, включая подзапросы. При использовании ORM, как правило, эти цели указываются в виде конструкции aliased()
, однако это не является обязательным, особенно если объединенная сущность не возвращается в результатах. Например, для соединения сущности User
с сущностью Address
, где сущность Address
представлена в виде подзапроса, ограниченного строками, мы сначала строим объект Subquery
с помощью Select.subquery()
, который затем может быть использован в качестве цели метода Select.join()
:
>>> subq = select(Address).where(Address.email_address == "pat999@aol.com").subquery()
>>> stmt = select(User).join(subq, User.id == subq.c.user_id)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = :email_address_1) AS anon_1
ON user_account.id = anon_1.user_id{stop}
Приведенный выше оператор SELECT при вызове через Session.execute()
вернет строки, содержащие сущности User
, но не содержащие сущностей Address
. Для того чтобы включить сущности Address
в набор сущностей, возвращаемых в наборе результатов, мы создаем объект aliased()
на основе сущности Address
и объекта Subquery
. Мы также можем пожелать применить имя к конструкции aliased()
, например, "address"
, используемое ниже, чтобы мы могли ссылаться на нее по имени в строке результатов:
>>> address_subq = aliased(Address, subq, name="address")
>>> stmt = select(User, address_subq).join(address_subq)
>>> for row in session.execute(stmt):
... print(f"{row.User} {row.address}")
{execsql}SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.user_id, anon_1.email_address
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id
[...] ('pat999@aol.com',){stop}
User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')
Присоединение к подзапросам по путям отношений¶
Форма подзапроса, проиллюстрированная в предыдущем разделе, может быть выражена более конкретно с помощью атрибута relationship()
-bound, используя одну из форм, указанных в Использование Relationship для объединения смежных целей. Например, чтобы создать то же самое присоединение, обеспечив при этом присоединение вдоль конкретного relationship()
, можно воспользоваться методом PropComparator.of_type()
, передав конструкцию aliased()
, содержащую объект Subquery
, являющийся целью присоединения:
>>> address_subq = aliased(Address, subq, name="address")
>>> stmt = select(User, address_subq).join(User.addresses.of_type(address_subq))
>>> for row in session.execute(stmt):
... print(f"{row.User} {row.address}")
{execsql}SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.user_id, anon_1.email_address
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id
[...] ('pat999@aol.com',){stop}
User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')
Подзапросы, относящиеся к нескольким сущностям¶
Подзапрос, содержащий столбцы, охватывающие более одной сущности ORM, может применяться одновременно к нескольким конструкциям aliased()
и использоваться в одной и той же конструкции Select
для каждой сущности в отдельности. Рендеринг SQL будет продолжать рассматривать все такие конструкции aliased()
как один и тот же подзапрос, однако с точки зрения ORM / Python различные возвращаемые значения и атрибуты объектов могут быть упомянуты с помощью соответствующей конструкции aliased()
.
Например, задан подзапрос, который ссылается как на User
, так и на Address
:
>>> user_address_subq = (
... select(User.id, User.name, User.fullname, Address.id, Address.email_address)
... .join_from(User, Address)
... .where(Address.email_address.in_(["pat999@aol.com", "squirrel@squirrelpower.org"]))
... .subquery()
... )
Мы можем создать конструкции aliased()
против User
и Address
, которые ссылаются на один и тот же объект:
>>> user_alias = aliased(User, user_address_subq, name="user")
>>> address_alias = aliased(Address, user_address_subq, name="address")
Конструкция Select
, выбирающая из обеих сущностей, выводит подзапрос один раз, но в контексте строки результата может возвращать объекты классов User
и Address
одновременно:
>>> stmt = select(user_alias, address_alias).where(user_alias.name == "sandy")
>>> for row in session.execute(stmt):
... print(f"{row.user} {row.address}")
{execsql}SELECT anon_1.id, anon_1.name, anon_1.fullname, anon_1.id_1, anon_1.email_address
FROM (SELECT user_account.id AS id, user_account.name AS name,
user_account.fullname AS fullname, address.id AS id_1,
address.email_address AS email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE address.email_address IN (?, ?)) AS anon_1
WHERE anon_1.name = ?
[...] ('pat999@aol.com', 'squirrel@squirrelpower.org', 'sandy'){stop}
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='squirrel@squirrelpower.org')
Установка крайнего левого предложения FROM в объединении¶
В тех случаях, когда левая часть текущего состояния Select
не соответствует тому, к чему мы хотим присоединиться, может быть использован метод Select.join_from()
:
>>> stmt = select(Address).join_from(User, User.addresses).where(User.name == "sandy")
>>> print(stmt)
{printsql}SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1
Метод Select.join_from()
принимает два или три аргумента, либо в виде (<join from>, <onclause>)
, либо (<join from>, <join to>, [<onclause>])
:
>>> stmt = select(Address).join_from(User, Address).where(User.name == "sandy")
>>> print(stmt)
{printsql}SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1
Для настройки начального предложения FROM для SELECT таким образом, чтобы в дальнейшем можно было использовать Select.join()
, можно также использовать метод Select.select_from()
:
>>> stmt = select(Address).select_from(User).join(Address).where(User.name == "sandy")
>>> print(stmt)
{printsql}SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1
Совет
На самом деле метод Select.select_from()
не имеет решающего значения для порядка следования таблиц в предложении FROM. Если в операторе также имеется ссылка на конструкцию Join
, которая ссылается на существующие таблицы в другом порядке, то приоритет имеет конструкция Join
. Когда мы используем такие методы, как Select.join()
и Select.join_from()
, эти методы в конечном итоге создают такой объект Join
. Поэтому мы можем видеть, как содержимое Select.select_from()
переопределяется в случае, подобном этому:
>>> stmt = select(Address).select_from(User).join(Address.user).where(User.name == "sandy")
>>> print(stmt)
{printsql}SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1
Там, где выше, мы видим, что в предложении FROM стоит address JOIN user_account
, хотя сначала было указано select_from(User)
. Из-за вызова метода .join(Address.user)
оператор в конечном итоге эквивалентен следующему:
>>> from sqlalchemy.sql import join
>>>
>>> user_table = User.__table__
>>> address_table = Address.__table__
>>>
>>> j = address_table.join(user_table, user_table.c.id == address_table.c.user_id)
>>> stmt = (
... select(address_table)
... .select_from(user_table)
... .select_from(j)
... .where(user_table.c.name == "sandy")
... )
>>> print(stmt)
{printsql}SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1
Приведенная выше конструкция Join
добавляется как еще одна запись в список Select.select_from()
, которая заменяет предыдущую запись.
Операторы отношения WHERE¶
Помимо использования конструкций relationship()
в методах Select.join()
и Select.join_from()
, relationship()
также играет роль в построении SQL-выражений, которые обычно используются в предложении WHERE, с помощью метода Select.where()
.
Формы EXISTS: has() / any()¶
Конструкция Exists
впервые была представлена в Унифицированный учебник по SQLAlchemy в разделе подзапросы EXISTS. Этот объект используется для визуализации ключевого слова SQL EXISTS в сочетании со скалярным подзапросом. Конструкция relationship()
предусматривает некоторые вспомогательные методы, которые могут быть использованы для генерации некоторых распространенных стилей запросов в терминах отношения EXISTS.
Для отношения «один-ко-многим», например User.addresses
, с помощью метода PropComparator.any()
можно получить EXISTS для таблицы address
, которая коррелирует с таблицей user_account
. Этот метод принимает необязательный критерий WHERE для ограничения строк, сопоставленных подзапросу:
>>> stmt = select(User.fullname).where(
... User.addresses.any(Address.email_address == "squirrel@squirrelpower.org")
... )
>>> session.execute(stmt).all()
{execsql}SELECT user_account.fullname
FROM user_account
WHERE EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id AND address.email_address = ?)
[...] ('squirrel@squirrelpower.org',){stop}
[('Sandy Cheeks',)]
Поскольку EXISTS, как правило, более эффективен для отрицательного поиска, часто встречающийся запрос состоит в том, чтобы найти сущности, в которых нет ни одной связанной сущности. Для этого используется фраза типа ~User.addresses.any()
, чтобы выбрать для User
сущностей, которые не имеют связанных строк Address
:
>>> stmt = select(User.fullname).where(~User.addresses.any())
>>> session.execute(stmt).all()
{execsql}SELECT user_account.fullname
FROM user_account
WHERE NOT (EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id))
[...] (){stop}
[('Eugene H. Krabs',)]
Метод PropComparator.has()
работает в основном так же, как PropComparator.any()
, за исключением того, что он используется для отношений «многие-к-одному», например, если бы мы хотели найти все объекты Address
, принадлежащие «sandy»:
>>> stmt = select(Address.email_address).where(Address.user.has(User.name == "sandy"))
>>> session.execute(stmt).all()
{execsql}SELECT address.email_address
FROM address
WHERE EXISTS (SELECT 1
FROM user_account
WHERE user_account.id = address.user_id AND user_account.name = ?)
[...] ('sandy',){stop}
[('sandy@sqlalchemy.org',), ('squirrel@squirrelpower.org',)]
Операторы сравнения экземпляров отношений¶
Атрибут relationship()
-bound также предлагает несколько реализаций SQL-конструкций, ориентированных на фильтрацию атрибута relationship()
-bound в терминах конкретного экземпляра связанного объекта, которые могут распаковывать соответствующие значения атрибутов из данного экземпляра объекта persistent (или реже detached) и строить критерии WHERE в терминах целевого relationship()
.
Сравнение многих с одним - конкретный экземпляр объекта можно сравнить с отношением «многие к одному», чтобы выбрать строки, в которых внешний ключ целевой сущности совпадает со значением первичного ключа заданного объекта:
>>> user_obj = session.get(User, 1) {execsql}SELECT ...{stop} >>> print(select(Address).where(Address.user == user_obj)) {printsql}SELECT address.id, address.user_id, address.email_address FROM address WHERE :param_1 = address.user_id
сравнение многих с одним не равно - также может быть использован оператор not equals:
>>> print(select(Address).where(Address.user != user_obj)) {printsql}SELECT address.id, address.user_id, address.email_address FROM address WHERE address.user_id != :user_id_1 OR address.user_id IS NULL
объект содержится в коллекции «один-ко-многим « - по сути, это версия сравнения «один-ко-многим», выбирающая строки, в которых первичный ключ равен значению внешнего ключа в связанном объекте:
>>> address_obj = session.get(Address, 1) {execsql}SELECT ...{stop} >>> print(select(User).where(User.addresses.contains(address_obj))) {printsql}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(user_obj, User.addresses))) {printsql}SELECT address.id, address.user_id, address.email_address FROM address WHERE :param_1 = address.user_id