sqlite3.Blob edits an existing fixed-size value; it cannot grow the column during a write.
Make this comfortable
Python SQLite blob handles: preallocate bytes and keep the write inside a transaction
Operation contract
The ledger reserves six bytes for receipt 47, writes its six-byte identifier through a blob handle, and checks that a seventh byte is rejected. The handle closes before the transaction commits. A fresh query observes exactly the accepted bytes.
Failure boundary
blobopen requires an existing row and does not work with a WITHOUT ROWID table. A blob handle is tied to its connection and row; close it before later row changes or connection teardown. This small fixture does not prove file-system crash durability or concurrent writer behavior.
Working program
import sqlite3
connection = sqlite3.connect(":memory:", isolation_level=None)
try:
connection.execute("CREATE TABLE receipts (receipt_id INTEGER PRIMARY KEY, code BLOB NOT NULL)")
connection.execute("BEGIN")
try:
connection.execute("INSERT INTO receipts VALUES (?, zeroblob(?))", (47, 6))
with connection.blobopen("receipts", "code", 47) as receipt_blob:
receipt_blob.write(b"R-0047")
try:
receipt_blob.write(b"!")
except ValueError:
print("overflow_rejected", True)
connection.execute("COMMIT")
except Exception:
connection.execute("ROLLBACK")
raise
stored_code = connection.execute(
"SELECT code FROM receipts WHERE receipt_id = ?", (47,)
).fetchone()[0]
print("stored", stored_code)
finally:
connection.close()Output
overflow_rejected True
stored b'R-0047'Costs and limits
Incremental access can avoid holding a large BLOB copy in Python, but SQLite still stores the reserved bytes. A write is bounded by the allocated length; reserving excessive capacity wastes storage.
Common Mistakes
- A blob write cannot extend a six-byte value to seven bytes.
- Keep the blob handle closed before changing its row or closing the connection.
- A successful COMMIT is not a claim about crash or power-loss survival.
Connected lessons
python
sqlite-blob-handle