Storing Scraped Data

Data Persistence Architectures#

A scraper's output is only as good as the place it lands. Storage decisions determine whether a crash loses an hour of work, whether re-running the job creates duplicates, whether you can answer "what did this price look like last month", and whether you can fix a parsing bug without re-crawling the site. The right choice depends on volume, how the data will be queried, and how many workers write at once.

Choosing a Store#

Many production scrapers use two or three of these at once: object storage or compressed files for raw responses, a relational database for current clean records, and Parquet snapshots for analysis.

Keep Raw and Parsed Data Separate#

Store what the server sent before you parse it, at least for a while. When you discover a parsing bug two weeks in, raw responses let you re-parse everything in minutes instead of re-crawling for days, and without putting any additional load on the target. A simple layout keyed by a URL hash works well:

import gzip, hashlib, pathlib

RAW = pathlib.Path("raw")

def save_raw(url: str, body: bytes) -> pathlib.Path:
    key = hashlib.sha256(url.encode()).hexdigest()
    path = RAW / key[:2] / f"{key}.html.gz"
    path.parent.mkdir(parents=True, exist_ok=True)
    path.write_bytes(gzip.compress(body))
    return path

The two-character prefix directory keeps any single folder from holding millions of files, which many filesystems handle poorly. HTML compresses very well with gzip, so raw storage is usually much cheaper than people expect. The caching and resume lesson builds on the same idea.

Flat Files Done Right#

JSONL writes one JSON object per line. Appending a line is cheap, a crash loses at most the line being written, and nested fields need no flattening.

import json

def append_jsonl(path, record):
    with open(path, "a", encoding="utf-8") as f:
        print(json.dumps(record, ensure_ascii=False), file=f)

ensure_ascii=False keeps accented and non-Latin text readable instead of turning every é into a six-character escape sequence. For high-volume writes, open the file once and keep it open rather than reopening per record.

CSV looks simpler but has more traps:

import csv

FIELDS = ["sku", "title", "price", "url"]

with open("products.csv", "w", newline="", encoding="utf-8-sig") as f:
    writer = csv.DictWriter(f, fieldnames=FIELDS, extrasaction="ignore")
    writer.writeheader()
    writer.writerows(records)

• newline="" is required by the csv module; without it, Windows produces blank lines between rows.
• utf-8-sig writes a byte-order mark so Excel detects UTF-8 instead of showing mojibake. Leave it out for files consumed by code.
• Always use the csv module, never ",".join(...). Product titles contain commas, quotes, and newlines, and only proper quoting survives them.
• extrasaction="ignore" drops unexpected keys instead of raising, useful when the scraper adds fields before the export does.

SQLite: Upserts, Change Tracking, and Batches#

For most single-machine scrapers, SQLite is the sweet spot: one file, no server, real transactions, and UNIQUE constraints that make re-runs safe. The example below stores current product state plus an observation log, updates rows only when something changed, and writes each batch in one transaction.

import sqlite3, datetime as dt, json

SCHEMA = '''
CREATE TABLE IF NOT EXISTS products (
    sku         TEXT PRIMARY KEY,
    title       TEXT NOT NULL,
    price_cents INTEGER,
    currency    TEXT,
    url         TEXT NOT NULL,
    raw         TEXT,
    first_seen  TEXT NOT NULL,
    updated_at  TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS price_history (
    sku         TEXT NOT NULL,
    price_cents INTEGER,
    seen_at     TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_hist_sku ON price_history(sku, seen_at);
'''

UPSERT = '''
INSERT INTO products (sku, title, price_cents, currency, url, raw, first_seen, updated_at)
VALUES (:sku, :title, :price_cents, :currency, :url, :raw, :now, :now)
ON CONFLICT(sku) DO UPDATE SET
    title       = excluded.title,
    price_cents = excluded.price_cents,
    raw         = excluded.raw,
    updated_at  = excluded.updated_at
WHERE products.price_cents IS NOT excluded.price_cents
   OR products.title IS NOT excluded.title
'''

def open_db(path="scrape.db"):
    conn = sqlite3.connect(path)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA synchronous=NORMAL")
    conn.executescript(SCHEMA)
    return conn

