Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 37 ยท Engine and Connections

Working with Results and Rows

Learn how SQLAlchemy Result objects work, how to read Row tuples by name or position, and when to use all, first, one, scalar, scalars, mappings and partitions.

Whenever you execute a statement, you get back a Result. It is an iterator over the rows the database returned, and it comes with a family of methods that shape those rows into exactly what you need: a list, a single value, dictionaries, or batches. Choosing the right method makes code shorter and communicates intent (for example, "I expect exactly one row").

Each row is a Row object, which behaves like a named tuple. You can read columns by position, by name as an attribute, or convert them to a plain dictionary. This lesson covers all of it.

The examples use a small scores table, created once at the top of each program.

Rows as named tuples

Python Row access patterns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Row objects are immutable. They are not dictionaries, but _asdict() or row._mapping gives you dictionary-style access when you need it.

Choosing a fetch method

Pick the method by what you expect:

Method Returns If there is no row If there are several
.all() list of all rows empty list list
.first() first row or None None first one (rest discarded)
.one() the only row raises NoResultFound raises MultipleResultsFound
.one_or_none() the only row or None None raises MultipleResultsFound
.scalar() first column of first row None first one
.scalar_one() the single value raises raises
.fetchmany(n) up to n rows empty list n rows

one() is the one to use when finding a row by primary key: it documents the expectation and fails loudly if the assumption is wrong.

Python all, first, one and the errors

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

scalars() and mappings()

Two helpers change what each item of the result is:

  • .scalars() takes the first column of every row and yields plain values. It is perfect for single-column queries and for ORM entities.
  • .mappings() yields dictionary-like RowMapping objects keyed by column name, which is handy for JSON output.
Python scalars() and mappings()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Streaming and batches

By default a result holds on to the cursor until you finish reading it. For very large result sets you can iterate row by row, take batches with fetchmany(), or use .partitions(size) to receive lists of rows. Pair this with the stream_results execution option on drivers that support server-side cursors (PostgreSQL's psycopg, for instance) so the database does not send everything at once.

Python Partitions: reading in batches

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A result can be read once

A Result is a one-way stream. After you consume it (with .all() or by iterating), it is exhausted, and the underlying cursor is closed. Call .all() first if you need to read the data twice, or run the statement again. Also, finish using a result inside the with block that created the connection, because leaving the block releases the connection back to the pool.

For INSERT, UPDATE and DELETE, the result has .rowcount (rows affected) and, for single-row inserts, .inserted_primary_key on Core inserts. Those come up in the Core chapter.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You look up one row by primary key and want an error if it is missing. Which method?

    one() raises NoResultFound if there is no row and MultipleResultsFound if there are several.
  2. 2. What does result.scalars().all() return for SELECT name FROM t?

    scalars() extracts the first column of each row, so you get plain values.
  3. 3. How can you read a Row as a dictionary?

    Row has _asdict(), and .mappings() on the result gives dictionary-like rows.
  4. 4. What happens if you call result.all() twice on the same Result?

    A Result is a stream that can be consumed once.

Summary

  • A Result iterates Row objects, which act like named tuples.
  • Use .all(), .first(), .one(), .one_or_none(), .scalar() according to how many rows you expect.
  • .scalars() returns plain values and .mappings() returns dictionary-like rows.
  • Read large results in batches with partitions() or fetchmany(), and consume a result only once.

Next: Connection Pooling and Events