Databases & Logging

Real programs store data and leave a trail. This chapter introduces SQLite, the file-based database built into Python, and the logging module, the standard way to record what a program did and why. Together they turn throwaway scripts into systems you can inspect, debug, and trust.

CRUD with SQLite3 and Python

CRUD stands for Create, Read, Update, Delete — the four fundamental operations on stored data. SQLite stores a whole database in one ordinary file, needs no server, and ships with Python, so it is perfect for learning and for small applications.

Connecting and Creating a Table

import sqlite3

conn = sqlite3.connect("shop.db")
cur = conn.cursor()

cur.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        price REAL,
        stock INTEGER
    )
""")
conn.commit()

Inserting Rows

cur.execute("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)",
            ("Mug", 12.50, 40))
cur.execute("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)",
            ("Notebook", 6.75, 120))
cur.executemany(
    "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)",
    [("Pen", 1.20, 300), ("Bag", 28.00, 15)]
)
conn.commit()

Selecting, Updating, and Deleting

cur.execute("SELECT * FROM products")
for row in cur.fetchall():
    print(row)

cur.execute("SELECT name, price FROM products WHERE stock < 50")
print(cur.fetchall())

cur.execute("UPDATE products SET stock = stock - 5 WHERE name = 'Mug'")
cur.execute("DELETE FROM products WHERE name = 'Bag'")
conn.commit()

cur.execute("SELECT name, stock FROM products")
print(cur.fetchall())
conn.close()

SQLite Assignments and Solutions

Exercise A: Build a Bookstore

1. Create a table books with columns title, author, and year.
2. Insert five books, update the year of one, and delete another.
3. Print all books published after 2000.

import sqlite3

conn = sqlite3.connect("books.db")
cur = conn.cursor()
cur.execute("""CREATE TABLE IF NOT EXISTS books (
    title TEXT, author TEXT, year INTEGER)""")

books = [
    ("The Left Hand of Darkness", "Ursula K. Le Guin", 1969),
    ("Dune", "Frank Herbert", 1965),
    ("Project Hail Mary", "Andy Weir", 2021),
    ("Klara and the Sun", "Kazuo Ishiguro", 2021),
    ("Neuromancer", "William Gibson", 1984),
]
cur.executemany("INSERT INTO books VALUES (?, ?, ?)", books)

cur.execute("UPDATE books SET year = 1966 WHERE title = 'Dune'")
cur.execute("DELETE FROM books WHERE title = 'Neuromancer'")

cur.execute("SELECT * FROM books WHERE year > 2000")
print(cur.fetchall())
conn.commit()
conn.close()

Exercise B: Aggregates

Extend Exercise A to answer: how many books exist in the table, and what is the newest year? Use SELECT COUNT(*) and SELECT MAX(year).

Practical Logging in Python

print() is fine while you are exploring, but it cannot be turned on and off per severity, written to a file, or filtered at runtime. The logging module gives you all of that.

Levels

Messages are ranked by severity. The defaults, from least to most serious:

• DEBUG — detailed diagnostics, usually only enabled while developing.
• INFO — confirmation that things are working as expected.
• WARNING — something unexpected, but the program continues.
• ERROR — a real problem, though the run may continue.
• CRITICAL — the program cannot continue safely.

import logging

logging.basicConfig(level=logging.INFO,
                    format="%(asctime)s | %(levelname)s | %(message)s")

logging.debug("hidden unless DEBUG is enabled")
logging.info("program started")
logging.warning("disk space is low")
logging.error("failed to connect to service")

Handlers and Formatters

A handler decides where a record goes — the console, a file, or elsewhere — and a formatter decides how it is written. Multiple handlers can exist at once.

logger = logging.getLogger("app")
logger.setLevel(logging.DEBUG)

file_handler = logging.FileHandler("app.log")
stream_handler = logging.StreamHandler()
formatter = logging.Formatter("%(asctime)s %(name)s %(levelname)s %(message)s")
file_handler.setFormatter(formatter)
stream_handler.setFormatter(formatter)

logger.addHandler(file_handler)
logger.addHandler(stream_handler)

logger.info("Both handlers received this line")

Multiple Loggers

# In utils.py
import logging
util_logger = logging.getLogger("app.utils")

# In main.py
import logging
main_logger = logging.getLogger("app.main")

main_logger.info("starting pipeline")
util_logger.warning("utils module noticed an odd value")

Because loggers form a hierarchy (app.main is a child of app), handlers attached to app receive records from all its children — unless you explicitly stop propagation with logger.propagate = False.

A Real-World Logging Example

Here is a miniature data-processing job that logs each phase and any failures.

import logging

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s %(levelname)s %(name)s: %(message)s",
    filename="pipeline.log",
)

log = logging.getLogger("etl")

def clean_row(row: dict) -> dict:
    try:
        cleaned = {k: str(v).strip() for k, v in row.items()}
        log.info("cleaned row for id=%s", row.get("id"))
        return cleaned
    except Exception as exc:
        log.error("failed to clean id=%s: %s", row.get("id"), exc)
        raise

rows = [{"id": 1, "name": "  Maya "}, {"id": 2, "name": None}]
for r in rows:
    clean_row(r)

log.warning("total rows processed: %d", len(rows))

Logging Assignments and Solutions

Exercise A: Level Router

Configure logging so that INFO and above goes to a file app.log, while WARNING and above is also printed to the console. Emit one message of each severity and confirm both destinations receive the expected records.

import logging

log = logging.getLogger("router")
log.setLevel(logging.INFO)

file_h = logging.FileHandler("app.log")
file_h.setLevel(logging.INFO)
console_h = logging.StreamHandler()
console_h.setLevel(logging.WARNING)
fmt = logging.Formatter("%(levelname)s: %(message)s")
file_h.setFormatter(fmt)
console_h.setFormatter(fmt)

log.addHandler(file_h)
log.addHandler(console_h)

log.info("to file only")
log.warning("to file and console")

Exercise B: Logging the Pipeline

Wrap a numeric function (for example, dividing two numbers) in a try/except that logs the inputs at DEBUG, the result at INFO, and a ZeroDivisionError at ERROR. Run it twice, once successfully and once with a zero denominator, then read pipeline.log.