Учебник по языку выражений SQL (API 1.x)¶

Проверка версии¶

Быстрая проверка, чтобы убедиться, что мы находимся как минимум на версии 1.4 SQLAlchemy:

>>> import sqlalchemy
>>> sqlalchemy.__version__  
1.4.0

Подключение¶

В этом учебнике мы будем использовать базу данных SQLite, хранящуюся только в памяти. Это простой способ тестирования без необходимости иметь где-либо определенную базу данных. Для подключения мы используем create_engine():

>>> from sqlalchemy import create_engine
>>> engine = create_engine("sqlite:///:memory:", echo=True)

Флаг echo - это короткий путь к настройке логирования SQLAlchemy, которое осуществляется с помощью стандартного модуля Python logging. Если флаг включен, мы увидим весь сгенерированный SQL. Если вы работаете над этим учебником и хотите получить меньше результатов, установите значение False. В этом учебнике SQL будет отформатирован за всплывающим окном, чтобы он не мешал нам; просто щелкните по ссылкам «SQL», чтобы увидеть, что генерируется.

Возвращаемое значение create_engine() является экземпляром Engine, и он представляет собой основной интерфейс к базе данных, адаптированный через dialect, который обрабатывает детали базы данных и DBAPI в использовании. В данном случае диалект SQLite будет интерпретировать инструкции к встроенному в Python модулю sqlite3.

При первом вызове такого метода, как Engine.execute() или Engine.connect(), Engine устанавливает реальное DBAPI соединение с базой данных, которое затем используется для выдачи SQL.

Определение и создание таблиц¶

Язык выражений SQL в большинстве случаев строит свои выражения на основе столбцов таблицы. В SQLAlchemy столбец чаще всего представлен объектом Column, и во всех случаях Column связан с Table. Коллекция объектов Table и связанных с ними дочерних объектов называется метаданными базы данных. В этом учебнике мы явно разложим несколько объектов Table, но обратите внимание, что SA также может «импортировать» целые наборы объектов Table автоматически из существующей базы данных (этот процесс называется отражение таблицы).

Мы определяем наши таблицы в каталоге под названием MetaData, используя конструкцию Table, которая напоминает обычные операторы SQL CREATE TABLE. Мы создадим две таблицы, одна из которых представляет «пользователей» в приложении, а другая - ноль или более «адресов электронной почты» для каждой строки в таблице «пользователи»:

>>> from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
>>> metadata_obj = MetaData()
>>> users = Table(
...     "users",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("name", String),
...     Column("fullname", String),
... )

>>> addresses = Table(
...     "addresses",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("user_id", None, ForeignKey("users.id")),
...     Column("email_address", String, nullable=False),
... )

Все о том, как определять объекты Table, а также как автоматически создавать их из существующей базы данных, описано в Описание баз данных с помощью метаданных.

Далее, чтобы сообщить MetaData, что мы действительно хотим создать наш выбор таблиц в базе данных SQLite, мы используем create_all(), передавая ему экземпляр engine, который указывает на нашу базу данных. Это позволит сначала проверить наличие каждой таблицы перед созданием, поэтому можно вызывать несколько раз:

sql>>> metadata_obj.create_all(engine)
BEGIN...
CREATE TABLE users (
    id INTEGER NOT NULL,
    name VARCHAR,
    fullname VARCHAR,
    PRIMARY KEY (id)
)
[...] ()
CREATE TABLE addresses (
    id INTEGER NOT NULL,
    user_id INTEGER,
    email_address VARCHAR NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(user_id) REFERENCES users (id)
)
[...] ()
COMMIT

Column("name", String(50))

from sqlalchemy import Sequence

Column("id", Integer, Sequence("user_id_seq"), primary_key=True)

users = Table(
    "users",
    metadata_obj,
    Column("id", Integer, Sequence("user_id_seq"), primary_key=True),
    Column("name", String(50)),
    Column("fullname", String(50)),
    Column("nickname", String(50)),
)

Вставить выражения¶

Первым выражением SQL, которое мы создадим, будет конструкция Insert, которая представляет собой оператор INSERT. Обычно оно создается относительно целевой таблицы:

>>> ins = users.insert()

Чтобы увидеть пример SQL, который получается при использовании этой конструкции, используйте функцию str():

>>> str(ins)
'INSERT INTO users (id, name, fullname) VALUES (:id, :name, :fullname)'

Обратите внимание, что оператор INSERT называет каждый столбец в таблице users. Это можно ограничить, используя метод values(), который устанавливает пункт VALUES оператора INSERT в явном виде:

>>> ins = users.insert().values(name="jack", fullname="Jack Jones")
>>> str(ins)
'INSERT INTO users (name, fullname) VALUES (:name, :fullname)'

Выше, хотя метод values ограничил предложение VALUES только двумя столбцами, фактические данные, которые мы поместили в values, не были выведены в строку; вместо этого мы получили именованные параметры связывания. Как оказалось, наши данные хранятся в нашей конструкции Insert, но обычно они появляются только при выполнении оператора; поскольку данные состоят из буквальных значений, SQLAlchemy автоматически генерирует для них параметры связывания. Пока что мы можем посмотреть на эти данные, взглянув на скомпилированную форму оператора:

>>> ins.compile().params  
{'fullname': 'Jack Jones', 'name': 'jack'}

Выполнение¶

Интересной частью Insert является его выполнение. Для этого используется соединение с базой данных, которое представлено объектом Connection. Для получения соединения мы будем использовать метод Engine.connect():

>>> conn = engine.connect()
>>> conn
<sqlalchemy.engine.base.Connection object at 0x...>

Объект Connection представляет собой активно проверяемый ресурс соединения DBAPI. Давайте передадим ему наш объект Insert и посмотрим, что произойдет:

>>> result = conn.execute(ins)
INSERT INTO users (name, fullname) VALUES (?, ?)
[...] ('jack', 'Jack Jones')
COMMIT

