SQLAlchemy or DB-API structured access

Talking to a relational database from Python in a structured way. The intro tutorials showed basic SQL; here we focus on safe connections, parameters, and a light ORM when raw SQL gets repetitive.

SQLite for learning and small tools

SQLite ships with Python. No server to install – a file on disk is enough for tutorials and many CLI utilities.

import sqlite3
from pathlib import Path

db_path = Path("app.db")
connection = sqlite3.connect(db_path)
connection.row_factory = sqlite3.Row

DB-API with parameters

Never interpolate user input into SQL strings. Use placeholders.

def find_user_by_email(connection, email: str):
    cursor = connection.execute(
        "SELECT id, email FROM users WHERE email = ?",
        (email,),
    )
    return cursor.fetchone()

connection.execute(
    "INSERT INTO users (email) VALUES (?)",
    ("alice@example.com",),
)
connection.commit()

Context manager for connections

from contextlib import contextmanager

@contextmanager
def get_connection(path: Path):
    conn = sqlite3.connect(path)
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

SQLAlchemy Core for slightly larger apps

SQLAlchemy gives you an engine, connection pooling, and composable queries without full ORM ceremony.

pip install sqlalchemy

from sqlalchemy import create_engine, text

engine = create_engine("sqlite:///app.db", future=True)

with engine.connect() as conn:
    result = conn.execute(
        text("SELECT id, email FROM users WHERE active = :active"),
        {"active": True},
    )
    rows = result.mappings().all()

Declarative models when objects help

For Flask apps and CRUD-heavy code, mapped classes can reduce boilerplate.

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

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(unique=True)

with Session(engine) as session:
    user = User(email="bob@example.com")
    session.add(user)
    session.commit()

Migrations mindset

Schema changes happen. Even on SQLite, keep a simple migration habit – SQL files in version control, or Alembic when the project grows. Changing tables by hand without recording the change is how production drifts.

Choose your layer

• Raw DB-API – fine for scripts and a handful of queries.
• SQLAlchemy Core – structured SQL with less string glue.
• SQLAlchemy ORM – helpful when objects map cleanly to tables.

We use SQLite in examples so you can follow along locally. The same patterns apply to PostgreSQL or MySQL with a different connection URL. Next: Flask beyond hello world.