SQLAlchemy ORM: "AttributeError: Could not locate column in row"

Question

I'm learning SQLAlchemy right now, but I've encountered an error that puzzles me. Yes, there are similar questions here on SO already, but none of them seem to be solved.

My goal is to use the ORM mode to query the database. So I create a model:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import Session, registry
from sqlalchemy.sql import select

database_url = "mysql+pymysql://..."

mapper_registry = registry()
Base = mapper_registry.generate_base()


class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    name = Column(String(32))


engine = create_engine(database_url, echo=True)
mapper_registry.metadata.create_all(engine)

New I want to load the whole row for all entries in the table:

with Session(engine) as session:
    for row in session.execute(select(User)):
        print(row.name)

#- Error: #

Traceback (most recent call last):
...
    print(row.name)
AttributeError: Could not locate column in row for column 'name'

What am I doing wrong here? Shouldn't I be able to access the fields of the ORM model? Or am I misunderstanding the idea of ORM?

I'm using Python 3.8 with PyMySQL 1.0.2 and SQLAlchemy 1.4.15 and the server runs MariaDB.

This is example is as minimal as I could make it, I hope anyone can point me in the right direction. Interestingly, inserting new rows works like a charm.

Answer 1

session.execute(select(User)) will return a list of Row instances (tuples), which you need to unpack:

for row in session.execute(select(Object)):
    # print(row[0].name)  # or 
    print(row["Object"].name)

But I would use the .query which returns instances of Object directly:

for row in session.query(Object):
    print(row.name)