Использование операторов SELECT

Select() Connection.execute() Session.execute() Result o

:ref:`queryguide_toplevel`*

В результате, в результате

Конструкция <<<0>> insert() generative >

>>> from sqlalchemy import select
>>> stmt = select(user_table).where(user_table.c.name == "spongebob")
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1

:class:`_engine.Row`Al

>>> with engine.connect() as conn:
...     for row in conn.execute(stmt):
...         print(row)
{execsql}BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',){stop}
(1, 'spongebob', 'Spongebob Squarepants')
{execsql}ROLLBACK{stop}

execute() Session Row User а

>>> stmt = select(User).where(User.name == "spongebob")
>>> with Session(engine) as session:
...     for row in session.execute(stmt):
...         print(row)
{execsql}BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',){stop}
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
{execsql}ROLLBACK{stop}

В результате, по сравнению с предыдущими годами, были получены следующие результаты

Установить

В качестве источника питания используется select() f Column Table u

>>> print(select(user_table))
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account

c Table FromClause а

>>> print(select(user_table.c.name, user_table.c.fullname))
{printsql}SELECT user_account.name, user_account.fullname
FROM user_account

FromClause.c`A :class:.FromClause` Table select() l

>>> print(select(user_table.c["name", "fullname"]))
{printsql}SELECT user_account.name, user_account.fullname
FROM user_account

Добавлено в версии 2.0: Добавлено t

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

User``ИЛ ``User.name User user_table И

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

Session.execute`Когд ``User`() user_table User Row User а

>>> row = session.execute(select(User)).first()
{execsql}BEGIN...
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] (){stop}
>>> 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 A

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

Column() l

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

:meth:`_orm.Session.execute`Когда w

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

name``В качестве ``User примера Address можно привести а

>>> session.execute(
...     select(User.name, Address).where(User.id == Address.user_id).order_by(Address.id)
... ).all()
{execsql}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
[...] (){stop}
[('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'))]

:ref:`orm_queryguide_select_columns`Приложение

См.также

Выбор сущностей и атрибутов ORM - Руководство по составлению запросов в ORM в

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

Встретились ColumnElement.label()

>>> 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}")
{execsql}BEGIN (implicit)
SELECT ? || user_account.name AS username
FROM user_account ORDER BY user_account.name
[...] ('Username: ',){stop}
Username: patrick
Username: sandy
Username: spongebob
{execsql}ROLLBACK{stop}

См.также

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

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

select Table Column table metadata w

Конструкция <<<0>> Работа с транзакциями и DBAPI Select 'some phrase' >

>>> 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())
{execsql}BEGIN (implicit)
SELECT 'some phrase', user_account.name
FROM user_account ORDER BY user_account.name
[generated in ...] ()
{stop}[('some phrase', 'patrick'), ('some phrase', 'sandy'), ('some phrase', 'spongebob')]
{execsql}ROLLBACK{stop}

literal_column() text() i

>>> 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}")
{execsql}BEGIN (implicit)
SELECT 'some phrase' AS p, user_account.name
FROM user_account ORDER BY user_account.name
[generated in ...] ()
{stop}some phrase, patrick
some phrase, sandy
some phrase, spongebob
{execsql}ROLLBACK{stop}

literal_column() е

Предложение WHERE

name = 'squidward'``S ``user_id > 10 Column == != < >= True False Q

>>> print(user_table.c.name == "squidward")
user_account.name = :name_1

>>> print(address_table.c.user_id > 10)
address.user_id > :user_id_1

:meth:`_sql.Select.where`Мы c

>>> print(select(user_table).where(user_table.c.name == "squidward"))
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1

:meth:`_sql.Select.where`На

>>> print(
...     select(address_table.c.email_address)
...     .where(user_table.c.name == "squidward")
...     .where(address_table.c.user_id == user_table.c.id)
... )
{printsql}SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id

:meth:`_sql.Select.where`A

>>> print(
...     select(address_table.c.email_address).where(
...         user_table.c.name == "squidward",
...         address_table.c.user_id == user_table.c.id,
...     )
... )
{printsql}SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id

and_() or_() A

>>> from sqlalchemy import and_, or_
>>> print(
...     select(Address.email_address).where(
...         and_(
...             or_(User.name == "squidward", User.name == "sandy"),
...             Address.user_id == User.id,
...         )
...     )
... )
{printsql}SELECT address.email_address
FROM address, user_account
WHERE (user_account.name = :name_1 OR user_account.name = :name_2)
AND address.user_id = user_account.id

:meth:`_sql.Select.filter_by`Fo

