Выбор строк с помощью ядра или ORM¶

Выбор сущностей и столбцов ORM¶

ORM-сущности, такие как наш класс User, а также атрибуты, отображаемые на столбцы, такие как User.name, также участвуют в системе SQL Expression Language, представляющей таблицы и столбцы. Ниже показан пример выбора из сущности User, который в конечном итоге отображается так же, как если бы мы использовали непосредственно user_table:

>>> print(select(User))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account

При выполнении оператора, подобного приведенному выше, с использованием метода ORM Session.execute(), существует важное различие, когда мы выбираем из полной сущности, такой как User, в отличие от user_table, которое заключается в том, что сама сущность возвращается как один элемент в каждой строке. То есть, когда мы получаем строки из приведенного выше оператора, поскольку в списке объектов для выборки есть только сущность User, мы получаем обратно объекты Row, имеющие только один элемент, которые содержат экземпляры класса User:

>>> row = session.execute(select(User)).first()
BEGIN...
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] ()
>>> row
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)

Приведенный выше Row имеет только один элемент, представляющий сущность User:

>>> row[0]
User(id=1, name='spongebob', fullname='Spongebob Squarepants')

Настоятельно рекомендуемый удобный метод достижения того же результата, что и выше, заключается в использовании метода Session.scalars() для непосредственного выполнения оператора; этот метод вернет объект ScalarResult, который доставит первый «столбец» каждой строки сразу, в данном случае экземпляры класса User:

>>> user = session.scalars(select(User)).first()
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] ()
>>> user
User(id=1, name='spongebob', fullname='Spongebob Squarepants')

В качестве альтернативы, мы можем выбрать отдельные столбцы сущности ORM как отдельные элементы в строках результатов, используя атрибуты, связанные с классом; когда они передаются в такую конструкцию, как select(), они преобразуются в Column или другое выражение SQL, представленное каждым атрибутом:

>>> print(select(User.name, User.fullname))
SELECT user_account.name, user_account.fullname
FROM user_account

Когда мы вызываем этот оператор с помощью Session.execute(), мы теперь получаем строки, которые имеют отдельные элементы на каждое значение, каждый из которых соответствует отдельному столбцу или другому выражению SQL:

>>> row = session.execute(select(User.name, User.fullname)).first()
SELECT user_account.name, user_account.fullname
FROM user_account
[...] ()
>>> row
('spongebob', 'Spongebob Squarepants')

Подходы также можно смешивать, как показано ниже, где мы ВЫБИРАЕМ атрибут name сущности User в качестве первого элемента строки, и объединяем его с полными сущностями Address во втором элементе:

>>> session.execute(
...     select(User.name, Address).where(User.id == Address.user_id).order_by(Address.id)
... ).all()
SELECT user_account.name, address.id, address.email_address, address.user_id
FROM user_account, address
WHERE user_account.id = address.user_id ORDER BY address.id
[...] ()
[('spongebob', Address(id=1, email_address='spongebob@sqlalchemy.org')),
('sandy', Address(id=2, email_address='sandy@sqlalchemy.org')),
('sandy', Address(id=3, email_address='sandy@squirrelpower.org'))]

Подходы к выбору сущностей и столбцов ORM, а также общие методы преобразования строк обсуждаются далее в Выбор сущностей и атрибутов ORM.

Выборка из меченых выражений SQL¶

Метод ColumnElement.label(), а также одноименный метод, доступный в атрибутах ORM, предоставляет SQL-метку столбца или выражения, позволяя ему иметь определенное имя в наборе результатов. Это может быть полезно при обращении к произвольным SQL-выражениям в строке результата по имени:

>>> from sqlalchemy import func, cast
>>> stmt = select(
...     ("Username: " + user_table.c.name).label("username"),
... ).order_by(user_table.c.name)
>>> with engine.connect() as conn:
...     for row in conn.execute(stmt):
...         print(f"{row.username}")
BEGIN (implicit)
SELECT ? || user_account.name AS username
FROM user_account ORDER BY user_account.name
[...] ('Username: ',)
Username: patrick
Username: sandy
Username: spongebob
ROLLBACK

Выбор с помощью текстовых выражений столбцов¶

Когда мы создаем объект Select с помощью функции select(), мы обычно передаем ему серию объектов Table и Column, которые были определены с помощью table metadata, или при использовании ORM мы можем передавать ORM-mapped атрибуты, представляющие столбцы таблицы. Однако иногда возникает необходимость в производстве произвольных блоков SQL внутри операторов, таких как константные строковые выражения или просто произвольный SQL, который быстрее написать буквально.

