Руководство по составлению запросов ORM

В этом разделе представлен обзор создания запросов с помощью SQLAlchemy ORM с использованием 2.0 style.

Читатели этого раздела должны быть знакомы с обзором SQLAlchemy в Самоучитель SQLAlchemy 1.4 / 2.0, и в частности, большая часть содержания здесь развивает содержание в Выбор строк с помощью ядра или ORM.

Утверждения SELECT

Операторы SELECT создаются функцией select(), которая возвращает объект Select:

>>> from sqlalchemy import select
>>> stmt = select(User).where(User.name == "spongebob")

Чтобы вызвать Select с помощью ORM, его передают в Session.execute():

sql>>> result = session.execute(stmt)
>>> for user_obj in result.scalars():
...     print(f"{user_obj.name} {user_obj.fullname}")
spongebob Spongebob Squarepants

Выбор сущностей и атрибутов ORM

Конструкция select() принимает сущности ORM, включая сопоставленные классы, а также атрибуты уровня класса, представляющие сопоставленные столбцы, которые преобразуются в ORM-аннотированные элементы FromClause и ColumnElement во время построения.

Объект Select, содержащий ORM-аннотированные сущности, обычно выполняется с использованием объекта Session, а не объекта Connection, чтобы могли действовать функции, связанные с ORM, включая возврат экземпляров ORM-сопоставленных объектов. При использовании Connection непосредственно строки результата будут содержать только данные на уровне столбцов.

Ниже мы выбираем из сущности User, создавая Select, которая выбирает из сопоставленного Table, к которому сопоставлен User:

sql>>> result = session.execute(select(User).order_by(User.id))

При выборе из сущностей ORM сама сущность возвращается в результате как строка с одним элементом, а не как серия отдельных столбцов; например, выше Result возвращает объекты Row, которые имеют только один элемент в строке, этот элемент держится на объекте User:

>>> result.fetchone()
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)

При выборе списка одноэлементных строк, содержащих сущности ORM, типично пропустить генерацию объектов Row и вместо этого получить сущности ORM напрямую, что достигается с помощью метода Result.scalars():

>>> result.scalars().all()
[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 именуются в строке результата на основе имени их класса, как показано ниже, где мы одновременно ВЫБИРАЕМ из User и Address:

>>> stmt = select(User, Address).join(User.addresses).order_by(User.id, Address.id)

sql>>> for row in session.execute(stmt):
...     print(f"{row.User.name} {row.Address.email_address}")
spongebob spongebob@sqlalchemy.org
sandy sandy@sqlalchemy.org
sandy squirrel@squirrelpower.org
patrick pat999@aol.com
squidward stentcl@sqlalchemy.org

Выбор отдельных атрибутов

Атрибуты сопоставленного класса, такие как User.name и Address.email_address, имеют такое же поведение, как и сам класс сущности, например User в том смысле, что они автоматически преобразуются в ORM-аннотированные объекты Core при передаче в select(). Они могут использоваться так же, как и столбцы таблицы:

sql>>> result = session.execute(
...     select(User.name, Address.email_address)
...     .join(User.addresses)
...     .order_by(User.id, Address.id)
... )

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

>>> 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)
sql>>> for row in session.execute(stmt):
...     print(f"{row.user.name} {row.user.fullname} {row.email.email_address}")
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 потенциально полезен для создания облегченных представлений, а также пользовательских группировок столбцов, таких как отображения.

См.также

Пучки колонн - в документации по загрузке ORM.

Выбор псевдонимов ORM

Как обсуждалось в учебнике Использование псевдонимов, для создания SQL-псевдонима сущности ORM используется конструкция aliased() против сопоставленного класса:

>>> from sqlalchemy.orm import aliased
>>> u1 = aliased(User)
>>> print(select(u1).order_by(u1.id))
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)
sql>>> row = session.execute(stmt).first()
>>> print(f"{row.u1.name}")
spongebob

Конструкция aliased также является центральной для использования подзапросов в ORM; подробнее об этом говорится в разделах Выбор объектов из подзапросов и Присоединение к подзапросам.

Получение результатов 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 или эквивалентные им, и в этом случае мы можем напрямую использовать атрибуты класса User, отображенные на ORM:

>>> textual_sql = textual_sql.columns(User.id, User.name, User.fullname)

Теперь у нас есть ORM-конфигурированная SQL-конструкция, которая, как указано, может загружать столбцы «id», «name» и «fullname» по отдельности. Чтобы использовать этот оператор SELECT в качестве источника полных сущностей User, мы можем связать эти столбцы с обычной конструкцией Select с поддержкой ORM, используя метод Select.from_statement():

