Python SQLite upsert: make a replay update explicit

Operation contract

The owned connection stores a receipt ID and amount. Repeating the same ID updates its amount rather than adding a second row, so the printed row count remains one. The conflict target is a primary key; a different constraint collision would not be handled by this clause. The fixture uses a transaction but makes no claim about remote event delivery.

Failure and ownership boundary

An upsert is not automatically idempotent. If a retry carries a different amount, the second write changes state, as this fixture shows. Define whether replays must be identical or rejected before using this pattern for payments. Python SQLite job project: reject conflicting replays by request identity, Python sqlite3 transactions: bind values and roll back failed batches and Python SQLite UNIQUE with NULL: an absent key can repeat should be read together.

Working program

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE receipt(id TEXT PRIMARY KEY, minor INTEGER NOT NULL CHECK(minor >= 0))")
def store(identifier, minor):
    with connection:
        connection.execute(
            "INSERT INTO receipt(id, minor) VALUES (?, ?) "
            "ON CONFLICT(id) DO UPDATE SET minor=excluded.minor",
            (identifier, minor),
        )

store("R41", 125)
store("R41", 150)
print(connection.execute("SELECT id, minor FROM receipt").fetchall())
connection.close()

Output

[('R41', 150)]

Costs and limits

With an indexed key, lookup and update cost depend on the database index and storage engine state. Two small in-memory writes do not establish throughput, durability or cross-process ordering.

Common Mistakes

• Upsert can overwrite different replay data.
• Name the intended conflict target and validate the payload before writing.

Connected lessons

Python SQLite job project: reject conflicting replays by request identity, Python sqlite3 transactions: bind values and roll back failed batches, Python SQLite UNIQUE with NULL: an absent key can repeat.