Теория: Транзакции

При подключении к базе создается объект connection. Этот объект управляет всеми транзакциями в базе. Рассмотрим подробнее как работают соединения в Psycopg2.

По умолчанию, когда первая команда отправляется в базу данных, с помощью cursor, создается новая транзакция. Все последующие запросы в базу данных будут выполняться в контексте той же транзакции. И тут не только запросы первого курсора, но и запросы всех курсоров в рамках того же соединения. Если какая-либо команда завершится неудачно, транзакция будет прервана, и никакие дальнейшие команды не будут выполняться до вызова метода rollback().

# здесь транзакция еще не началась
curs = conn.cursor()

# здесь исполняется запрос, теперь транзакция началась
curs.execute("SELECT count(*) FROM table")

curs_2 = conn.cursor()
# новый запрос исполняется в той же транзакции
curs_2.execute("INSERT INTO data VALUES (%s)", ("Hello",))

# так как не было commit(), то вставка INSERT выше будет отменена
conn.close()

Соединение отвечает за завершение своей транзакции, вызывая метод commit() или rollback(). Все изменения немедленно становятся постоянными в базе данных. Если соединение закрывается с помощью метода close() или же уничтожается после выхода из области видимости во время выполнения транзакции, то сервер отменит транзакцию.

conn = psycopg2.connect("postgresql://user:password@host:port/hexlet_test")
sql = "INSERT INTO users (username, phone) VALUES ('tommy', '123456789');"

curs = conn.cursor()
curs.execute(sql)

# Никакие изменения не сохраняются, пока не вызван commit().
conn.commit()
conn.close()

Коммиты

По умолчанию даже простой SELECT начинает транзакцию. А значит, пока мы не закоммитим действия, то сессия соединения так и останется в ожидании - "idle in transaction". Новички часто совершают подобную ошибку, недоумевая почему после запроса программа перестает отвечать. Ведь в режиме транзакции сессия удерживает блокировки на таблицу. Потому важно сохранять коммитом все изменения в базе, либо можно установить параметр соединения autocommit, тогда все запросы будут сохраняться автоматически.