Конструкция text(), введенная в Работа с транзакциями и DBAPI, может быть встроена в конструкцию Select непосредственно, как показано ниже, где мы создаем жестко закодированный строковый литерал 'some label' и встраиваем его в оператор SELECT:

>>> from sqlalchemy import text
>>> stmt = select(text("'some phrase'"), user_table.c.name).order_by(user_table.c.name)
>>> with engine.connect() as conn:
...     print(conn.execute(stmt).all())
BEGIN (implicit)
SELECT 'some phrase', user_account.name
FROM user_account ORDER BY user_account.name
[generated in ...] ()
[('some phrase', 'patrick'), ('some phrase', 'sandy'), ('some phrase', 'spongebob')]
ROLLBACK

Хотя конструкцию text() можно использовать в большинстве мест для введения буквальных фраз SQL, чаще всего мы имеем дело с текстовыми единицами, каждая из которых представляет собой отдельное выражение столбца. В этом случае мы можем получить больше функциональности от нашего текстового фрагмента, используя вместо него конструкцию literal_column(). Этот объект похож на text() за исключением того, что вместо того, чтобы представлять произвольный SQL в любой форме, он явно представляет один «столбец» и может быть затем помечен и упомянут в подзапросах и других выражениях:

>>> from sqlalchemy import literal_column
>>> stmt = select(literal_column("'some phrase'").label("p"), user_table.c.name).order_by(
...     user_table.c.name
... )
>>> with engine.connect() as conn:
...     for row in conn.execute(stmt):
...         print(f"{row.p}, {row.name}")
BEGIN (implicit)
SELECT 'some phrase' AS p, user_account.name
FROM user_account ORDER BY user_account.name
[generated in ...] ()
some phrase, patrick
some phrase, sandy
some phrase, spongebob
ROLLBACK

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

Установка положения о включении¶

Предыдущие примеры JOIN иллюстрировали, что конструкция Select может соединять две таблицы и автоматически создавать предложение ON. Это происходит в тех примерах потому, что объекты user_table и address_table Table включают одно определение ForeignKeyConstraint, которое используется для формирования предложения ON.

Если левая и правая цели объединения не имеют такого ограничения, или существует несколько ограничений, нам необходимо указать предложение ON напрямую. И Select.join(), и Select.join_from() принимают дополнительный аргумент для предложения ON, который указывается с помощью той же механики SQL Expression, о которой мы говорили в Предложение ГДЕ:

>>> print(
...     select(address_table.c.email_address)
...     .select_from(user_table)
...     .join(address_table, user_table.c.id == address_table.c.user_id)
... )
SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

ВНЕШНЕЕ и ПОЛНОЕ соединение¶

Оба метода Select.join() и Select.join_from() принимают ключевые аргументы Select.join.isouter и Select.join.full, которые будут отображать LEFT OUTER JOIN и FULL OUTER JOIN, соответственно:

>>> print(select(user_table).join(address_table, isouter=True))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account LEFT OUTER JOIN address ON user_account.id = address.user_id
>>> print(select(user_table).join(address_table, full=True))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account FULL OUTER JOIN address ON user_account.id = address.user_id

Существует также метод Select.outerjoin(), который эквивалентен использованию .join(..., isouter=True).

ПОРЯДКОВЫЙ НОМЕР¶

Предложение ORDER BY строится в терминах конструкций SQL Expression, обычно основанных на Column или аналогичных объектах. Метод Select.order_by() принимает одно или несколько таких выражений позиционно:

>>> print(select(user_table).order_by(user_table.c.name))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.name

Возрастание / убывание доступно из модификаторов ColumnElement.asc() и ColumnElement.desc(), которые также присутствуют из ORM-связанных атрибутов:

>>> print(select(User).order_by(User.fullname.desc()))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.fullname DESC

Приведенное выше утверждение даст строки, отсортированные по столбцу user_account.fullname в порядке убывания.

Агрегатные функции с GROUP BY / HAVING¶

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

SQLAlchemy предоставляет функции SQL в открытом виде, используя пространство имен, известное как func. Это специальный объект-конструктор, который будет создавать новые экземпляры Function, когда ему дается имя конкретной SQL-функции, которая может иметь любое имя, а также ноль или более аргументов для передачи функции, которые, как и во всех других случаях, являются конструкциями SQL-выражений. Например, чтобы вывести функцию SQL COUNT() для столбца user_account.id, мы обращаемся к имени count():

