Storing and Exporting Scraped Data

Where scraped records land decides how much of a crawl survives a crash, and this guide — part of Scaling Python Web Scrapers — covers choosing that destination and writing to it without losing or duplicating rows. The scope is the write path: format selection, batching, deduplication keys, and the failure modes that only appear on the second run.

Storage is the stage where scraping mistakes become permanent. A parser bug produces a wrong value once and is fixed on the next run; a missing unique constraint produces four copies of every record and there is no clean way to tell which copy came from which crawl. The decisions worth being deliberate about are small in number: what the natural key is, how often you commit, what happens to a partially written file, and whether the format you chose can still be read at a hundred times the current volume.

When to Use Each Sink

Two heuristics cover most cases. If the data will be read by a person, use CSV or a spreadsheet export; if it will be read by a program, use a database or Parquet. And if the crawl runs more than once against overlapping URLs, you need a keyed store — a file format has no way to express "this record replaces that one".

Keeping the raw response bodies alongside the extracted records is worth considering separately. Gzipped HTML compresses to roughly 15–20% of its original size, so a million pages averaging 120 KB stores in about 24 GB. That is cheap insurance: when a selector turns out to have been wrong, you re-parse from disk instead of re-crawling the site.

Prerequisites

Python 3.10 or newer. sqlite3, csv and json ship with the standard library; the rest are optional and only needed for the sinks you actually use.

pip install "pydantic==2.9.2" "psycopg[binary]==3.2.3" "pyarrow==17.0.0" "pandas==2.2.3"

For PostgreSQL, a local server is enough to follow along:

docker run --rm -e POSTGRES_PASSWORD=scraper -p 5432:5432 postgres:16-alpine

Step-by-Step: Building a Durable Write Path

1. Choose the Physical Layout Before the Library

Row stores, column stores and document stores differ in which access pattern is cheap, and that decision outlives whichever library you use to write them.

A row store keeps all of one record's fields adjacent, so reading or updating a single record touches one page of disk. A column store keeps all values of one field adjacent, so SELECT avg(price) over ten million rows reads one narrow, well-compressed column and ignores the rest — often a 20× reduction in bytes read. A document store keeps a self-describing blob per record, which tolerates schema drift but forces a full parse to answer anything.

Scraped data is written once and read many times, usually analytically, which is why Parquet wins so decisively at scale. It is also why appending a single record to Parquet is awkward: the format's compression and statistics are computed per row group, so "append" means writing a new file. The standard pattern is to accumulate in JSON Lines during the crawl and convert to Parquet as a separate step — covered in Exporting Scraped Data to CSV and Parquet.

2. Validate at the Boundary

The write is the last place a bad record can be stopped cheaply. After it, the bad value is in every downstream report. A typed model gives you a single definition of what a valid record is, and an error naming the field rather than a silent None.

from datetime import date

from pydantic import BaseModel, Field, HttpUrl, ValidationError, field_validator


class Book(BaseModel):
    url: HttpUrl
    upc: str = Field(min_length=8, max_length=32)
    title: str = Field(min_length=1)
    price_gbp: float = Field(gt=0, lt=10_000)
    in_stock: bool
    scraped_on: date

    @field_validator("price_gbp", mode="before")
    @classmethod
    def parse_price(cls, value: object) -> float:
        if isinstance(value, (int, float)):
            return float(value)
        cleaned = str(value).replace("£", "").replace(",", "").strip()
        return float(cleaned)

    @field_validator("title")
    @classmethod
    def collapse_whitespace(cls, value: str) -> str:
        return " ".join(value.split())


def validate_rows(rows: list[dict[str, object]]) -> tuple[list[Book], list[str]]:
    good: list[Book] = []
    bad: list[str] = []
    for row in rows:
        try:
            good.append(Book(**row))
        except ValidationError as exc:
            bad.append(f"{row.get('url')}: {exc.error_count()} problems")
    return good, bad

Returning the rejects rather than logging and dropping them matters. A rejection rate that jumps from 0.2% to 30% is the clearest early signal that a site changed its markup, and it is only useful if something counts it — see Detecting Silent Scraper Failures. The full modelling vocabulary is covered in Validating Scraped Data with Pydantic, and the normalisation rules that should run before validation in Cleaning and Validating Scraped Data.

3. Write Files Atomically

