KAHIBARO
Discord Login Register ☰
☰

12.6. Reading Records

Table of Contents

Reading Data with an ORM

Reading data is the operation you perform most often in real applications. In ORM terms this is called querying. In this chapter you will learn how to fetch data from the database using an ORM, with concrete examples in SQLAlchemy, which is the ORM used throughout this course.

You already learned what an ORM is and how models and sessions work in the previous chapters. Here we focus only on how to read records in different ways.

Basic Queries: Getting All Records

The most basic query is “give me all rows of this table”.

Assume you have this SQLAlchemy model:

python 
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import String, Integer
class Base(DeclarativeBase):
    pass
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    email: Mapped[str] = mapped_column(String, unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String, nullable=False)
    age: Mapped[int] = mapped_column(Integer)

And a session:

python 
from sqlalchemy.orm import Session
session = Session(engine)

Select all rows for a model

SQLAlchemy 2 style:

python 
from sqlalchemy import select
stmt = select(User)
result = session.execute(stmt)
users = result.scalars().all()

What happens:

You can loop directly:

python 
for user in users:
    print(user.id, user.email)

Older style (still seen in many code bases):

python 
users = session.query(User).all()

Both are equivalent in effect.

Rule: Use select(Model) and result.scalars() to get ORM objects. Use .all() only when you really want all rows in memory.

Getting a Single Record

Most of the time you want one record, not all of them.

Get by primary key

If you know the primary key, use session.get:

python 
user = session.get(User, 1)  # primary key = 1
if user is None:
    print("User not found")
else:
    print(user.email)

This will run something like:

sql 
SELECT * FROM users WHERE id = 1;

session.get:

Get the first matching row

If you want the first row matching some condition:

python 
from sqlalchemy import select
stmt = select(User).where(User.email == "alice@example.com")
result = session.execute(stmt)
user = result.scalars().first()
if user:
    print("Found:", user.name)
else:
    print("No such user")

.first() returns:

Exactly one row: `.one()` and `.one_or_none()`

Sometimes you expect exactly one row:

python 
stmt = select(User).where(User.email == "alice@example.com")
result = session.execute(stmt)
user = result.scalars().one()
python 
from sqlalchemy.exc import NoResultFound, MultipleResultsFound
try:
    user = result.scalars().one()
except NoResultFound:
    print("User does not exist")
except MultipleResultsFound:
    print("More than one user with that email")

.one_or_none() is similar but returns None instead of raising NoResultFound:

python 
user = result.scalars().one_or_none()
if user is None:
    print("Not found")

Rule:

  • Use session.get(Model, id) when you have a primary key.
  • Use .first() when you want at most one row and None is fine.
  • Use .one() or .one_or_none() when the database must contain exactly one matching row.

Filtering Records (WHERE clause)

Filtering in ORM is how you represent WHERE in SQL.

Basic pattern:

python 
stmt = select(User).where(User.age >= 18)
users = session.execute(stmt).scalars().all()

This corresponds to:

sql 
SELECT * FROM users WHERE age >= 18;

Common filter operators

Python expressionSQL equivalent
User.age == 30age = 30
User.age != 30age <> 30
User.age > 18age > 18
User.age >= 18age >= 18
User.age < 60age < 60
User.age <= 60age <= 60
User.name == "Alice"name = 'Alice'
User.name.like("A%")name LIKE 'A%'
User.name.ilike("a%")LOWER(name) LIKE 'a%'
User.age.in_([20, 30])age IN (20, 30)
User.age.is_(None)age IS NULL
User.age.is_not(None)age IS NOT NULL

Examples:

python 
# All adult users
stmt = select(User).where(User.age >= 18)
# Users named exactly "Alice"
stmt = select(User).where(User.name == "Alice")
# Users whose name starts with "A" or "a"
stmt = select(User).where(User.name.ilike("a%"))
# Users whose age is 20 or 30
stmt = select(User).where(User.age.in_([20, 30]))
# Users with unknown age (NULL in DB)
stmt = select(User).where(User.age.is_(None))

Combining Conditions (AND, OR, NOT)

SQL lets you combine conditions with AND, OR, and NOT. The ORM has equivalents.

AND

To require multiple conditions:

python 
from sqlalchemy import and_
# Users older than 18 and name starts with A
stmt = select(User).where(
    and_(
        User.age >= 18,
        User.name.ilike("a%")
    )
)
users = session.execute(stmt).scalars().all()