>>> from sqlalchemy import func
>>> count_fn = func.count(user_table.c.id)
>>> print(count_fn)
count(user_account.id)

Функции SQL более подробно описаны далее в этом учебнике в Работа с функциями SQL.

При использовании агрегатных функций в SQL предложение GROUP BY является важным, поскольку оно позволяет разделить строки на группы, где агрегатные функции будут применяться к каждой группе отдельно. При запросе неагрегированных столбцов в предложении COLUMNS оператора SELECT, SQL требует, чтобы все эти столбцы были подчинены предложению GROUP BY, прямо или косвенно на основе ассоциации с первичным ключом. Предложение HAVING затем используется аналогично предложению WHERE, за исключением того, что оно отфильтровывает строки на основе агрегированных значений, а не прямого содержимого строки.

SQLAlchemy предусматривает эти два условия с помощью методов Select.group_by() и Select.having(). Ниже мы проиллюстрируем выбор полей имени пользователя, а также подсчет адресов для тех пользователей, которые имеют более одного адреса:

>>> with engine.connect() as conn:
...     result = conn.execute(
...         select(User.name, func.count(Address.id).label("count"))
...         .join(Address)
...         .group_by(User.name)
...         .having(func.count(Address.id) > 1)
...     )
...     print(result.all())
BEGIN (implicit)
SELECT user_account.name, count(address.id) AS count
FROM user_account JOIN address ON user_account.id = address.user_id GROUP BY user_account.name
HAVING count(address.id) > ?
[...] (1,)
[('sandy', 2)]
ROLLBACK

Упорядочивание или группировка по метке¶

Важным приемом, особенно в некоторых бэкендах баз данных, является возможность ORDER BY или GROUP BY выражения, которое уже указано в предложении columns, без повторного указания выражения в предложении ORDER BY или GROUP BY, а вместо этого используя имя столбца или имя метки из предложения COLUMNS. Эта форма доступна, если передать строковый текст имени в метод Select.order_by() или Select.group_by(). Переданный текст не отображается напрямую; вместо него отображается имя, заданное выражению в клаузе columns и отображаемое как имя этого выражения в контексте, выдавая ошибку, если совпадение не найдено. Унарные модификаторы asc() и desc() также могут быть использованы в этой форме:

>>> from sqlalchemy import func, desc
>>> stmt = (
...     select(Address.user_id, func.count(Address.id).label("num_addresses"))
...     .group_by("user_id")
...     .order_by("user_id", desc("num_addresses"))
... )
>>> print(stmt)
SELECT address.user_id, count(address.id) AS num_addresses
FROM address GROUP BY address.user_id ORDER BY address.user_id, num_addresses DESC

Псевдонимы сущностей ORM¶

ORM-эквивалентом метода FromClause.alias() является функция ORM aliased(), которая может быть применена к объекту, такому как User и Address. В результате получается внутренний объект Alias, который противопоставляется оригинальному сопоставленному объекту Table, сохраняя при этом функциональность ORM. Приведенный ниже SELECT выбирает из объекта User все объекты, включающие два определенных адреса электронной почты:

>>> from sqlalchemy.orm import aliased
>>> address_alias_1 = aliased(Address)
>>> address_alias_2 = aliased(Address)
>>> print(
...     select(User)
...     .join_from(User, address_alias_1)
...     .where(address_alias_1.email_address == "patrick@aol.com")
...     .join_from(User, address_alias_2)
...     .where(address_alias_2.email_address == "patrick@gmail.com")
... )
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
WHERE address_1.email_address = :email_address_1
AND address_2.email_address = :email_address_2

Общие табличные выражения (CTE)¶

Использование конструкции CTE в SQLAlchemy практически не отличается от использования конструкции Subquery. Изменив вызов метода Select.subquery() на использование Select.cte() вместо него, мы можем использовать полученный объект в качестве элемента FROM таким же образом, но в качестве SQL используется совсем другой синтаксис обычного табличного выражения:

>>> subq = (
...     select(func.count(address_table.c.id).label("count"), address_table.c.user_id)
...     .group_by(address_table.c.user_id)
...     .cte()
... )

>>> stmt = select(user_table.c.name, user_table.c.fullname, subq.c.count).join_from(
...     user_table, subq
... )