>>> print(select(User).filter_by(name="spongebob", fullname="Spongebob Squarepants"))
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1 AND user_account.fullname = :fullname_1

См.также

Справочник оператора -

Явные предложения FROM и JOIN

:class:`_sql.Select`Как

Table Table I

>>> print(select(user_table.c.name))
{printsql}SELECT user_account.name
FROM user_account

Если мы поместим столбцы из двух таблиц, то получим предложение FROM, разделенное запятыми:

>>> print(select(user_table.c.name, address_table.c.email_address))
{printsql}SELECT user_account.name, address.email_address
FROM user_account, address

Select Select.join_from() В

>>> print(
...     select(user_table.c.name, address_table.c.email_address).join_from(
...         user_table, address_table
...     )
... )
{printsql}SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

:meth:`_sql.Select.join`В качестве примера можно привести

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

Select.select_from`М ``user_table`() Select.join() address_table ы

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

Select.select_from`An ``count(*)`() sqlalchemy.sql.expression.func count() o

>>> from sqlalchemy import func
>>> print(select(func.count("*")).select_from(user_table))
{printsql}SELECT count(:count_2) AS count_1
FROM user_account

См.также

Установка крайнего левого предложения FROM в объединении - Руководство по составлению запросов в ORM Select.select_from() Select.join() в

Установить

Select`Предварительны ``user_table` address_table Table ForeignKeyConstraint й

Select.join() Select.join_from() Предложение WHERE I

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

relationship() Объявление сопоставленных классов Использование отношений для присоединения *

Внешнее и полное соединение

Select.join() Select.join_from() Select.join.isouter Select.join.full O

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

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

Select.outerjoin`The `().join(…, isouter=True)`` r

Совет

В SQL также существует «RIGHT OUTER JOIN». SQLAlchemy не позволяет сделать это напрямую; вместо этого следует изменить порядок таблиц и использовать «LEFT OUTER JOIN».

ПОРЯДОК ПО, ГРУППА ПО, НАЛИЧИЕ

SQL-оператор SELECT содержит предложение ORDER BY, которое используется для возврата выбранных строк в заданном порядке.

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

ПОРЯДОК СЛЕДОВАНИЯ

order_by я

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

ColumnElement.desc() к

>>> print(select(User).order_by(User.fullname.desc()))
{printsql}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 агрегатные функции позволяют агрегировать выражения столбцов в нескольких строках для получения единого результата. Примерами могут служить подсчеты, вычисление средних значений, а также нахождение максимального или минимального значения в наборе значений.

Function user_account.id count() Q

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

:ref:`tutorial_functions`SQ

Когда

Select.having() Q

>>> 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())
{execsql}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,){stop}
[('sandy', 2)]
{execsql}ROLLBACK{stop}

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

Select.group_by() 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)
{printsql}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

Использование псевдонимов

Теперь, когда мы выбираем из нескольких таблиц и используем объединения, мы быстро сталкиваемся со случаем, когда нам нужно несколько раз обратиться к одной и той же таблице в предложении FROM оператора. Для этого в SQL используются aliases, которые представляют собой синтаксис, задающий альтернативное имя таблицы или подзапроса, по которому на нее можно ссылаться в операторе.

Alias FromClause.alias() Alias Table Column Alias.c .

>>> user_alias_1 = user_table.alias()
>>> user_alias_2 = user_table.alias()
>>> print(
...     select(user_alias_1.c.name, user_alias_2.c.name).join_from(
...         user_alias_1, user_alias_2, user_alias_1.c.id > user_alias_2.c.id
...     )
... )
{printsql}SELECT user_account_1.name, user_account_2.name AS name_1
FROM user_account AS user_account_1
JOIN user_account AS user_account_2 ON user_account_1.id > user_account_2.id

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

alias`Операционна :func:`_orm.aliased() User Address Alias Table 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")
... )
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
WHERE address_1.email_address = :email_address_1
AND address_2.email_address = :email_address_2

Совет

tutorial_select_join_onclause`Ка :func:`_orm.relationship relationship() Использование Relationship для объединения смежных целей к

Подзапросы и CTE

Подзапрос в SQL - это оператор SELECT, заключенный в круглые скобки и помещенный в контекст вложенного оператора, как правило, оператора SELECT, но не обязательно.

В этом разделе рассматривается так называемый «нескалярный» подзапрос, который обычно помещается в предложение FROM вложенного SELECT. Также будет рассмотрено общее табличное выражение (Common Table Expression или CTE), которое используется аналогично подзапросу, но имеет дополнительные возможности.

CTE Select.subquery() Select.cte() select() Q