def save_batch(conn, records):
    now = dt.datetime.now(dt.timezone.utc).isoformat(timespec="seconds")
    rows = [{**r, "raw": json.dumps(r, ensure_ascii=False), "now": now} for r in records]
    with conn:   # one transaction: commits on success, rolls back on exception
        before = conn.total_changes
        conn.executemany(UPSERT, rows)
        changed = conn.total_changes - before
        conn.executemany(
            "INSERT INTO price_history (sku, price_cents, seen_at) VALUES (:sku, :price_cents, :now)",
            rows,
        )
    return changed

conn = open_db()
batch = [
    {"sku": "A1", "title": "Laptop Pro", "price_cents": 120000, "currency": "USD", "url": "/item/1"},
    {"sku": "B2", "title": "Wireless Mouse", "price_cents": 4500, "currency": "USD", "url": "/item/2"},
]
print("run 1 changed:", save_batch(conn, batch))
print("run 2 changed:", save_batch(conn, batch))
batch[1]["price_cents"] = 3999
print("run 3 changed:", save_batch(conn, batch))

Output on a fresh database:

run 1 changed: 2
run 2 changed: 0
run 3 changed: 1

The decisions worth copying:

• The key is a business identifier (SKU, external ID, canonical URL), not an auto-increment ID. That is what makes the second run a no-op instead of a duplicate.
• The WHERE clause on the upsert skips rows whose values did not change, so updated_at means "last changed" and the change count is an honest signal. IS NOT is SQLite's null-safe comparison.
• Prices are integer cents. Floats cannot represent most decimal prices exactly, and summing them drifts.
• Timestamps are UTC ISO 8601 strings, which sort correctly as text and never depend on the server's local time zone.
• A raw JSON column keeps every field you extracted, including ones you have not modeled yet, so a new column later can be backfilled with a query instead of a re-crawl.
• with conn: wraps the batch in one transaction. Thousands of single-row commits are far slower than one commit of thousands of rows, and WAL mode with synchronous=NORMAL makes each commit cheaper still.

Concurrency and "database is locked"#

SQLite allows many readers but one writer at a time. If ten threads or processes write directly, you will eventually see sqlite3.OperationalError: database is locked. The robust pattern is a single writer: workers put parsed records on a queue, and one task drains it and calls save_batch every few hundred records or few seconds. That also gives you natural batching. When you genuinely need many machines writing, move to PostgreSQL.

PostgreSQL for Fleets#

The same upsert pattern works in PostgreSQL with IS DISTINCT FROM as the null-safe comparison, and JSONB for the raw payload so you can index and query inside it:

INSERT INTO products (sku, title, price_cents, raw, updated_at)
VALUES ($1, $2, $3, $4, now())
ON CONFLICT (sku) DO UPDATE SET
    title = EXCLUDED.title,
    price_cents = EXCLUDED.price_cents,
    raw = EXCLUDED.raw,
    updated_at = now()
WHERE products.price_cents IS DISTINCT FROM EXCLUDED.price_cents
   OR products.title IS DISTINCT FROM EXCLUDED.title;

Use a connection pool, keep transactions short, and batch inserts rather than committing per row.

Binary Files and Object Storage#

Images, PDFs, and archives do not belong in database rows. Put them in object storage or on disk under a content-addressed key (the SHA-256 of the bytes), and store the key plus metadata (source URL, size, content type, fetched time) in the database. Content addressing deduplicates automatically: the same image linked from a thousand pages is stored once. See file downloads and streaming for fetching large files without loading them into memory.

Where Storage Goes Wrong#

• Everything stored as strings. "$1,299.00" in a price column cannot be sorted or summed. Normalize types at write time; the data cleaning and validation lesson covers parsing prices, dates, and units.
• No fetched_at. Without it you cannot tell stale data from fresh, and incremental scraping is impossible.
• Duplicates from unstable keys. URLs with tracking parameters or session IDs make the same item look new every run. Normalize URLs before using them as keys.
• Silent overwrites. An upsert that replaces good values with None when a selector breaks destroys data. Validate records before saving, and consider refusing to overwrite a non-null field with null.

Practice#

Run the SQLite example against a real paginated listing you are allowed to crawl, twice, a day apart. Then write queries for: items first seen in the second run, items missing from the second run, and every price change with old and new values. If any query is hard to write, your schema is missing a column.