>>> print(stmt)
WITH anon_1 AS
(SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id)
 SELECT user_account.name, user_account.fullname, anon_1.count
FROM user_account JOIN anon_1 ON user_account.id = anon_1.user_id

Конструкция CTE также имеет возможность использования в «рекурсивном» стиле, и в более сложных случаях может быть составлена из пункта RETURNING оператора INSERT, UPDATE или DELETE. Подробные сведения об этих дополнительных шаблонах содержатся в документальной строке для CTE.

В обоих случаях подзапрос и CTE были названы на уровне SQL с помощью «анонимного» имени. В коде Python нам совсем не нужно предоставлять эти имена. Идентификатор объекта Subquery или CTE служит синтаксическим идентификатором объекта при отображении. Имя, которое будет отображаться в SQL, можно указать, передав его в качестве первого аргумента методов Select.subquery() или Select.cte().

ORM Entity Subqueries/CTEs¶

В ORM конструкция aliased() может быть использована для ассоциации сущности ORM, такой как наш класс User или Address, с любой концепцией FromClause, которая представляет собой источник строк. В предыдущем разделе Псевдонимы сущностей ORM показано использование aliased() для ассоциации сопоставленного класса с Alias его сопоставленного Table. Здесь мы иллюстрируем, как aliased() делает то же самое в отношении как Subquery, так и CTE, созданного в отношении конструкции Select, которая в конечном счете является производной от того же сопоставленного Table.

Ниже приведен пример применения aliased() к конструкции Subquery, чтобы из ее строк можно было извлекать сущности ORM. Результат показывает серию объектов User и Address, где данные для каждого объекта Address в конечном итоге получены из подзапроса к таблице address, а не непосредственно из этой таблицы:

>>> subq = select(Address).where(~Address.email_address.like("%@aol.com")).subquery()
>>> address_subq = aliased(Address, subq)
>>> stmt = (
...     select(User, address_subq)
...     .join_from(User, address_subq)
...     .order_by(User.id, address_subq.id)
... )
>>> with Session(engine) as session:
...     for user, address in session.execute(stmt):
...         print(f"{user} {address}")
BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.email_address, anon_1.user_id
FROM user_account JOIN
(SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id
FROM address
WHERE address.email_address NOT LIKE ?) AS anon_1 ON user_account.id = anon_1.user_id
ORDER BY user_account.id, anon_1.id
[...] ('%@aol.com',)
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')
ROLLBACK

Далее следует другой пример, который точно такой же, только вместо него используется конструкция CTE:

>>> cte_obj = select(Address).where(~Address.email_address.like("%@aol.com")).cte()
>>> address_cte = aliased(Address, cte_obj)
>>> stmt = (
...     select(User, address_cte)
...     .join_from(User, address_cte)
...     .order_by(User.id, address_cte.id)
... )
>>> with Session(engine) as session:
...     for user, address in session.execute(stmt):
...         print(f"{user} {address}")
BEGIN (implicit)
WITH anon_1 AS
(SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id
FROM address
WHERE address.email_address NOT LIKE ?)
SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.email_address, anon_1.user_id
FROM user_account
JOIN anon_1 ON user_account.id = anon_1.user_id
ORDER BY user_account.id, anon_1.id
[...] ('%@aol.com',)
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')
ROLLBACK

ЛАТЕРАЛЬНАЯ корреляция¶

ЛАТЕРАЛЬНАЯ корреляция - это особая подкатегория SQL-корреляции, которая позволяет выбираемой единице ссылаться на другую выбираемую единицу в одном предложении FROM. Это чрезвычайно специальный случай использования, который, хотя и является частью стандарта SQL, поддерживается только в последних версиях PostgreSQL.

Обычно, если оператор SELECT ссылается на table1 JOIN (SELECT ...) AS subquery в своем предложении FROM, подзапрос с правой стороны не может ссылаться на выражение «table1» с левой стороны; корреляция может ссылаться только на таблицу, которая является частью другого SELECT, полностью охватывающего этот SELECT. Ключевое слово LATERAL позволяет нам перевернуть это поведение и разрешить корреляцию из правого JOIN.

SQLAlchemy поддерживает эту возможность с помощью метода Select.lateral(), который создает объект, известный как Lateral. Lateral относится к тому же семейству, что и Subquery и Alias, но также включает корреляционное поведение, когда конструкция добавляется в предложение FROM вложенного SELECT. Следующий пример иллюстрирует SQL-запрос, в котором используется LATERAL, выбирая данные «учетная запись пользователя / количество адресов электронной почты», как обсуждалось в предыдущем разделе:

