Data Science Database Tables

How it works

A database table stores rows with a schema and can enforce relationships and constraints. The grain is what one row represents. Here one row is one kiosk opening day, identified by a date-like key. Aggregation changes that grain: a sum over the table is a period total, not another daily measurement. Inspect the query’s filters and groupings before comparing its output with a DataFrame.

Run the code

This is a local example: it uses native Python SQLite; the reviewed browser runtime has no sqlite3 module.

Local review: Python 3.12.14, NumPy 2.5.4, Pandas 3.0.6 and SciPy 1.18.0. Browser review: Pyodide 0.27.7, Python 3.12.7, NumPy 2.0.2, Pandas 2.2.3 and SciPy 1.14.1 in their respective editors. Save the download as database-tables.py and run python database-tables.py in your separate environment. Apache and MySQL are not needed. Environment setup.

Download the complete source

Example

import sqlite3
with sqlite3.connect(":memory:") as db:
    db.execute("CREATE TABLE daily (day TEXT PRIMARY KEY, litres REAL NOT NULL CHECK(litres >= 0))")
    db.executemany("INSERT INTO daily VALUES (?, ?)", [("2026-05-04", 18.), ("2026-05-05", 22.)])
    count, total = db.execute("SELECT COUNT(*), SUM(litres) FROM daily").fetchone()
    assert count == 2 and total == 40.
    print("Two daily rows; period total 40.0 litres")

Expected result

Two daily rows; period total 40.0 litres

The program creates an isolated in-memory SQLite database, inserts synthetic records with parameters and queries their total. The primary key prevents duplicate day identities. It does not prove the meter readings are accurate. A real data extraction also needs a clear date boundary and a consistent snapshot when the source changes.