Import CSV with validation

Parse a CSV stream, validate values and insert the batch in one transaction.

This small fixture keeps the lesson self-contained. For a real file, open it with newline="" and encoding="utf-8" instead of StringIO. Validate headers and domain rules before adapting it to your own data. Avoid assembling INSERT statements from CSV text.

Run it on your computer

python3 csv-import.py

Requires Python 3.12+. The script creates its own in-memory sample. It does not read your browser database or upload anything.

import csv
import io
import sqlite3

source = io.StringIO("name,quantity\nNotebook,5\nPencil,12\n")
con = sqlite3.connect(":memory:", autocommit=False)
try:
    with con:
        con.execute("CREATE TABLE stock (name TEXT NOT NULL, quantity INTEGER CHECK(quantity >= 0))")
    with con:
        reader = csv.DictReader(source)
        if reader.fieldnames != ["name", "quantity"]:
            raise ValueError("Unexpected CSV headers")
        for row in reader:
            if not row["name"].strip():
                raise ValueError("Empty name")
            con.execute("INSERT INTO stock VALUES (?, ?)", (row["name"], int(row["quantity"])))
    print(con.execute("SELECT SUM(quantity) FROM stock").fetchone()[0])
finally:
    con.close()

Expected output

17

Try a change

Add a malformed quantity. Confirm that the batch fails rather than accepting partial imported data.

Practice SQL in the browser or use the Python sqlite3 documentation for API details.