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.

score 17 · Accepted Answer · answered May 21 '21 at 04:59

17

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)

answered May 21 '21 at 04:59

van

74,297
13
168
171

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. – Johannes Gontrum May 21 '21 at 10:07
4

yeh, I would note that query is deprecated in the v2 API – Chris Sewell Sep 03 '21 at 22:40
@ChrisSewell: Is there a function that would produce the same result, for those of us who don't like unpacking tuples unnecessarily? – May 19 '22 at 18:24
1

Found a fix for now: for row, in session.execute(select(Object)): – May 19 '22 at 18:25
3

@MontanaBurr `session.execute(select(Object)).all()`. The `scalars()` method unpacks the tuples. You can omit it to get the tuples as is. – Mensch May 26 '22 at 14:32

score 15 · Answer 2 · answered Aug 12 '21 at 12:05

15

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.

answered Aug 12 '21 at 12:05

Mensch

670
7
16

score 0 · Answer 3 · answered Jul 09 '22 at 13:11

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

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

3 Answers3

Linked