Теперь оператор INSERT был передан в базу данных. Хотя на выходе мы получили позиционные параметры привязки «qmark» вместо «именованных» параметров привязки. Как так получилось? Потому что при выполнении Connection использовался диалект SQLite, чтобы помочь сгенерировать оператор; когда мы используем функцию str(), оператор не знает об этом диалекте и возвращается к умолчанию, которое использует именованные параметры. Мы можем просмотреть это вручную следующим образом:

>>> ins.bind = engine
>>> str(ins)
'INSERT INTO users (name, fullname) VALUES (?, ?)'

А как насчет переменной result, которую мы получили при вызове execute()? Поскольку объект SQLAlchemy Connection ссылается на соединение DBAPI, результат, известный как объект CursorResult, является аналогом объекта курсора DBAPI. В случае INSERT мы можем получить из него важную информацию, например, значения первичных ключей, которые были сгенерированы из нашего оператора с помощью CursorResult.inserted_primary_key:

>>> result.inserted_primary_key
(1,)

Значение 1 было автоматически сгенерировано SQLite, но только потому, что мы не указали столбец id в нашем операторе Insert; в противном случае было бы использовано наше явное значение. В любом случае, SQLAlchemy всегда знает, как получить новое значение первичного ключа, даже если метод их генерации различен в разных базах данных; Dialect каждой базы данных знает конкретные шаги, необходимые для определения правильного значения (или значений; обратите внимание, что CursorResult.inserted_primary_key возвращает список, так что он поддерживает составные первичные ключи). Методы здесь варьируются от использования cursor.lastrowid, выбора из функции, специфичной для базы данных, до использования синтаксиса INSERT..RETURNING; все это происходит прозрачно.

Выполнение нескольких утверждений¶

Наш пример вставки, приведенный выше, был намеренно немного затянут, чтобы показать некоторые различные варианты поведения конструкций языка выражения. В обычном случае оператор Insert обычно компилируется с параметрами, передаваемыми методу execute() на Connection, так что нет необходимости использовать ключевое слово values с Insert. Давайте снова создадим общий оператор Insert и используем его «обычным» способом:

>>> ins = users.insert()
>>> conn.execute(ins, {"id": 2, "name": "wendy", "fullname": "Wendy Williams"})
INSERT INTO users (id, name, fullname) VALUES (?, ?, ?)
[...] (2, 'wendy', 'Wendy Williams')
COMMIT
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x...>

Выше, поскольку мы указали все три столбца в методе execute(), скомпилированный Insert включает все три столбца. Оператор Insert компилируется во время выполнения на основе параметров, которые мы указали; если бы мы указали меньше параметров, Insert имел бы меньше записей в предложении VALUES.

Чтобы выполнить множество вставок с помощью метода DBAPI executemany(), мы можем послать список словарей, каждый из которых содержит отдельный набор параметров для вставки, как мы делаем здесь, чтобы добавить несколько адресов электронной почты:

>>> conn.execute(
...     addresses.insert(),
...     [
...         {"user_id": 1, "email_address": "jack@yahoo.com"},
...         {"user_id": 1, "email_address": "jack@msn.com"},
...         {"user_id": 2, "email_address": "www@www.org"},
...         {"user_id": 2, "email_address": "wendy@aol.com"},
...     ],
... )
INSERT INTO addresses (user_id, email_address) VALUES (?, ?)
[...] ((1, 'jack@yahoo.com'), (1, 'jack@msn.com'), (2, 'www@www.org'), (2, 'wendy@aol.com'))
COMMIT
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x...>

Выше мы снова полагались на автоматическую генерацию SQLite идентификаторов первичных ключей для каждой строки addresses.

При выполнении нескольких наборов параметров, каждый словарь должен иметь одинаковый набор ключей; т.е. вы не можете иметь меньше ключей в одних словарях, чем в других. Это происходит потому, что оператор Insert компилируется против первого словаря в списке, и предполагается, что все последующие словари аргументов совместимы с этим оператором.

Стиль вызова «executemany» доступен для каждой из конструкций insert(), update() и delete().

Выбор¶

Мы начали со вставок, просто чтобы в нашей тестовой базе данных были какие-то данные. Более интересной частью данных является их выборка! Мы рассмотрим операторы UPDATE и DELETE позже. Основной конструкцией, используемой для создания операторов SELECT, является функция select():

>>> from sqlalchemy.sql import select
>>> s = select(users)
>>> result = conn.execute(s)
SELECT users.id, users.name, users.fullname
FROM users
[...] ()

Выше мы выполнили базовый вызов select(), поместив таблицу users в предложение COLUMNS селекта, а затем выполнив его. SQLAlchemy развернула таблицу users в набор всех ее столбцов, а также сгенерировала для нас предложение FROM.

Возвращаемым результатом снова является объект CursorResult, который действует подобно курсору DBAPI, включая такие методы, как fetchone() и fetchall(). Эти методы возвращают объекты строк, которые предоставляются через класс Row. Объект результата можно итерировать напрямую, чтобы получить итератор объектов Row:

>>> for row in result:
...     print(row)
(1, u'jack', u'Jack Jones')
(2, u'wendy', u'Wendy Williams')

Выше мы видим, что печать каждого Row дает простой кортеж. Наиболее канонический способ в Python получить доступ к значениям этих кортежей по мере получения строк - это присвоение кортежей:

sql>>> result = conn.execute(s)
SELECT users.id, users.name, users.fullname
FROM users
[...] ()
>>> for id, name, fullname in result:
...     print("name:", name, "; fullname: ", fullname)
name: jack ; fullname:  Jack Jones
name: wendy ; fullname:  Wendy Williams

Объект Row фактически ведет себя как именованный кортеж в Python, поэтому мы также можем получить доступ к этим атрибутам из самой строки, используя доступ к атрибутам:

sql>>> result = conn.execute(s)
SELECT users.id, users.name, users.fullname
FROM users
[...] ()
>>> for row in result:
...     print("name:", row.name, "; fullname: ", row.fullname)
name: jack ; fullname:  Jack Jones
name: wendy ; fullname:  Wendy Williams

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

sql>>> result = conn.execute(s)
SELECT users.id, users.name, users.fullname
FROM users
[...] ()
>>> row = result.fetchone()
>>> print("name:", row._mapping["name"], "; fullname:", row._mapping["fullname"])
name: jack ; fullname: Jack Jones

