Python and SQL: Databases with SQLite from Scratch

Welcome to Level 4: Expert. You can already code, analyze data with pandas and call APIs. Now you'll add four skills that show up in almost every data and developer job posting: SQL databases, charts, web scraping and an introduction to machine learning. We start with SQL, the language used to query the databases behind nearly every company in the world.

Why a database instead of a CSV

A CSV is fine for exchanging data, but it falls short when data grows or when several programs use it at once:

SQLite is a complete database that lives in a single file and comes with Python through the sqlite3 module. Web browsers and phone apps use it, and the SQL you learn here is nearly identical to PostgreSQL, MySQL and SQL Server.

Create the database and load the data

import csv
import sqlite3

con = sqlite3.connect("roaster.db")   # creates the file if it doesn't exist
cur = con.cursor()

cur.execute("""
    CREATE TABLE IF NOT EXISTS sales (
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
        date     TEXT NOT NULL,
        region   TEXT NOT NULL,
        rep      TEXT NOT NULL,
        product  TEXT NOT NULL,
        units    INTEGER NOT NULL,
        price    REAL NOT NULL
    )
""")

with open("sales.csv", encoding="utf-8") as f:
    rows = [(r["date"], r["region"], r["rep"], r["product"],
             int(r["units"]), float(r["price"])) for r in csv.DictReader(f)]

cur.executemany(
    "INSERT INTO sales (date, region, rep, product, units, price) "
    "VALUES (?, ?, ?, ?, ?, ?)",
    rows,
)
con.commit()
print(cur.execute("SELECT COUNT(*) FROM sales").fetchone()[0], "sales loaded")

10 sales loaded

• CREATE TABLE defines the columns and their types. PRIMARY KEY gives each row a unique ID, and NOT NULL blocks missing values.
• The ? marks are parameters: the values travel separately, in the tuple. You'll see below why that matters so much.
• commit() saves the changes. Without it, they're lost when the program ends.

Querying: SELECT, WHERE and ORDER BY

for row in cur.execute(
    "SELECT date, rep, units FROM sales "
    "WHERE region = 'West' ORDER BY date"
):
    print(row)

('2026-01-05', 'Emily', 120)
('2026-01-20', 'Emily', 100)
('2026-02-21', 'Emily', 40)
('2026-03-18', 'Emily', 55)

A query is always written in this order:

SELECT   columns or calculations
FROM     table
WHERE    row filter
GROUP BY grouping
HAVING   group filter
ORDER BY sort order
LIMIT    maximum number of rows

But the database doesn't run it in that order, which explains why WHERE can't filter on a SUM():

1. FROM and JOINGather the tablesWhere the data comes from.
2. WHEREFilter rowsNo totals exist yet: SUM() isn't available here.
3. GROUP BY and HAVINGGroup and filter groupsNow there are totals, and HAVING filters them.
4. SELECTCompute the columnsThis is where aliases like AS total are born.
5. ORDER BY and LIMITSort and trimThat's why ORDER BY total can see the alias.

Summarizing: GROUP BY

It's the equivalent of a pivot table, or pandas' groupby:

query = """
    SELECT region,
           SUM(units * price) AS total,
           COUNT(*)           AS n_sales
    FROM sales
    GROUP BY region
    ORDER BY total DESC
"""
for region, total, n in cur.execute(query):
    print(f"{region:<8}{total:>10,.2f}{n:>4}")

West      4,600.00   4
East      2,707.50   3
Midwest   1,890.00   2
            980.00   1

What's that last row with no name? It's the sale with a blank region you already found while cleaning data in lesson 11. SQL has functions to handle it at query time: COALESCE(NULLIF(region, ''), 'Unknown') turns the empty string into "Unknown."

Linking tables: JOIN

Each rep's commission rate lives in another table. JOIN combines them on a shared column, the way XLOOKUP does in Excel:

cur.execute("CREATE TABLE IF NOT EXISTS reps "
            "(name TEXT PRIMARY KEY, commission REAL NOT NULL)")
cur.executemany("INSERT OR REPLACE INTO reps VALUES (?, ?)",
                [("Emily", 0.05), ("Jake", 0.04), ("Maria", 0.06)])
con.commit()

query = """
    SELECT s.rep,
           SUM(s.units * s.price)                AS total,
           SUM(s.units * s.price) * r.commission AS commission
    FROM sales AS s
    JOIN reps AS r ON r.name = s.rep
    GROUP BY s.rep
    ORDER BY commission DESC
"""
for name, total, commission in cur.execute(query):
    print(f"{name:<6}{total:>10,.2f}{commission:>9,.2f}")

Emily   4,600.00   230.00
Maria   2,870.00   172.20
Jake    2,707.50   108.30

JOIN (or INNER JOIN) only returns rows that have a match in both tables. If a rep were missing from the reps table, their sales would vanish from the result. To keep them, use LEFT JOIN: it keeps every row from the first table and leaves blanks where there's no match. For more practice, our data analytics course has a full lesson on SQL GROUP BY and joins.

Parameters: security rule number one

Never build a query by pasting in text that came from a user:

# ❌ DANGEROUS: the user's text gets mixed into the SQL
name = input("Rep: ")
cur.execute(f"SELECT * FROM sales WHERE rep = '{name}'")

If someone types Jake' OR '1'='1, the query turns into "return every sale" (we tested it: all 10 rows come back), and other inputs could delete tables. That's called SQL injection, one of the most exploited vulnerabilities in history; our cybersecurity course covers it in the lesson on web security and the OWASP Top 10. With parameters, the value is never treated as SQL:

