Web Scraping & Browser Automation (Python and JavaScript) Module 7 – Cleaning, validating and storing data

Store and dedupe: SQLite, JSON Lines, Parquet

Choose stores for raw pages, items and history, upsert on natural keys so re-runs never duplicate rows, and export CSV that cannot run formulas.

Intermediate
20 minutes
Examples run with Python 3.14.8, Pyodide 314.0.7, Node.js 24.21.0 and quickjs 0.32.0
By MySmartCoPilot

Free: Raw pages, records and history belong in different stores · A natural key turns a re-run into an update · Text from a page goes in as a value, never as SQL. With a pass: 3 in-depth parts and 3 interview answers, about 12 minutes.

What you will learn

Choose where raw pages, current records and history live, among SQLite, JSON Lines and Parquet
Write an upsert on a natural key, so that running a crawl again never duplicates rows
Use parameters for every value from a page, never SQL built from strings
Build a store with a price history that a crawl can run into twice without changing anything
Export scraped text to CSV so that no cell can run as a spreadsheet formula

Before you start

Validate every record: schemas and quarantine

What you’ll learn · Before you start

What you will learn

Choose where raw pages, current records and history live, among SQLite, JSON Lines and Parquet
Write an upsert on a natural key, so that running a crawl again never duplicates rows
Use parameters for every value from a page, never SQL built from strings
Build a store with a price history that a crawl can run into twice without changing anything
Export scraped text to CSV so that no cell can run as a spreadsheet formula

Before you start

Validate every record: schemas and quarantine

On this page

Three crawls of the practice shop’s catalogue, the last one restarted halfway after a crash, left 841 rows in a table that should hold 243, with four copies of some products and two prices for others. Every report built on it counted wrong. To store and dedupe scraped data takes three decisions: where each kind of data lives, which key lets a second crawl update rows instead of adding them, and how text from a page goes in without being read as code.

Raw pages, records and history belong in different stores

A collector keeps three kinds of data, and each behaves differently. Raw pages are kept for re-parsing: written once, read rarely. Current records, one per product, change with every crawl. History, one row per change, only grows and is read in bulk. Three formats cover most needs. SQLite keeps a whole SQL database in one file, with transactions and unique keys, and Python and Node both include it. JSON Lines stores one JSON value per UTF-8 line, so a new record is one more line at the end of a file. Parquet is a column-oriented file format built for efficient storage and retrieval, read by tools such as pyarrow and DuckDB. Answer three questions about your data:

Interactive · answer and compare Which store fits this data? Answer three questions about one kind of data, current records, a log or a history. The table marks what fits and says why the rest does not.

Answer the questions above: the table then marks what fits your case and says why the rest does not.

Four formats against five things a collector does with what it stores
Format	Add a record	Change one in place	Find one by key	Analyse one column of millions	Open in a spreadsheet app
SQLite Keeps: tables in one file, with keys	Good an INSERT, inside a transaction	Good an upsert or UPDATE by key, in place	Good the key is an index: one row read	Mixed reads whole rows: fine for thousands, slower for millions	Mixed needs a tool or an export first
JSON Lines Keeps: one JSON value per line	Good one line written at the end, nothing else touched	Poor the file is rewritten, or a second copy is appended	Poor lines are read until the key turns up	Mixed every line is read and parsed	Poor a spreadsheet app does not open it
Parquet Keeps: columns, compressed	Mixed a file is written whole: a new file per batch	Poor the file is rewritten	Mixed statistics skip parts of a file; no index per key	Good only the columns asked for are read, compressed	Poor needs a tool such as DuckDB or pyarrow
CSV Keeps: rows of text	Good one line at the end	Poor the file is rewritten	Poor rows are read until the key turns up	Mixed every row is read and split	Good opens anywhere, once formula cells are neutralised

How the answers decide

“Do the records change after they are written, as a product's current price does?” Yes, they change: rules out JSON Lines, Parquet and CSV. Only SQLite changes one record in place by its key; a file has to be rewritten, or grows a second copy of the record.
“Do the records change after they are written, as a product's current price does?” No, they are only added: adds a caveat to SQLite. Records that are only added need appending, which a JSON Lines log does with one line each; SQLite works too, with more machinery.
“Will you analyse millions of rows a column at a time, such as a year of daily prices?” Yes, by column: adds a caveat to SQLite, JSON Lines and CSV. These read every row in full. For millions of rows by column, Parquet reads only the columns asked for.
“Must people open the data themselves in a spreadsheet app?” Yes: rules out JSON Lines and Parquet and adds a caveat to SQLite. A spreadsheet app opens none of these files; people get a CSV or a workbook exported from the store.
“Must people open the data themselves in a spreadsheet app?” Yes: adds a caveat to CSV. Scraped text can start with a formula character, so neutralise those cells before people open the file.

