Python Lesson 41 – Databases | Dataplexa

Databases

Almost every real application stores data that outlives the program — user accounts, orders, logs, content. Databases are the standard solution. Python gives you two main paths: the built-in sqlite3 module for lightweight, file-based databases with no setup, and SQLAlchemy — the most widely used Python database toolkit — for working with any database engine through a clean, Pythonic interface.

This lesson covers both: raw SQL with sqlite3, the SQLAlchemy Core for flexible query building, and the SQLAlchemy ORM for mapping Python classes directly to database tables.

Relational Databases — Core Concepts

Before writing any code, a few terms worth pinning down:

Table — stores data in rows and columns, like a spreadsheet with strict types
Row — one record in a table
Column — a named, typed field in every row
Primary key — a column (usually id) that uniquely identifies each row
Foreign key — a column that references the primary key of another table, creating a relationship
SQL — Structured Query Language — the standard language for reading and writing relational data

SQLite with the Built-in sqlite3 Module

SQLite stores an entire database in a single file. No server, no installation, no configuration — just import and go. It is the right tool for scripts, prototypes, small apps, and local data storage.

Real-world use: mobile apps, desktop tools, data pipelines, test suites, and any project where you need a real database but not the overhead of PostgreSQL or MySQL.

# SQLite basics — create, insert, query, update, delete

import sqlite3

# connect() creates the file if it does not exist
# Use ":memory:" for a temporary in-memory database
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

# CREATE TABLE
cursor.execute("""
    CREATE TABLE users (
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
        name    TEXT    NOT NULL,
        email   TEXT    UNIQUE NOT NULL,
        age     INTEGER
    )
""")

# INSERT — use ? placeholders, never f-strings (SQL injection risk)
users = [
    ("Alice",   "alice@example.com",  30),
    ("Bob",     "bob@example.com",    25),
    ("Charlie", "charlie@example.com", 35),
]
cursor.executemany(
    "INSERT INTO users (name, email, age) VALUES (?, ?, ?)", users
)
conn.commit()   # persist changes

# SELECT
cursor.execute("SELECT id, name, age FROM users WHERE age > ?", (26,))
rows = cursor.fetchall()   # list of tuples
for row in rows:
    print(row)

# UPDATE
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, "Alice"))
conn.commit()

# DELETE
cursor.execute("DELETE FROM users WHERE name = ?", ("Bob",))
conn.commit()

# Verify final state
cursor.execute("SELECT * FROM users")
print("\nFinal table:")
for row in cursor.fetchall():
    print(row)

conn.close()

(1, 'Alice', 30)
(3, 'Charlie', 35)

Final table:
(1, 'Alice', 'alice@example.com', 31)
(3, 'Charlie', 'charlie@example.com', 35)

Always use ? placeholders — never string-format values into SQL queries. Doing so opens a SQL injection vulnerability.
conn.commit() saves changes — without it, inserts/updates/deletes are not persisted
cursor.fetchall() returns all rows as a list of tuples; cursor.fetchone() returns a single row
cursor.executemany() inserts multiple rows efficiently in one call

Using sqlite3 as a Context Manager

SQLite connections work as context managers. The with conn: block automatically commits on success and rolls back on exception — cleaner and safer than manual commit/rollback.

# Context manager — automatic commit and rollback

import sqlite3

conn = sqlite3.connect(":memory:")

# Create table
conn.execute("""
    CREATE TABLE products (
        id    INTEGER PRIMARY KEY AUTOINCREMENT,
        name  TEXT    NOT NULL,
        price REAL    NOT NULL
    )
""")

# with conn: automatically commits on success, rolls back on exception
with conn:
    conn.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Notebook", 4.99))
    conn.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Pen", 1.50))
    conn.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Desk", 89.99))

# Use Row factory for dict-like access
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM products ORDER BY price")

for row in cursor.fetchall():
    print(f"{row['name']:10} ${row['price']:.2f}")

conn.close()

Pen $1.50
Notebook $4.99
Desk $89.99

conn.row_factory = sqlite3.Row lets you access columns by name (row["name"]) instead of index (row[0])
The with conn: context manager handles transactions — it does not close the connection
Call conn.close() explicitly when done, or use with sqlite3.connect(...) as conn: for both

SQLAlchemy — The Pythonic Database Toolkit

SQLAlchemy is the standard Python database library for production applications. It provides two levels: the Core (close to raw SQL but with Python objects) and the ORM (maps Python classes to tables, abstracts SQL almost entirely). Both use the same connection engine and work with SQLite, PostgreSQL, MySQL, and more — swap the connection string and the code stays the same.

# SQLAlchemy — installation and engine setup
# pip install sqlalchemy

from sqlalchemy import create_engine, text

# SQLite in-memory — swap URL for PostgreSQL, MySQL etc. without changing any other code
# PostgreSQL: "postgresql://user:password@localhost/dbname"
# MySQL:      "mysql+pymysql://user:password@localhost/dbname"
engine = create_engine("sqlite:///:memory:", echo=False)

# execute raw SQL through SQLAlchemy
with engine.connect() as conn:
    conn.execute(text("""
        CREATE TABLE employees (
            id         INTEGER PRIMARY KEY,
            name       TEXT    NOT NULL,
            department TEXT,
            salary     REAL
        )
    """))
    conn.execute(text("""
        INSERT INTO employees (name, department, salary) VALUES
        ('Alice',   'Engineering', 95000),
        ('Bob',     'Marketing',   72000),
        ('Charlie', 'Engineering', 88000),
        ('Diana',   'HR',          65000)
    """))
    conn.commit()

