Руководство по составлению запросов 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)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',)
>>> 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))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.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}")
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
[...] ()
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)
... )
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
[...] ()
Атрибуты 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}")
SELECT user_account.name, user_account.fullname, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ()
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()
SELECT u1.id, u1.name, u1.fullname
FROM user_account AS u1 ORDER BY u1.id
[...] ()
>>> 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:
Формы EXISTS: has() / any() - помощники для генерации предложений EXISTS с использованием
relationship()
Общие операторы отношений - помощники для создания сравнений в терминах
relationship()
в ссылке на конкретный экземпляр объекта
Параметры загрузчика 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)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
...
Обычно объекты 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.
См.также
Я повторно загружаю данные с помощью Session, но он не видит изменений, которые я сделал в другом месте. - в Часто задаваемые вопросы
Обновление / истечение срока действия - в документации ORM Session
Автопромывка¶
Эта опция, переданная как False
, заставит Session
не вызывать шаг «autoflush». Это эквивалентно использованию менеджера контекста Session.no_autoflush
для отключения автозамывки:
>>> stmt = select(User).execution_options(autoflush=False)
sql>>> session.execute(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
...
Эта опция также будет работать с запросами 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)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] ()
(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)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] ()
(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)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] ()
(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
.