{"course":{"slug":"python-for-ai","title":"Python, from Zero, for AI","url":"https://addaly.com/learn/python-for-ai","level":"beginner","track":"build"},"lesson":{"slug":"a-sqlite-response-cache","title":"A response cache on sqlite: the re-run that costs nothing","url":"https://addaly.com/learn/python-for-ai/a-sqlite-response-cache","position":82,"of":89,"minutes":9,"answer":"Key the cache on a hash of everything that changes the answer (model, messages, parameters) serialised with sort_keys so the same request always hashes the same; sqlite3 is in the standard library, survives restarts.","keyIdea":"Key the cache on a hash of everything that changes the answer (model, messages, parameters) serialised with sort_keys so the same request always hashes the same; sqlite3 is in the standard library, survives restarts, and makes a twenty-times-rerun script free after the first."},"body":{"format":"markdown","text":"## Why a disk cache\n\nModule 7's `lru_cache` lives in memory and dies with the process. During development you run the same script twenty times an afternoon, and each run sends the same forty prompts to a paid model. Twenty runs of forty calls is eight hundred charged requests to check whether your CSV parser handles a comma. A cache that survives restarts makes runs two to twenty free, and makes a test suite that hits a real model affordable to run on every commit.\n\n`sqlite3` is in the standard library, needs no server, stores everything in one file, and handles a million rows without noticing. It is the right tool for this.\n\n## The key\n\nThe cache must return a stored answer only for a request that is *the same in every way that matters*. Model, messages, temperature, max tokens, any tool definitions: all of it. Serialise the whole request and hash it:\n\n```python\nimport hashlib, json\n\ndef cache_key(request: dict) -> str:\n    canonical = json.dumps(request, sort_keys=True, ensure_ascii=False, separators=(\",\", \":\"))\n    return hashlib.sha256(canonical.encode(\"utf-8\")).hexdigest()\n```\n\nThree details carry the correctness.\n\n`sort_keys=True`. Two dicts with the same keys in a different order are equal in Python and serialise to different strings without it. A request built as `{\"model\": ..., \"messages\": ...}` in one place and `{\"messages\": ..., \"model\": ...}` in another would miss the cache every time, and you would never know why the hit rate was low.\n\n`separators=(\",\", \":\")` removes the spaces `json.dumps` inserts by default, so formatting choices cannot change the key either.\n\n`sha256` rather than Python's built-in `hash()`. `hash(\"abc\")` is randomised per process for security reasons, so it differs between runs: the opposite of what a disk cache needs. `hashlib` is stable across runs, machines and years.\n\nInclude the model name and every parameter, even the ones you never change. The day you change one, the cache must miss.\n\n## The table\n\n```python\nimport sqlite3, time\n\n\nclass ResponseCache:\n    def __init__(self, path=\"cache.sqlite\"):\n        self.conn = sqlite3.connect(path)\n        self.conn.execute(\"\"\"\n            CREATE TABLE IF NOT EXISTS responses (\n                key TEXT PRIMARY KEY,\n                request TEXT NOT NULL,\n                response TEXT NOT NULL,\n                created REAL NOT NULL\n            )\"\"\")\n\n    def get(self, key):\n        row = self.conn.execute(\n            \"SELECT response FROM responses WHERE key = ?\",\n            (key,),\n        ).fetchone()\n        return json.loads(row[0]) if row else None\n\n    def put(self, key, request, response):\n        with self.conn:\n            self.conn.execute(\n                (\n                    \"INSERT OR REPLACE INTO \"\n                    \"responses VALUES (?, ?, \"\n                    \"?, ?)\"\n                ),\n                (\n                    key,\n                    json.dumps(request, sort_keys=True),\n                    json.dumps(response),\n                    time.time(),\n                ),\n            )\n```\n\n`CREATE TABLE IF NOT EXISTS` makes the first run and every later run identical. `PRIMARY KEY` on `key` gives an index, so lookups are instant at any size. Storing the `request` alongside the response costs a little space and makes the cache inspectable: `SELECT request FROM responses LIMIT 5` shows what was asked, which is how you find out that the prompt you thought you were sending is not the one you were sending.\n\nThe `?` placeholders are not optional. Building the SQL with an f-string works until a prompt contains a quote mark, and then it either breaks or, if the text is hostile, does something else, the same lesson as the previous lesson's injection, with a database instead of a model. `with self.conn:` commits on success and rolls back on error, from module 7's context-manager lesson.\n\n## Wrapping the call\n\n```python\ndef complete_cached(provider, cache, request):\n    key = cache_key(request)\n    hit = cache.get(key)\n    if hit is not None:\n        return hit\n    response = provider.complete_raw(request)\n    cache.put(key, request, response)\n    return response\n```\n\nOr as a decorator from module 7, if every call goes through one function. Log hits and misses at `DEBUG`; a hit rate near zero after the first run means the key is wrong.\n\n## What not to cache\n\n**Failures.** An exception or an empty reply must not be stored, or one transient 503 becomes a permanent wrong answer for that prompt. Only `put` after validating the response.\n\n**Anything sampled on purpose.** At temperature 0.8 you asked for variety; a cache returns the first draw forever. Either exclude such calls, or include a `run_id` in the request so each run gets its own entries.\n\n**Anything time-dependent.** A prompt containing today's date is a different prompt tomorrow, and a prompt that *should* contain it but does not will be cached with a stale answer. If the answer depends on when it was asked, the when must be in the key.\n\n## Expiry and size\n\nA `created` column allows `DELETE FROM responses WHERE created < ?` for entries older than a month. `SELECT page_count * page_size FROM pragma_page_count(), pragma_page_size()` reports the database file's size in bytes; a cache of ten thousand responses is a few tens of megabytes. `VACUUM` reclaims space after deleting.\n\n## Threads and the connection\n\nA `sqlite3` connection may not be shared across threads by default. Open one per thread, or pass `check_same_thread=False` and guard writes with a lock. For the thread pool from module 6, one connection per worker is the simple and correct answer. Concurrent *processes* writing to the same file work (sqlite locks the file) but will occasionally raise \"database is locked\"; a `timeout=30` argument to `connect` makes them wait rather than fail.\n\n## Alternatives\n\n`shelve` is a dict-on-disk in the standard library. Since Python 3.13 it stores new shelves in SQLite by default and copes with many thousands of entries, but it is unsafe with several writers at once, and its values are pickled, so never open a shelf from an untrusted source. `diskcache` (free) is a polished library doing what this lesson does with expiry and size limits built in. For sharing a cache between machines, Redis. The hand-built version is the one to understand first, because when any of the others misbehaves, the question is always \"what is the key?\"\n\n## Try this now\n\nBuild `ResponseCache`, wrap your provider, run a script of twenty prompts twice, and confirm the second run makes no network calls. Then change `max_tokens` and confirm every call misses. Finally build a request dict with keys in two orders and check `cache_key` agrees."},"check":{"question":"A cache keys each call on `hashlib.sha256(json.dumps(request).encode()).hexdigest()`. Two runs of the same script with identical prompts produce different keys for about a third of the calls, so the cache misses. What is the most likely cause?","options":[{"text":"SHA-256 includes a random salt on each call to defend against rainbow tables, so the same input never hashes the same twice within a process, let alone across runs.","correct":false,"tempting":"SHA-256 is deterministic; identical bytes always produce identical digests, which is the whole reason it works as a key."},{"text":"Python's string hashing is randomised per process, so `hexdigest()` differs between runs of the same program.","correct":false,"tempting":"Hash randomisation affects `hash()` on str, not `hashlib`, whose output is stable across processes and machines."},{"text":"The sqlite database was opened without a transaction, so keys written in the first run were never committed.","correct":false,"tempting":"Uncommitted writes would cause misses on every call, not a third, and would not change the computed key."},{"text":"`json.dumps` keeps insertion order, and the request dicts were built with keys in varying order, so equal requests serialised differently.","correct":true}]},"prev":{"slug":"prompt-templates-safely","title":"Prompt templates: building a prompt from parts without letting the parts rewrite it","url":"https://addaly.com/learn/python-for-ai/prompt-templates-safely"},"next":{"slug":"chunking-text","title":"Chunking: splitting a document that does not fit, without cutting a sentence in half","url":"https://addaly.com/learn/python-for-ai/chunking-text"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Source and assessment review continues. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'A response cache on sqlite: the re-run that costs nothing', in Python, from Zero, for AI. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/python-for-ai/a-sqlite-response-cache (Accessed: 21 November 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"A response cache on sqlite: the re-run that costs nothing","courseTitle":"Python, from Zero, for AI","module":{"index":9,"slug":"building-the-thing","title":"Building the thing: a small AI program, end to end"},"answer":null,"keyIdea":"Key the cache on a hash of everything that changes the answer (model, messages, parameters) serialised with sort_keys so the same request always hashes the same; sqlite3 is in the standard library, survives restarts, and makes a twenty-times-rerun script free after the first.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"why-a-disk-cache","runs":[{"text":"Why a disk cache"}]},{"t":"p","runs":[{"text":"Module 7’s "},{"text":"lru_cache","code":true},{"text":" lives in memory and dies with the process. During development you run the same script twenty times an afternoon, and each run sends the same forty prompts to a paid model. Twenty runs of forty calls is eight hundred charged requests to check whether your CSV parser handles a comma. A cache that survives restarts makes runs two to twenty free, and makes a test suite that hits a real model affordable to run on every commit."}]},{"t":"p","runs":[{"text":"sqlite3","code":true},{"text":" is in the standard library, needs no server, stores everything in one file, and handles a million rows without noticing. It is the right tool for this."}]},{"t":"h2","id":"the-key","runs":[{"text":"The key"}]},{"t":"p","runs":[{"text":"The cache must return a stored answer only for a request that is "},{"text":"the same in every way that matters","i":true},{"text":". Model, messages, temperature, max tokens, any tool definitions: all of it. Serialise the whole request and hash it:"}]},{"t":"code","lang":"python","label":"Python","text":"import hashlib, json\n\ndef cache_key(request: dict) -> str:\n    canonical = json.dumps(request, sort_keys=True, ensure_ascii=False, separators=(\",\", \":\"))\n    return hashlib.sha256(canonical.encode(\"utf-8\")).hexdigest()"},{"t":"p","runs":[{"text":"Three details carry the correctness."}]},{"t":"p","runs":[{"text":"sort_keys=True","code":true},{"text":". Two dicts with the same keys in a different order are equal in Python and serialise to different strings without it. A request built as "},{"text":"{\"model\": ..., \"messages\": ...}","code":true},{"text":" in one place and "},{"text":"{\"messages\": ..., \"model\": ...}","code":true},{"text":" in another would miss the cache every time, and you would never know why the hit rate was low."}]},{"t":"p","runs":[{"text":"separators=(\",\", \":\")","code":true},{"text":" removes the spaces "},{"text":"json.dumps","code":true},{"text":" inserts by default, so formatting choices cannot change the key either."}]},{"t":"p","runs":[{"text":"sha256","code":true},{"text":" rather than Python’s built-in "},{"text":"hash()","code":true},{"text":". "},{"text":"hash(\"abc\")","code":true},{"text":" is randomised per process for security reasons, so it differs between runs: the opposite of what a disk cache needs. "},{"text":"hashlib","code":true},{"text":" is stable across runs, machines and years."}]},{"t":"p","runs":[{"text":"Include the model name and every parameter, even the ones you never change. The day you change one, the cache must miss."}]},{"t":"h2","id":"the-table","runs":[{"text":"The table"}]},{"t":"code","lang":"python","label":"Python","text":"import sqlite3, time\n\n\nclass ResponseCache:\n    def __init__(self, path=\"cache.sqlite\"):\n        self.conn = sqlite3.connect(path)\n        self.conn.execute(\"\"\"\n            CREATE TABLE IF NOT EXISTS responses (\n                key TEXT PRIMARY KEY,\n                request TEXT NOT NULL,\n                response TEXT NOT NULL,\n                created REAL NOT NULL\n            )\"\"\")\n\n    def get(self, key):\n        row = self.conn.execute(\n            \"SELECT response FROM responses WHERE key = ?\",\n            (key,),\n        ).fetchone()\n        return json.loads(row[0]) if row else None\n\n    def put(self, key, request, response):\n        with self.conn:\n            self.conn.execute(\n                (\n                    \"INSERT OR REPLACE INTO \"\n                    \"responses VALUES (?, ?, \"\n                    \"?, ?)\"\n                ),\n                (\n                    key,\n                    json.dumps(request, sort_keys=True),\n                    json.dumps(response),\n                    time.time(),\n                ),\n            )"},{"t":"p","runs":[{"text":"CREATE TABLE IF NOT EXISTS","code":true},{"text":" makes the first run and every later run identical. "},{"text":"PRIMARY KEY","code":true},{"text":" on "},{"text":"key","code":true},{"text":" gives an index, so lookups are instant at any size. Storing the "},{"text":"request","code":true},{"text":" alongside the response costs a little space and makes the cache inspectable: "},{"text":"SELECT request FROM responses LIMIT 5","code":true},{"text":" shows what was asked, which is how you find out that the prompt you thought you were sending is not the one you were sending."}]},{"t":"p","runs":[{"text":"The "},{"text":"?","code":true},{"text":" placeholders are not optional. Building the SQL with an f-string works until a prompt contains a quote mark, and then it either breaks or, if the text is hostile, does something else, the same lesson as the previous lesson’s injection, with a database instead of a model. "},{"text":"with self.conn:","code":true},{"text":" commits on success and rolls back on error, from module 7’s context-manager lesson."}]},{"t":"h2","id":"wrapping-the-call","runs":[{"text":"Wrapping the call"}]},{"t":"code","lang":"python","label":"Python","text":"def complete_cached(provider, cache, request):\n    key = cache_key(request)\n    hit = cache.get(key)\n    if hit is not None:\n        return hit\n    response = provider.complete_raw(request)\n    cache.put(key, request, response)\n    return response"},{"t":"p","runs":[{"text":"Or as a decorator from module 7, if every call goes through one function. Log hits and misses at "},{"text":"DEBUG","code":true},{"text":"; a hit rate near zero after the first run means the key is wrong."}]},{"t":"h2","id":"what-not-to-cache","runs":[{"text":"What not to cache"}]},{"t":"p","runs":[{"text":"Failures.","b":true},{"text":" An exception or an empty reply must not be stored, or one transient 503 becomes a permanent wrong answer for that prompt. Only "},{"text":"put","code":true},{"text":" after validating the response."}]},{"t":"p","runs":[{"text":"Anything sampled on purpose.","b":true},{"text":" At temperature 0.8 you asked for variety; a cache returns the first draw forever. Either exclude such calls, or include a "},{"text":"run_id","code":true},{"text":" in the request so each run gets its own entries."}]},{"t":"p","runs":[{"text":"Anything time-dependent.","b":true},{"text":" A prompt containing today’s date is a different prompt tomorrow, and a prompt that "},{"text":"should","i":true},{"text":" contain it but does not will be cached with a stale answer. If the answer depends on when it was asked, the when must be in the key."}]},{"t":"h2","id":"expiry-and-size","runs":[{"text":"Expiry and size"}]},{"t":"p","runs":[{"text":"A "},{"text":"created","code":true},{"text":" column allows "},{"text":"DELETE FROM responses WHERE created < ?","code":true},{"text":" for entries older than a month. "},{"text":"SELECT page_count * page_size FROM pragma_page_count(), pragma_page_size()","code":true},{"text":" reports the database file’s size in bytes; a cache of ten thousand responses is a few tens of megabytes. "},{"text":"VACUUM","code":true},{"text":" reclaims space after deleting."}]},{"t":"h2","id":"threads-and-the-connection","runs":[{"text":"Threads and the connection"}]},{"t":"p","runs":[{"text":"A "},{"text":"sqlite3","code":true},{"text":" connection may not be shared across threads by default. Open one per thread, or pass "},{"text":"check_same_thread=False","code":true},{"text":" and guard writes with a lock. For the thread pool from module 6, one connection per worker is the simple and correct answer. Concurrent "},{"text":"processes","i":true},{"text":" writing to the same file work (sqlite locks the file) but will occasionally raise “database is locked”; a "},{"text":"timeout=30","code":true},{"text":" argument to "},{"text":"connect","code":true},{"text":" makes them wait rather than fail."}]},{"t":"h2","id":"alternatives","runs":[{"text":"Alternatives"}]},{"t":"p","runs":[{"text":"shelve","code":true},{"text":" is a dict-on-disk in the standard library. Since Python 3.13 it stores new shelves in SQLite by default and copes with many thousands of entries, but it is unsafe with several writers at once, and its values are pickled, so never open a shelf from an untrusted source. "},{"text":"diskcache","code":true},{"text":" (free) is a polished library doing what this lesson does with expiry and size limits built in. For sharing a cache between machines, Redis. The hand-built version is the one to understand first, because when any of the others misbehaves, the question is always “what is the key?”"}]},{"t":"h2","id":"try-this-now","runs":[{"text":"Try this now"}],"role":"try"},{"t":"p","runs":[{"text":"Build "},{"text":"ResponseCache","code":true},{"text":", wrap your provider, run a script of twenty prompts twice, and confirm the second run makes no network calls. Then change "},{"text":"max_tokens","code":true},{"text":" and confirm every call misses. Finally build a request dict with keys in two orders and check "},{"text":"cache_key","code":true},{"text":" agrees."}]}],"check":{"question":[{"text":"A cache keys each call on "},{"text":"hashlib.sha256(json.dumps(request).encode()).hexdigest()","code":true},{"text":". Two runs of the same script with identical prompts produce different keys for about a third of the calls, so the cache misses. What is the most likely cause?"}],"options":[{"text":[{"text":"SHA-256 includes a random salt on each call to defend against rainbow tables, so the same input never hashes the same twice within a process, let alone across runs."}],"correct":false,"tempting":[{"text":"SHA-256 is deterministic; identical bytes always produce identical digests, which is the whole reason it works as a key."}]},{"text":[{"text":"Python’s string hashing is randomised per process, so "},{"text":"hexdigest()","code":true},{"text":" differs between runs of the same program."}],"correct":false,"tempting":[{"text":"Hash randomisation affects "},{"text":"hash()","code":true},{"text":" on str, not "},{"text":"hashlib","code":true},{"text":", whose output is stable across processes and machines."}]},{"text":[{"text":"The sqlite database was opened without a transaction, so keys written in the first run were never committed."}],"correct":false,"tempting":[{"text":"Uncommitted writes would cause misses on every call, not a third, and would not change the computed key."}]},{"text":[{"text":"json.dumps","code":true},{"text":" keeps insertion order, and the request dicts were built with keys in varying order, so equal requests serialised differently."}],"correct":true,"tempting":null}],"order":[1,0,3,2],"why":null},"prev":{"slug":"prompt-templates-safely","title":"Prompt templates: building a prompt from parts without letting the parts rewrite it","minutes":8},"next":{"slug":"chunking-text","title":"Chunking: splitting a document that does not fit, without cutting a sentence in half","minutes":8}}}