Работа с транзакциями и DBAPI

Теперь, когда объект Engine готов к работе, мы можем погрузиться в базовые операции Engine и его основные компоненты: Connection и Result. Также мы познакомимся с ORM-фасадом для этих объектов — Session.

При использовании ORM объектом Engine управляет Session. Session в современной SQLAlchemy использует паттерн выполнения транзакций и SQL, который практически идентичен паттерну Connection, описанному ниже. Поэтому, хотя этот подраздел ориентирован на Core, все концепции здесь актуальны и для работы с ORM. Мы рекомендуем изучить этот материал всем, кто работает с ORM. В конце раздела мы сравним паттерны выполнения Connection и Session.

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

Получение Connection

Назначение Engine — подключаться к базе данных, предоставляя объект Connection. При прямой работе с Core объект Connection является единственным способом взаимодействия с базой данных. Поскольку Connection создает открытый ресурс для работы с базой данных, мы хотим ограничить использование этого объекта определенным контекстом. Лучший способ сделать это — использовать контекстный менеджер Python, также известный как конструкция with. Ниже мы используем текстовый SQL-запрос для вывода “Hello World”. Текстовый SQL создается с помощью конструкции text(), которую мы подробнее обсудим позже:

from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(text("select 'hello world'"))
    print(result.all())

Вывод:

BEGIN (implicit)
select 'hello world'
[...] ()
[('hello world',)]
ROLLBACK

В примере выше контекстный менеджер создает подключение к базе данных и выполняет операцию в транзакции. Поведение Python DBAPI по умолчанию таково, что транзакция всегда активна; когда соединение освобождается, выполняется ROLLBACK для завершения транзакции. Транзакция не фиксируется автоматически; если мы хотим зафиксировать данные, нам нужно вызвать Connection.commit(), как мы увидим в следующем разделе.

Режим “autocommit” доступен для особых случаев. Раздел “Setting Transaction Isolation Levels including DBAPI Autocommit” обсуждает это подробнее.

Результат нашего SELECT был возвращен в объекте Result, который мы обсудим позже. На данный момент отметим, что лучше всего использовать этот объект внутри блока “connect” и не использовать его за пределами области видимости нашего соединения.

Фиксация изменений

Мы только что узнали, что соединение DBAPI не фиксирует изменения автоматически. Что если мы хотим зафиксировать какие-то данные? Мы можем изменить наш пример выше, чтобы создать таблицу, вставить данные и затем зафиксировать транзакцию с помощью метода Connection.commit() внутри блока, где у нас есть объект Connection:

# "commit as you go" (фиксация по ходу)
with engine.connect() as conn:
    conn.execute(text("CREATE TABLE some_table (x int, y int)"))
    conn.execute(
        text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
        [{"x": 1, "y": 1}, {"x": 2, "y": 4}],
    )
    conn.commit()

Вывод:

BEGIN (implicit)
CREATE TABLE some_table (x int, y int)
[...] ()
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(1, 1), (2, 4)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

Выше мы выполняем два SQL-выражения: выражение “CREATE TABLE” и параметризованное выражение “INSERT” (синтаксис параметризации мы обсудим позже в разделе “Отправка нескольких параметров”). Чтобы зафиксировать работу, выполненную в нашем блоке, мы вызываем метод Connection.commit(), который фиксирует транзакцию. После этого мы можем продолжать выполнять SQL-выражения и снова вызывать Connection.commit() для этих выражений. SQLAlchemy называет этот стиль commit as you go (фиксация по ходу).

Существует также другой стиль фиксации данных. Мы можем объявить наш блок “connect” транзакционным блоком заранее. Для этого мы используем метод Engine.begin() для получения соединения вместо метода Engine.connect(). Этот метод будет управлять областью видимости Connection и также заключит все внутри транзакции с COMMIT в конце, если блок выполнен успешно, или ROLLBACK, если возникло исключение. Этот стиль известен как begin once (начать один раз):

# "begin once" (начать один раз)
with engine.begin() as conn:
    conn.execute(
        text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
        [{"x": 6, "y": 8}, {"x": 9, "y": 10}],
    )

Вывод:

BEGIN (implicit)
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(6, 8), (9, 10)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

В большинстве случаев предпочтительнее использовать стиль “begin once”, потому что он короче и сразу показывает намерение всего блока. Однако в этом руководстве мы будем использовать стиль “commit as you go”, так как он более гибкий для демонстрационных целей.

Вы могли заметить строку лога “BEGIN (implicit)” в начале транзакционного блока. “implicit” здесь означает, что SQLAlchemy фактически не отправил никакой команды в базу данных; он просто считает это началом неявной транзакции DBAPI. Вы можете зарегистрировать обработчики событий для перехвата этого события.