row = result.fetchone()
name, fullname = row["name"], row["fullname"]

Поскольку Row является кортежем, можно также использовать доступ к последовательности (т.е. целому числу или срезу):

>>> row = result.fetchone()
>>> print("name:", row[1], "; fullname:", row[2])
name: wendy ; fullname: Wendy Williams

Более специализированный метод доступа к столбцам заключается в использовании конструкции SQL, которая непосредственно соответствует определенному столбцу, в качестве ключа сопоставления; в данном примере это означает, что мы будем использовать объекты Column, выбранные в нашем SELECT, непосредственно в качестве ключей в сочетании с коллекцией Row._mapping:

sql>>> for row in conn.execute(s):
...     print(
...         "name:",
...         row._mapping[users.c.name],
...         "; fullname:",
...         row._mapping[users.c.fullname],
...     )
SELECT users.id, users.name, users.fullname
FROM users
[...] ()
name: jack ; fullname: Jack Jones
name: wendy ; fullname: Wendy Williams

Объект CursorResult имеет функцию «автозакрытия», которая закрывает базовый объект DBAPI cursor, когда все ожидающие строки результата были получены. Если объект CursorResult должен быть отброшен до того, как произойдет такое автозакрытие, его можно явно закрыть с помощью метода CursorResult.close():

>>> result.close()

Выбор конкретных столбцов¶

Если мы хотим более тщательно контролировать столбцы, которые помещаются в предложение COLUMNS в select, мы ссылаемся на отдельные объекты Column из нашего Table. Они доступны в виде именованных атрибутов атрибута c объекта Table:

>>> s = select(users.c.name, users.c.fullname)
sql>>> result = conn.execute(s)
SELECT users.name, users.fullname
FROM users
[...] ()
>>> for row in result:
...     print(row)
(u'jack', u'Jack Jones')
(u'wendy', u'Wendy Williams')

Заметим кое-что интересное в предложении FROM. В то время как сгенерированный оператор содержит два отдельных раздела, часть «SELECT columns» и часть «FROM table», наша конструкция select() имеет только список, содержащий столбцы. Как это работает? Давайте попробуем поместить две таблицы в наш оператор select():