A process killed mid-write leaves a truncated file, and a truncated CSV or Parquet file is often worse than no file because it parses successfully and returns partial data. Writing to a temporary file in the same directory and renaming it at the end makes the swap atomic on POSIX filesystems.

import csv
import json
import os
import tempfile
from pathlib import Path


def write_csv_atomic(books: list[Book], path: Path) -> None:
    fields = ["url", "upc", "title", "price_gbp", "in_stock", "scraped_on"]
    directory = path.parent
    directory.mkdir(parents=True, exist_ok=True)
    handle = tempfile.NamedTemporaryFile(
        "w", delete=False, dir=directory, newline="", encoding="utf-8", suffix=".tmp"
    )
    try:
        writer = csv.DictWriter(handle, fieldnames=fields, quoting=csv.QUOTE_MINIMAL)
        writer.writeheader()
        for book in books:
            row = book.model_dump(mode="json")
            writer.writerow({key: row[key] for key in fields})
        handle.flush()
        os.fsync(handle.fileno())
    finally:
        handle.close()
    os.replace(handle.name, path)


def append_jsonl(books: list[Book], path: Path) -> None:
    with open(path, "a", encoding="utf-8") as out:
        for book in books:
            out.write(json.dumps(book.model_dump(mode="json"), ensure_ascii=False) + "\n")
        out.flush()
        os.fsync(out.fileno())

Three details are load-bearing. newline="" on the CSV handle prevents the csv module's \r\n line terminator from becoming \r\r\n on Windows. encoding="utf-8" is explicit because Python's default depends on the platform locale, which is how the same script produces mojibake on one machine and clean text on another — see Fixing Common Unicode Errors in Python Scraping. And os.fsync before the rename is what makes the guarantee real; without it the rename can land before the data does.

JSON Lines is the right in-flight format for a running crawl precisely because a truncated final line is trivially recoverable: read line by line, skip anything that fails to parse, and you have lost one record instead of a file.

4. Batch Writes Instead of Committing Per Row

Per-row commits are the single most common reason a scraper's write path becomes the bottleneck. Each commit is a network round trip plus a durable flush of the write-ahead log, and both costs are per commit, not per row.

from collections.abc import Iterator, Sequence

import psycopg

DDL = """
CREATE TABLE IF NOT EXISTS books (
    upc         TEXT PRIMARY KEY,
    url         TEXT NOT NULL,
    title       TEXT NOT NULL,
    price_gbp   NUMERIC(10, 2) NOT NULL,
    in_stock    BOOLEAN NOT NULL,
    scraped_on  DATE NOT NULL,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
)
"""

UPSERT = """
INSERT INTO books (upc, url, title, price_gbp, in_stock, scraped_on)
VALUES (%s, %s, %s, %s, %s, %s)
ON CONFLICT (upc) DO UPDATE SET
    url        = EXCLUDED.url,
    title      = EXCLUDED.title,
    price_gbp  = EXCLUDED.price_gbp,
    in_stock   = EXCLUDED.in_stock,
    scraped_on = EXCLUDED.scraped_on,
    updated_at = now()
"""


def chunked(items: Sequence[Book], size: int) -> Iterator[Sequence[Book]]:
    for start in range(0, len(items), size):
        yield items[start : start + size]


def save_books(books: list[Book], dsn: str, batch_size: int = 500) -> int:
    written = 0
    with psycopg.connect(dsn) as conn:
        with conn.cursor() as cur:
            cur.execute(DDL)
        conn.commit()
        for batch in chunked(books, batch_size):
            rows = [
                (
                    book.upc,
                    str(book.url),
                    book.title,
                    book.price_gbp,
                    book.in_stock,
                    book.scraped_on,
                )
                for book in batch
            ]
            with conn.cursor() as cur:
                cur.executemany(UPSERT, rows)
            conn.commit()
            written += len(rows)
    return written

ON CONFLICT (upc) DO UPDATE is what makes the crawl idempotent: running it twice produces one row per book with the newer values, not two rows. Choosing that key well is the whole game — a stable product identifier is ideal, the canonical URL is the usual fallback, and the page title is never acceptable because it changes.

A batch size of 500 is a reasonable default. Below about 100 the round-trip overhead still dominates; above a few thousand the transaction holds locks longer and a failure discards more work. Connection setup, indexing and COPY for bulk loads are covered in Saving Scraped Data to PostgreSQL.

5. Use SQLite When There Is Only One Writer