>>> subq = (
...     select(
...         func.count(address_table.c.id).label("address_count"),
...         address_table.c.email_address,
...         address_table.c.user_id,
...     )
...     .where(user_table.c.id == address_table.c.user_id)
...     .lateral()
... )
>>> stmt = (
...     select(user_table.c.name, subq.c.address_count, subq.c.email_address)
...     .join_from(user_table, subq)
...     .order_by(user_table.c.id, subq.c.email_address)
... )
>>> print(stmt)
SELECT user_account.name, anon_1.address_count, anon_1.email_address
FROM user_account
JOIN LATERAL (SELECT count(address.id) AS address_count,
address.email_address AS email_address, address.user_id AS user_id
FROM address
WHERE user_account.id = address.user_id) AS anon_1
ON user_account.id = anon_1.user_id
ORDER BY user_account.id, anon_1.email_address

Выше, правая сторона JOIN является подзапросом, который соотносится с таблицей user_account, находящейся на левой стороне соединения.

При использовании Select.lateral() поведение методов Select.correlate() и Select.correlate_except() применяется и к конструкции Lateral.

Выбор объектов ORM из объединений¶

В предыдущих примерах было показано, как построить UNION на основе двух объектов Table, чтобы затем вернуть строки базы данных. Если мы хотим использовать UNION или другую операцию набора для выбора строк, которые мы затем получаем как объекты ORM, можно использовать два подхода. В обоих случаях мы сначала создаем объект select() или CompoundSelect, который представляет оператор SELECT / UNION / etc, который мы хотим выполнить; этот оператор должен быть составлен против целевых объектов ORM или их базовых сопоставленных объектов Table:

>>> stmt1 = select(User).where(User.name == "sandy")
>>> stmt2 = select(User).where(User.name == "spongebob")
>>> u = union_all(stmt1, stmt2)

Для простого SELECT с UNION, который еще не вложен в подзапрос, их часто можно использовать в контексте выборки объектов ORM с помощью метода Select.from_statement(). При таком подходе оператор UNION представляет весь запрос; никакие дополнительные критерии не могут быть добавлены после использования Select.from_statement():

>>> orm_stmt = select(User).from_statement(u)
>>> with Session(engine) as session:
...     for obj in session.execute(orm_stmt).scalars():
...         print(obj)
BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[generated in ...] ('sandy', 'spongebob')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
ROLLBACK

Для более гибкого использования UNION или другой конструкции, связанной с множеством, в качестве компонента, связанного с сущностью, конструкция CompoundSelect может быть организована в подзапрос с помощью CompoundSelect.subquery(), который затем связывается с объектами ORM с помощью функции aliased(). Это работает так же, как и в ORM Entity Subqueries/CTEs, сначала создается специальное «отображение» нужной нам сущности на подзапрос, а затем из него выбирается новая сущность, как если бы это был любой другой отображенный класс. В примере ниже мы можем добавить дополнительные критерии, такие как ORDER BY, вне самого UNION, так как мы можем фильтровать или упорядочивать по столбцам, экспортированным подзапросом:

>>> user_alias = aliased(User, u.subquery())
>>> orm_stmt = select(user_alias).order_by(user_alias.id)
>>> with Session(engine) as session:
...     for obj in session.execute(orm_stmt).scalars():
...         print(obj)
BEGIN (implicit)
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.name = ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.name = ?) AS anon_1 ORDER BY anon_1.id
[generated in ...] ('sandy', 'spongebob')
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
ROLLBACK

Функции имеют возвратные типы¶

Поскольку функции являются выражениями столбцов, они также имеют SQL datatypes, которые описывают тип данных сгенерированного SQL-выражения. Здесь мы называем эти типы «возвращаемыми типами SQL», имея в виду тип значения SQL, возвращаемого функцией в контексте SQL-выражения на стороне базы данных, в отличие от «возвращаемого типа» функции Python.

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

>>> func.now().type
DateTime()

Эти типы возврата SQL имеют значение при использовании выражения функции в контексте более крупного выражения; то есть, математические операторы будут работать лучше, если тип данных выражения будет таким, как Integer или Numeric, а аксессоры JSON для работы должны использовать такой тип, как JSON. Некоторые классы функций возвращают целые строки вместо значений столбцов, когда необходимо обратиться к конкретным столбцам; такие функции относятся к типу table valued functions.

