In SQLAlchemy, how do Result.one(), first(), one_or_none(), scalar_one(), scalars() and mappings() differ, and when do they raise?
answer
- rows versus single values
- exactly one, at most one, any
- two exception classes
- first applies no LIMIT
basics
~20 sSQLAlchemy'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 sA `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 linesfrom 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 requiredgo deeper
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.
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.
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.
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.