DDL относится к подмножеству SQL, которое указывает базе данных создавать, изменять или удалять конструкции уровня схемы, такие как таблицы. DDL “CREATE TABLE” к примеру, должны находиться в транзакционном блоке, который заканчивается COMMIT, поскольку многие базы данных используют транзакционный DDL, при котором изменения схемы не вступают в силу до фиксации транзакции. Однако, как мы увидим позже, обычно мы позволяем SQLAlchemy выполнять последовательности DDL за нас в рамках операций более высокого уровня, где нам обычно не нужно беспокоиться о COMMIT.

Основы выполнения выражений

Мы видели несколько примеров выполнения SQL-выражений в базе данных, используя метод Connection.execute() в сочетании с объектом text() и возвращая объект Result. В этом разделе мы более подробно проиллюстрируем механику и взаимодействия этих компонентов.

Большая часть содержимого этого раздела в равной степени применима к современному использованию ORM при использовании метода Session.execute(), который работает очень похоже на Connection.execute(), включая то, что строки результатов ORM доставляются с использованием того же интерфейса Result, который используется Core.

Получение строк

Сначала мы более подробно проиллюстрируем объект Result, используя строки, которые мы вставили ранее, выполнив текстовый SELECT-запрос к созданной нами таблице:

with engine.connect() as conn:
    result = conn.execute(text("SELECT x, y FROM some_table"))
    for row in result:
        print(f"x: {row.x}  y: {row.y}")

Вывод:

BEGIN (implicit)
SELECT x, y FROM some_table
[...] ()
x: 1  y: 1
x: 2  y: 4
x: 6  y: 8
x: 9  y: 10
ROLLBACK

Выше выражение “SELECT”, которое мы выполнили, выбрало все строки из нашей таблицы. Возвращенный объект называется Result и представляет собой итерируемый объект строк результата.

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

Сами объекты Row предназначены для работы как именованные кортежи Python. Ниже мы иллюстрируем различные способы доступа к строкам.

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

result = conn.execute(text("select x, y from some_table"))

for x, y in result:
    ...

Целочисленный индекс — кортежи являются последовательностями Python, поэтому доступен также обычный целочисленный доступ:

result = conn.execute(text("select x, y from some_table"))

for row in result:
    x = row[0]

Имя атрибута — поскольку это именованные кортежи Python, кортежи имеют динамические имена атрибутов, соответствующие именам каждого столбца. Эти имена обычно являются именами, которые SQL-выражение присваивает столбцам в каждой строке. Хотя они обычно довольно предсказуемы и также могут контролироваться метками, в менее определенных случаях они могут зависеть от поведения конкретной базы данных:

result = conn.execute(text("select x, y from some_table"))

for row in result:
    y = row.y

    # иллюстрация использования с f-строками Python
    print(f"Row: {row.x} {y}")

Доступ через отображение — чтобы получить строки как объекты отображения Python, что по сути является версией интерфейса Python к обычному объекту dict только для чтения, Result может быть преобразован в объект MappingResult с помощью модификатора Result.mappings(); это объект результата, который возвращает объекты RowMapping, похожие на словари, а не объекты Row:

result = conn.execute(text("select x, y from some_table"))

for dict_row in result.mappings():
    x = dict_row["x"]
    y = dict_row["y"]

Отправка параметров

SQL-выражения обычно сопровождаются данными, которые должны быть переданы вместе с самим выражением, как мы видели в примере INSERT ранее. Поэтому метод Connection.execute() также принимает параметры, которые известны как связанные параметры. Простой пример может быть, если мы хотим ограничить наше SELECT-выражение только строками, которые соответствуют определенному критерию, например, строками, где значение “y” больше определенного значения, переданного в функцию.

Чтобы достичь этого таким образом, чтобы SQL-выражение могло оставаться фиксированным, а драйвер мог правильно санировать значение, мы добавляем критерий WHERE к нашему выражению, который называет новый параметр “y”; конструкция text() принимает их, используя формат с двоеточием :y. Фактическое значение для :y затем передается в качестве второго аргумента в Connection.execute() в форме словаря:

with engine.connect() as conn:
    result = conn.execute(
        text("SELECT x, y FROM some_table WHERE y > :y"),
        {"y": 2})
    for row in result:
        print(f"x: {row.x}  y: {row.y}")

Вывод:

BEGIN (implicit)
SELECT x, y FROM some_table WHERE y > ?
[...] (2,)
x: 2  y: 4
x: 6  y: 8
x: 9  y: 10
ROLLBACK

