Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 39 ยท Storing and Cleaning Data

Saving to CSV, JSON and SQLite

Store scraped data in the right format: CSV with the csv module, JSON and JSON Lines, SQLite with upserts, and pandas exports. Runnable Python examples.

Scraping is only useful if the data ends up somewhere you can use it. Which storage is right depends on how much data you have and what you will do with it. This lesson covers the four formats you will use ninety percent of the time: CSV, JSON, JSON Lines and SQLite, plus how to get from them to pandas.

A rule that applies to all of them: write as you go. Do not hold ten thousand scraped rows in memory and save at the very end; one crash and everything is lost. Append each row (or each page of rows) as soon as you have it.

In the browser editor files vanish after each run, so the examples write into in-memory buffers (io.StringIO) that behave exactly like files, and SQLite uses an in-memory database. The code for real files is shown next to each example.

Choosing a format

Format Good for Weak spot
CSV Flat tables, opening in Excel or Sheets No types, no nesting, quoting quirks
JSON Nested data, APIs Must be written as one document
JSON Lines (.jsonl) Large or ongoing scrapes: one JSON object per line, appendable Not opened by spreadsheets
SQLite Queries, dedupe, incremental updates, millions of rows Needs a schema
pandas / Parquet Analysis Not for streaming writes

CSV with the csv module

Use csv.DictWriter so each row is a dictionary and the columns are explicit. Never join values with commas yourself: names and descriptions contain commas, quotes and newlines, and the module quotes them correctly.

Python Write and read CSV

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Two real-file details: open with newline="" (otherwise Windows gets blank lines) and encoding="utf-8". If the file must open correctly in Excel on Windows, use encoding="utf-8-sig". Everything read back is a string: "19.5", not 19.5. CSV does not remember types.

To append while scraping, open in "a" mode and write the header only when the file is new:

Python
import csv, os

path = "products.csv"
new_file = not os.path.exists(path) or os.path.getsize(path) == 0
with open(path, "a", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=FIELDS)
    if new_file:
        writer.writeheader()
    writer.writerow(row)

JSON and JSON Lines

JSON keeps nesting and types (numbers, booleans, null). Use ensure_ascii=False to keep non-English text readable.

Python JSON and JSON Lines

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

For scrapers JSON Lines is usually the best raw format. Each page's items are appended with f.write(json.dumps(item) + "\n"). If the script dies, everything written so far is still valid, and you never have to rewrite a big file to add one record. Scrapy's -o items.jsonl writes exactly this.

SQLite: a real database in one file

sqlite3 ships with Python. A table with a primary key makes duplicates impossible and lets you query, filter and update. Use parameter placeholders (?), never string formatting, so odd characters in scraped text cannot break (or inject into) your SQL.

Python Store products in SQLite with an upsert

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The ON CONFLICT ... DO UPDATE clause is an upsert: insert the row, or update it if the key already exists. That one statement makes your scraper safe to run again and again. A scrape can then be interrupted and restarted, and the table never fills with duplicates.

History instead of overwriting

For price tracking you want every observation, not just the latest. Use a second table where each scrape adds a row:

Python Keep a price history

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

pandas: from rows to analysis

When you are ready to analyze, read from SQLite or from your records straight into a DataFrame, and export with a single call:

Python DataFrame to CSV, JSON and back from SQLite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

On disk the exports are df.to_csv("out.csv", index=False), df.to_json("out.jsonl", orient="records", lines=True), and df.to_parquet("out.parquet") (needs pyarrow) for compact, typed, fast analysis files.

Practical advice

  • Include provenance: the url and a scraped_at timestamp on every record. You will need them.
  • Save raw HTML (or the raw JSON) in a raw/ folder when you can; fix parsing later without re-fetching.
  • Back up your database file; do not scrape into the only copy.
  • Escape on the way out, not in: store the clean text, and format when displaying.
  • Use one writer: if many threads write to SQLite, funnel rows through a queue to one thread.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why use csv.DictWriter instead of joining values with commas yourself?

    The csv module handles quoting so text fields cannot corrupt the columns.
  2. 2. What makes JSON Lines well suited to long scrapes?

    Appending one object per line avoids rewriting a whole document and survives interruptions.
  3. 3. What does an upsert (ON CONFLICT DO UPDATE) do?

    It makes re-running a scraper safe: existing records are updated instead of duplicated.
  4. 4. Why use ? placeholders in SQL for scraped text?

    Parameter binding keeps data separate from SQL code.
  5. 5. When reading a CSV back with the csv module, what type do all values have?

    CSV stores text only; you must convert numbers and dates yourself.

Summary

  • Write results as you scrape; do not keep everything in memory until the end.
  • CSV for flat tables (no types), JSON for nesting, JSON Lines for appendable raw output, SQLite for queries and updates.
  • Use csv.DictWriter, newline="" and utf-8; bind SQL parameters with ?.
  • Upserts and primary keys make scrapers safely repeatable; a history table records change over time.
  • Keep url and scraped_at on every record, and save raw pages when possible.

Next: Deduplicating and Updating Data