You can also write multiple .where calls, they are combined with AND:

python 
stmt = (
    select(User)
    .where(User.age >= 18)
    .where(User.name.ilike("a%"))
)

OR

Use or_:

python 
from sqlalchemy import or_
stmt = select(User).where(
    or_(
        User.age < 18,
        User.age > 60
    )
)
# minors or seniors

NOT

Use the bitwise not operator ~ on conditions:

python 
from sqlalchemy import not_
# People who are NOT adults
stmt = select(User).where(~(User.age >= 18))
# Or using not_ function
stmt = select(User).where(not_(User.age >= 18))

You can nest and combine:

python 
stmt = select(User).where(
    and_(
        User.age >= 18,
        or_(
            User.name.ilike("a%"),
            User.name.ilike("b%")
        )
    )
)
# Adults whose name starts with A or B

Sorting Results (ORDER BY)

To order your results you use .order_by.

Basic ascending and descending

python 
# Order by name ascending
stmt = select(User).order_by(User.name.asc())
# Order by age descending
stmt = select(User).order_by(User.age.desc())

You can omit .asc() since ascending is the default:

python 
stmt = select(User).order_by(User.name)

Multiple sort keys

python 
# First by age ascending, then by name ascending
stmt = select(User).order_by(User.age, User.name)
# First by age descending, then by name ascending
stmt = select(User).order_by(User.age.desc(), User.name)

Example usage:

python 
stmt = (
    select(User)
    .where(User.age >= 18)
    .order_by(User.age.desc(), User.name)
)
adults = session.execute(stmt).scalars().all()

Limiting and Offsetting (Pagination Basics)

Limiting and offsetting correspond to SQL LIMIT and OFFSET.

Limit number of rows

python 
stmt = select(User).limit(10)
first_ten_users = session.execute(stmt).scalars().all()

Skip some rows (offset)

python 
# Skip first 20 rows, then get next 10
stmt = select(User).offset(20).limit(10)
page = session.execute(stmt).scalars().all()

This is useful for pagination.

Simple function for “page / page_size” style:

python 
def get_users_page(session, page: int, page_size: int = 20):
    offset_value = (page - 1) * page_size
    stmt = (
        select(User)
        .order_by(User.id)
        .offset(offset_value)
        .limit(page_size)
    )
    return session.execute(stmt).scalars().all()

Rule: For pagination, always combine ORDER BY with LIMIT and OFFSET. Without ORDER BY your page order is not guaranteed.

Selecting Specific Columns

Sometimes you do not need full model objects, you just need a few fields. You can select specific columns.

Raw columns (tuples)

python 
stmt = select(User.id, User.email)
result = session.execute(stmt)
for row in result:
    print(row.id, row.email)

Each row is a Row object that behaves like a named tuple.

You can turn them into dictionaries:

python 
rows = result.mappings().all()
for row in rows:
    print(row["id"], row["email"])

Mixed columns and models

You can also mix a model with extra columns:

python 
stmt = select(User, User.age + 10)
result = session.execute(stmt)
for user, age_plus_10 in result:
    print(user.name, age_plus_10)

Counting and Aggregations

To answer questions like “how many users do we have” or “what is the average age” you use aggregate functions.

Count rows

python 
from sqlalchemy import func, select
stmt = select(func.count(User.id))
result = session.execute(stmt)
total_users = result.scalar_one()  # get the single scalar value
print("Total users:", total_users)

Other aggregates

Common aggregates:

FunctionDescription
func.count(column)Number of rows
func.max(column)Maximum value
func.min(column)Minimum value
func.avg(column)Average value
func.sum(column)Sum of values

Example:

python 
stmt = select(
    func.count(User.id),
    func.avg(User.age),
)
result = session.execute(stmt)
count_users, avg_age = result.one()
print("Users:", count_users, "Average age:", avg_age)

Grouping

Quick glimpse, full grouping is covered in SQL and Queries chapters. Example:

python 
stmt = (
    select(User.age, func.count(User.id))
    .group_by(User.age)
    .order_by(User.age)
)
result = session.execute(stmt)
for age, count in result:
    print(age, "years:", count, "users")

Lazy Loading and N+1 Problem (Reading Related Data)

