Store and retrieve a BLOB fixture
Also published in our primary archive.
A BLOB is SQLite’s binary value type. Python’s built-in sqlite3 module binds a bytes object as a BLOB and returns BLOB values as bytes by default. This makes a short byte sequence a useful fixture when an example needs to preserve non-text values such as a zero byte, a non-ASCII byte, and a newline. The Python sqlite3 type mapping documents both directions.
The example creates an in-memory database, so it creates no fixture file. It inserts b"\x00Batu\xff\n" with a qmark placeholder, then selects the row with its integer key. The first assertion checks byte-for-byte equality; the second confirms the fetched Python value is bytes. The printed hexadecimal form is deterministic: it exposes every stored byte without relying on terminal handling of binary data. bytes=7 is the fixture length, and 0042617475ff0a is its exact encoding.
This uses no newer sqlite3 API; ordinary bytes BLOB binding is available throughout supported Python 3 releases. It is deliberately a small fixture: fetching a BLOB this way materializes the complete value in Python memory. For very large existing BLOBs, Python 3.11 added Connection.blobopen(), whose handle cannot change a BLOB’s size. The equality assertion verifies this one insert-and-read round trip, not durability, encryption, or an application’s broader binary-format rules.
AI assistance disclosure: Batu Lab Notes used AI assistance to draft this article; verify behavior in the Python and SQLite versions you deploy.
import sqlite3
con = sqlite3.connect(":memory:")
fixture = b"\x00Batu\xff\n"
con.execute("CREATE TABLE fixture (id INTEGER PRIMARY KEY, payload BLOB NOT NULL)")
con.execute("INSERT INTO fixture(payload) VALUES (?)", (fixture,))
stored = con.execute(
"SELECT payload FROM fixture WHERE id = ?", (1,)
).fetchone()[0]
assert stored == fixture
assert isinstance(stored, bytes)
print(f"bytes={len(stored)} hex={stored.hex()}")
con.close()bytes=7 hex=0042617475ff0a