# Query with parameters
with engine.connect() as conn:
    result = conn.execute(
        text("SELECT name, salary FROM employees WHERE department = :dept ORDER BY salary DESC"),
        {"dept": "Engineering"}
    )
    for row in result:
        print(f"{row.name}: ${row.salary:,.0f}")

Alice: $95,000
Charlie: $88,000

create_engine(url) sets up the connection — it does not open a connection immediately
Use :param_name placeholders with a dict in SQLAlchemy (not ? like bare sqlite3)
engine.connect() as a context manager auto-closes the connection
Changing the database is just changing the URL string — all query code remains identical

SQLAlchemy ORM — Classes as Tables

The ORM maps Python classes to database tables. You define your schema once as Python classes and interact with the database entirely through objects — no SQL strings required for most operations.

Real-world use: Django and Flask-SQLAlchemy both use ORM patterns. Every model in a web app — User, Order, Product — is an ORM class.

# SQLAlchemy ORM — define models and query with Python objects

from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.orm import DeclarativeBase, Session

engine = create_engine("sqlite:///:memory:")

# Define the base class all models inherit from
class Base(DeclarativeBase):
    pass

# Define a model — maps to the "products" table
class Product(Base):
    __tablename__ = "products"

    id       = Column(Integer, primary_key=True, autoincrement=True)
    name     = Column(String,  nullable=False)
    category = Column(String)
    price    = Column(Float,   nullable=False)

    def __repr__(self):
        return f"Product({self.name!r}, ${self.price:.2f})"

# Create all tables
Base.metadata.create_all(engine)

# Insert rows using Python objects
with Session(engine) as session:
    session.add_all([
        Product(name="Laptop",   category="Electronics", price=999.99),
        Product(name="Notebook", category="Stationery",  price=4.99),
        Product(name="Monitor",  category="Electronics", price=349.99),
        Product(name="Pen",      category="Stationery",  price=1.50),
    ])
    session.commit()

# Query — filter, order, limit
with Session(engine) as session:
    # All electronics sorted by price
    electronics = (
        session.query(Product)
        .filter(Product.category == "Electronics")
        .order_by(Product.price.desc())
        .all()
    )
    for p in electronics:
        print(p)

    # Count all products
    total = session.query(Product).count()
    print(f"\nTotal products: {total}")

Product('Laptop', $999.99)
Product('Monitor', $349.99)

Total products: 4

Each model class inherits from Base and uses __tablename__ to name the table
session.add(obj) stages one object; session.add_all([...]) stages many
session.commit() persists all staged changes; session.rollback() cancels them
session.query(Model).filter(...).order_by(...).all() is the standard query pattern

ORM — Update and Delete

# ORM — updating and deleting records

with Session(engine) as session:
    # Update — fetch, modify, commit
    laptop = session.query(Product).filter(Product.name == "Laptop").first()
    if laptop:
        laptop.price = 899.99
        session.commit()
        print("Updated:", laptop)

    # Delete — fetch and delete
    pen = session.query(Product).filter(Product.name == "Pen").first()
    if pen:
        session.delete(pen)
        session.commit()
        print("Deleted pen")

    # Verify
    remaining = session.query(Product).all()
    print("Remaining products:", remaining)

Updated: Product('Laptop', $899.99)
Deleted pen
Remaining products: [Product('Laptop', $899.99), Product('Notebook', $4.99), Product('Monitor', $349.99)]

Summary Table

Tool	Best For	Key Usage
`sqlite3`	Scripts, prototypes, local storage	`conn.execute(sql, params)`
SQLAlchemy Core	Flexible SQL with Python, multi-DB	`conn.execute(text(sql), params)`
SQLAlchemy ORM	Applications, web apps, complex models	`session.query(Model).filter(...)`
`conn.row_factory`	Named column access in sqlite3	`sqlite3.Row`
`?` / `:name`	Safe parameter binding	Always use — never f-string SQL values

Practice Questions

Practice 1. Why should you never use f-strings or string formatting to insert values into SQL queries?

Practice 2. What does conn.commit() do in sqlite3?

Practice 3. What does setting conn.row_factory = sqlite3.Row enable?

Practice 4. In SQLAlchemy ORM, what method stages multiple new objects for insertion in one call?

Practice 5. What is the main advantage of using SQLAlchemy over bare sqlite3 for production applications?

Quiz

Quiz 1. What connection string would you use for an in-memory SQLite database?

sqlite:///:memory:
:memory:
sqlite:///ram
memory://sqlite

Quiz 2. What happens inside a with conn: block in sqlite3 if an exception is raised?

The transaction is automatically rolled back
The transaction is committed regardless
The connection is closed
The exception is suppressed

Quiz 3. In SQLAlchemy ORM, what class attribute tells SQLAlchemy which table a model maps to?

__tablename__
__table__
table_name
__db_table__

Quiz 4. What is the placeholder syntax for named parameters in SQLAlchemy's text() queries?

:param_name
?
%(param_name)s
${param_name}

Quiz 5. Which SQLAlchemy method creates all tables defined by ORM models in the database?

Base.metadata.create_all(engine)
engine.create_tables()
session.create_all()
Base.create(engine)

Next up — CSV & XML: reading, writing, and parsing structured data files in Python.

← Previous Course Index Next →

Python Course