>>> # using from_statement()
>>> orm_sql = select(User).from_statement(textual_sql)
>>> for user_obj in session.execute(orm_sql).scalars():
...     print(user_obj)
SELECT id, name, fullname FROM user_account ORDER BY id [...] ()
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(), аналогично тому, как это рассмотрено ниже в Выбор объектов из подзапросов:

>>> # using aliased() to select from a subquery
>>> orm_subquery = aliased(User, textual_sql.subquery())
>>> stmt = select(orm_subquery)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
SELECT anon_1.id, anon_1.name, anon_1.fullname FROM (SELECT id, name, fullname FROM user_account ORDER BY id) AS anon_1 [...] ()
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 не создается подзапрос. В некоторых сценариях это может быть выгодно с точки зрения производительности или сложности.

См.также

Использование INSERT, UPDATE и ON CONFLICT (т.е. upsert) для возврата объектов ORM - Метод Select.from_statement() также работает с операторами DML, которые поддерживают RETURNING.

Выбор объектов из подзапросов

Конструкцию aliased(), рассмотренную в предыдущем разделе, можно использовать с любой конструкцией Subquery, которая исходит из такого метода, как 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)
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,)
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_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)
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)
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)
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)
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 1.4 / 2.0.

Использование 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)
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(), «предложением включения», то есть оно указывает, как должна быть построена часть «ON» JOIN.

Цепочка из нескольких объединений

Для построения цепочки соединений можно использовать несколько вызовов 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, в результате чего в результирующем SQL: получается три элемента JOIN:

>>> stmt = select(User).join(User.orders).join(Order.items)
>>> print(stmt)
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)
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)
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». Эта форма вызова в конечном итоге приведет к ошибке, если либо между двумя сопоставленными конструкциями ForeignKeyConstraint не будет установлено Table, либо если между ними будет несколько переходов ForeignKeyConstraint, так что соответствующее ограничение будет использоваться неоднозначно.

Примечание

При использовании Select.join() или Select.join_from() без указания условия ON, конфигурированные в ORM конструкции relationship() не принимаются во внимание. Только настроенные отношения ForeignKeyConstraint между сущностями на уровне сопоставленных объектов Table учитываются при попытке вывести условие ON для JOIN.

Присоединение к цели с помощью предложения ON

Третья форма вызова позволяет передавать как целевую сущность, так и предложение ON в явном виде. Пример, включающий выражение SQL в качестве условия ON, выглядит следующим образом:

>>> stmt = select(User).join(Address, User.id == Address.user_id)
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN address ON user_account.id = address.user_id

Предложение ON, основанное на выражении, также может быть атрибутом, связанным с отношениями; эта форма фактически дважды указывает цель Address, однако это принимается:

>>> stmt = select(User).join(Address, User.addresses)
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN address ON user_account.id = address.user_id

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

>>> a1 = aliased(Address)
>>> a2 = aliased(Address)
>>> stmt = (
...     select(User)
...     .join(a1, User.addresses)
...     .join(a2, User.addresses)
...     .where(a1.email_address == "ed@foo.com")
...     .where(a2.email_address == "ed@bar.com")
... )
>>> print(stmt)
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(). Аналогичный пример с использованием этого метода будет выглядеть так:

>>> stmt = (
...     select(User)
...     .join(User.addresses.of_type(a1))
...     .join(User.addresses.of_type(a2))
...     .where(a1.email_address == "ed@foo.com")
...     .where(a2.email_address == "ed@bar.com")
... )
>>> print(stmt)
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

Дополнение встроенных предложений ON

В качестве замены для предоставления полного пользовательского условия ON для существующего отношения, функция PropComparator.and_() может быть применена к атрибуту отношения для добавления дополнительных критериев в условие ON; дополнительные критерии будут объединены с критериями по умолчанию с помощью AND. Ниже, критерий ON между user_account и address содержит два отдельных элемента, соединенных AND, первый из которых является естественным соединением по внешнему ключу, а второй - пользовательским ограничивающим критерием:

>>> stmt = select(User).join(User.addresses.and_(Address.email_address != "foo@bar.com"))
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname FROM user_account JOIN address ON user_account.id = address.user_id AND address.email_address != :email_address_1

См.также

Метод PropComparator.and_() также работает со стратегиями загрузчика. Пример см. в разделе Добавление критериев к параметрам загрузчика.

