[Python] SqlAlchemy 2.x with specific columns makes scalars() return non-orm objects

This question is probably me not understanding architecture of (new) sqlalchemy, typically I use code like this:

query = select(models.Organization).where(
models.Organization.organization_id == organization_id
)
result = await self.session.execute(query)

return result.scalars().all()


Works fine, I get a list of models (if any).

With a query with specific columns only:

query = (
select(
models.Payment.organization_id,
models.Payment.id,
models.Payment.payment_type,
)
.where(
models.Payment.is_cleared.is_(True),
)
.limit(10)
)

result = await self.session.execute(query)

return result.scalars().all()


I am getting first row, first column only. Same it seems to: https://docs.sqlalchemy.org/en/14/c...hlight=scalar#sqlalchemy.engine.Result.scalar

My understanding so far was that in new sqlalchemy we should always call scalars() on the query, as described here: https://docs.sqlalchemy.org/en/14/changelog/migration_20.html#migration-orm-usage

But with specific columns, it seems we cannot use scalars() at all. What is even more confusing is that result.scalars() returns sqlalchemy.engine.result.ScalarResult that has fetchmany(), fechall() among other methods that I am unable to iterate in any meaningful way.

My question is, what do I not understand?

Continue reading...