Transactions avec Python sqlite3

Pour tous ceux qui voudraient travailler avec la lib sqlite3 indépendamment de ses défauts, j'ai trouvé que vous pouvez garder un certain contrôle des transactions si vous faites ces deux choses:

1. set Connection.isolation_level = None (selon les documents , cela signifie le mode autocommit)
2. évitez d'utiliser executescript, car selon les documents , Il "émet d'abord une instruction de validation" - c'est-à-dire, un problème. En effet, j'ai trouvé qu'il interfère avec toutes les transactions définies manuellement

Alors, ce qui suit l'adaptation de votre test fonctionne pour moi:

import sqlite3

sql = sqlite3.connect("/tmp/test.db")
sql.isolation_level = None
try:
    c = sql.cursor()
    c.execute("begin")
    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")

Par les documents ,

Les objets de connexion peuvent être utilisés comme gestionnaires de contexte valider ou annuler les transactions. Dans le cas d'une exception, l' la transaction est annulée; sinon, la transaction est validée:

Par conséquent, si vous laissez Python quitter l'instruction with lorsqu'une exception se produit, la transaction sera annulée.

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

Rendements

(99,)

Comme prévu.

Voici ce que je pense qui se passe en fonction de ma lecture des liaisons sqlite3 de Python ainsi que des documents Sqlite3 officiels. La réponse courte est que si vous voulez une transaction correcte, vous devriez vous en tenir à cet idiome:

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

Contrairement à mon intuition, with connectionNe Pas appeler BEGIN en entrant dans la portée. En fait, il ne fait rien du tout dans __enter__. Il n'a d'effet que lorsque vous __exit__ la portée, En choisissant COMMIT ou ROLLBACK selon que le scope sort normalement ou avec une exception .

Par conséquent, la bonne chose à faire est de toujours marquer explicitement le début de vos transactions en utilisant BEGIN. Cela rend isolation_level Non pertinent dans les transactions, car heureusement, il n'a d'effet que lorsque le mode autocommit est activé et que le mode autocommit est toujours supprimé dans les blocs de transaction .

Une autre bizarrerie est executescript, qui émet toujours un COMMIT avant d'exécuter votre script . Cela peut facilement gâcher les transactions, donc votre choix est soit

• utilisez exactement un executescript dans une transaction et rien d'autre, ou
• évitez executescript entièrement; vous pouvez appeler execute autant de fois que vous le souhaitez, sous réserve de l'état-par-execute limitation.

Vous pouvez utiliser la connexion en tant que gestionnaire de contexte. Il annulera ensuite automatiquement les transactions en cas d'exception ou les validera autrement.

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

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

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

Normal .execute() fonctionne comme prévu avec le mode de validation automatique par défaut confortable et le gestionnaire de contexte with conn: ...effectuant une validation automatique ourollback-sauf pour les transactions protégées en lecture - modification-écriture, qui sont expliquées à la fin de cette réponse.

Le module Sqlite3 non standard conn_or_cursor.executescript() ne participe pas au mode de validation automatique (par défaut) (et ne fonctionne donc pas normalement avec le gestionnaire de contexte with conn: ...) mais transmet le script plutôt brut. À cet effet, il juste commet un potentiellement en attente transactions de validation automatique au démarrage , avant de "passer à l'état brut".

Cela signifie également que sans un "BEGIN" à l'intérieur du script executescript() fonctionne sans transaction, et donc pas d'option de restauration en cas d'erreur ou autrement.

Donc, avec executescript(), il vaut mieux utiliser un BEGIN explicite (tout comme votre script de création de schéma inital l'a fait pour l'outil de ligne de commande sqlite en mode "brut"). Et cette interaction montre étape par étape ce qui se passe:

>>> 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,)]
>>> 

Le script n'a pas atteint le "COMMIT". Et ainsi nous pourrions voir l'état intermédiaire actuel et décider de la restauration (ou de la validation néanmoins)

Donc un travail d'essai sauf restauration via excecutescript() ressemble à ceci:

>>> 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,)]
>>> 

(notez la restauration via le script ici, car aucun .execute() n'a pris le contrôle de commit)

Et voici une note sur le mode de validation automatique en combinaison avec la question plus difficile d'une transaction protégée en lecture-modification-écriture - qui a fait dire à @ Jeremie "de toutes les nombreuses choses écrites sur les transactions dans sqlite/python, c'est la seule chose qui me permet de faire ce que je veux (avoir un verrou de lecture Exclusif sur la base de données)." dans un commentaire sur un exemple qui comprenait un c.execute("begin"). Bien que sqlite3 ne fasse normalement pas un verrou de lecture Exclusif à long blocage, sauf pour la durée de la réécriture réelle, mais des verrous à 5 étapes plus intelligents pour obtenir une protection suffisante contre les changements qui se chevauchent.

Le contexte de validation automatique with conn: ne met pas ou ne déclenche pas déjà un verrou suffisamment fort pour une lecture-modification-écriture protégée dans le schéma de verrouillage en 5 étapes de sqlite3 . Un tel verrouillage n'est fait implicitement que lorsque la première commande de modification des données est émise-donc trop tard. Seul un BEGIN (DEFERRED) (TRANSACTION) explicite déclenche le comportement recherché:

La première opération read sur une base de données crée un verrou partagé et la première opération d'écriture crée un verrou réservé.

Donc un protégé la transaction read-modify-write qui utilise le langage de programmation de manière générale (et non une clause de mise à jour SQL atomique spéciale) ressemble à ceci:

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

En cas d'échec, une telle transaction de lecture-modification-écriture pourrait être rejugée plusieurs fois.

C'est un peu vieux thread mais si cela aide, j'ai trouvé que faire une restauration sur l'objet de connexion fait l'affaire.