sql>>> for row in conn.execute(select(users, addresses)):
...     print(row)
SELECT users.id, users.name, users.fullname, addresses.id AS id_1, addresses.user_id, addresses.email_address
FROM users, addresses
[...] ()
(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com')
(1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')
(1, u'jack', u'Jack Jones', 3, 2, u'www@www.org')
(1, u'jack', u'Jack Jones', 4, 2, u'wendy@aol.com')
(2, u'wendy', u'Wendy Williams', 1, 1, u'jack@yahoo.com')
(2, u'wendy', u'Wendy Williams', 2, 1, u'jack@msn.com')
(2, u'wendy', u'Wendy Williams', 3, 2, u'www@www.org')
(2, u'wendy', u'Wendy Williams', 4, 2, u'wendy@aol.com')

Он поместил обе таблицы в предложение FROM. Но при этом получился настоящий беспорядок. Те, кто знаком с SQL-соединениями, знают, что это картезианское произведение; каждая строка из таблицы users производится против каждой строки из таблицы addresses. Поэтому, чтобы придать этому утверждению некоторую вменяемость, нам нужно предложение WHERE. Мы сделаем это с помощью Select.where():

>>> s = select(users, addresses).where(users.c.id == addresses.c.user_id)
sql>>> for row in conn.execute(s):
...     print(row)
SELECT users.id, users.name, users.fullname, addresses.id AS id_1,
   addresses.user_id, addresses.email_address
FROM users, addresses
WHERE users.id = addresses.user_id
[...] ()
(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com')
(1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')
(2, u'wendy', u'Wendy Williams', 3, 2, u'www@www.org')
(2, u'wendy', u'Wendy Williams', 4, 2, u'wendy@aol.com')

Так это выглядит намного лучше, мы добавили выражение к нашему select(), которое имело эффект добавления WHERE users.id = addresses.user_id к нашему утверждению, и наши результаты управляются вниз так, что соединение строк users и addresses имеет смысл. Но давайте посмотрим на это выражение? В нем используется просто оператор равенства Python между двумя различными объектами Column. Должно быть ясно, что что-то не так. Выражение 1 == 1 дает True, а 1 == 2 дает False, а не предложение WHERE. Итак, давайте посмотрим, что именно делает это выражение:

>>> users.c.id == addresses.c.user_id
<sqlalchemy.sql.elements.BinaryExpression object at 0x...>

Ух ты, сюрприз! Это не True и не False. Что же это такое?

>>> str(users.c.id == addresses.c.user_id)
'users.id = addresses.user_id'

Как видите, оператор == производит объект, который очень похож на объекты Insert и select(), которые мы создали до сих пор, благодаря встроенной программе Python __eq__(); вы вызываете str() на нем, и он производит SQL. К настоящему моменту можно видеть, что все, с чем мы работаем, в конечном итоге является объектом одного типа. SQLAlchemy называет базовый класс всех этих выражений ColumnElement.

Операторы¶

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

>>> print(users.c.id == addresses.c.user_id)
users.id = addresses.user_id

Если мы используем буквальное значение (буквальное значение, а не объект клаузулы SQLAlchemy), мы получим параметр связывания:

>>> print(users.c.id == 7)
users.id = :id_1

Литерал 7 вложен в результирующий ColumnElement; чтобы увидеть его, мы можем использовать тот же трюк, что и с объектом Insert:

>>> (users.c.id == 7).compile().params
{u'id_1': 7}

Большинство операторов Python, как выяснилось, производят здесь SQL-выражение, например, equals, not equals и т.д.:

>>> print(users.c.id != 7)
users.id != :id_1

>>> # None converts to IS NULL
>>> print(users.c.name == None)
users.name IS NULL

>>> # reverse works too
>>> print("fred" > users.c.name)
users.name < :name_1

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

>>> print(users.c.id + addresses.c.id)
users.id + addresses.id

Интересно, что тип Column важен! Если мы используем + с двумя колонками на основе строк (вспомните, что в начале мы поместили типы Integer и String на наши объекты Column), мы получим нечто иное:

>>> print(users.c.name + users.c.fullname)
users.name || users.fullname

Где || - оператор конкатенации строк, используемый в большинстве баз данных. Но не во всех. Пользователи MySQL, не бойтесь:

>>> print(
...     (users.c.name + users.c.fullname).compile(bind=create_engine("mysql://"))
... )  
concat(users.name, users.fullname)

Выше показан SQL, который генерируется для Engine, подключенного к базе данных MySQL; оператор || теперь компилируется как функция MySQL concat().

Если вы столкнулись с оператором, который действительно недоступен, вы всегда можете использовать метод Operators.op(); он генерирует любой оператор, который вам нужен:

>>> print(users.c.name.op("tiddlywinks")("foo"))
users.name tiddlywinks :name_1

Эта функция также может быть использована для того, чтобы сделать побитовые операторы явными. Например:

somecolumn.op("&")(0xFF)

является побитовым И из значения в somecolumn.

При использовании Operators.op() может быть важен возвращаемый тип выражения, особенно если оператор используется в выражении, которое будет отправлено в колонку результатов. В этом случае обязательно явно укажите тип, если он не тот, который обычно ожидается, используя type_coerce():

from sqlalchemy import type_coerce

expr = type_coerce(somecolumn.op("-%>")("foo"), MySpecialType())
stmt = select(expr)

Для булевых операторов используйте метод Operators.bool_op(), который гарантирует, что возвращаемый тип выражения будет обработан как boolean:

somecolumn.bool_op("-->")("some value")

Союзы¶

Мы хотели бы продемонстрировать некоторые из наших операторов внутри конструкций select(). Но нам нужно еще немного объединить их вместе, поэтому давайте сначала введем конъюнкцию. Конъюнкция - это такие маленькие слова, как AND и OR, которые соединяют все вместе. Мы также рассмотрим НЕ. and_(), or_() и not_() могут работать с соответствующими функциями, которые предоставляет SQLAlchemy (обратите внимание, что мы также добавили ColumnOperators.like()):

>>> from sqlalchemy.sql import and_, or_, not_
>>> print(
...     and_(
...         users.c.name.like("j%"),
...         users.c.id == addresses.c.user_id,
...         or_(
...             addresses.c.email_address == "wendy@aol.com",
...             addresses.c.email_address == "jack@yahoo.com",
...         ),
...         not_(users.c.id > 5),
...     )
... )
users.name LIKE :name_1 AND users.id = addresses.user_id AND
(addresses.email_address = :email_address_1
   OR addresses.email_address = :email_address_2)
AND users.id <= :id_1

Вы также можете использовать побитовые операторы AND, OR и NOT, хотя из-за старшинства операторов в Python вам придется следить за скобками:

>>> print(
...     users.c.name.like("j%")
...     & (users.c.id == addresses.c.user_id)
...     & (
...         (addresses.c.email_address == "wendy@aol.com")
...         | (addresses.c.email_address == "jack@yahoo.com")
...     )
...     & ~(users.c.id > 5)
... )
users.name LIKE :name_1 AND users.id = addresses.user_id AND
(addresses.email_address = :email_address_1
    OR addresses.email_address = :email_address_2)
AND users.id <= :id_1

Итак, используя весь этот словарный запас, давайте выберем всех пользователей, имеющих адрес электронной почты в AOL или MSN, чье имя начинается с буквы от «m» до «z», а также создадим столбец, содержащий их полное имя в сочетании с адресом электронной почты. Мы добавим к этому утверждению две новые конструкции, ColumnOperators.between() и ColumnElement.label(). ColumnOperators.between() создает предложение BETWEEN, а ColumnElement.label() используется в выражении столбца для создания меток с помощью ключевого слова AS; это рекомендуется при выборе из выражений, которые иначе не имели бы имени:

>>> s = select((users.c.fullname + ", " + addresses.c.email_address).label("title")).where(
...     and_(
...         users.c.id == addresses.c.user_id,
...         users.c.name.between("m", "z"),
...         or_(
...             addresses.c.email_address.like("%@aol.com"),
...             addresses.c.email_address.like("%@msn.com"),
...         ),
...     )
... )
>>> conn.execute(s).fetchall()
SELECT users.fullname || ? || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
[...] (', ', 'm', 'z', '%@aol.com', '%@msn.com')
[(u'Wendy Williams, wendy@aol.com',)]

И снова SQLAlchemy определила предложение FROM для нашего оператора. На самом деле он определит пункт FROM на основе всех остальных элементов: пункта columns, пункта where, а также некоторых других элементов, которые мы еще не рассматривали, включая ORDER BY, GROUP BY и HAVING.

Краткосрочный способ использования and_() заключается в соединении нескольких пунктов Select.where(). Вышеприведенное можно также записать как:

>>> s = (
...     select((users.c.fullname + ", " + addresses.c.email_address).label("title"))
...     .where(users.c.id == addresses.c.user_id)
...     .where(users.c.name.between("m", "z"))
...     .where(
...         or_(
...             addresses.c.email_address.like("%@aol.com"),
...             addresses.c.email_address.like("%@msn.com"),
...         )
...     )
... )
>>> conn.execute(s).fetchall()
SELECT users.fullname || ? || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
[...] (', ', 'm', 'z', '%@aol.com', '%@msn.com')
[(u'Wendy Williams, wendy@aol.com',)]

Способ, которым мы можем построить конструкцию select() через последовательные вызовы методов, называется method chaining.

Использование текстового SQL¶

Наш последний пример действительно стал сложным для ввода. Переход от текстового выражения SQL к конструкции Python, которая группирует компоненты вместе в программном стиле, может быть трудным. Именно поэтому SQLAlchemy позволяет вам просто использовать строки, для тех случаев, когда SQL уже известен и нет сильной необходимости в том, чтобы оператор поддерживал динамические функции. Конструкция text() используется для составления текстового утверждения, которое передается в базу данных практически без изменений. Ниже мы создадим объект text() и выполним его:

>>> from sqlalchemy.sql import text
>>> s = text(
...     "SELECT users.fullname || ', ' || addresses.email_address AS title "
...     "FROM users, addresses "
...     "WHERE users.id = addresses.user_id "
...     "AND users.name BETWEEN :x AND :y "
...     "AND (addresses.email_address LIKE :e1 "
...     "OR addresses.email_address LIKE :e2)"
... )
>>> conn.execute(s, {"x": "m", "y": "z", "e1": "%@aol.com", "e2": "%@msn.com"}).fetchall()
SELECT users.fullname || ', ' || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
[...] ('m', 'z', '%@aol.com', '%@msn.com')
[(u'Wendy Williams, wendy@aol.com',)]

Выше мы видим, что связанные параметры указываются в text() с использованием формата именованного двоеточия; этот формат является последовательным независимо от бэкенда базы данных. Чтобы передать значения для параметров, мы передали их в метод Connection.execute() в качестве дополнительных аргументов.

stmt = text("SELECT * FROM users WHERE users.name BETWEEN :x AND :y")
stmt = stmt.bindparams(x="m", y="z")

stmt = stmt.bindparams(bindparam("x", type_=String), bindparam("y", type_=String))
result = conn.execute(stmt, {"x": "m", "y": "z"})

stmt = stmt.columns(id=Integer, name=String)

stmt = text("SELECT id, name FROM users")
stmt = stmt.columns(users.c.id, users.c.name)

j = stmt.join(addresses, stmt.c.id == addresses.c.user_id)

new_stmt = select(stmt.c.id, addresses.c.id).select_from(j).where(stmt.c.name == "x")

>>> stmt = text(
...     "SELECT users.id, addresses.id, users.id, "
...     "users.name, addresses.email_address AS email "
...     "FROM users JOIN addresses ON users.id=addresses.user_id "
...     "WHERE users.id = 1"
... ).columns(
...     users.c.id,
...     addresses.c.id,
...     addresses.c.user_id,
...     users.c.name,
...     addresses.c.email_address,
... )
>>> result = conn.execute(stmt)
SELECT users.id, addresses.id, users.id, users.name,
    addresses.email_address AS email
FROM users JOIN addresses ON users.id=addresses.user_id WHERE users.id = 1
[...] ()

>>> row = result.fetchone()
>>> row._mapping[addresses.c.email_address]
'jack@yahoo.com'

>>> row._mapping["id"]
Traceback (most recent call last):
...
InvalidRequestError: Ambiguous column name 'id' in result set column descriptions

>>> s = (
...     select(text("users.fullname || ', ' || addresses.email_address AS title"))
...     .where(
...         and_(
...             text("users.id = addresses.user_id"),
...             text("users.name BETWEEN 'm' AND 'z'"),
...             text(
...                 "(addresses.email_address LIKE :x "
...                 "OR addresses.email_address LIKE :y)"
...             ),
...         )
...     )
...     .select_from(text("users, addresses"))
... )
>>> conn.execute(s, {"x": "%@aol.com", "y": "%@msn.com"}).fetchall()
SELECT users.fullname || ', ' || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z'
AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
[...] ('%@aol.com', '%@msn.com')
[(u'Wendy Williams, wendy@aol.com',)]

>>> from sqlalchemy import select, and_, text, String
>>> from sqlalchemy.sql import table, literal_column
>>> s = (
...     select(
...         literal_column("users.fullname", String)
...         + ", "
...         + literal_column("addresses.email_address").label("title")
...     )
...     .where(
...         and_(
...             literal_column("users.id") == literal_column("addresses.user_id"),
...             text("users.name BETWEEN 'm' AND 'z'"),
...             text(
...                 "(addresses.email_address LIKE :x OR "
...                 "addresses.email_address LIKE :y)"
...             ),
...         )
...     )
...     .select_from(table("users"))
...     .select_from(table("addresses"))
... )

>>> conn.execute(s, {"x": "%@aol.com", "y": "%@msn.com"}).fetchall()
SELECT users.fullname || ? || addresses.email_address AS anon_1
FROM users, addresses
WHERE users.id = addresses.user_id
AND users.name BETWEEN 'm' AND 'z'
AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
[...] (', ', '%@aol.com', '%@msn.com')
[(u'Wendy Williams, wendy@aol.com',)]

>>> from sqlalchemy import func
>>> stmt = (
...     select(addresses.c.user_id, func.count(addresses.c.id).label("num_addresses"))
...     .group_by("user_id")
...     .order_by("user_id", "num_addresses")
... )

sql>>> conn.execute(stmt).fetchall()
SELECT addresses.user_id, count(addresses.id) AS num_addresses
FROM addresses GROUP BY addresses.user_id ORDER BY addresses.user_id, num_addresses
[...] ()
[(1, 2), (2, 2)]

>>> from sqlalchemy import func, desc
>>> stmt = (
...     select(addresses.c.user_id, func.count(addresses.c.id).label("num_addresses"))
...     .group_by("user_id")
...     .order_by("user_id", desc("num_addresses"))
... )

sql>>> conn.execute(stmt).fetchall()
SELECT addresses.user_id, count(addresses.id) AS num_addresses
FROM addresses GROUP BY addresses.user_id ORDER BY addresses.user_id, num_addresses DESC
[...] ()
[(1, 2), (2, 2)]

>>> u1a, u1b = users.alias(), users.alias()
>>> stmt = (
...     select(u1a, u1b).where(u1a.c.name > u1b.c.name).order_by(u1a.c.name)
... )  # using "name" here would be ambiguous

sql>>> conn.execute(stmt).fetchall()
SELECT users_1.id, users_1.name, users_1.fullname, users_2.id AS id_1,
users_2.name AS name_1, users_2.fullname AS fullname_1
FROM users AS users_1, users AS users_2
WHERE users_1.name > users_2.name ORDER BY users_1.name
[...] ()
[(2, u'wendy', u'Wendy Williams', 1, u'jack', u'Jack Jones')]

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

Псевдоним в SQL соответствует «переименованной» версии таблицы или оператора SELECT, что происходит всякий раз, когда вы говорите «SELECT … FROM sometable AS someothername». Символ AS создает новое имя для таблицы. Псевдонимы - это ключевая конструкция, поскольку они позволяют ссылаться на любую таблицу или подзапрос по уникальному имени. В случае с таблицей это позволяет называть одну и ту же таблицу в предложении FROM несколько раз. В случае оператора SELECT это обеспечивает родительское имя для столбцов, представленных в операторе, позволяя ссылаться на них относительно этого имени.

В SQLAlchemy любой Table или другой FromClause, основанный на выборе, может быть превращен в псевдоним с помощью метода FromClause.alias(), который создает конструкцию Alias. Alias - это объект FromClause, который ссылается на отображение объектов Column через свою коллекцию FromClause.c и может использоваться в предложении FROM любого последующего оператора SELECT, путем ссылки на его элементы столбцов в столбцах или предложении WHERE оператора, или путем явного размещения в предложении FROM, либо напрямую, либо в рамках объединения.

В качестве примера предположим, что мы знаем, что у нашего пользователя jack есть два определенных адреса электронной почты. Как мы можем найти Джека по комбинации этих двух адресов? Для этого мы используем соединение с таблицей addresses, по одному разу для каждого адреса. Мы создадим две конструкции Alias против addresses, а затем используем их обе в конструкции select():

>>> a1 = addresses.alias()
>>> a2 = addresses.alias()
>>> s = select(users).where(
...     and_(
...         users.c.id == a1.c.user_id,
...         users.c.id == a2.c.user_id,
...         a1.c.email_address == "jack@msn.com",
...         a2.c.email_address == "jack@yahoo.com",
...     )
... )
>>> conn.execute(s).fetchall()
SELECT users.id, users.name, users.fullname
FROM users, addresses AS addresses_1, addresses AS addresses_2
WHERE users.id = addresses_1.user_id
    AND users.id = addresses_2.user_id
    AND addresses_1.email_address = ?
    AND addresses_2.email_address = ?
[...] ('jack@msn.com', 'jack@yahoo.com')
[(1, u'jack', u'Jack Jones')]

Обратите внимание, что конструкция Alias породила имена addresses_1 и addresses_2 в конечном результате SQL. Генерация этих имен определяется положением конструкции в операторе. Если бы мы создали запрос, используя только второй псевдоним a2, имя получилось бы addresses_1. Генерация имен также является детерминированной, то есть одна и та же конструкция оператора SQLAlchemy будет выдавать идентичную строку SQL каждый раз, когда она создается для определенного диалекта.

Поскольку снаружи мы обращаемся к псевдониму с помощью самой конструкции Alias, нам не нужно заботиться о генерируемом имени. Однако для целей отладки его можно указать, передав строковое имя методу FromClause.alias():

>>> a1 = addresses.alias("a1")

SELECT-ориентированные конструкции, которые расширяются из SelectBase, могут быть превращены в псевдоподзапросы с помощью метода SelectBase.subquery(), который создает конструкцию Subquery; для удобства использования существует также метод SelectBase.alias(), который является синонимом SelectBase.subquery(). Как и Alias, Subquery также является объектом FromClause, который может быть частью любого вложенного SELECT, используя те же приемы, что и для Alias.

Мы можем самостоятельно присоединить таблицу users обратно к созданной нами select(), сделав Subquery из всего оператора:

>>> address_subq = s.subquery()
>>> s = select(users.c.name).where(users.c.id == address_subq.c.id)
>>> conn.execute(s).fetchall()
SELECT users.name
FROM users,
    (SELECT users.id AS id, users.name AS name, users.fullname AS fullname
        FROM users, addresses AS addresses_1, addresses AS addresses_2
        WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id
        AND addresses_1.email_address = ?
        AND addresses_2.email_address = ?) AS anon_1
WHERE users.id = anon_1.id
[...] ('jack@msn.com', 'jack@yahoo.com')
[(u'jack',)]

Использование объединений¶

Мы уже на полпути к тому, чтобы иметь возможность построить любое выражение SELECT. Следующим краеугольным камнем SELECT является выражение JOIN. Мы уже делали объединения в наших примерах, просто помещая две таблицы либо в предложение columns, либо в предложение where конструкции select(). Но если мы хотим сделать настоящую конструкцию «JOIN» или «OUTERJOIN», мы используем методы FromClause.join() и FromClause.outerjoin(), доступ к которым чаще всего осуществляется из левой таблицы в соединении:

>>> print(users.join(addresses))
users JOIN addresses ON users.id = addresses.user_id

Бдительный читатель увидит еще больше сюрпризов; SQLAlchemy придумала, как соединить эти две таблицы! Условие ON соединения, как оно называется, было автоматически сгенерировано на основе объекта ForeignKey, который мы поместили в таблицу addresses в начале этого руководства. Уже сейчас конструкция join() выглядит гораздо лучшим способом объединения таблиц.

Конечно, вы можете присоединиться к любому выражению, которое вы хотите, например, если мы хотим присоединиться ко всем пользователям, которые используют то же имя в адресе электронной почты в качестве имени пользователя:

>>> print(users.join(addresses, addresses.c.email_address.like(users.c.name + "%")))
users JOIN addresses ON addresses.email_address LIKE users.name || :name_1

Когда мы создаем конструкцию select(), SQLAlchemy просматривает таблицы, которые мы упоминали, и затем помещает их в предложение FROM оператора. Однако, когда мы используем JOIN, мы знаем, какой пункт FROM нам нужен, поэтому здесь мы используем метод Select.select_from():

>>> s = select(users.c.fullname).select_from(
...     users.join(addresses, addresses.c.email_address.like(users.c.name + "%"))
... )
sql>>> conn.execute(s).fetchall()
SELECT users.fullname
FROM users JOIN addresses ON addresses.email_address LIKE users.name || ?
[...] ('%',)
[(u'Jack Jones',), (u'Jack Jones',), (u'Wendy Williams',)]

Метод FromClause.outerjoin() создает конструкции LEFT OUTER JOIN и используется так же, как FromClause.join():

>>> s = select(users.c.fullname).select_from(users.outerjoin(addresses))
>>> print(s)
SELECT users.fullname
    FROM users
    LEFT OUTER JOIN addresses ON users.id = addresses.user_id

Это вывод outerjoin(), если, конечно, вы не застряли в программе, использующей Oracle до версии 9, и вы настроили свой движок (который будет использовать OracleDialect) на использование SQL, специфичного для Oracle:

>>> from sqlalchemy.dialects.oracle import dialect as OracleDialect
>>> print(s.compile(dialect=OracleDialect(use_ansi=False)))
SELECT users.fullname
FROM users, addresses
WHERE users.id = addresses.user_id(+)

Если вы не знаете, что означает этот SQL, не волнуйтесь! Тайное племя DBA Oracle не хочет, чтобы их черная магия была обнаружена ;).

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

Общие табличные выражения в настоящее время поддерживаются всеми основными базами данных, включая современные MySQL, MariaDB, SQLite, PostgreSQL, Oracle и MS SQL Server. SQLAlchemy поддерживает эту конструкцию через объект CTE, который обычно приобретается с помощью метода Select.cte() в конструкции Select:

>>> users_cte = select(users.c.id, users.c.name).where(users.c.name == "wendy").cte()
>>> stmt = (
...     select(addresses)
...     .where(addresses.c.user_id == users_cte.c.id)
...     .order_by(addresses.c.id)
... )
>>> conn.execute(stmt).fetchall()
WITH anon_1 AS
(SELECT users.id AS id, users.name AS name
FROM users
WHERE users.name = ?)
 SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses, anon_1
WHERE addresses.user_id = anon_1.id ORDER BY addresses.id
[...] ('wendy',)
[(3, 2, 'www@www.org'), (4, 2, 'wendy@aol.com')]

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

Когда мы создаем объект CTE, мы используем его как любую другую таблицу в операторе. Однако вместо того, чтобы быть добавленным в предложение FROM в качестве подзапроса, он выходит на первое место, что имеет дополнительное преимущество - не вызывает неожиданных картезианских произведений.

Рекурсивный формат CTE доступен при использовании параметра Select.cte.recursive. Рекурсивный CTE обычно требует, чтобы мы ссылались на себя как на псевдоним. Общая форма такой операции включает в себя объединение исходного CTE с самим собой. Учитывая, что наши примеры таблиц не очень подходят для создания действительно полезного запроса с такой функцией, эта форма выглядит следующим образом:

>>> users_cte = select(users.c.id, users.c.name).cte(recursive=True)
>>> users_recursive = users_cte.alias()
>>> users_cte = users_cte.union(
...     select(users.c.id, users.c.name).where(users.c.id > users_recursive.c.id)
... )
>>> stmt = (
...     select(addresses)
...     .where(addresses.c.user_id == users_cte.c.id)
...     .order_by(addresses.c.id)
... )
>>> conn.execute(stmt).fetchall()
WITH RECURSIVE anon_1(id, name) AS
(SELECT users.id AS id, users.name AS name
FROM users UNION SELECT users.id AS id, users.name AS name
FROM users, anon_1 AS anon_2
WHERE users.id > anon_2.id)
 SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses, anon_1
WHERE addresses.user_id = anon_1.id ORDER BY addresses.id
[...] ()
[(1, 1, 'jack@yahoo.com'), (2, 1, 'jack@msn.com'), (3, 2, 'www@www.org'), (4, 2, 'wendy@aol.com')]

Все остальное¶

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

>>> from sqlalchemy.sql import bindparam
>>> s = users.select().where(users.c.name == bindparam("username"))
sql>>> conn.execute(s, {"username": "wendy"}).fetchall()
SELECT users.id, users.name, users.fullname
FROM users
WHERE users.name = ?
[...] ('wendy',)
[(2, u'wendy', u'Wendy Williams')]

>>> s = users.select().where(
...     users.c.name.like(bindparam("username", type_=String) + text("'%'"))
... )
sql>>> conn.execute(s, {"username": "wendy"}).fetchall()
SELECT users.id, users.name, users.fullname
FROM users
WHERE users.name LIKE ? || '%'
[...] ('wendy',)
[(2, u'wendy', u'Wendy Williams')]

>>> s = (
...     select(users, addresses)
...     .where(
...         or_(
...             users.c.name.like(bindparam("name", type_=String) + text("'%'")),
...             addresses.c.email_address.like(
...                 bindparam("name", type_=String) + text("'@%'")
...             ),
...         )
...     )
...     .select_from(users.outerjoin(addresses))
...     .order_by(addresses.c.id)
... )
sql>>> conn.execute(s, {"name": "jack"}).fetchall()
SELECT users.id, users.name, users.fullname, addresses.id AS id_1,
    addresses.user_id, addresses.email_address
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
WHERE users.name LIKE ? || '%' OR addresses.email_address LIKE ? || '@%'
ORDER BY addresses.id
[...] ('jack', 'jack')
[(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com'), (1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')]

>>> from sqlalchemy.sql import func
>>> print(func.now())
now()

>>> print(func.concat("x", "y"))
concat(:concat_1, :concat_2)

>>> print(func.xyz_my_goofy_function())
xyz_my_goofy_function()

>>> print(func.current_timestamp())
CURRENT_TIMESTAMP

stmt = select(func.date(some_table.c.date_string, type_=Date))

>>> conn.execute(
...     select(func.max(addresses.c.email_address, type_=String).label("maxemail"))
... ).scalar()
SELECT max(addresses.email_address) AS maxemail
FROM addresses
[...] ()
u'www@www.org'

>>> from sqlalchemy.sql import column
>>> calculate = select(column("q"), column("z"), column("r")).select_from(
...     func.calculate(bindparam("x"), bindparam("y"))
... )
>>> calc = calculate.alias()
>>> print(select(users).where(users.c.id > calc.c.z))
SELECT users.id, users.name, users.fullname
FROM users, (SELECT q, z, r
FROM calculate(:x, :y)) AS anon_1
WHERE users.id > anon_1.z

>>> calc1 = calculate.alias("c1").unique_params(x=17, y=45)
>>> calc2 = calculate.alias("c2").unique_params(x=5, y=12)
>>> s = select(users).where(users.c.id.between(calc1.c.z, calc2.c.z))
>>> print(s)
SELECT users.id, users.name, users.fullname
FROM users,
    (SELECT q, z, r FROM calculate(:x_1, :y_1)) AS c1,
    (SELECT q, z, r FROM calculate(:x_2, :y_2)) AS c2
WHERE users.id BETWEEN c1.z AND c2.z

>>> s.compile().params  
{u'x_2': 5, u'y_2': 12, u'y_1': 45, u'x_1': 17}

>>> s = select(users.c.id, func.row_number().over(order_by=users.c.name))
>>> print(s)
SELECT users.id, row_number() OVER (ORDER BY users.name) AS anon_1
FROM users

>>> s = select(users.c.id, func.row_number().over(order_by=users.c.name, rows=(-2, None)))
>>> print(s)
SELECT users.id, row_number() OVER
(ORDER BY users.name ROWS BETWEEN :param_1 PRECEDING AND UNBOUNDED FOLLOWING) AS anon_1
FROM users

>>> from sqlalchemy import cast
>>> s = select(cast(users.c.id, String))
>>> conn.execute(s).fetchall()
SELECT CAST(users.id AS VARCHAR) AS id
FROM users
[...] ()
[('1',), ('2',)]

>>> 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

>>> from sqlalchemy.sql import union
>>> u = union(
...     addresses.select().where(addresses.c.email_address == "foo@bar.com"),
...     addresses.select().where(addresses.c.email_address.like("%@yahoo.com")),
... ).order_by(addresses.c.email_address)

sql>>> conn.execute(u).fetchall()
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address = ?
UNION
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ? ORDER BY email_address
[...] ('foo@bar.com', '%@yahoo.com')
[(1, 1, u'jack@yahoo.com')]

>>> from sqlalchemy.sql import except_
>>> u = except_(
...     addresses.select().where(addresses.c.email_address.like("%@%.com")),
...     addresses.select().where(addresses.c.email_address.like("%@msn.com")),
... )

sql>>> conn.execute(u).fetchall()
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ?
EXCEPT
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ?
[...] ('%@%.com', '%@msn.com')
[(1, 1, u'jack@yahoo.com'), (4, 2, u'wendy@aol.com')]

>>> u = except_(
...     union(
...         addresses.select().where(addresses.c.email_address.like("%@yahoo.com")),
...         addresses.select().where(addresses.c.email_address.like("%@msn.com")),
...     )
...     .subquery()
...     .select(),  # apply subquery here
...     addresses.select().where(addresses.c.email_address.like("%@msn.com")),
... )
sql>>> conn.execute(u).fetchall()
SELECT anon_1.id, anon_1.user_id, anon_1.email_address
FROM (SELECT addresses.id AS id, addresses.user_id AS user_id,
    addresses.email_address AS email_address
    FROM addresses
    WHERE addresses.email_address LIKE ?
    UNION
    SELECT addresses.id AS id,
        addresses.user_id AS user_id,
        addresses.email_address AS email_address
    FROM addresses
    WHERE addresses.email_address LIKE ?) AS anon_1
EXCEPT
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ?
[...] ('%@yahoo.com', '%@msn.com', '%@msn.com')
[(1, 1, u'jack@yahoo.com')]

>>> u = union(
...     addresses.select().where(addresses.c.email_address == "foo@bar.com"),
...     addresses.select().where(addresses.c.email_address.like("%@yahoo.com")),
... )
>>> u = u.order_by(u.selected_columns.email_address)
>>> print(u)
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address = :email_address_1
UNION SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE :email_address_2 ORDER BY email_address

>>> subq = (
...     select(func.count(addresses.c.id))
...     .where(users.c.id == addresses.c.user_id)
...     .scalar_subquery()
... )

>>> conn.execute(select(users.c.name, subq)).fetchall()
SELECT users.name, (SELECT count(addresses.id) AS count_1
FROM addresses
WHERE users.id = addresses.user_id) AS anon_1
FROM users
[...] ()
[(u'jack', 2), (u'wendy', 2)]

>>> subq = (
...     select(func.count(addresses.c.id))
...     .where(users.c.id == addresses.c.user_id)
...     .label("address_count")
... )
>>> conn.execute(select(users.c.name, subq)).fetchall()
SELECT users.name, (SELECT count(addresses.id) AS count_1
FROM addresses
WHERE users.id = addresses.user_id) AS address_count
FROM users
[...] ()
[(u'jack', 2), (u'wendy', 2)]

>>> stmt = (
...     select(addresses.c.user_id)
...     .where(addresses.c.user_id == users.c.id)
...     .where(addresses.c.email_address == "jack@yahoo.com")
... )
>>> enclosing_stmt = select(users.c.name).where(users.c.id == stmt.scalar_subquery())
>>> conn.execute(enclosing_stmt).fetchall()
SELECT users.name
FROM users
WHERE users.id = (SELECT addresses.user_id
    FROM addresses
    WHERE addresses.user_id = users.id
    AND addresses.email_address = ?)
[...] ('jack@yahoo.com',)
[(u'jack',)]

>>> stmt = (
...     select(users.c.id)
...     .where(users.c.id == addresses.c.user_id)
...     .where(users.c.name == "jack")
...     .correlate(addresses)
... )
>>> enclosing_stmt = (
...     select(users.c.name, addresses.c.email_address)
...     .select_from(users.join(addresses))
...     .where(users.c.id == stmt.scalar_subquery())
... )
>>> conn.execute(enclosing_stmt).fetchall()
SELECT users.name, addresses.email_address
 FROM users JOIN addresses ON users.id = addresses.user_id
 WHERE users.id = (SELECT users.id
 FROM users
 WHERE users.id = addresses.user_id AND users.name = ?)
 [...] ('jack',)
 
[(u'jack', u'jack@yahoo.com'), (u'jack', u'jack@msn.com')]