Records that change belong in SQLite, logs that only grow in JSON Lines, history you analyse by column in Parquet, and a CSV is for handing data to people.

A natural key turns a re-run into an update

A table whose only unique column is an automatic id treats every crawl as new data. A natural key: A key taken from the data itself, such as a product's SKU or a seller and a listing number, that names the same thing on every crawl, so that a second sighting updates its row instead of adding one., here the shop’s SKU, says which product a row is, so a second sighting updates the row instead of adding one. SQLite’s upsert: An insert that becomes an update when a row with the same key already exists. SQLite writes it INSERT … ON CONFLICT (key) DO UPDATE, with an optional WHERE that skips rows whose values did not change., INSERT … ON CONFLICT (sku) DO UPDATE, does that in one statement, and an optional WHERE turns the update into nothing when no value changed. Follow five records through it:

Interactive · stage by stage One record meets the upsert Follow five records of the three crawls and see which ones insert a row, update one or write nothing.

The drawing shows the whole scene; its 5 steps are listed below.

Every step as text

The first crawl. Crawl 1 brings NB-0001, Basmati rice 250 g at 2500 paise. No row has that SKU yet, so the upsert inserts one.
A price that changed. Crawl 2 brings NB-0005 at 31500 paise. Its SKU is stored, with 31000, so the conflict clause turns the insert into an update of that one row.
The same record again. Crawl 2 also brings NB-0001, unchanged. The WHERE clause of the update finds nothing different, so nothing is written. A plain INSERT would have added a second NB-0001.
A page seen twice. Crawl 3 was restarted after a crash, so NB-0001 arrives twice in one crawl. Both times the key matches and nothing differs: the 120 pages seen twice wrote nothing.
A new product. Crawl 3 also brings NB-0241, Kokum 250 g, which the shop has just added. Its SKU is new, so it is inserted, one of 7 rows that crawl wrote.

The program stores the same three crawls three ways:

Three crawls stored with INSERT, INSERT OR IGNORE and an upsert

store/three_crawls.py

"""The same three crawls stored three ways: plain INSERT, INSERT OR IGNORE, and an upsert on the natural key."""
import sqlite3

from crawls import catalogue, crawl

STRATEGIES = {
    "plain INSERT": "INSERT INTO items (sku, name, price_paise) VALUES (:sku, :name, :price_paise)",
    "INSERT OR IGNORE": "INSERT OR IGNORE INTO items (sku, name, price_paise) VALUES (:sku, :name, :price_paise)",
    "upsert": """INSERT INTO items (sku, name, price_paise) VALUES (:sku, :name, :price_paise)
                 ON CONFLICT (sku) DO UPDATE SET name = excluded.name, price_paise = excluded.price_paise
                 WHERE items.name IS NOT excluded.name OR items.price_paise IS NOT excluded.price_paise""",
}
latest = {p["sku"]: p["price_paise"] for p in crawl(3)}  # what the shop shows after crawl 3
print(f"{'strategy':17} {'rows after 1, 2, 3':>18} {'written per crawl':>18} {'NB-0001 rows':>12} {'wrong prices':>12}")
for name, sql in STRATEGIES.items():
    db = sqlite3.connect(":memory:")
    key = "" if name == "plain INSERT" else " UNIQUE"  # the natural key: the shop's SKU
    db.execute(f"CREATE TABLE items (id INTEGER PRIMARY KEY, sku TEXT{key}, name TEXT, price_paise INTEGER)")
    rows, written = [], []
    for n in (1, 2, 3):
        before = db.total_changes
        db.executemany(sql, crawl(n))
        written.append(db.total_changes - before)
        rows.append(db.execute("SELECT count(*) FROM items").fetchone()[0])
    stored = {}
    for sku, paise in db.execute("SELECT sku, price_paise FROM items"):
        stored.setdefault(sku, set()).add(paise)
    wrong = sum(1 for sku, paise in latest.items() if stored.get(sku) != {paise})  # missing, old, or two answers
    copies = db.execute("SELECT count(*) FROM items WHERE sku = 'NB-0001'").fetchone()[0]
    print(f"{name:17} {', '.join(map(str, rows)):>18} {', '.join(map(str, written)):>18} {copies:12} {wrong:12}")
