Querying — select(), Filtering, and Joins

Mental Model

Picture session.execute() as a low-level SQL execution engine that returns raw database Row records. To turn these records into Python ORM objects, you need an additional "entity converter" step, which scalars() provides, like a specialized parser for ORM entities.

Rule: When querying whole ORM entities with select(), always invoke session.scalars() or use .scalars().all() to convert SQL row sequences to plain model instances.

The Setup

You are writing an analytical reporting query. You execute a select() statement, but instead of retrieving model objects, your loop outputs weird Python tuples, causing attribute lookup errors.

What Does This Print?

⚠ Broken code

Python

from sqlalchemy import create_engine, select, ForeignKey, Column, Integer, String
from sqlalchemy.orm import declarative_base, relationship, sessionmaker

Base = declarative_base()
class Company(Base):
    __tablename__ = 'companies'
    id = Column(Integer, primary_key=True)
    name = Column(String)

class Employee(Base):
    __tablename__ = 'employees'
    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'))
    name = Column(String)
    company = relationship("Company")

engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

c = Company(name="TechCorp")
session.add_all([c, Employee(name="Dave", company=c)])
session.commit()

# Execute select query using modern 2.0 pattern
stmt = select(Employee).join(Company).where(Company.name == "TechCorp")
results = session.execute(stmt)

for row in results:
    try:
        print(f"Employee name: {row.name}")
    except AttributeError as e:
        print(f"Failed: {e} (Type of row: {type(row)})")

Predict what the for row in results loop prints. Can you access .name directly on the returned rows?

The Output

What actually happens

Failed: 'Row' object has no attribute 'name' (Type of row: <class 'sqlalchemy.engine.row.Row'>)

In SQLAlchemy 2.0, session.execute(select(...)) returns a Result object, which yields database-style Row objects. A Row acts like a named tuple containing the columns or entities requested in the select. Even if you query a single entity (select(Employee)), the database API yields a one-element tuple for each row. Trying to call .name directly on this row tuple fails because the model object is actually nested inside the first index of the row.

Why Python Does This

SQLAlchemy 2.0 enforces standard SQL-style behavior. Its internal results abstraction uses the Row class (implemented in C for performance inside CPython, or optimized as a fast sequence). This design ensures unified handling of partial column queries, scalar queries, and full-entity queries. When you pass select(Employee) to execute(), SQLAlchemy returns elements as Row(Employee_instance, ). To unpack this cleanly, SQLAlchemy provides helper methods like .scalars() (which extracts the first column of each row) or .scalars().all() to convert the sequence of raw tuple-like rows into a flat list of model objects.

The Fix

✓ Corrected pattern

Python

from sqlalchemy import create_engine, select, ForeignKey, Column, Integer, String
from sqlalchemy.orm import declarative_base, relationship, sessionmaker

Base = declarative_base()
class Company(Base):
    __tablename__ = 'companies'
    id = Column(Integer, primary_key=True)
    name = Column(String)

class Employee(Base):
    __tablename__ = 'employees'
    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'))
    name = Column(String)
    company = relationship("Company")

engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

c = Company(name="TechCorp")
session.add_all([c, Employee(name="Dave", company=c)])
session.commit()

stmt = select(Employee).join(Company).where(Company.name == "TechCorp")
# FIX: Use session.scalars() to unpack the Employee entity from the SQL rows
employees = session.scalars(stmt).all()

for emp in employees:
    print(f"Employee name: {emp.name}")  # Output: Employee name: Dave

session.scalars() is designed to extract the first column of each Row as a complete ORM entity when a single entity type is selected. This automatically converts the database Row into the Employee model instance, making its attributes directly accessible.

How This Fails in Real Systems

During a code modernization sprint, a developer replaced an old 1.x .query() call with a modern select() statement. They forgot to use .scalars(), and the application began throwing AttributeError in production on a critical reports route, crashing the dashboard for a major client.

Key Takeaway

When querying whole ORM entities with select(), always invoke session.scalars() or use .scalars().all() to convert SQL row sequences to plain model instances.

Common mistake: Developers expect session.execute(select(ORM_Model)) to return ORM model instances directly, but it returns raw Row objects which require specific unwrapping for entity access.