SQLite Bound Writes and Atomic Changes

INSERT creates records, UPDATE changes matching records and DELETE removes matching records. A WHERE clause decides which rows an UPDATE or DELETE targets; omitting it can affect every row. Preview a condition with SELECT on a disposable fixture, then verify the changed identities and values. A syntactically valid statement can legitimately match zero rows, so success without an exception is not enough.

Python sqlite3 uses ? placeholders with a separate sequence of values. The one-value tuple is (value,), including its comma. Binding lets an apostrophe or SQL-looking input remain data. Do not build a statement by concatenating raw input. Placeholders represent values rather than arbitrary table or column identifiers; choose any dynamic identifiers from an explicit permitted set.

Transferring stock requires subtracting from one record and adding to another. If the destination does not exist, the subtraction must not remain as a completed transfer. Start one transaction, check the source update’s row count, perform and check the destination update, and commit only after both succeed. On failure, roll back and re-raise the error so the caller does not report a false success.

The function below requires a connection with no pending transaction and a positive integer quantity between distinct items. It rejects booleans as quantities because bool is a subclass of int in Python. The guarded source UPDATE combines existence and availability with the decrement. A zero row count indicates failure; a missing destination also raises an error and restores the original source quantity.

The demonstration starts with quantities 10 and 1. Moving three gives 7 and 4. A later transfer to missing item 99 is rejected and leaves 7 and 4. Test an exact remaining-stock transfer, one unit too many, zero, a negative quantity, the same source/destination and an absent source. Resource cleanup belongs in a finally block.

These examples use the Python sqlite3 transaction behaviour in the tested environment, explicitly BEGIN the transfer, and complete it through the connection. A production API must also define concurrency, retries and transaction ownership. A database transaction’s rollback does not reverse an external email or physical shipment; keep the claimed atomic boundary around the actual database changes. SQLite does not implement TRUNCATE TABLE; DELETE FROM table removes all rows without dropping the schema.

import sqlite3

def transfer_stock(connection, source, destination, quantity):
    if type(quantity) is not int or quantity <= 0 or source == destination:
        raise ValueError('Use a positive integer quantity and distinct items')
    if connection.in_transaction:
        raise ValueError('Finish the previous transaction first')
    connection.execute('BEGIN')
    try:
        cursor = connection.cursor()
        cursor.execute('UPDATE Stock SET Quantity=Quantity-? WHERE ItemID=? AND Quantity>=?',
                       (quantity, source, quantity))
        if cursor.rowcount != 1:
            raise ValueError('Source missing or insufficient stock')
        cursor.execute('UPDATE Stock SET Quantity=Quantity+? WHERE ItemID=?',
                       (quantity, destination))
        if cursor.rowcount != 1:
            raise ValueError('Destination missing')
        connection.commit()
    except Exception:
        connection.rollback()
        raise

connection = sqlite3.connect(':memory:')
try:
    connection.execute('CREATE TABLE Stock (ItemID INTEGER PRIMARY KEY, Quantity INTEGER NOT NULL CHECK(Quantity>=0))')
    connection.executemany('INSERT INTO Stock VALUES (?, ?)', [(1, 10), (2, 1)])
    connection.commit()
    transfer_stock(connection, 1, 2, 3)
    print(connection.execute('SELECT * FROM Stock ORDER BY ItemID').fetchall())
    try:
        transfer_stock(connection, 1, 99, 2)
    except ValueError:
        print('transfer rejected')
    remaining = connection.execute('SELECT * FROM Stock ORDER BY ItemID').fetchall()
    print(remaining)
finally:
    connection.close()

Attempt the task before opening its worked response. Record each change of state, explain the rule used, and check the result against the original requirements. Code examples labelled Python 3 use that language’s conventions; pseudocode states its own assumptions.