print(f"The shop has {len(latest)} products after crawl 3, of the {len(catalogue())} it had at crawl 1.")

Output

strategy          rows after 1, 2, 3  written per crawl NB-0001 rows wrong prices
plain INSERT           240, 480, 841      240, 240, 361            4           10
INSERT OR IGNORE       240, 240, 243          240, 0, 3            1           10
upsert                 240, 240, 243          240, 6, 7            1            0
The shop has 241 products after crawl 3, of the 240 it had at crawl 1.

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 three_crawls.py

crawls.py (three crawls) · store/crawls.py

"""Three crawls of the practice shop's catalogue, as cleaned and validated records. Every product is invented.

Crawl 2 finds 6 new prices. Crawl 3 finds 4 more, 2 products gone and 3 new ones, and it was restarted after a crash,
so the first 120 products arrive twice.
"""
KINDS = ["Basmati rice", "Toor dal", "Green tea", "Jaggery", "Turmeric", "Mustard oil", "Poha", "Rock salt",
         "Cashews", "Cardamom", "Ragi flour", "Ghee", "Honey", "Peanuts", "Coffee", "Raisins", "Sesame seeds",
         "Tamarind", "Cumin", "Black pepper", "Rolled oats", "Cloves", "Fennel", "Moong dal", "Chana dal",
         "Wheat flour", "Semolina", "Coriander", "Red chilli", "Almonds", "Dates", "Coconut oil", "Brown rice",
         "Millet", "Flax seeds", "Cinnamon", "Ginger powder", "Vermicelli", "Masoor dal", "Sago"]
SIZES = [250, 500, 1000, 2000, 5000, 10000]  # grams