For a local crawl, SQLite gives you the same idempotence with no server. The default journal mode is the main thing to change: WAL allows readers while a write is in progress and is substantially faster for the append-heavy pattern a scraper produces.

import sqlite3
from pathlib import Path

SCHEMA = """
CREATE TABLE IF NOT EXISTS books (
    upc        TEXT PRIMARY KEY,
    url        TEXT NOT NULL,
    title      TEXT NOT NULL,
    price_gbp  REAL NOT NULL,
    in_stock   INTEGER NOT NULL,
    scraped_on TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS books_scraped_on ON books (scraped_on);
"""


def open_db(path: Path) -> sqlite3.Connection:
    conn = sqlite3.connect(path, isolation_level=None)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA synchronous=NORMAL")
    conn.executescript(SCHEMA)
    return conn


def save_batch(conn: sqlite3.Connection, books: list[Book]) -> int:
    rows = [
        (
            book.upc,
            str(book.url),
            book.title,
            book.price_gbp,
            int(book.in_stock),
            book.scraped_on.isoformat(),
        )
        for book in books
    ]
    conn.execute("BEGIN")
    conn.executemany(
        """
        INSERT INTO books (upc, url, title, price_gbp, in_stock, scraped_on)
        VALUES (?, ?, ?, ?, ?, ?)
        ON CONFLICT(upc) DO UPDATE SET
            url=excluded.url, title=excluded.title, price_gbp=excluded.price_gbp,
            in_stock=excluded.in_stock, scraped_on=excluded.scraped_on
        """,
        rows,
    )
    conn.execute("COMMIT")
    return len(rows)

isolation_level=None turns off the driver's implicit transaction handling so the explicit BEGIN/COMMIT pair controls the batch. synchronous=NORMAL under WAL trades a tiny durability window on power loss for a large throughput gain, which is the right trade for data you can re-crawl.

The same insert-or-update logic belongs in a Scrapy pipeline rather than a spider callback when you are working in that framework — see Writing Scrapy Item Pipelines.

6. Convert to Parquet for Analysis

Once the crawl is done, converting JSON Lines to partitioned Parquet gives you a compact archive that analytical tools read directly.

from pathlib import Path

import pyarrow as pa
import pyarrow.json as paj
import pyarrow.parquet as pq

SCHEMA = pa.schema(
    [
        ("url", pa.string()),
        ("upc", pa.string()),
        ("title", pa.string()),
        ("price_gbp", pa.float64()),
        ("in_stock", pa.bool_()),
        ("scraped_on", pa.date32()),
    ]
)


def jsonl_to_parquet(src: Path, dest: Path) -> int:
    table = paj.read_json(src, parse_options=paj.ParseOptions(explicit_schema=SCHEMA))
    dest.parent.mkdir(parents=True, exist_ok=True)
    pq.write_table(table, dest, compression="zstd", row_group_size=100_000)
    return table.num_rows


if __name__ == "__main__":
    count = jsonl_to_parquet(Path("books.jsonl"), Path("warehouse/books.parquet"))
    print(f"converted {count} rows")

Declaring the schema explicitly rather than letting pyarrow infer it prevents the classic failure where a column is int64 in one file and string in the next because one crawl happened to produce a null. zstd compresses roughly 10–20% better than snappy at similar read speed, and a 100,000-row group size keeps column statistics granular enough for predicate pushdown to skip most of the file.

Performance and Scaling Considerations

The commit rate is the throughput ceiling. A single-row commit against a local PostgreSQL is roughly 90 rows per second because it waits for an fsync each time. The same rows in batches of 500 reach several thousand per second, and COPY from an in-memory buffer reaches tens of thousands. If a crawl is fetching 40 pages a second and writing 90 rows a second, the database is the bottleneck and no amount of concurrency will help.

Indexes cost on write and save on read. Every index is updated on every insert. For a bulk load, drop non-essential indexes, load, then rebuild — often a 3–5× improvement. Keep the primary key, since it is what makes the upsert work.

Files grow faster than you expect. A million records of six short fields is roughly 180 MB as CSV, 400 MB as JSON Lines, 240 MB as an uncompressed SQLite table, and 35 MB as zstd Parquet. The JSON Lines penalty is repeated key names on every line, which compresses away almost entirely — gzipping the stream as you write costs a few percent of CPU and removes most of the gap.

