В этом материале рассмотрим, как управлять транзакциями PostgreSQL из Python с помощью psycopg2. Транзакция — это единица работы (unit of work), затрагивающая набор операций базы данных.
Подготовка
В большинстве случаев выполнять операцию в базе данных нужно лишь после завершения некой другой операции. Например, в банковской системе перевести сумму со счета А на счет Б можно только после снятия средств со счета А.
В транзакции или все операции выполняются, или не выполняется ни одна из них. Таким образом требуется выполнить все операции в одной транзакции, чтобы она была успешной.
Вот о чем дальше пойдет речь:
- Включение и выключение режима автокомита (автосохранения).
- Транзакции для сохранения изменений в базе данных.
- Поддержка свойства ACID транзакции.
- Откат всех операций в случае неудачи.
- Изменение уровня изоляции транзакции PostgreSQL из Python.
Управление транзакциями psycopg2
Транзакции PostgreSQL обрабатываются объектом соединения. Он отвечает за сохранение изменений или откат в случае неудачи.
С помощью объекта cursor выполняются операции в базе данных. Можно создать неограниченное количество объектов cursor из объекта connection. Если любая из команд объекта cursor завершается неудачно, или же транзакция отменяется, то следующие команды не выполняются вплоть до вызова метода connection.rollback().
- Объект соединения отвечает за остановку транзакции. Это можно сделать с помощью методов
commit()лиrollback(). - После использования метода
commit()изменения сохраняются в базе данных. - С помощью метода
rollback()можно откатить изменения.
Примечание: вызов метода
closе()или любого другого, отвечающего за уничтожение объекта соединения, приводит к неявному вызовуrollback(), вследствие чего все изменения откатываются.
Connection.autocommit
По умолчанию соединение работает в режиме автоматического сохранения, то есть свойство auto-commit равно True. Это значит, что при успешном выполнении запроса изменения немедленно сохраняются в базу данных, а откат становится невозможным.
Для выполнения запросов в транзакции это свойство нужно отключить. Для этого нужно сделать connection.autocommit=False. В этом случае будет возможность откатить выполненный запрос к оригинальному состоянию в случае неудачи.
Синтаксис autocommit:
connection.autocommit=True or FalseсConnection.commit
Если все операции в транзакции завершены, используйте connection.commit() для сохранения изменений в базе данных. Если метод не использовать, то все эффекты взаимодействия с данными не будут применены.
Синтаксис commit:
connection.commit()Connection.rollback
Если в транзакции хотя бы одна операция завершается неудачно, то отменить изменения можно с помощью connection.rollback().
Синтаксис rollback:
connection.rollback()Пример управления транзакциями PostgreSQL из Python
- Отключите режим автосохранения (auto-commit).
- Если все операции были выполнены успешно, используйте
connection.commit()для их сохранения в базе данных. - Если какая-то из операций была завершена неудачно, то откатиться к последнему состоянию можно с помощью
connection.rollback().
Примечание: транзакция остается открытой до явного вызова
commit()илиrollback().
Давайте понизим цену на один телефон и повысим на второй.
import psycopg2
from psycopg2 import Error
try:
# Подключиться к существующей базе данных
connection = psycopg2.connect(user="postgres",
# пароль, который указали при установке PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
connection.autocommit=False
cursor = connection.cursor()
amount = 200
query = """select price from mobile where id = 1"""
cursor.execute(query)
record = cursor.fetchone() [0]
price_a = int(record)
price_a -= amount
# Понизить цену у первого
sql_update_query = """update mobile set price = %s where id = 1"""
cursor.execute(sql_update_query,(price_a,))
query = """select price from mobile where id = 2"""
cursor.execute(query)
record = cursor.fetchone() [0]
price_b = int(record)
price_b += amount
# Повысить цену у второго
sql_update_query = """Update mobile set price = %s where id = 2"""
cursor.execute(sql_update_query, (price_b,))
# совершение транзакции
connection.commit()
print("Транзакция успешно завершена")
except (Exception, psycopg2.DatabaseError) as error :
print ("Ошибка в транзакции. Отмена всех остальных операций транзакции", error)
connection.rollback()
finally:
if connection:
cursor.close()
connection.close()
print("Соединение с PostgreSQL закрыто")
Вы получите следующий вывод после успешного завершения всех операций в транзакции.
Транзакция успешно завершена
Соединение с PostgreSQL закрытоЕсли хотя бы одна из операций будет завершена с ошибкой, то вывод будет таким.
Ошибка в транзакции. Отмена всех остальных операций транзакции
Соединение с PostgreSQL закрытоИнструкция With для управления транзакциями
Для создания транзакции внутри определенного блока в коде используйте инструкцию with.
Что делает инструкция with?Соединения и объекта cursor в psycopg2 — это всего лишь контекстные менеджеры, которые можно использовать с with. Основное преимущество в том, что это позволяет не писать явно commit или rollback.
Синтаксис:
with psycopg2.connect(connection_arguments) as conn:
with conn.cursor() as cursor:
cursor.execute(Query)
Когда соединение выходит из блока with, а запрос выполняется без ошибок или исключений, транзакция сохраняется. Если же в процессе возникла проблема, то транзакция откатывается.
При выходе за пределы блока соединение не закрывается, но объекты cursor и любые другие связанные объекты — да. Соединение можно использовать в более чем одном with, и каждый блок with — это всего лишь транзакция.
В этом примере выполним транзакцию в платформе для онлайн-торговки. Приобретем один товар, уменьшим баланс покупателя и переведем соответствующую сумму на счет компании.
import psycopg2
from psycopg2 import Error
connection = psycopg2.connect(**params)
with connection:
with connection.cursor() as cursor:
# Поиск цены товара
query = """select price from itemstable where itemid = 876"""
cursor.execute(query)
record = cursor.fetchone() [0]
item_price = int(record)
# Получение остатка на счету
query = """select balance from ewallet where userId = 23"""
cursor.execute(query)
record = cursor.fetchone() [0]
ewallet_balance = int(record)
new_ewallet_balance -= item_price
# Обновление баланса с учетом расхода
sql_update_query = """Update ewallet set balance = %s where id = 23"""
cursor.execute(sql_update_query,(new_ewallet_balance,))
# Зачисление на баланс компании
query = """select balance from account where accountId = 2236781258763"""
cursor.execute(query)
record = cursor.fetchone()
account_balance = int(record)
new_account_balance += item_price
# Обновление счета компании
sql_update_query = """Update account set balance = %s where id = 132456"""
cursor.execute(sql_update_query, (new_account_balance,))
print("Транзакция успешно завершена")
Уровни изоляции psycopg2
В системах баз данных с помощью уровней изоляции можно определить какой уровень целостности транзакции будет виден остальным пользователям и системам.
Например, когда пользователь выполняет действие или операцию, которые еще не завершены, подробности о них доступны другим пользователям, которые могут выполнить последовательные действия. Например, потребитель приобретает товар. Информация об этом становится известна другим системам, что нужно для подготовки счета, квитанции и подсчетов скидки, что ускоряет весь процесс.
Если уровень изоляции низкий, множество пользователей может получить доступ к одним и тем же данным одновременно. Однако это же может привести и к некоторым проблемам вплоть до утраченных обновлений. Поэтому нужно учитывать все. Более высокий уровень изоляции может заблокировать других пользователей или транзакции до завершения.
psycopg2.extensions предоставляет следующие уровня изоляции:
- psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT,
- psycopg2.extensions.ISOLATION_LEVEL_READ_UNCOMMITTED,
- psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED,
- psycopg2.extensions.ISOLATION_LEVEL_REPEATABLE_READ,
- psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE,
- psycopg2.extensions.ISOLATION_LEVEL_DEFAULT.
Как задать уровни изоляции
Это можно сделать с помощью класса connection:
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)Также можно использовать метод connection.set_session.
connectio.set_session(isolation_level=None, readonly=None, deferrable=None, autocommit=None)
В этом случае isolation_level могут быть READ UNCOMMITED, REPEATABLE READ, SERIALIZE или другие из списка констант.