Присоединение к подзапросам

Целью объединения может быть любая «выбираемая» сущность, которая может включать подзапросы. При использовании 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)
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

Приведенный выше оператор SELECT при вызове через Session.execute() вернет строки, содержащие User сущности, но не Address сущности. Чтобы добавить сущности Address к набору сущностей, которые будут возвращены в наборы результатов, мы создаем объект aliased() на основе сущности Address и пользовательского подзапроса. Обратите внимание, что мы также применяем имя "address" к конструкции aliased(), чтобы мы могли ссылаться на нее по имени в строке результата:

>>> 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}")
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',)
User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')

На один и тот же подзапрос могут ссылаться несколько сущностей, если подзапрос представляет более одной сущности. Сам подзапрос будет оставаться уникальным в рамках оператора, в то время как сущности, связанные с ним с помощью aliased, относятся к разным наборам столбцов:

>>> user_address_subq = (
...     select(User.id, User.name, Address.id, Address.email_address)
...     .join_from(User, Address)
...     .where(Address.email_address.in_(["pat999@aol.com", "squirrel@squirrelpower.org"]))
...     .subquery()
... )
>>> user_alias = aliased(User, user_address_subq, name="user")
>>> address_alias = aliased(Address, user_address_subq, name="address")
>>> stmt = select(user_alias, address_alias).where(user_alias.name == "sandy")
>>> for row in session.execute(stmt):
...     print(f"{row.user} {row.address}")
SELECT anon_1.id, anon_1.name, anon_1.id_1, anon_1.email_address FROM (SELECT user_account.id AS id, user_account.name AS name, 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')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='squirrel@squirrelpower.org')

Управление тем, к чему присоединяться

В случаях, когда левая часть текущего состояния Select не соответствует тому, к чему мы хотим присоединиться, можно использовать метод Select.join_from():

>>> stmt = select(Address).join_from(User, User.addresses).where(User.name == "sandy")
>>> print(stmt)
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)
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)
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)
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) оператор в конечном итоге эквивалентен следующему:

>>> user_table = User.__table__
>>> address_table = Address.__table__
>>> from sqlalchemy.sql import join
>>>
>>> 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)
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(), который заменяет предыдущий элемент.

Операторы по особым отношениям

Как подробно описано в Самоучитель SQLAlchemy 1.4 / 2.0 в Использование взаимосвязей в запросах, атрибуты ORM, отображаемые relationship(), могут быть использованы различными способами в качестве вспомогательных средств построения SQL. В дополнение к приведенной выше документации по Присоединяется к, отношения могут также создавать критерии для использования в предложении WHERE. См. связанные разделы ниже.

См.также

Секции в разделе Работа со связанными объектами Самоучитель SQLAlchemy 1.4 / 2.0:

Параметры загрузчика ORM

Опции загрузчика - это объекты, передаваемые методу Select.options(), которые влияют на загрузку как столбцов, так и атрибутов, ориентированных на отношения. Большинство опций загрузчика спускаются из иерархии Load. Для полного обзора использования опций загрузчика см. связанные разделы ниже.

См.также

  • Загрузка столбцов - подробное описание параметров отображения и загрузки, которые влияют на то, как загружаются атрибуты отображения столбцов и SQL-выражений

  • Техники загрузки отношений - подробные отношения и параметры загрузки, которые влияют на то, как загружаются сопоставленные атрибуты relationship()

Варианты выполнения ORM

Опции выполнения - это аргументы ключевых слов, передаваемые методу «execution_options», которые происходят на уровне выполнения оператора. Основной метод «опции выполнения» находится в Core по адресу Connection.execution_options(). В ORM опции выполнения также могут быть переданы в Session.execute() с помощью параметра Session.execute.execution_options. Возможно, более кратко, большинство опций выполнения, включая те, которые специфичны для ORM, могут быть назначены непосредственно оператору, используя метод Executable.execution_options(), так что опции могут быть связаны непосредственно с оператором, а не настраиваться отдельно. В примерах ниже будет использована эта форма.

Заполнить существующие

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

Пример использования выглядит так:

>>> stmt = select(User).execution_options(populate_existing=True)
sql>>> result = session.execute(stmt)

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

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

