SQLite transactions in Python

Grouping writes so they all commit or all roll back.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE accounts (name TEXT, cents INTEGER)")
conn.executemany("INSERT INTO accounts VALUES(?, ?)",
                 [("ada", 1000), ("grace", 0)])
conn.commit()

try:
    with conn:  # commits on success, rolls back on any exception
        conn.execute("UPDATE accounts SET cents = cents - 500 WHERE name=?",
                     ("ada",))
        conn.execute("UPDATE accounts SET cents = cents + 500 WHERE name=?",
                     ("grace",))
except sqlite3.Error:
    print("transfer rolled back")
conn.close()

How it works

1. with conn: opens a transaction block.
2. Both updates commit together on success.
3. Any exception rolls the whole transfer back.

Keywords and builtins used here

• except
• print
• try
• with

The run, in numbers

Lines

17

Characters to type

507

Tokens

103

Three-star pace

110 tpm

At the three-star pace of 110 tokens a minute, this run takes about 56 seconds.

Type this snippet

Step 3 of 3 in Databases, step 16 of 41 in Domain tools.

← Previous Next →