How to retain sqlalchemy model after adding `row_number()`?

I'm try to filter rows in some method so I need the output model to be of the same type as input model to the sqlAlchemy query.

I followed this answer https://stackoverflow.com/a/38160409/1374078 . However would it be possible to get the original model, so that I can access the model's methods by name? e.g. row.foo_field, otherwise getting generic row type

> type(row)
<class 'sqlalchemy.engine.row.Row'>

Solution

Assuming that your code looks something like this*:

with orm.Session(engine) as s:
    row_number_column = (
        sa.func.row_number()
        .over(partition_by=User.name, order_by=sa.desc(User.id))
        .label('row_number')
    )
    q = sa.select(User)
    q = q.add_columns(row_number_column)
    for row in s.execute(q):
        print(row)

Then the results look like this:

(<__main__.User object at 0x7fb6e2fe5580>, 1)
(<__main__.User object at 0x7fb6e2fe5670>, 1)
(<__main__.User object at 0x7fb6e2fe56a0>, 1)
(<__main__.User object at 0x7fb6e2fe56d0>, 1)
(<__main__.User object at 0x7fb6e2fe5700>, 1)

As you can see, each result row is a tuple of (user_instance, row_number) so you can access the user instance as row[0], or unpack during iteration:

    for user, row in s.execute(q):
        print(user, row_number)

* This is a SQLAlchemy 2.0-style query, but the legacy Query style query in the Q&A linked in the question produces the same result.