stmt = (
    select(User).
    where(User.name.in_(names)).
    execution_options(populate_existing=True).
    options(selectinload(User.addresses)
)
# will refresh all matching User objects as well as the related
# Address objects
users = session.execute(stmt).scalars().all()

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

  • Опция with_expression()

  • Метод PropComparator.and_(), который может изменять то, что загружает стратегия загрузчика

  • Опция contains_eager()

  • Опция with_loader_criteria()

Опция выполнения populate_existing эквивалентна методу Query.populate_existing() в запросах 1.x style ORM.

Автопромывка

Эта опция, переданная как False, заставит Session не вызывать шаг «autoflush». Это эквивалентно использованию менеджера контекста Session.no_autoflush для отключения автозамывки:

>>> stmt = select(User).execution_options(autoflush=False)
sql>>> session.execute(stmt)

Эта опция также будет работать с запросами Update и Delete, поддерживаемыми ORM.

Опция выполнения autoflush эквивалентна методу Query.autoflush() в запросах 1.x style ORM.

См.также

Промывка

Выборка больших наборов результатов с доходностью за

Опция выполнения yield_per представляет собой целочисленное значение, которое заставит Result буферизировать только ограниченное количество строк и/или объектов ORM за один раз, прежде чем сделать данные доступными для клиента.

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

Цель опции yield_per - изменить это поведение, чтобы набор результатов ORM был оптимизирован для итерации очень больших наборов результатов (> 10K строк), где пользователь определил, что вышеуказанные шаблоны не применимы. Когда используется yield_per, ORM будет вместо этого собирать результаты ORM в подколлекции и выдавать строки из каждой подколлекции по отдельности по мере итерации объекта Result, так что интерпретатору Python не нужно объявлять очень большие области памяти, что отнимает много времени и приводит к чрезмерному использованию памяти. Опция влияет как на способ использования курсора базы данных, так и на то, как ORM конструирует строки и объекты для передачи в Result.

Совет

Из вышесказанного следует, что Result должен потребляться итерационно, то есть с использованием итерации, например for row in result или с использованием методов частичного ряда, таких как Result.fetchmany() или Result.partitions(). Вызов Result.all() уничтожит цель использования yield_per.

Использование yield_per эквивалентно использованию как опции выполнения Connection.execution_options.stream_results, которая выбирает курсоры на стороне сервера для использования бэкендом, если они поддерживаются, так и метода Result.yield_per() на возвращаемом объекте Result, который устанавливает фиксированный размер извлекаемых строк, а также соответствующее ограничение на то, сколько объектов ORM будет создано одновременно.

Совет

yield_per теперь доступен и как вариант выполнения Core, подробно описанный в Использование курсоров на стороне сервера (они же потоковые результаты). В этом разделе подробно описывается использование yield_per в качестве опции выполнения с ORM Session. Опция ведет себя максимально одинаково в обоих контекстах.

yield_per при использовании с ORM обычно устанавливается либо через метод Executable.execution_options() на данном утверждении, либо путем передачи его в параметр Session.execute.execution_options метода Session.execute() или другого подобного метода Session. В приведенном ниже примере он вызывается на операторе:

>>> stmt = select(User).execution_options(yield_per=10)
sql>>> for row in session.execute(stmt):
...     print(row)
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
...

Приведенный выше код в основном эквивалентен использованию опции выполнения Connection.execution_options.stream_results, установке Connection.execution_options.max_row_buffer на заданный целочисленный размер, а затем использованию метода Result.yield_per() на Result, возвращаемом Session, как в следующем примере:

# equivalent code
>>> stmt = select(User).execution_options(stream_results=True, max_row_buffer=10)
sql>>> for row in session.execute(stmt).yield_per(10):
...     print(row)
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
...

yield_per также часто используется в сочетании с методом Result.partitions(), который выполняет итерацию строк в сгруппированных разделах. Размер каждого раздела по умолчанию равен целочисленному значению, переданному в yield_per, как в приведенном ниже примере:

>>> stmt = select(User).execution_options(yield_per=10)
sql>>> for partition in session.execute(stmt).partitions():
...     for row in partition:
...         print(row)
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
...

Опция выполнения yield_per не совместима с загрузкой «subquery» eager loading или «joined» eager loading при использовании коллекций. Он потенциально совместим с «select in» eager loading, при условии, что драйвер базы данных поддерживает несколько независимых курсоров.

Кроме того, вариант выполнения yield_per не совместим с методом Result.unique(); поскольку этот метод основан на хранении полного набора идентификаторов для всех строк, он обязательно нарушит цель использования yield_per, которая заключается в обработке произвольно большого количества строк.

Изменено в версии 1.4.6: Возникает исключение, когда строки ORM извлекаются из объекта Result, использующего фильтр Result.unique(), одновременно с использованием опции выполнения yield_per.

При использовании унаследованного объекта Query с использованием 1.x style ORM, метод Query.yield_per() будет иметь тот же результат, что и вариант выполнения yield_per.

ORM Update / Delete с произвольным предложением WHERE

Метод Session.execute(), в дополнение к работе с объектами Select, поддерживаемыми ORM, также может работать с объектами Update и Delete, которые UPDATE или DELETE любого количества строк базы данных, при этом синхронизируя состояние соответствующих объектов, локально присутствующих в Session. Подробнее об этой возможности см. в разделе UPDATE и DELETE с произвольным предложением WHERE.

Проверка сущностей и столбцов из операторов SELECT и DML с поддержкой ORM

Конструкция select(), а также конструкции insert(), update() и delete() (для последних конструкций DML, начиная с версии SQLAlchemy 1.4.33), поддерживают возможность проверки сущностей, для которых создаются эти утверждения, а также столбцов и типов данных, которые будут возвращены в наборе результатов.

Для объекта Select эта информация доступна из атрибута Select.column_descriptions. Этот атрибут работает так же, как и унаследованный атрибут Query.column_descriptions. Возвращаемый формат представляет собой список словарей:

>>> from pprint import pprint
>>> user_alias = aliased(User, name="user2")
>>> stmt = select(User, User.id, user_alias)
>>> pprint(stmt.column_descriptions)
[{'aliased': False,
    'entity': <class 'User'>,
    'expr': <class 'User'>,
    'name': 'User',
    'type': <class 'User'>},
    {'aliased': False,
    'entity': <class 'User'>,
    'expr': <....InstrumentedAttribute object at ...>,
    'name': 'id',
    'type': Integer()},
    {'aliased': True,
    'entity': <AliasedClass ...; User>,
    'expr': <AliasedClass ...; User>,
    'name': 'user2',
    'type': <class 'User'>}]

Когда Select.column_descriptions используется с не-ORM объектами, такими как обычные Table или Column объекты, записи будут содержать основную информацию об отдельных колонках, возвращаемых во всех случаях:

>>> stmt = select(user_table, address_table.c.id)
>>> pprint(stmt.column_descriptions)
[{'expr': Column('id', Integer(), table=<user_account>, primary_key=True, nullable=False),
    'name': 'id',
    'type': Integer()},
    {'expr': Column('name', String(length=30), table=<user_account>),
    'name': 'name',
    'type': String(length=30)},
    {'expr': Column('fullname', String(), table=<user_account>),
    'name': 'fullname',
    'type': String()},
    {'expr': Column('id', Integer(), table=<address>, primary_key=True, nullable=False),
    'name': 'id_1',
    'type': Integer()}]

Изменено в версии 1.4.33: Атрибут Select.column_descriptions теперь возвращает значение при использовании против Select, который не поддерживает ORM. Ранее при этом возникала ошибка NotImplementedError.

Для конструкций insert(), update() и delete() есть два отдельных атрибута. Один из них UpdateBase.entity_description возвращает информацию о первичной сущности ORM и таблице базы данных, на которую будет воздействовать конструкция DML:

>>> from sqlalchemy import update
>>> stmt = update(User).values(name="somename").returning(User.id)
>>> pprint(stmt.entity_description)
{'entity': <class 'User'>,
    'expr': <class 'User'>,
    'name': 'User',
    'table': Table('user_account', ...),
    'type': <class 'User'>}

Совет

UpdateBase.entity_description включает запись "table", которая на самом деле является таблицей, которая будет вставлена, обновлена или удалена оператором, что **не всегда совпадает с SQL «selectable», с которой может быть сопоставлен класс. Например, в сценарии наследования объединенных таблиц "table" будет ссылаться на локальную таблицу для данной сущности.

Другим является UpdateBase.returning_column_descriptions, который предоставляет информацию о столбцах, присутствующих в коллекции RETURNING, примерно таким же образом, как и Select.column_descriptions:

>>> pprint(stmt.returning_column_descriptions)
[{'aliased': False,
    'entity': <class 'User'>,
    'expr': <sqlalchemy.orm.attributes.InstrumentedAttribute ...>,
    'name': 'id',
    'type': Integer()}]

Добавлено в версии 1.4.33: Добавлены атрибуты UpdateBase.entity_description и UpdateBase.returning_column_descriptions.

Вернуться на верх