# ✅ SAFE
def sales_by(rep):
    return cur.execute(
        "SELECT COUNT(*), SUM(units) FROM sales WHERE rep = ?",
        (rep,),
    ).fetchone()

print(sales_by("Jake"))
print(sales_by("Jake' OR '1'='1"))

(3, 285)
(0, None)

Notice the comma in (rep,): parameters always go in a tuple, even when there's only one.

Changing data: UPDATE and DELETE

with con:   # commits when the block ends (or rolls back on an error)
    con.execute("UPDATE sales SET region = 'Midwest' WHERE region = ''")

The sale with no region belonged to Maria, who covers the Midwest, so we fix it in the database. Always double-check the WHERE before running an UPDATE or DELETE: without it, the change hits every row in the table. A good habit is to run a SELECT with the same WHERE first to see exactly which rows will change.

From SQL to pandas

The best of both worlds: SQL to filter and summarize inside the database, pandas to keep analyzing or chart the result:

import pandas as pd

df = pd.read_sql(
    "SELECT region, SUM(units * price) AS total FROM sales GROUP BY region",
    con,
)
print(df)
con.close()

    region   total
0     East  2707.5
1  Midwest  2870.0
2     West  4600.0

Maria's $980 sale now counts toward the Midwest. To save a DataFrame as a new table, use df.to_sql("summary", con, if_exists="replace", index=False).

Real case: appointments at a San Diego dental office

A dental office in San Diego wants to know how much it bills each month and which patients have never booked an appointment, so the front desk can call them. There are two tables linked by patient_id:

import sqlite3

con = sqlite3.connect(":memory:")      # temporary in-memory database, great for practice
con.executescript("""
    CREATE TABLE patients (id INTEGER PRIMARY KEY, name TEXT NOT NULL, neighborhood TEXT);
    CREATE TABLE appointments (id INTEGER PRIMARY KEY, patient_id INTEGER REFERENCES patients(id),
                               date TEXT NOT NULL, treatment TEXT NOT NULL, fee INTEGER NOT NULL);
""")
con.executemany("INSERT INTO patients VALUES (?, ?, ?)", [
    (1, "Camila Ross", "Hillcrest"), (2, "Andrew Gomez", "La Jolla"),
    (3, "Laura Mendez", "Point Loma"), (4, "Julian Parker", "North Park"),
])
con.executemany("INSERT INTO appointments (patient_id, date, treatment, fee) VALUES (?, ?, ?, ?)", [
    (1, "2026-03-03", "Cleaning", 120), (2, "2026-03-05", "Filling", 180),
    (1, "2026-04-07", "Whitening", 450), (3, "2026-04-10", "Cleaning", 120),
    (2, "2026-04-22", "Checkup", 60),
])

for month, n, total in con.execute("""
        SELECT substr(date, 1, 7) AS month, COUNT(*), SUM(fee)
        FROM appointments GROUP BY month ORDER BY month"""):
    print(f"{month}: {n} appointments, ${total:,}")

no_visits = con.execute("""
    SELECT p.name, p.neighborhood FROM patients AS p
    LEFT JOIN appointments AS a ON a.patient_id = p.id
    WHERE a.id IS NULL""").fetchall()
print("Patients with no appointments:", no_visits)

2026-03: 2 appointments, $300
2026-04: 3 appointments, $630
Patients with no appointments: [('Julian Parker', 'North Park')]

The trick in the second query: LEFT JOIN keeps every patient and fills the appointment columns with NULL for anyone who has none; WHERE a.id IS NULL keeps exactly those. And substr(date, 1, 7) trims "2026-04-07" to "2026-04" so you can group by month.

Common SQLite errors

The most puzzling sqlite3 error for beginners is this one:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 4 supplied.

The query has one ? but received 4 values. Why 4? Because you wrote ("Jake") without a comma: that isn't a tuple, it's the string "Jake," and Python walked through it letter by letter.

print(cur.execute("""
    SELECT product, SUM(units), SUM(units * price) AS revenue
    FROM sales GROUP BY product ORDER BY revenue DESC
""").fetchall())
# [('Coffee', 425, 5950.0), ('Tea', 285, 2707.5), ('Chocolate', 95, 1520.0)]

print(cur.execute("""
    SELECT rep, MAX(units * price) FROM sales GROUP BY rep
""").fetchall())
# [('Emily', 1680.0), ('Jake', 1045.0), ('Maria', 1050.0)]

for row in cur.execute("""
    SELECT date, rep, product, units * price AS amount
    FROM sales WHERE units * price > 1000 ORDER BY amount DESC
"""):
    print(row)
# ('2026-01-05', 'Emily', 'Coffee', 1680.0)
# ('2026-01-20', 'Emily', 'Coffee', 1400.0)
# ('2026-03-02', 'Maria', 'Coffee', 1050.0)
# ('2026-02-15', 'Jake', 'Tea', 1045.0)

Summary

• SQLite comes with Python: a complete database in one file, through the sqlite3 module.
• SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY is the shape of almost every query.
• JOIN combines tables on a shared column; LEFT JOIN keeps rows with no match.
• Always pass values as parameters (?) to prevent SQL injection.
• pd.read_sql brings a query's result into pandas.

Next lesson: Python Charts with matplotlib: Bar Charts, Line Charts and a Report Dashboard. You'll turn these results into clear charts ready for a report.