skip to content

In SQLAlchemy, how do Result.one(), first(), one_or_none(), scalar_one(), scalars() and mappings() differ, and when do they raise?

level: middleimportance: should knowfreq 48%

answer

  1. rows versus single values
  2. exactly one, at most one, any
  3. two exception classes
  4. first applies no LIMIT

basics

~20 s

SQLAlchemy's one() demands exactly one row, raising NoResultFound or MultipleResultsFound; one_or_none() allows zero; first() returns the first row or None and silently discards the rest. scalar_one() and scalars() return column values instead of rows; mappings() returns dict-like rows.

solid answer

~40 s

A `Result` from `Connection.execute()` yields `Row` objects, which behave like named tuples. The single-row methods differ in what they enforce: `one()` raises `NoResultFound` for zero rows and `MultipleResultsFound` for more than one; `one_or_none()` returns `None` for zero and still raises on several; `first()` returns the first row or `None`, discards the remaining rows without any error and adds no `LIMIT` to the SQL. The `scalar_*` versions return the first column's value instead of a `Row`: `scalar_one()`, `scalar_one_or_none()`, and `scalar()`, which checks nothing. `scalars()` turns the result into a stream of first-column values, and `mappings()` into `RowMapping` objects keyed by column name. Each single-row method also closes the result, so call one fetch per execution and choose the method that states what the query promises.

code

python · 21 lines
python
from sqlalchemy import create_engine, text
from sqlalchemy.exc import MultipleResultsFound

engine = create_engine("sqlite://")
with engine.begin() as conn:
    conn.execute(text("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, total INTEGER)"))
    conn.execute(
        text("INSERT INTO orders (id, customer, total) VALUES (:id, :customer, :total)"),
        [{"id": 1, "customer": "acme", "total": 40}, {"id": 2, "customer": "acme", "total": 15}],
    )

q = text("SELECT id, total FROM orders WHERE customer = :c")
with engine.connect() as conn:
    print(conn.execute(q, {"c": "acme"}).first())  # (1, 40) - the second row is discarded silently
    print(conn.execute(q, {"c": "nobody"}).one_or_none())  # None
    print(conn.execute(q, {"c": "acme"}).scalars().all())  # [1, 2]
    print(conn.execute(q, {"c": "acme"}).mappings().all())  # [{'id': 1, 'total': 40}, {'id': 2, 'total': 15}]
    try:
        conn.execute(q, {"c": "acme"}).scalar_one()
    except MultipleResultsFound as exc:
        print(exc)  # Multiple rows were found when exactly one was required

go deeper

for a junior

Know that scalars().all() gives a list of values, mappings() gives dict-like rows, and one() raises when the row count is not exactly one.

for a middle

Explain the full one / one_or_none / first matrix, the two exception classes, why first() hides duplicates, and that every single-row method closes the result.

for a senior

Spot first() used for a lookup that must be unique, or on an unbounded query, and replace it with scalar_one() or an explicit LIMIT.

for a principal

Make the fetch method part of the data-access contract, so a lookup that must be unique fails loudly rather than returning an arbitrary row.

