Databases with Python — Python Course

What you'll learn

Almost every real application stores data in a database. This lesson covers relational databases and SQL, talking to them from Python with the built-in sqlite3, staying safe from injection, and working at a higher level with an ORM.

By the end of this lesson you'll be able to:

Read and write basic SQL (CREATE, INSERT, SELECT, UPDATE, DELETE)
Query a database with sqlite3 and the DB-API
Prevent SQL injection with parameterized queries
Group related changes into transactions
Explain what an ORM does, using SQLAlchemy

Relational databases & SQL

A relational database stores data in tables of rows and typed columns. SQL is the language you use to define and manipulate it — the same core statements work across SQLite, PostgreSQL, MySQL, and others.

basics.sql

-- Tables hold rows; each column has a type
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    age   INTEGER
);

INSERT INTO users (name, age) VALUES ('Ada', 36);

SELECT name FROM users WHERE age > 30 ORDER BY name;
UPDATE users SET age = 37 WHERE name = 'Ada';
DELETE FROM users WHERE id = 1;

sqlite3 & the DB-API

SQLite is a full SQL database in a single file, built right into Python — perfect for learning, tests, and small apps. Python's DB-API gives every database driver the same shape: connect, get a cursor, execute, commit, fetch.

db.py

import sqlite3

conn = sqlite3.connect("app.db")     # a file (or ":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")

cur.execute("INSERT INTO users (name) VALUES (?)", ("Ada",))
conn.commit()                        # save the change

cur.execute("SELECT id, name FROM users")
print(cur.fetchall())                # [(1, 'Ada')]
conn.close()

Parameterized queries

The single most important database rule: never build SQL by concatenating user input. Use ? placeholders and pass values separately — the driver escapes them, closing the door on SQL injection.

safe.py

name = "Robert'); DROP TABLE users; --"

# NEVER build SQL by formatting strings — this is SQL injection:
cur.execute(f"SELECT * FROM users WHERE name = '{name}'")   # DANGER

# ALWAYS pass values as parameters — the driver escapes them safely:
cur.execute("SELECT * FROM users WHERE name = ?", (name,))  # safe

Watch out

The dangerous version lets input like Robert'); DROP TABLE users; -- execute as SQL. This is the classic "Little Bobby Tables" attack. Parameterized queries make it impossible — the input is always treated as data, never as code.

Transactions

A transaction groups multiple changes so they either all succeed or all roll back — no half-finished states. A money transfer is the classic example: debit and credit must happen together. With sqlite3, a with conn: block is a transaction.

transfer.py

import sqlite3
conn = sqlite3.connect("bank.db")

try:
    with conn:                       # a transaction: commit on success, rollback on error
        conn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        conn.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    # both updates land together, or neither does
except sqlite3.Error:
    print("transfer failed — rolled back")

Key idea

Transactions give you the A in ACID — atomicity. If the second update fails, the first is undone, so you never lose money or leave data inconsistent. Wrap any multi-step change that must stay consistent in a transaction.

ORMs & SQLAlchemy

An ORM (Object-Relational Mapper) lets you work with database rows as Python objects instead of raw SQL strings. SQLAlchemy is the standard: define a class per table, and it generates the SQL for you.

orm.py

from sqlalchemy import create_engine, select
from sqlalchemy.orm import Session, DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class User(Base):                        # a Python class maps to a table
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]

engine = create_engine("sqlite:///app.db")
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(User(name="Ada"))        # INSERT, as an object
    session.commit()
    users = session.scalars(select(User)).all()   # rows come back as User objects

Note

ORMs reduce boilerplate, map results to typed objects, and use parameterized queries by default (safer). The trade-off is a layer of abstraction — for complex reports or performance-critical queries, dropping to raw SQL is perfectly normal, even alongside an ORM.

Recap & quick check

Key takeaways

Relational databases store typed rows in tables; SQL (CREATE/INSERT/SELECT/UPDATE/DELETE) manipulates them.
sqlite3 is built in; the DB-API pattern is connect -> cursor -> execute -> commit -> fetch.
ALWAYS use parameterized queries (? placeholders) — never format user input into SQL (injection risk).
A transaction (with conn:) makes multiple changes atomic: all commit or all roll back.
An ORM like SQLAlchemy maps tables to Python classes and rows to objects, generating safe SQL.
Use an ORM for everyday CRUD; drop to raw SQL for complex or performance-critical queries.

Quick check

1. Which SQL statement reads data?

2. How do you safely include user input in a sqlite3 query?

3. What does a transaction guarantee?

4. What is SQL injection?

5. What does an ORM like SQLAlchemy do?

Databases, APIs, and data analysis covered — next we automate the boring stuff and gather data from the web. Next up: Module 38 — Automation, Scripting & Web Scraping.