Deduplicate on a key, not on the whole record. Comparing full records fails as soon as one whitespace character changes. Use the natural key for exact matching, and reserve similarity matching for the genuinely hard cases described in Deduplicating Records with Fuzzy Matching.

Store when the record was seen, not just what it said. A scraped_on column turns a table into a time series and makes it possible to answer "what changed since the last run" without re-fetching anything. It also pairs with conditional requests, described in Incremental Crawls with ETag and Last-Modified.

Write incrementally, always. Accumulating a full crawl in memory and writing once at the end means an hour-long run has a one-hour window in which any crash costs everything. Flushing every few hundred records bounds the loss to seconds of work.

Common Errors and Fixes

sqlite3.OperationalError: database is locked Another connection holds a write lock, or a long read is blocking a write in the default rollback journal mode. Enable WAL with PRAGMA journal_mode=WAL, set a busy timeout with sqlite3.connect(path, timeout=30), and make sure only one process writes. SQLite is a single-writer database; concurrent writers need PostgreSQL.

UnicodeEncodeError: 'charmap' codec can't encode character '’' The file was opened without an encoding on a system whose locale is not UTF-8. Always pass encoding="utf-8" to open. If a consumer requires a legacy encoding, use errors="replace" deliberately rather than letting it fail on a smart quote.

psycopg.errors.UniqueViolation: duplicate key value violates unique constraint "books_pkey" A plain INSERT hit an existing key on the second run. Add ON CONFLICT (key) DO UPDATE for an upsert or DO NOTHING to keep the first value. Catching the exception and retrying per row works but is orders of magnitude slower.

_csv.Error: field larger than field limit (131072) A CSV cell exceeded the module's default limit, usually because a full HTML blob ended up in a column. Raise it with csv.field_size_limit(10**7), but treat the value as the real bug — large text belongs in a separate file keyed by the record, not in a spreadsheet column.

pyarrow.lib.ArrowInvalid: Failed to parse string: '£24.00' as a scalar of type double A price was written as a string with a currency symbol. Normalise to a number during validation, before it reaches the writer. Parquet columns are strictly typed and will not coerce this for you.

A CSV opens in a spreadsheet with every row in one column The consumer expects a different delimiter for its locale, or the file begins without a byte-order mark and the application guessed the wrong encoding. Write with encoding="utf-8-sig" when the destination is Excel; the BOM is what makes it detect UTF-8.

Row counts grow every run even though the crawl is the same size There is no unique constraint, so each run appends a fresh copy. Add a primary key on the natural key and switch the insert to an upsert. To clean up existing duplicates, delete by keeping the row with the newest scraped_on per key.

Frequently Asked Questions

CSV, JSON Lines, or a database? CSV if a person will open it in a spreadsheet and the data is flat. JSON Lines while a crawl is running, because it appends safely and survives truncation. A database as soon as the crawl repeats over overlapping URLs, because that is the point at which you need a key that says which record replaces which.

What should the deduplication key be? The most stable identifier the site exposes: a product code, an ISBN, a numeric ID in the URL path. Failing that, the canonical URL with tracking parameters stripped. Never a title or a price, and never a hash of the whole record, since either changes when anything on the page is edited.

How often should I commit? Every few hundred records. That bounds the work lost to a crash at a second or two while amortising the fsync cost across enough rows that it stops being the bottleneck. Committing per row is roughly fifty times slower for no practical durability benefit in a re-runnable crawl.

Should I keep the raw HTML? If storage is affordable, yes. Gzipped pages cost around 20 KB each, and having them means a selector bug is fixed by re-parsing locally in minutes instead of re-crawling the site over days. Store them in an object store keyed by a hash of the URL, alongside the fetch timestamp.

Is Parquet worth it for a few hundred thousand rows? Usually not for storage size alone, but it is worth it if the data will be queried repeatedly. The gains come from reading only the columns a query touches and skipping row groups that cannot match, both of which need enough rows to matter. Below roughly a million rows, SQLite is simpler and fast enough.

Related

• Scaling Python Web Scrapers — where the write path sits in the overall pipeline
• Exporting Scraped Data to CSV and Parquet — format-specific export recipes
• Saving Scraped Data to PostgreSQL — schemas, upserts and bulk loading
• Cleaning and Validating Scraped Data — what to fix before the record is written
• Detecting Silent Scraper Failures — catching a rejection-rate spike early