Back
+20 XP
Concept

Storing Raw Binary BLOBs in SQLite

SQLite natively supports arbitrary binary data using the `BLOB` (Binary Large Object) data type. Wrap raw Python bytes in `sqlite3.Binary(data)` or pass `bytes` directly 📦.

python
import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE files (name TEXT, payload BLOB)")

# Binary PNG magic header bytes:
png_header = b"\x89PNG\r\n\x1a\n"
conn.execute("INSERT INTO files VALUES (?, ?)", ("logo.png", sqlite3.Binary(png_header)))

blob_out = conn.execute("SELECT payload FROM files").fetchone()[0]
print(f"Retrieved BLOB: {blob_out} 📦")
conn.close()

# 21. Storing Raw Binary BLOBs in SQLite SQLite natively supports arbitrary binary data using the `BLOB` (Binary Large Object) data type. Wrap raw Python bytes in `sqlite3.Binary(data)` or pass `bytes` directly 📦. ```python import sqlite3 conn = sqlite3.connect(":memory:") conn.execute("CREATE TABLE files (name TEXT, payload BLOB)") # Binary PNG magic header bytes: png_header = b"\x89PNG\r\n\x1a\n" conn.execute("INSERT INTO files VALUES (?, ?)", ("logo.png", sqlite3.Binary(png_header))) blob_out = conn.execute("SELECT payload FROM files").fetchone()[0] print(f"Retrieved BLOB: {blob_out} 📦") conn.close() ``` Never store raw binary bytes inside `TEXT` columns; doing so causes UTF-8 decoding crashes on arbitrary byte combinations! 📦 --- ### Question Which SQL column type stores raw binary image bytes, encryption keys, or compressed archives in SQLite? 🧠 - [ ] a. `BLOB` (Binary Large Object) 📦 - [ ] b. `TEXT` - [ ] c. `VARCHAR` - [ ] d. `RAW_STREAM` --- ### Challenge Create table `keys (name TEXT, secret BLOB)` in `:memory:`. Insert `('api_token', b'\xDE\xAD\xBE\xEF')`. Fetch row and print whether retrieved secret equals `b'\xDE\xAD\xBE\xEF'`. 🎯 <details> <summary>Solution</summary> ```python import sqlite3 conn = sqlite3.connect(":memory:") conn.execute("CREATE TABLE keys (name TEXT, secret BLOB)") raw_secret = b"\xDE\xAD\xBE\xEF" conn.execute("INSERT INTO keys VALUES (?, ?)", ("api_token", sqlite3.Binary(raw_secret))) res = conn.execute("SELECT secret FROM keys WHERE name='api_token'").fetchone()[0] print(res == raw_secret) conn.close() ``` </details>

Checkpoint

Which SQL column type stores raw binary image bytes, encryption keys, or compressed archives in SQLite? 🧠

Practice

Create table `keys (name TEXT, secret BLOB)` in `:memory:`. Insert `('api_token', b'\xDE\xAD\xBE\xEF')`. Fetch row and print whether retrieved secret equals `b'\xDE\xAD\xBE\xEF'`. 🎯

Terminal
01
Tap code blocks below to assemble
Available Blocks