Flask: SQLAlchemy 2 models, sessions and selected reads

pythonflaskindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

Flask: SQLAlchemy 2 models, sessions and selected reads

Concept and contract

Use mapped columns and explicit ordered queries in an application-scoped session.

Flask-SQLAlchemy integrates SQLAlchemy’s model and session facilities with Flask application contexts. The session tracks pending changes and coordinates database transactions; it is not the browser session cookie.

Current SQLAlchemy style uses select statements, mapped_column and typed Mapped declarations. Keeping those concepts distinct prevents a common confusion between user identity state and database unit-of-work state.

Mechanism and worked example

The factory always creates an owned SQLite in-memory database and one Role table.

The factory always creates an owned SQLite in-memory database and one Role table. The helper enters the application context, stages supplied rows, commits them and reads an explicitly ordered statement.

The returned title list is selected from persisted rows rather than echoed from the input. No production URI, environment file or external database is opened by this example.

Complete worked example

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import select, String
from sqlalchemy.orm import Mapped, mapped_column

def make_store():
    app = Flask(__name__)
    app.config.update(TESTING=True, SQLALCHEMY_DATABASE_URI='sqlite:///:memory:')
    db = SQLAlchemy(app)
    class Role(db.Model):
        id: Mapped[int] = mapped_column(primary_key=True)
        title: Mapped[str] = mapped_column(String(20), nullable=False)
    with app.app_context():
        db.create_all()
    return app, db, Role

def stored_titles(titles):
    app, db, Role = make_store()
    with app.app_context():
        db.session.add_all(Role(title=title) for title in titles)
        db.session.commit()
        statement = select(Role).order_by(Role.id)
        return [role.title for role in db.session.scalars(statement)]

Reasoning: The factory always creates an owned SQLite in-memory database and one Role table. The helper enters the application context, stages supplied rows, commits them and reads an explicitly ordered statement.

Counterexamples and common mistakes

The counterexample forgets to add rows to the session, so the selected collection remains empty..

The counterexample forgets to add rows to the session, so the selected collection remains empty.

Other mistakes include querying outside the required application context, relying on unspecified row order or assuming add alone guarantees a durable commit. create_all initializes this disposable demonstration schema; it is not a migration system for safely upgrading existing production tables.

Counterexample: a runnable framework mistake

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import select, String
from sqlalchemy.orm import Mapped, mapped_column

def make_store():
    app = Flask(__name__)
    app.config.update(TESTING=True, SQLALCHEMY_DATABASE_URI='sqlite:///:memory:')
    db = SQLAlchemy(app)
    class Role(db.Model):
        id: Mapped[int] = mapped_column(primary_key=True)
        title: Mapped[str] = mapped_column(String(20), nullable=False)
    with app.app_context():
        db.create_all()
    return app, db, Role

def stored_titles(titles):
    app, db, Role = make_store()
    with app.app_context():
        pass  # rows were never staged
        db.session.commit()
        statement = select(Role).order_by(Role.id)
        return [role.title for role in db.session.scalars(statement)]

Reasoning: The counterexample forgets to add rows to the session, so the selected collection remains empty.

Independent practice: one bounded framework change

Add an optional prefix filter to the selected title query.

Add an optional prefix filter to the selected title query. Keep the default empty prefix returning all rows and preserve identifier order.

Use a SQL expression rather than filtering an already materialized full collection. This introduces one query condition while retaining the same persistence flow; the task does not require designing a generic search language.

Step 1: Add an optional SQL prefix condition to the ordered read. Step 2: Extend the helper signature with an empty default.

Practice starter: solve before reading the solution

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import select, String
from sqlalchemy.orm import Mapped, mapped_column

def make_store():
    app = Flask(__name__)
    app.config.update(TESTING=True, SQLALCHEMY_DATABASE_URI='sqlite:///:memory:')
    db = SQLAlchemy(app)
    class Role(db.Model):
        id: Mapped[int] = mapped_column(primary_key=True)
        title: Mapped[str] = mapped_column(String(20), nullable=False)
    with app.app_context():
        db.create_all()
    return app, db, Role

def stored_titles(titles):
    # Remove this placeholder, then adapt the retained baseline:
    # Add an optional SQL prefix condition to the ordered read.
    raise NotImplementedError
    app, db, Role = make_store()
    with app.app_context():
        db.session.add_all(Role(title=title) for title in titles)
        db.session.commit()
        statement = select(Role).order_by(Role.id)
        return [role.title for role in db.session.scalars(statement)]

Reasoning: Add an optional SQL prefix condition to the ordered read. Extend the helper signature with an empty default.

Hint 1: Apply Role.title.startswith(prefix) to select.
Hint 2: Retain duplicate rows and ordering.

Solution, tests and trade-offs

Fixtures inspect two inserted rows, an empty input and repeated titles.

Fixtures inspect two inserted rows, an empty input and repeated titles. Practice selects matching prefixes and confirms that no matches produce an empty list.

Distinct rows with the same title remain distinct; display text is not an identity key. The checks establish this small SQLite behavior, not cross-database collation equivalence, concurrency safety or production-scale performance.

Practice solution and acceptance checks

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import select, String
from sqlalchemy.orm import Mapped, mapped_column

def make_store():
    app = Flask(__name__)
    app.config.update(TESTING=True, SQLALCHEMY_DATABASE_URI='sqlite:///:memory:')
    db = SQLAlchemy(app)
    class Role(db.Model):
        id: Mapped[int] = mapped_column(primary_key=True)
        title: Mapped[str] = mapped_column(String(20), nullable=False)
    with app.app_context():
        db.create_all()
    return app, db, Role

def stored_titles(titles, prefix=""):
    app, db, Role = make_store()
    with app.app_context():
        db.session.add_all(Role(title=title) for title in titles)
        db.session.commit()
        statement = select(Role).where(Role.title.startswith(prefix)).order_by(Role.id)
        return [role.title for role in db.session.scalars(statement)]

Reasoning: Fixtures inspect two inserted rows, an empty input and repeated titles. Practice selects matching prefixes and confirms that no matches produce an empty list.

Interview reasoning and next step

In a larger application validate input before staging writes, use explicit transaction boundaries and let Flask-SQLAlchemy remove request-scoped session state at teardown..

In a larger application validate input before staging writes, use explicit transaction boundaries and let Flask-SQLAlchemy remove request-scoped session state at teardown.

Plan schema changes through migrations and measure queries with realistic data. The next lessons separate rollback and schema evolution from this basic mapped-model workflow, so each responsibility can be reasoned about and tested independently.

Optional stretch — after the core task works: Inspect the generated SQL and compare a parameterized index-aware query.

Computer practice: https://applydjinn.com/typorithm/python-frameworks-lab-20261105.zip. Install the pinned dependencies in an isolated environment, run verify.py --track flask, then edit the retained exercise baseline and check it with --practice fl-sqlalchemy. The lab uses owned in-memory databases, local framework clients and tiny CPU tensors. It never reads the working ApplyDjinn database, contacts production feeds or downloads models.

Use mapped columns and explicit ordered queries in an application-scoped session.