APPLYDJINN
← SQLAlchemy and Alembic: reliable database workProgress & review ↗

Typorithm · easy

Rows, scalar columns and ORM result shapes

pythonsqlalchemyalembicdatabasesindependent-practice
Saved on this device
Full lesson · 4 learning steps Open any exercise for focused practice
1

Step 1 of 4 · intro card

Rows, scalar columns and ORM result shapes

0%

Concept and contract

Choose the result API from the selected expressions rather than assuming every query returns model objects.

A SELECT describes its output expressions as well as its input tables. Selecting Role, selecting Role.score, and selecting Role.id together with Role.score are different contracts. The execution API must match the shape that the caller expects instead of guessing from the fact that an ORM model appears somewhere in the statement.

Session.execute returns result rows. Session.scalars extracts the first selected expression from each row. That expression may be an ORM entity or a plain value. The fixture deliberately selects two scalar columns so that accidentally discarding the second column becomes visible.

Mechanism and worked example

The example stores scores with explicit IDs, constructs select(Role.id, Role.score), and orders by ID.

The example stores scores with explicit IDs, constructs select(Role.id, Role.score), and orders by ID. Iterating session.execute yields two-column rows, converted to ordinary lists in the returned report. For scores [4, 9], the expected result is [[1, 4], [2, 9]].

The returned lists are detached data rather than live ORM instances. SQL row objects have their own access interfaces, and ORM identity behavior is a separate concern. Converting at the boundary makes it clear which fields the caller receives and avoids relying on a serialization library to guess what a SQL result means.

Complete worked example

python

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

class Base(DeclarativeBase):
    pass

class Role(Base):
    __tablename__ = "roles"
    id: Mapped[int] = mapped_column(primary_key=True)
    score: Mapped[int]

def inspect_example(scores):
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        Base.metadata.create_all(engine)
        with Session(engine) as session, session.begin():
            session.add_all(Role(id=i, score=value) for i, value in enumerate(scores, 1))
        with Session(engine) as session:
            statement = select(Role.id, Role.score).order_by(Role.id)
            return [list(row) for row in session.execute(statement)]
    finally:
        engine.dispose()
Reasoning
The example stores scores with explicit IDs, constructs select(Role.id, Role.score), and orders by ID. Iterating session.execute yields two-column rows, converted to ordinary lists in the returned report. For scores [4, 9], the expected result is [[1, 4], [2, 9]]. The returned lists are detached data rather than live ORM instances. SQL row objects have their own access interfaces, and ORM identity behavior is a separate concern. Converting at the boundary makes it clear which fields the caller receives and avoids relying on a serialization library to guess what a SQL result means.
Download the SQLAlchemy and Alembic computer lab ↗

Focused decision: context is in the complete example

python

statement = select(Role.id, Role.score).order_by(Role.id)
rows = session.execute(statement).all()
Database decision
The SELECT has two expressions, so execute preserves a row with both. For a flat score list, select that column alone before extracting scalars.
Download the SQLAlchemy and Alembic computer lab ↗

Counterexamples and common mistakes

The controlled bug runs scalars on the unchanged two-column SELECT.

The controlled bug runs scalars on the unchanged two-column SELECT. It produces [1, 2], because ID is the first selected expression; score has been discarded. These values are valid integers from real rows, so a truthiness check or a count-only check would miss the defect.

Do not repair the problem by hardcoding the missing values or assuming scalars always means a mapped object. Also distinguish all, first and one: they impose different consumption and cardinality expectations. An empty result is a legitimate empty list here, not automatically an exceptional missing-object lookup.

Controlled bug: observe the rejected database contract

python

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

class Base(DeclarativeBase):
    pass

class Role(Base):
    __tablename__ = "roles"
    id: Mapped[int] = mapped_column(primary_key=True)
    score: Mapped[int]

def inspect_example(scores):
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        Base.metadata.create_all(engine)
        with Session(engine) as session, session.begin():
            session.add_all(Role(id=i, score=value) for i, value in enumerate(scores, 1))
        with Session(engine) as session:
            statement = select(Role.id, Role.score).order_by(Role.id)
            return list(session.scalars(statement))
    finally:
        engine.dispose()
Reasoning
The controlled bug runs scalars on the unchanged two-column SELECT. It produces [1, 2], because ID is the first selected expression; score has been discarded. These values are valid integers from real rows, so a truthiness check or a count-only check would miss the defect. Do not repair the problem by hardcoding the missing values or assuming scalars always means a mapped object. Also distinguish all, first and one: they impose different consumption and cardinality expectations. An empty result is a legitimate empty list here, not automatically an exceptional missing-object lookup.
Download the SQLAlchemy and Alembic computer lab ↗

