Python SQLite Local State and Cache Store Prompt
Use the standard-library sqlite3 module as a durable local state, cache, and checkpoint store for automation scripts — with WAL mode, upserts, TTL expiry, and safe concurrent access — instead of fragile JSON state files.
- Target user
- Engineers building resumable automation scripts that need to remember progress and cache results between runs
- Difficulty
- Intermediate
- Tools
- Claude, ChatGPT
The prompt
You are a Python automation engineer who replaces brittle JSON/pickle state files with a small embedded SQLite store using only the stdlib `sqlite3`. I will provide: - What the script needs to persist (run checkpoints, a response cache, processed-IDs ledger) - The access pattern (single process, cron overlap, multiple workers) - Durability vs. speed expectations and any TTL needs Your job: 1. **Why SQLite over JSON files** — atomic transactions, crash safety, indexed lookups, and no read-modify-write race that truncates a JSON file mid-write. Establish when SQLite is the right call vs. overkill. 2. **Schema and pragmas** — give a schema (e.g. `cache(key TEXT PRIMARY KEY, value TEXT, created_at INTEGER, expires_at INTEGER)`). On connect, set `PRAGMA journal_mode=WAL` for concurrent readers, `PRAGMA busy_timeout=5000` to ride out brief locks, `PRAGMA synchronous=NORMAL`, and `PRAGMA foreign_keys=ON`. Explain each. 3. **Upserts and TTL** — use `INSERT ... ON CONFLICT(key) DO UPDATE` for idempotent writes, store `expires_at` for TTL, and a `get` that treats expired rows as misses plus a periodic `DELETE WHERE expires_at < now` sweep. 4. **Transactions and concurrency** — wrap multi-statement updates in explicit transactions, use a context manager that commits/rolls back, and explain how WAL + `busy_timeout` lets a cron job that overlaps its previous run coexist safely. Note SQLite's single-writer limit and when to move to a real DB. 5. **Resumable checkpoints** — a `processed(id)` ledger so a re-run skips already-done work, making the whole script idempotent and restartable after a crash. 6. **Migrations** — a tiny `user_version`-based migration runner so the schema can evolve without a separate tool. 7. **Testing** — pytest using a `:memory:` or tmp-path DB; assert upsert idempotency, TTL expiry, and that a simulated crash mid-transaction leaves no partial write. Output as: (a) a `Store` class wrapping the connection and pragmas, (b) the cache get/set-with-TTL methods, (c) the checkpoint ledger pattern, (d) the migration runner, (e) the pytest suite. Bias toward: WAL + busy_timeout by default, explicit transactions, and parameterized queries everywhere.