How to retrieve ORM object(s) instead of Row object(s)?

Question

I want to use imperative dataclass mapping to map my dataclasses to columns

following tutorial i would define model:

@dataclass
class User:
    id: int = field(init=False)
    name: str = None
    fullname: str = None
    nickname: str = None
    addresses: List[Address] = field(default_factory=list)

and then map it to Column:

user = Table(
    "user",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("name", String(50)),
    Column("fullname", String(50)),
    Column("nickname", String(12)),
)

And finally i fill database with some objects. Problem occures when i want to retrive my model back;

q = select(User)
first = engine.execute(q).first()

This gives me a sqlalchemy.engine.row.LegacyRow instance. (I know i get dict under _mapping property) Its more of a low-level contruct when I want to get User instances as I'd expect from an ORM.

Moreover, creating a contructor classmethod won't help due to init=False option on id field.

So is it possible to work with my models?

Answer 1

As noted in the comments to the question:

1. Session.execute() is used to return ORM objects.
2. engine.execute() is considered legacy (deprecated) in SQLAlchemy 1.4 and will be removed in 2.0.

So if we do

from sqlalchemy.orm import Session
from sqlalchemy import select
with Session(engine) as sess:
    result = sess.execute(select(User)).first()
    print(result)
    # (User(id=1, name='Gord', fullname=None, nickname=None, addresses=[]),)
    print(type(result))  # <class 'sqlalchemy.engine.row.Row'>

we notice that we still get a Row object, and if we want just the ORM object we need to extract it from the row:

    item = result[0]
    print(item)
    # User(id=1, name='Gord', fullname=None, nickname=None, addresses=[])
    print(type(item))  # <class '__main__.User'>

To avoid that nuisance we can invoke .scalars() after the .execute()

    result = sess.execute(select(User)).scalars().first()
    print(result)
    # User(id=1, name='Gord', fullname=None, nickname=None, addresses=[])
    print(type(result))  # <class '__main__.User'>

although the preferred method is to use Session.scalars(…), which is a convenient shorthand for Session.execute(…).scalars()

    result = sess.scalars(select(User)).first()
    print(result)
    # User(id=1, name='Gord', fullname=None, nickname=None, addresses=[])
    print(type(result))  # <class '__main__.User'>