Тип SQL-возврата функции также может иметь значение при выполнении оператора и получении строк обратно, в тех случаях, когда SQLAlchemy приходится применять обработку набора результатов. Ярким примером этого являются функции, связанные с датой на SQLite, где SQLAlchemy DateTime и связанные с ним типы данных берут на себя роль преобразования строковых значений в объекты Python datetime() по мере получения строк результата.

Чтобы применить определенный тип к создаваемой функции, мы передаем его с помощью параметра Function.type_; аргументом типа может быть либо класс TypeEngine, либо экземпляр. В примере ниже мы передаем класс JSON для создания функции PostgreSQL json_object(), отмечая, что возвращаемый тип SQL будет иметь тип JSON:

>>> from sqlalchemy import JSON
>>> function_expr = func.json_object('{a, 1, b, "def", c, 3.5}', type_=JSON)

Создавая нашу функцию JSON с типом данных JSON, объект выражения SQL приобретает возможности, связанные с JSON, например, доступ к элементам:

>>> stmt = select(function_expr["def"])
>>> print(stmt)
SELECT json_object(:json_object_1)[:json_object_2] AS anon_1

Встроенные функции имеют заранее настроенные типы возврата¶

Для распространенных агрегатных функций, таких как count, max, min, а также очень небольшого числа функций даты, таких как now, и строковых функций, таких как concat, тип возврата SQL устанавливается соответствующим образом, иногда на основе использования. Функция max и аналогичные функции агрегатной фильтрации устанавливают тип возврата SQL на основе заданного аргумента:

>>> m1 = func.max(Column("some_int", Integer))
>>> m1.type
Integer()

>>> m2 = func.max(Column("some_str", String))
>>> m2.type
String()

Функции даты и времени обычно соответствуют выражениям SQL, описываемым символами DateTime, Date или Time:

>>> func.now().type
DateTime()
>>> func.current_date().type
Date()

Известная строковая функция, такая как concat, будет знать, что выражение SQL будет иметь тип String:

>>> func.concat("x", "y").type
String()

Однако для подавляющего большинства функций SQL SQLAlchemy не имеет их явного присутствия в своем очень небольшом списке известных функций. Например, хотя обычно нет проблем с использованием SQL-функций func.lower() и func.upper() для преобразования регистра строк, SQLAlchemy на самом деле не знает об этих функциях, поэтому они имеют «нулевой» тип возврата SQL:

>>> func.upper("lowercase").type
NullType()

Для простых функций, таких как upper и lower, проблема обычно не существенна, поскольку строковые значения могут быть получены из базы данных без какой-либо специальной обработки типов на стороне SQLAlchemy, а правила когеренции типов SQLAlchemy часто могут правильно определить намерение; например, оператор Python + будет правильно интерпретирован как оператор конкатенации строк на основе рассмотрения обеих сторон выражения:

>>> print(select(func.upper("lowercase") + " suffix"))
SELECT upper(:upper_1) || :upper_2 AS anon_1

В целом, сценарий, в котором параметр Function.type_, скорее всего, необходим, таков:

1. функция еще не является встроенной функцией SQLAlchemy; об этом можно судить, создав функцию и заметив атрибут Function.type, то есть:

   >>> func.count().type
   Integer()

   против:

   >>> func.json_object('{"a", "b"}').type
   NullType()

2. Необходима поддержка выражений с поддержкой функций; чаще всего это относится к специальным операторам, связанным с типами данных, таким как JSON или ARRAY.

3. Необходима обработка значения результата, который может включать такие типы, как DateTime, Boolean, Enum, или снова специальные типы данных, такие как JSON, ARRAY.

Расширенные методы работы с функциями SQL¶

Следующие подразделы иллюстрируют больше вещей, которые можно сделать с помощью функций SQL. Хотя эти методы менее распространены и более сложны, чем использование базовых функций SQL, они, тем не менее, чрезвычайно популярны, во многом благодаря тому, что PostgreSQL уделяет особое внимание более сложным формам функций, включая формы с табличными и столбцовыми значениями, которые популярны при работе с данными JSON.

