🎓 A1Learner
🔍

Python Project: CSV → SQLite → JSON Pipeline

A pipeline is a chain of steps. The output of one step becomes the input of the next.

The supplied sales rows

These five rows are the input data. The first step saves them as sales.csv.

sales_text = (
    "product,units,price_cents\n"
    "pen,3,150\n"
    "book,2,1200\n"
    "pen,1,150\n"
    "mug,4,800\n"
    "book,1,1200\n"
)
with open("sales.csv", "w", newline="") as file:
    file.write(sales_text)

Read the CSV rows

DictReader reads the rows. The values still arrive as strings here.

import csv

with open("sales.csv", newline="") as file:
    reader = csv.DictReader(file)
    rows = list(reader)
print(len(rows))
print(rows[0])

Store the rows in SQLite

Convert units and price to int as you insert. The database then holds real numbers.

import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
cursor.execute("CREATE TABLE sales (product TEXT, units INTEGER, price_cents INTEGER)")
for row in rows:
    cursor.execute(
        "INSERT INTO sales VALUES (?, ?, ?)",
        (row["product"], int(row["units"]), int(row["price_cents"])),
    )
cursor.execute("SELECT COUNT(*) FROM sales")
print(cursor.fetchone()[0])

Aggregate units by product

An aggregate combines many rows into one summary value. GROUP BY makes one summary per product.

cursor.execute("SELECT product, SUM(units) FROM sales GROUP BY product ORDER BY product")
for product, total_units in cursor.fetchall():
    print(product, total_units)

Total the money in cents

Prices are integer cents, so the total is exact. No float rounding can creep in.

cursor.execute("SELECT SUM(units * price_cents) FROM sales")
total_cents = cursor.fetchone()[0]
print(total_cents)
print(total_cents // 100, "dollars and", total_cents % 100, "cents")

Example

import csv
import json
import sqlite3

sales_text = (
    "product,units,price_cents\n"
    "pen,3,150\n"
    "book,2,1200\n"
    "pen,1,150\n"
    "mug,4,800\n"
    "book,1,1200\n"
)
with open("sales.csv", "w", newline="") as file:
    file.write(sales_text)

with open("sales.csv", newline="") as file:
    reader = csv.DictReader(file)
    rows = list(reader)

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
cursor.execute("CREATE TABLE sales (product TEXT, units INTEGER, price_cents INTEGER)")
for row in rows:
    cursor.execute(
        "INSERT INTO sales VALUES (?, ?, ?)",
        (row["product"], int(row["units"]), int(row["price_cents"])),
    )

cursor.execute("SELECT product, SUM(units) FROM sales GROUP BY product ORDER BY product")
units_by_product = {}
for product, total_units in cursor.fetchall():
    units_by_product[product] = total_units

cursor.execute("SELECT SUM(units * price_cents) FROM sales")
total_cents = cursor.fetchone()[0]

report = {
    "sales_rows": len(rows),
    "total_cents": total_cents,
    "units_by_product": units_by_product,
}
print(json.dumps(report, indent=2, sort_keys=True))

Key points

  • Pipeline: a chain of steps where one step feeds the next
  • Use CSV for data that arrives as a table of text
  • Use SQLite to query the data once it is stored
  • Use SUM() with GROUP BY for one total per group
  • Use json.dumps() with sort_keys=True for a report with a fixed key order

In this example, one program runs the whole pipeline. It first saves the supplied rows as sales.csv. DictReader then reads the rows back as dictionaries. The loop converts units and price_cents to int and inserts each row into SQLite. The first aggregate query totals units for each product. ORDER BY product keeps that order fixed. The second aggregate query totals units times price_cents across all rows. That gives 7400 cents exactly. The report dictionary joins the three results. json.dumps() prints it with sorted keys and two-space indents.

Note

Money in floats can print noise like 73.99999999999999.

Integer cents keep every total exact.

Try it Yourself

Edit the code, then press Run.

Running is simulated · Python Playground

Exercise

Why does this pipeline store price_cents as an integer instead of dollars as a float?