Subquery`Мы  ``address` Агрегатные функции с GROUP BY / HAVING c

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

:class:`_sql.Select`S

>>> print(subq)
{printsql}SELECT count(address.id) AS count, address.user_id
FROM address GROUP BY address.user_id

В качестве параметра Subquery выступае Table Subquery.c user_id count т

>>> print(select(subq.c.user_id, subq.c.count))
{printsql}SELECT anon_1.user_id, anon_1.count
FROM (SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id) AS anon_1

subq Select user_account W

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

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

user_account address Select.join_from() subq.c.user_id address_table.c.user_id user_table.c.id В

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

CTE Subquery Select.subquery() Select.cte() U

>>> 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)
{printsql}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

Конструкция <<<0>> CTE >

Subquery CTE Select.subquery() Select.cte() В

См.также

Select.subquery() -

Select.cte() -

ORM Entity Subqueries/CTEs

aliased`В т ``User`() Address FromClause Псевдонимы сущностей в ORM aliased() Alias Table aliased() Subquery CTE Select Table .

Subquery() 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}")
{execsql}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',){stop}
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')
{execsql}ROLLBACK{stop}

:class:`_sql.CTE`Ano

>>> 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}")
{execsql}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',){stop}
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')
{execsql}ROLLBACK{stop}

См.также

Выбор сущностей из подзапросов - Руководство по составлению запросов в ORM в

Скалярные и коррелированные подзапросы

:term:`correlated subquery`A

ColumnElement Subquery FromClause Q

Агрегатные функции с GROUP BY / HAVING Select.scalar_subquery() S

>>> subq = (
...     select(func.count(address_table.c.id))
...     .where(user_table.c.id == address_table.c.user_id)
...     .scalar_subquery()
... )
>>> print(subq)
{printsql}(SELECT count(address.id) AS count_1
FROM address, user_account
WHERE user_account.id = address.user_id)

``subq``В качестве ColumnElement примера можно привести

>>> print(subq == 5)
{printsql}(SELECT count(address.id) AS count_1
FROM address, user_account
WHERE user_account.id = address.user_id) = :param_1

user_account``A ``address select() user_account user_account l

>>> stmt = select(user_table.c.name, subq.label("address_count"))
>>> print(stmt)
{printsql}SELECT user_account.name, (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id) AS address_count
FROM user_account

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

>>> stmt = (
...     select(
...         user_table.c.name,
...         address_table.c.email_address,
...         subq.label("address_count"),
...     )
...     .join_from(user_table, address_table)
...     .order_by(user_table.c.id, address_table.c.id)
... )
>>> print(stmt)
Traceback (most recent call last):
...
InvalidRequestError: Select statement '<... Select object at ...>' returned
no FROM clauses due to auto-correlation; specify correlate(<tables>) to
control correlation manually.

``user_table``К ScalarSelect.correlate() ScalarSelect.correlate_except() с

>>> subq = (
...     select(func.count(address_table.c.id))
...     .where(user_table.c.id == address_table.c.user_id)
...     .scalar_subquery()
...     .correlate(user_table)
... )

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

>>> with engine.connect() as conn:
...     result = conn.execute(
...         select(
...             user_table.c.name,
...             address_table.c.email_address,
...             subq.label("address_count"),
...         )
...         .join_from(user_table, address_table)
...         .order_by(user_table.c.id, address_table.c.id)
...     )
...     print(result.all())
{execsql}BEGIN (implicit)
SELECT user_account.name, address.email_address, (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id) AS address_count
FROM user_account JOIN address ON user_account.id = address.user_id ORDER BY user_account.id, address.id
[...] (){stop}
[('spongebob', 'spongebob@sqlalchemy.org', 1), ('sandy', 'sandy@sqlalchemy.org', 2),
 ('sandy', 'sandy@squirrelpower.org', 2)]
{execsql}ROLLBACK{stop}

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

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

``table1 JOIN (SELECT …) AS subquery``Нет

Select.lateral`S :class:().Lateral` Lateral Subquery Alias Q

>>> 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)
{printsql}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

``user_account``Ab

Select.correlate() Select.correlate_except() Lateral а

См.также

Lateral

Select.lateral()

UNION, UNION ALL и другие операции над множествами

В SQL операторы SELECT могут быть объединены с помощью SQL-операции UNION или UNION ALL, которая позволяет получить набор всех строк, созданных одним или несколькими операторами вместе. Возможны и другие операции над множествами, такие как INTERSECT [ALL] и EXCEPT [ALL].

union intersect() except_() union_all() intersect_all() except_all() Select Q

