Choosing the right scalar helper in SQLAlchemy: scalar vs scalar_one_or_none and handling duplicates

Learn the difference between SQLAlchemy scalar and scalar_one_or_none, to enforce uniqueness vs take first row, how MultipleResultsFound impacts queries

Choosing the right scalar helper in SQLAlchemy matters when you expect a single row but your query could return more. Two common patterns look similar on the surface, yet they diverge in a critical way once multiple rows enter the picture. Understanding that difference helps you write queries that either strictly enforce uniqueness or deliberately accept the first match.

Problem setup

Consider two functions that load a single entity by id. Both appear to return either a single model instance or None when nothing is found.

def find_group_lenient(conn: Session, group_id: int) -> Group | None:
    q = select(Group).filter_by(id=group_id)
    return conn.scalar(q)


def find_group_strict(conn: Session, group_id: int) -> Group | None:
    q = select(Group).filter_by(id=group_id)
    return conn.execute(q).scalar_one_or_none()

What is actually happening

The difference shows up when the resultset contains more than one row. Calling scalar returns the first element from the first row of the resultset. In contrast, scalar_one_or_none raises MultipleResultsFound if more than one row is present, and returns None only when there are no rows at all.

It helps to think of a resultset as a list of tuples, each tuple containing one or more elements. A scalars method transforms that list by taking the first element from each tuple. A scalar method then takes just the first element of the first tuple. When the query returns model instances, each element in a tuple is a model instance. When the query returns bare column values, each element is a simple value such as int or str.

If you want to observe the SQL being issued for these calls, you can create your engine with echo=True and watch what gets emitted.

Solution

Pick the API that matches your intent. If your contract assumes at most one row, use scalar_one_or_none to surface MultipleResultsFound when duplicates exist. If your intent is to grab the first row’s first element regardless of how many rows match, scalar does exactly that.

def get_group_enforcing_uniqueness(conn: Session, group_id: int) -> Group | None:
    q = select(Group).filter_by(id=group_id)
    return conn.execute(q).scalar_one_or_none()

Why this matters

The choice affects how your application behaves when the database returns multiple rows. One path silently takes the first row, the other path raises a clear exception. That difference changes control flow and error handling, especially in places where uniqueness is expected.

Takeaways

Use scalar_one_or_none when the query must yield zero or one row and you want an exception if more are found. Use scalar when returning the first element of the first row is acceptable. Keep in mind the mental model of resultsets as lists of tuples, where scalars pulls the first element from each tuple and scalar returns just the first element of the very first tuple. When in doubt, enable echo=True to inspect the executed SQL and better understand how your query is evaluated.