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

user_4587312 0
Viewed 11395

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.

pymysql mariadb sqlalchemy orm python
3 Answers

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.

user_117284010

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)
user_995940

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
user_6593890
Related