В логированном SQL-выводе мы видим, что связанный параметр :y был преобразован в знак вопроса, когда он был отправлен в базу данных SQLite. Это происходит потому, что драйвер базы данных SQLite использует формат, называемый “qmark parameter style”, который является одним из шести различных форматов, разрешенных спецификацией DBAPI. SQLAlchemy абстрагирует эти форматы в один единственный, а именно “именованный” формат с использованием двоеточия.

Всегда используйте связанные параметры

Как упоминалось в начале этого раздела, текстовый SQL — это не обычный способ работы с SQLAlchemy. Однако при использовании текстового SQL литеральное значение Python, даже не строки, такие как целые числа или даты, никогда не должно быть преобразовано в строку SQL напрямую; всегда должен использоваться параметр. Это наиболее известно как способ избежать атак SQL-инъекций, когда данные не являются доверенными. Однако это также позволяет диалектам SQLAlchemy и/или DBAPI правильно обрабатывать входящие данные для бэкенда. За пределами случаев использования обычного текстового SQL Core Expression API SQLAlchemy в противном случае гарантирует, что литеральные значения Python передаются как связанные параметры там, где это уместно.

Отправка нескольких параметров

В примере в разделе “Фиксация изменений” мы выполнили выражение INSERT, где казалось, что мы смогли вставить несколько строк в базу данных за один раз. Для DML-выражений, таких как “INSERT”, “UPDATE” и “DELETE”, мы можем отправить несколько наборов параметров методу Connection.execute(), передав список словарей вместо одного словаря, что указывает, что единственное SQL-выражение должно быть вызвано несколько раз, по одному разу для каждого набора параметров. Этот стиль выполнения известен как executemany:

with engine.connect() as conn:
    conn.execute(
        text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
        [{"x": 11, "y": 12}, {"x": 13, "y": 14}],
    )
    conn.commit()

Вывод:

BEGIN (implicit)
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(11, 12), (13, 14)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

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

Ключевое поведенческое различие между “execute” и “executemany” заключается в том, что последний не поддерживает возврат строк результатов, даже если выражение включает предложение RETURNING. Единственным исключением из этого является использование конструкции Core insert(), представленной позже в этом руководстве в разделе “Using INSERT Statements”, которая также указывает RETURNING с использованием метода Insert.returning(). В этом случае SQLAlchemy использует специальную логику для реорганизации выражения INSERT, чтобы его можно было вызывать для многих строк, при этом все еще поддерживая RETURNING.

Выполнение с ORM Session

Как упоминалось ранее, большинство паттернов и примеров выше также применимы к использованию ORM, поэтому здесь мы введем это использование, чтобы по мере продвижения руководства мы могли иллюстрировать каждый паттерн с точки зрения использования Core и ORM вместе.

Фундаментальным транзакционным / интерактивным объектом базы данных при использовании ORM является Session. В современной SQLAlchemy этот объект используется очень похожим образом на Connection, и фактически, когда Session используется, он внутренне ссылается на Connection, который использует для отправки SQL.

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

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

from sqlalchemy.orm import Session

stmt = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y")
with Session(engine) as session:
    result = session.execute(stmt, {"y": 6})
    for row in result:
        print(f"x: {row.x}  y: {row.y}")

Вывод:

BEGIN (implicit)
SELECT x, y FROM some_table WHERE y > ? ORDER BY x, y
[...] (6,)
x: 6  y: 8
x: 9  y: 10
x: 11  y: 12
x: 13  y: 14
ROLLBACK

Приведенный выше пример можно сравнить с примером в предыдущем разделе “Отправка параметров” — мы напрямую заменяем вызов with engine.connect() as conn на with Session(engine) as session, а затем используем метод Session.execute() так же, как мы используем метод Connection.execute().

Также, как и Connection, Session имеет поведение “commit as you go” с использованием метода Session.commit(), проиллюстрированного ниже с использованием текстового выражения UPDATE для изменения некоторых наших данных:

with Session(engine) as session:
    result = session.execute(
        text("UPDATE some_table SET y=:y WHERE x=:x"),
        [{"x": 9, "y": 11}, {"x": 13, "y": 15}],
    )
    session.commit()

Вывод:

BEGIN (implicit)
UPDATE some_table SET y=? WHERE x=?
[...] [(11, 9), (15, 13)]
COMMIT

Выше мы вызвали выражение UPDATE, используя связанный параметр, стиль выполнения “executemany”, представленный в разделе “Отправка нескольких параметров”, завершая блок фиксацией “commit as you go”.

Session фактически не удерживает объект Connection после завершения транзакции. Он получает новый Connection из Engine при следующей необходимости выполнения SQL в базе данных.

У Session, очевидно, есть гораздо больше трюков в рукаве, но понимание того, что у него есть метод Session.execute(), который используется так же, как Connection.execute(), позволит нам начать работу с примерами, которые следуют далее.

⬅️ Назад Вперед ➡️