CompoundSelect Select CompoundSelect union_all() Connection.execute() C

>>> from sqlalchemy import union_all
>>> stmt1 = select(user_table).where(user_table.c.name == "sandy")
>>> stmt2 = select(user_table).where(user_table.c.name == "spongebob")
>>> u = union_all(stmt1, stmt2)
>>> with engine.connect() as conn:
...     result = conn.execute(u)
...     print(result.all())
{execsql}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')
{stop}[(2, 'sandy', 'Sandy Cheeks'), (1, 'spongebob', 'Spongebob Squarepants')]
{execsql}ROLLBACK{stop}

Select SelectBase.subquery() Subquery FromClause.c select() u

>>> u_subq = u.subquery()
>>> stmt = (
...     select(u_subq.c.name, address_table.c.email_address)
...     .join_from(address_table, u_subq)
...     .order_by(u_subq.c.name, address_table.c.email_address)
... )
>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     print(result.all())
{execsql}BEGIN (implicit)
SELECT anon_1.name, address.email_address
FROM address JOIN
  (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 ON anon_1.id = address.user_id
ORDER BY anon_1.name, address.email_address
[generated in ...] ('sandy', 'spongebob')
{stop}[('sandy', 'sandy@sqlalchemy.org'), ('sandy', 'sandy@squirrelpower.org'), ('spongebob', 'spongebob@sqlalchemy.org')]
{execsql}ROLLBACK{stop}

Выбор сущностей ORM из объединений

select CompoundSelect Table й

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

Select.from_statement() o

>>> orm_stmt = select(User).from_statement(u)
>>> with Session(engine) as session:
...     for obj in session.execute(orm_stmt).scalars():
...         print(obj)
{execsql}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')
{stop}User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
{execsql}ROLLBACK{stop}

subquery aliased() ORM Entity Subqueries/CTEs u

>>> 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)
{execsql}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')
{stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
{execsql}ROLLBACK{stop}

См.также

Выбор сущностей из UNION и других операций над множествами - Руководство по составлению запросов в ORM в

подзапросы EXISTS

scalar subqueries ScalarSelect Exists SelectBase.exists() user_account address S

>>> subq = (
...     select(func.count(address_table.c.id))
...     .where(user_table.c.id == address_table.c.user_id)
...     .group_by(address_table.c.user_id)
...     .having(func.count(address_table.c.id) > 1)
... ).exists()
>>> with engine.connect() as conn:
...     result = conn.execute(select(user_table.c.name).where(subq))
...     print(result.all())
{execsql}BEGIN (implicit)
SELECT user_account.name
FROM user_account
WHERE EXISTS (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id GROUP BY address.user_id
HAVING count(address.id) > ?)
[...] (1,){stop}
[('sandy',)]
{execsql}ROLLBACK{stop}

``~``Сайт

>>> subq = (
...     select(address_table.c.id).where(user_table.c.id == address_table.c.user_id)
... ).exists()
>>> with engine.connect() as conn:
...     result = conn.execute(select(user_table.c.name).where(~subq))
...     print(result.all())
{execsql}BEGIN (implicit)
SELECT user_account.name
FROM user_account
WHERE NOT (EXISTS (SELECT address.id
FROM address
WHERE user_account.id = address.user_id))
[...] (){stop}
[('patrick',)]
{execsql}ROLLBACK{stop}

Работа с функциями SQL

tutorial_group_by_w_aggregates`F :data:`_sql.func Function select() i

  • ``count()``Fi

    >>> print(select(func.count()).select_from(user_table))
{printsql}SELECT count(*) AS count_1
FROM user_account

  • ``lower()``Fi

    >>> print(select(func.lower("A String With Much UPPERCASE")))
{printsql}SELECT lower(:lower_2) AS lower_1

  • ``now()``Fi

    >>> stmt = select(func.now())
>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     print(result.all())
{execsql}BEGIN (implicit)
SELECT CURRENT_TIMESTAMP AS now_1
[...] ()
[(datetime.datetime(...),)]
ROLLBACK

:data:`_sql.func`Как

>>> print(select(func.some_crazy_function(user_table.c.name, 17)))
{printsql}SELECT some_crazy_function(user_account.name, :some_crazy_function_2) AS some_crazy_function_1
FROM user_account

now max concat now е

>>> from sqlalchemy.dialects import postgresql
>>> print(select(func.now()).compile(dialect=postgresql.dialect()))
{printsql}SELECT now() AS now_1{stop}
>>> from sqlalchemy.dialects import oracle
>>> print(select(func.now()).compile(dialect=oracle.dialect()))
{printsql}SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL{stop}

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

:ref:`datatypes <types_toplevel>`Как

:attr:`_functions.Function.type`S

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

Numeric JSON table valued functions s

DateTime datetime() S

Function.type_`Н :class:`_types.TypeEngine JSON json_object() а

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

:class:`_types.JSON`На

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

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

max min now concat max o

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

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

Date Time a

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

concat String A

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

func.lower() func.upper() A

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

upper``F ``lower + o

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

:paramref:`_functions.Function.type_`Ov

  1. :attr:`_functions.Function.type`Ov

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

    Ov

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

  2. ARRAY u

  3. Boolean Enum JSON ARRAY s

Объявление

В результате, по сравнению с предыдущими годами, были получены следующие результаты

Использование оконных функций

``MAX()``A w

В SQL оконные функции позволяют указать строки, над которыми должна быть применена функция, значение «partition», которое рассматривает окно над различными подмножествами строк, и выражение «order by», которое указывает порядок, в котором строки должны быть применены к агрегатной функции.

func FunctionElement.over() Over В

``row_number()``A c

>>> 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())
{execsql}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
[...] ()
{stop}[(1, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (1, 'spongebob', 'spongebob@sqlalchemy.org')]
{printsql}ROLLBACK{stop}

FunctionElement.over.partition_by`A ``PARTITION BY` ORDER BY FunctionElement.over.order_by b

>>> 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())
{execsql}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
[...] ()
{stop}[(2, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (3, 'spongebob', 'spongebob@sqlalchemy.org')]
{printsql}ROLLBACK{stop}

:func:`_expression.over`Fu

Совет

over`Эт :class:`_sql.Over() о

Специальные модификаторы WITHIN GROUP, FILTER

percentile_cont()``В результате, ``rank() по мнению rank экспертов, dense_rank «в mode percentile_cont percentile_disc FunctionElement.within_group() «

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

:meth:`_functions.FunctionElement.filter`В результате, по мнению экспертов, «в»

>>> 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())
{execsql}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')
{stop}[(2, 1)]
{execsql}ROLLBACK

