Транзакции с Python sqlite3

Python DB API старается быть умным и начинает и фиксирует транзакции автоматически. .

Я бы рекомендовал использовать драйвер БД, который не использует Python DB API, например apsw .

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

1. установить Connection.isolation_level = None (согласно документам, это означает автоматическую фиксацию Режим)
2. вообще избегайте использования executescript, потому что согласно документам он "сначала выдает оператор COMMIT" - т.е. проблема. Действительно, я обнаружил, что это мешает любым установленным вручную транзакциям.

Итак, следующая адаптация вашего теста работает для меня:

import sqlite3

sql = sqlite3.connect("/tmp/test.db")
sql.isolation_level = None
c = sql.cursor()
c.execute("begin")
try:
    c.execute("update test set i = 1")
    c.execute("fnord")
    c.execute("update test set i = 0")
    c.execute("commit")
except sql.Error:
    print("failed!")
    c.execute("rollback")

Согласно документам,

Объекты подключения можно использовать в качестве менеджеров контекста, которые автоматически фиксируют или откатывают транзакции. В случае исключения транзакция откатывается; в противном случае транзакция фиксируется:

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

import sqlite3

filename = '/tmp/test.db'
with sqlite3.connect(filename) as conn:
    cursor = conn.cursor()
    sqls = [
        'DROP TABLE IF EXISTS test',
        'CREATE TABLE test (i integer)',
        'INSERT INTO "test" VALUES(99)',]
    for sql in sqls:
        cursor.execute(sql)
try:
    with sqlite3.connect(filename) as conn:
        cursor = conn.cursor()
        sqls = [
            'update test set i = 1',
            'fnord',   # <-- trigger error
            'update test set i = 0',]
        for sql in sqls:
            cursor.execute(sql)
except sqlite3.OperationalError as err:
    print(err)
    # near "fnord": syntax error
with sqlite3.connect(filename) as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM test')
    for row in cursor:
        print(row)
        # (99,)

урожаи

(99,)

как и ожидалось.

Вот что, по моему мнению, происходит, основываясь на моем чтении привязок Python sqlite3, а также официальных документов Sqlite3. Короткий ответ: если вы хотите провести надлежащую транзакцию, вам следует придерживаться этой идиомы:

with connection:
    db.execute("BEGIN")
    # do other things, but do NOT use 'executescript'

Вопреки моей интуиции, with connection не вызывает BEGIN при входе в область видимости. На самом деле он вообще ничего не делает в __enter__. Это имеет эффект только при __exit__ области видимости, выбрав либо COMMIT, либо ROLLBACK в зависимости от того, завершается ли область нормально или с исключением.

Поэтому правильнее всего будет всегда явно отмечать начало ваших транзакционных with connection блоков с помощью BEGIN. Это делает isolation_level неуместным внутри блока, потому что, к счастью, это имеет эффект только тогда, когда режим автоматической фиксации включен, а режим автоматической фиксации включен всегда подавляется в блоках транзакций.

Еще одна особенность — executescript, которая всегда выдает COMMIT перед запуском скрипта. . Это может легко испортить транзакционный блок with connection, поэтому вы можете либо

Обычный .execute() работает, как и ожидалось, с удобным режимом автоматической фиксации по умолчанию и диспетчером контекста with conn: ..., выполняющим автоматическую фиксацию ИЛИ отката, за исключением защищенных транзакций чтения-модификации-записи, которые объясняются в конце этого ответа.

Нестандартный conn_or_cursor.executescript() модуля sqlite3 не участвует в (по умолчанию) режиме автоматической фиксации (и поэтому не работает нормально с менеджером контекста with conn: ...), но пересылает сценарий довольно сырым. Поэтому он просто фиксирует потенциально ожидающие транзакции с автоматической фиксацией в начале, прежде чем «перейти к необработанным».

Это также означает, что без «НАЧАЛА» внутри скрипта executescript() работает без транзакции и, следовательно, без возможности отката в случае ошибки или иным образом.

Поэтому с executescript() нам лучше использовать явное НАЧАЛО (так же, как ваш начальный сценарий создания схемы делал для инструмента командной строки sqlite режима «сырой»). И это взаимодействие шаг за шагом показывает, что происходит:

>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> conn.executescript("BEGIN; UPDATE TEST SET i = 1; FNORD; COMMIT""")
Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
OperationalError: near "FNORD": syntax error
>>> list(conn.execute('SELECT * FROM test'))
[(1,)]
>>> conn.rollback()
>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> 

Сценарий не достиг "COMMIT". И, таким образом, мы могли бы просмотреть текущее промежуточное состояние и принять решение об откате (или, тем не менее, зафиксировать)

Таким образом, рабочий try-except-rollback через excecutescript() выглядит так:

>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> try: conn.executescript("BEGIN; UPDATE TEST SET i = 1; FNORD; COMMIT""")
... except Exception as ev: 
...     print("Error in executescript (%s). Rolling back" % ev)
...     conn.executescript('ROLLBACK')
... 
Error in executescript (near "FNORD": syntax error). Rolling back
<sqlite3.Cursor object at 0x011F56E0>
>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> 

(Обратите внимание на откат через скрипт здесь, потому что ни один .execute() не взял на себя управление фиксацией)

А здесь примечание о режиме автоматической фиксации в сочетании с более сложной проблемой защищенной транзакции чтения-модификации-записи, что заставило @Jeremie сказать: «Из всех многих, многих то, что написано о транзакциях в sqlite/python, это единственное, что позволяет мне делать то, что я хочу (иметь монопольную блокировку чтения в базе данных)" в комментарии к примеру, который включал c.execute("begin"). Хотя sqlite3 обычно не делает длинную блокировку эксклюзивной блокировки чтения, за исключением продолжительности фактической обратной записи, но более умные 5-этапные блокировки для обеспечения достаточной защиты от перекрывающихся изменений.

Контекст автоматической фиксации with conn: еще не устанавливает или не активирует блокировку, достаточно сильную для защищенного чтения-изменения-записи в 5. -этапная схема блокировки sqlite3. Такая блокировка выполняется неявно только тогда, когда выдается первая команда модификации данных - поэтому слишком поздно. Только явный BEGIN (DEFERRED) (TRANSACTION) вызывает желаемое поведение:

Первая операция чтения базы данных создает блокировку SHARED, а первая операция записи создает блокировку RESERVED. .

Таким образом, защищенная транзакция чтения-модификации-записи, использующая язык программирования в общем виде (а не специальное предложение атомарного SQL UPDATE), выглядит следующим образом:

with conn:
    conn.execute('BEGIN TRANSACTION')    # crucial !
    v = conn.execute('SELECT * FROM test').fetchone()[0]
    v = v + 1
    time.sleep(3)  # no read lock in effect, but only one concurrent modify succeeds
    conn.execute('UPDATE test SET i=?', (v,))

В случае сбоя такая транзакция чтения-изменения-записи может быть повторена несколько раз.

Вы можете использовать соединение в качестве менеджера контекста. Затем он автоматически откатывает транзакции в случае исключения или фиксирует их в противном случае.

try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))

except sqlite3.IntegrityError:
    print("couldn't add Joe twice")

См. https://docs.python.org/3/library/sqlite3.html#using-the-connection-as-a-context-manager

Это немного старая тема, но если это поможет, я обнаружил, что откат объекта соединения помогает.