def catalogue():
    out = []
    for k, kind in enumerate(KINDS):
        per_kg = 60 + (k * 37) % 900  # rupees a kilogram
        for grams in SIZES:
            size = f"{grams // 1000} kg" if grams >= 1000 else f"{grams} g"
            out.append({"sku": f"NB-{len(out) + 1:04d}", "name": f"{kind} {size}", "price_paise": per_kg * grams // 10 + 1000})
    return out


def crawl(n):
    """The records crawl n (1, 2 or 3) stored, in the order it visited the pages."""
    items = catalogue()
    if n >= 2:
        for i in (4, 51, 88, 140, 177, 230):  # 6 prices that changed before crawl 2
            items[i]["price_paise"] += 500
    if n == 3:
        for i in (12, 99, 150, 201):  # 4 more before crawl 3
            items[i]["price_paise"] -= 300
        items = [p for p in items if p["sku"] not in ("NB-0063", "NB-0118")]  # 2 products gone
        items += [{"sku": f"NB-{241 + i:04d}", "name": name, "price_paise": paise}
                  for i, (name, paise) in enumerate([("Kokum 250 g", 9000), ("Saffron 1 g", 35000), ("Amchur 100 g", 6500)])]
        items = items[:120] + items  # restarted after a crash: the first 120 pages again
    return [dict(p) for p in items]

Plain INSERT ended with 841 rows, four of them for NB-0001. INSERT OR IGNORE kept one row per SKU but ignored every change, so 10 prices were wrong. The upsert ended with 243 rows and no wrong price, and wrote only what changed: 6 rows in crawl 2, 7 in crawl 3, and nothing for the 120 pages seen twice. Two of the 243 are products the shop no longer sells: an upsert never deletes, so their rows stay with their last prices. That is how a store can dedupe every crawl: one row per product, however often the product is seen. When a page shows no SKU, other candidates for the key are its address, a canonical form of that address, the product’s name or a listing number the site shows.

Key idea

Key every table of records by what identifies the thing, not by when it was stored, so that a crawl can run again and change only what changed.

Checkpoint

Check: Raw pages, records and history in their stores · An upsert on a natural key

Question 1 of 2 A product's current price changes on many crawls and is looked up by its SKU. Which store fits it?
Choose one answer.

SQLite, keyed by the SKU A CSV file A JSON Lines file A Parquet file

Show the answer: Question 1 of 2

Answer: SQLite, keyed by the SKU

Records that change belong in SQLite, logs that only grow in JSON Lines, and history read by column in Parquet.
Question 2 of 2 INSERT OR IGNORE kept one row per SKU over the three crawls. Why were 10 prices still wrong?
Choose one answer.

It duplicated the rows of the changed products The SKU is not a good key The prices were stored as text When the key already exists it ignores the new record, so the changed prices never reached the rows

Show the answer: Question 2 of 2

Answer: When the key already exists it ignores the new record, so the changed prices never reached the rows

An upsert updates the stored row on a conflict, ON CONFLICT (sku) DO UPDATE, and its WHERE clause skips the write when nothing changed.

Text from a page goes in as a value, never as SQL

A product name is text from someone else’s page, and SQL built from it by string formatting lets that text decide what the statement says. The Python documentation warns that assembling queries with string operations makes them vulnerable to SQL injection, and points to placeholders, ? or :name, instead. The weakness shows long before anyone abuses it, because an apostrophe in an ordinary name breaks the statement. The program stores four names both ways and checks that each comes back as written:

Four product names, with SQL built from strings and with parameters Python · store/parameters.py

"""Product names from the shop's pages, stored by SQL built from strings and by SQL with parameters."""
import sqlite3

NAMES = ["Basmati rice 1 kg", "Nani's mango pickle 500 g", 'Kokum "Konkan" 250 g', "Rock salt; 1 kg"]


def store_built(db, sku, name):
    db.execute(f"INSERT INTO items VALUES ('{sku}', '{name}')")  # the weak version: the text becomes part of the SQL


def store_with_parameters(db, sku, name):
    db.execute("INSERT INTO items VALUES (?, ?)", (sku, name))  # the fix: the text travels as a value


def check(store):
    """Fails unless every name comes back exactly as the page wrote it."""
    db = sqlite3.connect(":memory:")
    db.execute("CREATE TABLE items (sku TEXT PRIMARY KEY, name TEXT)")
    for i, name in enumerate(NAMES):
        try:
            store(db, f"NB-{901 + i}", name)
        except sqlite3.Error as error:
            return f"FAIL on {name!r}: {type(error).__name__}: {error}"
    back = [name for (name,) in db.execute("SELECT name FROM items ORDER BY sku")]
    return "pass: every name stored exactly" if back == NAMES else f"FAIL: stored {back}"


print("SQL built from strings:", check(store_built))
print("SQL with parameters:   ", check(store_with_parameters))

Output

SQL built from strings: FAIL on "Nani's mango pickle 500 g": OperationalError: near "s": syntax error
SQL with parameters:    pass: every name stored exactly

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 parameters.py

SQL built from strings failed on Nani's mango pickle 500 g; with parameters, every name came back exactly.

Neutralise formula cells in every export

A spreadsheet app reads a cell that begins with =, +, - or @ as a formula, and OWASP adds tab, carriage return and line feed to that list. Before writing scraped text to a CSV for people, prefix such text cells with an apostrophe, and leave number columns as numbers.

Checkpoint

Check: Values from a page as parameters, and safe exports

One question Why did the SQL built from strings fail on the product name Nani's mango pickle 500 g?
Choose one answer.

SQLite cannot store apostrophes The name is too long for the column The table has no name column The apostrophe ended the quoted text early, so the rest of the name was read as SQL

Show the answer: One question

Answer: The apostrophe ended the quoted text early, so the rest of the name was read as SQL

With a placeholder, ? or :name, the value travels apart from the SQL, so no character of a page can change what the statement says.

Choosing a natural key when the page has no SKU Pro: opens with a Pro, Premium or Ultimate pass

The practice shop prints a SKU on every product; many sites print nothing so convenient. This part stores two crawls of a marketplace under four candidate keys and measures what each one gets wrong.

In this part:

Four candidate keys, measured on two crawls
What makes a key last

1 runnable example (Python) · about 3 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a pass I have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

History and re-runs that change nothing, measured Pro: opens with a Pro, Premium or Ultimate pass

A store with history keeps one current row per product and one row for every change, and a crawl run twice must leave both as they were. This part builds that store in SQLite, runs it on the three crawls and in Node, and has you write it.

In this part:

Current rows and a history of changes, measured
The same counts from node:sqlite, and the versions behind them
Practise: a store a crawl can run into twice
A store that a crawl can run into any number of times
- Results of the sample tests

2 runnable examples (crawls.mjs, crawls.py, upsert_history.mjs, upsert_history.py) · 1 exercise with sample tests · about 5 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a pass I have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Exports and raw snapshots that stay safe and small Pro: opens with a Pro, Premium or Ultimate pass

Two kinds of file leave the database: exports for people and raw snapshots for re-parsing. This part tests a CSV export for formula cells and fixes it without breaking numbers, then keeps 841 fetched pages in 6 % of their size.

In this part:

Formula cells in a CSV export, neutralised and tested
Raw pages kept once, by their hash

2 runnable examples (Python, crawls.py, snapshots.py) · about 4 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a pass I have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Practise storing records

Test yourself on stores, keys and parameters, weakest idea first:

Interview questions

Warm-up (fresher): What is an upsert, and why does a scraper need one?

An upsert is an insert that becomes an update when a row with the same key already exists: in SQLite, INSERT … ON CONFLICT (sku) DO UPDATE SET …. A scraper needs one because it sees the same products on every crawl, and on some crawls twice, after a restart. With a plain insert each sighting adds a row; with the shop’s SKU as the key and an upsert, each product keeps one row that takes its latest values. Adding WHERE to the update writes nothing when no value changed, so a crawl can be run again with no effect.

Three harder questions about stores come next: a doubled report after a restart, two years of daily prices, and a formula in a partner’s export. Their first steps and follow-up questions are in the module’s bank:

Fresher to mid

A crawl was restarted after a crash halfway through, and the daily report now counts about half of the products twice. How do you make the store safe to re-run, and how do you clean up the rows already doubled?

Model answer, Pro. Model answers open with any pass; prices are in the notice on this page. First step and follow-ups
Mid to senior

You must keep two years of daily prices for 50,000 products and answer what any product cost on any day. Where do you keep current records, raw pages and the price history, and what does each choice cost?

Model answer, Pro. First step and follow-ups
Fresher to mid

A partner opened your CSV export of scraped reviews in a spreadsheet app, and one review ran as a formula. What went wrong, and how do you export safely without corrupting values such as negative price changes?

Model answer, Pro. First step and follow-ups

Key takeaways

Keep raw pages, current records and history apart: SQLite for records that change, JSON Lines for logs that only grow, Parquet for history read by column.
Key records by a natural key such as the SKU and upsert them, so that a re-run updates instead of duplicating.
Pass every value from a page as a parameter, and neutralise formula cells before exporting text for people.

Terms in this lesson

natural key: A key taken from the data itself, such as a product's SKU or a seller and a listing number, that names the same thing on every crawl, so that a second sighting updates its row instead of adding one.
upsert: An insert that becomes an update when a row with the same key already exists. SQLite writes it INSERT … ON CONFLICT (key) DO UPDATE, with an optional WHERE that skips rows whose values did not change.

References

About SQLite (SQLite)
UPSERT (SQLite documentation) (SQLite)
Built-in scalar SQL functions, total_changes() and sqlite_version() (SQLite documentation) (SQLite)
The RETURNING clause (SQLite documentation) (SQLite)
JSON functions and operators (SQLite documentation) (SQLite)
sqlite3: DB-API 2.0 interface for SQLite databases (Python 3.14) (Python Software Foundation)
SQLite (Node.js 24 documentation) (OpenJS Foundation)
JSON Lines (JSON Lines)
Product (schema.org) (Schema.org)
Apache Parquet overview (The Apache Software Foundation)
Reading and writing the Apache Parquet format (PyArrow documentation) (The Apache Software Foundation)
CSV Injection (OWASP Foundation)
hashlib: Secure hashes and message digests (Python 3.14) (Python Software Foundation)

Report a problem with this lesson

In this module: Cleaning, validating and storing data

Clean prices, dates, units and Indian numbers Free lesson, Pro answers: 30 minutes
Validate every record: schemas and quarantine Free core, Pro depth: 7 of 20 minutes free
Store and dedupe: SQLite, JSON Lines, Parquet Free core, Pro depth: 8 of 20 minutes free
Detect changes over time: hashes and diffs Free core, Pro depth: 7 of 20 minutes free

Your country

Store and dedupe: SQLite, JSON Lines, Parquet

What you will learn

Before you start

What you will learn

Before you start

Raw pages, records and history belong in different stores

A natural key turns a re-run into an update

Text from a page goes in as a value, never as SQL

Choosing a natural key when the page has no SKU Pro: opens with a Pro, Premium or Ultimate pass

History and re-runs that change nothing, measured Pro: opens with a Pro, Premium or Ultimate pass

Exports and raw snapshots that stay safe and small Pro: opens with a Pro, Premium or Ultimate pass

Practise storing records

Interview questions

A crawl was restarted after a crash halfway through, and the daily report now counts about half of the products twice. How do you make the store safe to re-run, and how do you clean up the rows already doubled?

You must keep two years of daily prices for 50,000 products and answer what any product cost on any day. Where do you keep current records, raw pages and the price history, and what does each choice cost?

A partner opened your CSV export of scraped reviews in a spreadsheet app, and one review ran as a formula. What went wrong, and how do you export safely without corrupting values such as negative price changes?

Key takeaways

References

Related tools