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.

Kisumu

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)

Feathered

The .query method was exactly what I was looking for! Cannot believe that I didn't find it after reading the documentation for several hours. – Kisumu

yeh, I would note that query is deprecated in the v2 API – Jett

@ChrisSewell: Is there a function that would produce the same result, for those of us who don't like unpacking tuples unnecessarily? – Playsuit

Found a fix for now: for row, in session.execute(select(Object)): – Playsuit

@MontanaBurr session.execute(select(Object)).all(). The scalars() method unpacks the tuples. You can omit it to get the tuples as is. – Component

Answer 2

I'd like to add some to what above @Van said.

You can get object instances using session.execute() as well.

for row in session.execute(select(User)).scalars().all():
    print(row.name)

Which is mentioned in migrating to 2.0.

Component

Answer 3

I just encountered this error today when executing queries that join two or more tables.

It turned out that after updating psycopg2 (2.8.6 -> 2.9.3), SQLAlchemy (1.3.23 -> 1.4.39), and flask-sqlalchemy (2.4.4 -> 2.5.1) the Query.all() method return type is a list of sqlalchemy.engine.row.Rows and before it was a list of tuples. For instance:

query = database.session.query(model)
query = query.outerjoin(another_model, some_field == another_field)
results = query.all()
# type(results[0]) -> sqlalchemy.engine.row.Row
if isinstance(results[0], (list, tuple)):
     # Serialize as a list of rows
else:
     # Serialize as a single row

Washwoman