Independent practice

Type the two-column statement and its execute call before changing anything.

Type the two-column statement and its execute call before changing anything. The independent task asks for a different API output: return only score values as a flat scalar list, still ordered by ID. Change the selected expression as well as the result consumption so the shape is deliberate.

Keep the input list unchanged and preserve an empty result for an empty fixture. Use a zero score so that a falsey scalar is not confused with a missing row. Do not switch to an entity query or add unrelated relationships while solving this focused projection task.

Step 1: Project only score and return scalar values instead of two-column rows. Step 2: Keep ID ordering and preserve an empty list for no rows.

Practice starter: solve before reading the solution

python

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

class Base(DeclarativeBase):
    pass

class Role(Base):
    __tablename__ = "roles"
    id: Mapped[int] = mapped_column(primary_key=True)
    score: Mapped[int]

def inspect_example(scores):
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        Base.metadata.create_all(engine)
        with Session(engine) as session, session.begin():
            session.add_all(Role(id=i, score=value) for i, value in enumerate(scores, 1))
        with Session(engine) as session:
            statement = select(Role.id, Role.score).order_by(Role.id)
            return [list(row) for row in session.execute(statement)]
    finally:
        engine.dispose()

# Task: Project only score and return scalar values instead of two-column rows.
Reasoning
Project only score and return scalar values instead of two-column rows.
Download the SQLAlchemy and Alembic computer lab ↗
  • Hint 1: Read the first expression in the SELECT before choosing scalars.
  • Hint 2: A zero score must remain zero rather than being treated as missing.

Solution, tests and trade-offs

The solution selects Role.score and consumes that one expression with scalars.

The solution selects Role.score and consumes that one expression with scalars. The acceptance cases require [4, 9], an empty list and [0] for the corresponding inputs. They compare complete values and types, so returning row tuples, IDs or a count does not satisfy the new contract.

The code still runs a real SELECT through the Session and disposes the owned Engine afterward. On a service boundary, explicit DTO construction can be preferable to exposing ORM instances, particularly when the Session closes before a response is serialized. A result-shape decision is part of an API contract, not just a convenient method name.

Practice solution and acceptance checks

python

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

class Base(DeclarativeBase):
    pass

class Role(Base):
    __tablename__ = "roles"
    id: Mapped[int] = mapped_column(primary_key=True)
    score: Mapped[int]

def inspect_example(scores):
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        Base.metadata.create_all(engine)
        with Session(engine) as session, session.begin():
            session.add_all(Role(id=i, score=value) for i, value in enumerate(scores, 1))
        with Session(engine) as session:
            statement = select(Role.score).order_by(Role.id)
            return list(session.scalars(statement))
    finally:
        engine.dispose()
Reasoning
The solution selects Role.score and consumes that one expression with scalars. The acceptance cases require [4, 9], an empty list and [0] for the corresponding inputs. They compare complete values and types, so returning row tuples, IDs or a count does not satisfy the new contract. The code still runs a real SELECT through the Session and disposes the owned Engine afterward. On a service boundary, explicit DTO construction can be preferable to exposing ORM instances, particularly when the Session closes before a response is serialized. A result-shape decision is part of an API contract, not just a convenient method name.
Download the SQLAlchemy and Alembic computer lab ↗

Interview reasoning and next step

Given a SELECT, predict the types and values returned by execute, scalars and scalar before running it.

Given a SELECT, predict the types and values returned by execute, scalars and scalar before running it. Explain what happens when two selected columns are consumed as scalars and why the database has not somehow lost the second column. The consumption choice discarded it.

Next add a deterministic WHERE, ORDER BY and LIMIT. Result shape alone does not define which rows appear or how ties are broken. A correct projection over an unstable population can still produce a flaky page, an inconsistent report or a misleading acceptance test.

Optional stretch — after the core task works: Compare execute(...).mappings() with an explicitly constructed response dictionary.

Computer practice: https://applydjinn.com/typorithm/sqlalchemy-lab-20261107.zip. Install the pinned requirements in an isolated Python 3.13 environment and run verify.py. Edit the working exercise baseline, then run --practice sa-results. The lab uses disposable SQLite databases and actual SQLAlchemy/Alembic calls; it does not connect to application accounts, production databases or paid services. The app includes prepared results and explained questions without an editor or Python runtime.

Choose the result API from the selected expressions rather than assuming every query returns model objects.