ORMs can automatically load related objects when you access relationship attributes. This is called lazy loading. It is powerful but can cause performance problems, especially the N+1 query problem.

Assume models:

python 
from sqlalchemy.orm import relationship, Mapped
class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    content: Mapped[str]
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    author: Mapped["User"] = relationship(back_populates="posts")
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

Lazy loading example

python 
users = session.execute(select(User)).scalars().all()
for user in users:
    print(user.name, "has", len(user.posts), "posts")

If posts uses lazy loading, SQLAlchemy might do:

  1. SELECT * FROM users;
  2. For each user, when user.posts is first accessed:
    • SELECT * FROM posts WHERE user_id = :id

If you have 100 users this can become 1 + 100 queries. That is the N+1 problem.

You will learn more about eager loading and joins in the “Relationships” and “Queries” chapters. Here the key point is that reading related data can cause additional queries.

Rule: When reading many parents and their children, be aware of lazy loading. It can become N+1 queries and slow your application. Use proper querying patterns to load relations efficiently.

Querying Related Data with Joins (Preview)

You will cover joins in depth in the “Relationships” and “Queries” chapters. Here are two simple examples to show how you can read related data efficiently.

Inner join example

List posts with their author names:

python 
from sqlalchemy import select, join
stmt = (
    select(Post.title, User.name)
    .join(User, Post.user_id == User.id)
)
result = session.execute(stmt)
for title, author_name in result:
    print(title, "by", author_name)

You can also use relationship attributes for joining:

python 
stmt = select(Post, User).join(Post.author)
rows = session.execute(stmt).all()
for post, author in rows:
    print(post.title, "by", author.name)

Pattern: Reusable Query Functions

To keep your code clean, it is common to wrap queries in small repository or service functions. You will formalize this in the “Repository Pattern” chapter, but here are some practical examples.

Get user by email

python 
def get_user_by_email(session: Session, email: str) -> User | None:
    stmt = select(User).where(User.email == email)
    return session.execute(stmt).scalars().first()

Usage:

python 
user = get_user_by_email(session, "alice@example.com")
if not user:
    raise ValueError("User not found")

Search users by name substring

python 
def search_users_by_name(session: Session, query: str, limit: int = 20):
    pattern = f"%{query}%"
    stmt = (
        select(User)
        .where(User.name.ilike(pattern))
        .order_by(User.name)
        .limit(limit)
    )
    return session.execute(stmt).scalars().all()

Paginated list

python 
def list_users(
    session: Session,
    page: int = 1,
    page_size: int = 20,
    min_age: int | None = None,
):
    stmt = select(User).order_by(User.id)
    if min_age is not None:
        stmt = stmt.where(User.age >= min_age)
    stmt = stmt.offset((page - 1) * page_size).limit(page_size)
    return session.execute(stmt).scalars().all()

This approach:

Common Pitfalls When Reading Records

1. Loading too much data

Using .all() without filters can load thousands of rows into memory.

Bad:

python 
users = session.execute(select(User)).scalars().all()
# Then filter in Python
adults = [u for u in users if u.age >= 18]

Better:

python 
stmt = select(User).where(User.age >= 18)
adults = session.execute(stmt).scalars().all()

2. Forgetting to call `.scalars()`

If you select a model and then call .all() directly on the result, you get row objects, not model instances.

python 
result = session.execute(select(User))
# This returns Row objects, not User objects
rows = result.all()

You must use .scalars():

python 
users = session.execute(select(User)).scalars().all()

3. Relying on unspecified order

Without order_by, database order is not guaranteed.

Bad:

python 
users = session.execute(select(User).limit(10)).scalars().all()
# Assuming these are the "first" 10 users

Better:

python 
stmt = select(User).order_by(User.id).limit(10)
users = session.execute(stmt).scalars().all()

4. Ignoring “not found” cases

If you assume .first() or .one_or_none() always returns something, you will hit NoneType errors.

Bad:

python 
user = session.execute(stmt).scalars().first()
print(user.email)  # may raise AttributeError

Better:

python 
user = session.execute(stmt).scalars().first()
if user is None:
    # handle not found
    ...
else:
    print(user.email)

Summary

In this chapter you learned how to:

These are the essential tools for reading data with an ORM. In the next chapters you will build on this and learn more advanced querying, relationships, and how to use these queries inside a complete backend application.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3694 / 24h
Impressum / Imprint
Datenschutz / Data Policy