## What a Result is `Connection.execute()` returns a **Result** (for a real cursor, a `CursorResult`). It is a forward-only reader over the rows the statement produced. Each item it yields is a **Row**, which acts like a Python named tuple: unpack it (`order_id, total = row`), index it (`row[1]`), or read a column by name (`row.total`). A `Row` is not a dict: `row["total"]` raises `TypeError`; use `row._mapping["total"]` or the `mappings()` filter. Read the `Result` inside the `with engine.connect()` block that produced it, because it depends on that connection. ## Fetching many rows - `result.all()` returns every row as a list and closes the result; a second call returns an empty list. - Iterating with `for row in result:` yields rows one at a time. - `result.scalars()` returns a `ScalarResult` that yields the value of the first column (or the column given by index or name, as in `scalars(1)`) instead of whole rows, so `conn.execute(text("SELECT id FROM orders")).scalars().all()` is a plain list of ids. - `result.mappings()` returns a `MappingResult` that yields **RowMapping** objects, read-only dict-like views: `{"id": 1, "total": 40}`. `scalars()` and `mappings()` are **filters** on the same underlying result; they do not run the statement again, and both offer the same `all()`, `first()`, `one()` and `one_or_none()` methods. ## Fetching one row: what each method promises | Method | Returns | Zero rows | Two or more rows | |---|---|---|---| | `one()` | `Row` | raises `NoResultFound` | raises `MultipleResultsFound` | | `one_or_none()` | `Row` or `None` | `None` | raises `MultipleResultsFound` | | `first()` | `Row` or `None` | `None` | first row; the rest are discarded | | `scalar_one()` | value | raises `NoResultFound` | raises `MultipleResultsFound` | | `scalar_one_or_none()` | value or `None` | `None` | raises `MultipleResultsFound` | | `scalar()` | value or `None` | `None` | first row's first column; no check | Both exceptions live in `sqlalchemy.exc` and subclass `InvalidRequestError`. The messages read "No row was found when one was required" and "Multiple rows were found when exactly one was required" (or "when one or none was required" for the `_or_none` forms). ## The first() trap `first()` looks like a cheap "give me one", but two details matter: 1. **No LIMIT is added.** The SQL runs exactly as written. With a driver that buffers results, every matching row travels to Python and all but the first is thrown away. If you want one row from a large set, write `LIMIT 1` (or `.limit(1)` on a Core `select()`) yourself. The legacy ORM `Query.first()` did add a limit, which is where the expectation comes from. 2. **Duplicates are hidden.** If a lookup that should match one order matches two, `first()` returns whichever came first, and without an `ORDER BY` the database does not promise which one that is; nothing is raised. `one()` or `scalar_one()` turns that data problem into an immediate error. So the method is a statement of intent: use `one()` / `scalar_one()` when the row must exist and be unique (a lookup by primary key or unique column), `one_or_none()` / `scalar_one_or_none()` when it may be missing, and `first()` only for "any one row" with an explicit `ORDER BY` and `LIMIT`. ## Consumption rules - Every single-row method **closes the result**; calling `all()` afterwards raises `ResourceClosedError` ("This result object is closed."). - `all()` exhausts the result; later calls return `[]`. - Pick the shape (`scalars()`, `mappings()`) first, then the fetch method: `result.mappings().one()` returns one `RowMapping`. ## Choosing the shape - **A list of single values** (ids for a batch): `scalars().all()`. - **Rows to serialise to JSON** or pass to code that expects dicts: `mappings().all()`. - **Tuples to unpack** in a loop: iterate the `Result` directly. - **One aggregate** such as `SELECT count(*)`: `scalar_one()`, since an aggregate always returns exactly one row. ## Mistakes that show up in review - `first()` on a lookup by a column that is supposed to be unique: a duplicate in the data goes unnoticed, and the wrong order may be returned. - `scalar()` where `scalar_one()` was meant: zero rows quietly become `None`, and the `None` surfaces much later as an unrelated `TypeError` or a wrong total. - Returning a `Result` out of the `with engine.connect()` block and reading it after the connection has gone back to the pool; fetch with `all()` or `mappings().all()` inside the block and return the list. - Calling two fetch methods on one `Result`; the first single-row call already closed it. The ORM `Session.execute()` returns the same `Result` API, with mapped objects in the rows; selecting entities is covered with ORM queries.

  • Why can SQLAlchemy's Result.first() be slow on a large table even though it returns one row?
    `first()` adds no `LIMIT`; the statement runs as written. With a buffering driver every matching row is sent to Python and all but the first are discarded. Put `LIMIT 1` and an `ORDER BY` in the SQL when you want one row of many.
  • How do you read a column by name from a SQLAlchemy Row?
    Use attribute access, `row.total`, or the mapping view, `row._mapping["total"]`. A `Row` behaves like a named tuple, so `row["total"]` raises `TypeError`; call `result.mappings()` when you want every row as a dict-like `RowMapping`.

  • first() adds LIMIT 1 to the SQL, so it is always cheap.
  • first() raises if the query returned more than one row.
  • scalar() checks that exactly one row came back.
  • Rows are dicts, so row["total"] is the normal way to read a column.
  • You can call first() and then all() on the same Result.