>>> stmt = (
...     select(
...         func.row_number().over(partition_by=user_table.c.name),
...         user_table.c.name,
...         address_table.c.email_address,
...     )
...     .select_from(user_table)
...     .join(address_table)
... )
>>> with engine.connect() as conn:  
...     result = conn.execute(stmt)
...     print(result.all())
BEGIN (implicit)
SELECT row_number() OVER (PARTITION BY user_account.name) AS anon_1,
user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ()
[(1, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (1, 'spongebob', 'spongebob@sqlalchemy.org')]
ROLLBACK

>>> stmt = (
...     select(
...         func.count().over(order_by=user_table.c.name),
...         user_table.c.name,
...         address_table.c.email_address,
...     )
...     .select_from(user_table)
...     .join(address_table)
... )
>>> with engine.connect() as conn:  
...     result = conn.execute(stmt)
...     print(result.all())
BEGIN (implicit)
SELECT count(*) OVER (ORDER BY user_account.name) AS anon_1,
user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ()
[(2, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (3, 'spongebob', 'spongebob@sqlalchemy.org')]
ROLLBACK

>>> print(
...     func.unnest(
...         func.percentile_disc([0.25, 0.5, 0.75, 1]).within_group(user_table.c.name)
...     )
... )
unnest(percentile_disc(:percentile_disc_1) WITHIN GROUP (ORDER BY user_account.name))

>>> stmt = (
...     select(
...         func.count(address_table.c.email_address).filter(user_table.c.name == "sandy"),
...         func.count(address_table.c.email_address).filter(
...             user_table.c.name == "spongebob"
...         ),
...     )
...     .select_from(user_table)
...     .join(address_table)
... )
>>> with engine.connect() as conn:  
...     result = conn.execute(stmt)
...     print(result.all())
BEGIN (implicit)
SELECT count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_1,
count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_2
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ('sandy', 'spongebob')
[(2, 1)]
ROLLBACK

>>> onetwothree = func.json_each('["one", "two", "three"]').table_valued("value")
>>> stmt = select(onetwothree).where(onetwothree.c.value.in_(["two", "three"]))
>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     result.all()
BEGIN (implicit)
SELECT anon_1.value
FROM json_each(?) AS anon_1
WHERE anon_1.value IN (?, ?)
[...] ('["one", "two", "three"]', 'two', 'three')
[('two',), ('three',)]
ROLLBACK

>>> from sqlalchemy import select, func
>>> stmt = select(func.json_array_elements('["one", "two"]').column_valued("x"))
>>> print(stmt)
SELECT x
FROM json_array_elements(:json_array_elements_1) AS x

>>> from sqlalchemy.dialects import oracle
>>> stmt = select(func.scalar_strings(5).column_valued("s"))
>>> print(stmt.compile(dialect=oracle.dialect()))
SELECT COLUMN_VALUE s
FROM TABLE (scalar_strings(:scalar_strings_1)) s

type_coerce() - «приведение» только в Python¶

Иногда возникает необходимость, чтобы SQLAlchemy знал тип данных выражения по всем вышеупомянутым причинам, но не отображал само выражение CAST на стороне SQL, где оно может помешать SQL-операции, которая уже работает без него. Для этого довольно распространенного случая использования существует еще одна функция type_coerce(), которая тесно связана с cast(), в том смысле, что она устанавливает выражение Python как имеющее определенный тип базы данных SQL, но не отображает ключевое слово CAST или тип данных на стороне базы данных. type_coerce() особенно важно при работе с типом данных JSON, который обычно имеет сложную связь со строково-ориентированными типами данных на разных платформах и может даже не быть явным типом данных, как, например, в SQLite и MariaDB. Ниже мы используем type_coerce() для передачи структуры Python в виде строки JSON в одну из функций MySQL JSON:

>>> import json
>>> from sqlalchemy import JSON
>>> from sqlalchemy import type_coerce
>>> from sqlalchemy.dialects import mysql
>>> s = select(type_coerce({"some_key": {"foo": "bar"}}, JSON)["some_key"])
>>> print(s.compile(dialect=mysql.dialect()))
SELECT JSON_EXTRACT(%s, %s) AS anon_1

Выше SQL-функция MySQL JSON_EXTRACT была вызвана, поскольку мы использовали type_coerce(), чтобы указать, что наш словарь Python должен рассматриваться как JSON. В результате оператор Python __getitem__, ['some_key'] в данном случае, стал доступен и позволил отобразить выражение пути JSON_EXTRACT (не показано, однако в данном случае оно в конечном итоге будет '$."some_key"').