Функции с табличными значениями

``generate_series()``T

См.также

Табличные значения, функции, оценивающие таблицы и столбцы, объекты Row и Tuple - PostgreSQL в

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

table_valued`S :data:`_sql.func() TableValuedAlias Alias Использование псевдонимов json_each() Q

>>> 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()
{execsql}BEGIN (implicit)
SELECT anon_1.value
FROM json_each(?) AS anon_1
WHERE anon_1.value IN (?, ?)
[...] ('["one", "two", "three"]', 'two', 'three')
{stop}[('two',), ('three',)]
{execsql}ROLLBACK{stop}

json_each()``A ``value b

См.также

Функции с табличными значениями - PostgreSQL в

Функции, оцениваемые по столбцам - Функция, оцениваемая по таблице, как скалярный столбец

json_array_elements() json_object_keys() json_each_text() json_each() A

column_valued`S :class:`_functions.Function() Q

>>> from sqlalchemy import select, func
>>> stmt = select(func.json_array_elements('["one", "two"]').column_valued("x"))
>>> print(stmt)
{printsql}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()))
{printsql}SELECT s.COLUMN_VALUE
FROM TABLE (scalar_strings(:scalar_strings_1)) s

См.также

Функции, оцениваемые по столбцам - PostgreSQL в

Приведение данных и принуждение к типу

cast() CAST(user_account.id AS VARCHAR) user_table.c.id В

>>> from sqlalchemy import cast
>>> stmt = select(cast(user_table.c.id, String))
>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     result.all()
{execsql}BEGIN (implicit)
SELECT CAST(user_account.id AS VARCHAR) AS id
FROM user_account
[...] ()
{stop}[('1',), ('2',), ('3',)]
{execsql}ROLLBACK{stop}

В качестве источника питания используется cast() f cast() JSON u

>>> from sqlalchemy import JSON
>>> print(cast("{'a': 'b'}", JSON)["a"])
{printsql}CAST(:param_1 AS JSON)[:param_2]

В качестве источника питания используется fu

type_coerce`Некоторы :func:().cast` CAST type_coerce() JSON type_coerce() е

>>> 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()))
{printsql}SELECT JSON_EXTRACT(%s, %s) AS anon_1

JSON_EXTRACT``Некоторы :func:`.type_coerce`  :class:`_types.JSON`  ``__getitem__ ['some_key'] JSON_EXTRACT '$."some_key"' е

Использование операторов INSERT
Использование операторов UPDATE и DELETE
Вернуться на верх