SQLite Basics: Update Part of a BLOB with Python

For a large binary value, replacing the entire value may be unnecessary when only a small byte range changes. Python exposes a file-like BLOB handle for fixed-size reads and writes. This tiny fixture makes byte offsets easy to inspect.

Prepare a scratch directory

Tested on Linux with CPython 3.12.14 and SQLite 3.53.1. You need Python with SQLite support, Bash, a text editor, and standard mktemp, rm, and rmdir utilities. No third-party Python packages are required. Run Python without -O so the checks remain enabled.

Keep the same terminal open. Stop if setup fails, before saving the source:

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Run the example

Save this as demonstrate-incremental-blob.py:

import sqlite3

connection = sqlite3.connect(":memory:")
try:
    connection.execute("CREATE TABLE payloads (id INTEGER PRIMARY KEY, data BLOB)")
    connection.execute("INSERT INTO payloads VALUES (1, zeroblob(8))")
    connection.commit()

    with connection:
        with connection.blobopen("payloads", "data", 1) as blob:
            assert len(blob) == 8
            blob.write(b"HEAD")
            blob.seek(4)
            blob.write(b"tail")
            blob[0:4] = b"DATA"
            blob.seek(0)
            assert blob.read() == b"DATAtail"
            print("Length:", len(blob))
            print("Updated bytes:", b"DATAtail".decode("ascii"))
            blob.seek(8)
            try:
                blob.write(b"!")
            except ValueError:
                print("Extension rejected: ValueError")
            else:
                raise AssertionError("A BLOB handle must not extend the value")

    saved = connection.execute("SELECT data FROM payloads WHERE id = 1").fetchone()[0]
    assert saved == b"DATAtail"
    print("Stored hex:", saved.hex())
finally:
    connection.close()

Run it:

python3 -B demonstrate-incremental-blob.py

Observed output:

Length: 8
Updated bytes: DATAtail
Extension rejected: ValueError
Stored hex: 444154417461696c

Read the result

The first writes fill the eight allocated bytes. Slice assignment replaces the four-byte prefix without changing the size. Trying to write beyond the end raises the expected ValueError. A normal SQL query after closing the handle verifies the final binary value.

Apply it carefully

blobopen() requires Python 3.11 or newer. The handle addresses a row by its integer rowid and cannot open a WITHOUT ROWID table. Allocate the intended length first, for example with zeroblob(); this interface does not resize values. Offsets are bytes, not Unicode characters. Close the handle before completing the transaction, as shown. The in-memory fixture tests API behavior, not file persistence or concurrent modification.

The official reference documents the API behavior. These checks exercise the controlled fixture above.

Clean up

Remove the saved source, return to the starting directory, then remove the empty practice directory:

rm -- demonstrate-incremental-blob.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

If the directory is not empty, inspect its contents before removing anything else.

Acknowledgements

This post was created with assistance from AI.