Introduction

In modern SQLAlchemy, the safest way to convert a row result to a Python dictionary is to use the row’s mapping interface instead of poking at private internals. The usual answer is dict(row._mapping), but the best approach depends on whether you are dealing with Core rows, ORM objects, or full result sets.

Converting a Single Core Row

For SQLAlchemy Core result rows, use _mapping:

1from sqlalchemy import create_engine, text
2
3engine = create_engine("sqlite+pysqlite:///:memory:")
4
5with engine.begin() as conn:
6    conn.execute(text("CREATE TABLE users (id INTEGER, name TEXT)"))
7    conn.execute(text("INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Linus')"))
8
9with engine.connect() as conn:
10    row = conn.execute(text("SELECT id, name FROM users WHERE id = 1")).first()
11    data = dict(row._mapping)
12    print(data)

This produces:

{'id': 1, 'name': 'Ada'}

That is the standard modern pattern for row objects returned from SQLAlchemy statements.

Converting Many Rows at Once

If you want dictionaries for every row, iterate and convert each mapping:

1with engine.connect() as conn:
2    result = conn.execute(text("SELECT id, name FROM users ORDER BY id"))
3    rows = [dict(row._mapping) for row in result]
4    print(rows)

This is straightforward and keeps the conversion explicit.

An Even Cleaner Result API

SQLAlchemy also offers a mappings-oriented result view:

1with engine.connect() as conn:
2    result = conn.execute(text("SELECT id, name FROM users ORDER BY id"))
3    rows = result.mappings().all()
4    print(rows)

The returned objects behave like dictionaries already. If you need plain dict instances specifically, you can still wrap them with dict(...).

This is often the cleanest option when your downstream code is already expecting mapping-like rows.

ORM Objects Are Different

If you queried ORM model instances, those are not row mappings. They are normal Python objects with SQLAlchemy state attached.

1from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
2from sqlalchemy import Integer, String
3
4class Base(DeclarativeBase):
5    pass
6
7class User(Base):
8    __tablename__ = "users_orm"
9    id: Mapped[int] = mapped_column(Integer, primary_key=True)
10    name: Mapped[str] = mapped_column(String)
11
12    def to_dict(self):
13        return {"id": self.id, "name": self.name}

Then:

1with Session(engine) as session:
2    users = session.query(User).all()
3    payload = [user.to_dict() for user in users]
4    print(payload)

Calling __dict__ directly on ORM objects is usually a bad idea because it includes internal SQLAlchemy state you do not want to expose.

Label Columns Intentionally

If your query includes expressions or joins, label columns so the resulting dictionary keys are stable.

1with engine.connect() as conn:
2    result = conn.execute(
3        text("SELECT id AS user_id, name AS user_name FROM users")
4    )
5    rows = [dict(row._mapping) for row in result]
6    print(rows)

Clear labels make API output and debugging much easier.

Watch for JSON Serialization Types

Some database values are not directly JSON-friendly:

• 'datetime'
• 'Decimal'
• 'UUID'

If your dict is headed to an API response, convert those values in one serializer layer so the output format stays predictable.

The row-to-dict step solves structure, but not necessarily final serialization.

Common Pitfalls

One common mistake is using old row internals from outdated SQLAlchemy examples. SQLAlchemy changed row behavior across versions, so older tricks are often fragile.

Another issue is using __dict__ on ORM models and leaking _sa_instance_state into the output.

A third pitfall is forgetting to label complex query columns, which can produce confusing or unstable dictionary keys.

Summary

• For SQLAlchemy Core rows, use dict(row._mapping).
• For full result sets, result.mappings() is often the cleanest API.
• ORM model instances should usually expose an explicit to_dict() method.
• Label selected columns clearly for stable output keys.
• Row-to-dict conversion is only one step; JSON serialization concerns may still remain.