CODEWITHSUNDEEP
pythonsqlalchemyormmariadbpymysql
Johannes Gontrum
Johannes Gontrum

Reputation: 1161

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

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.

Upvotes: 10

Views: 29408

Answers (3)

koleS
koleS

Reputation: 1313

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

Upvotes: 0

Mensch
Mensch

Reputation: 700

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.

Upvotes: 15

van
van

Reputation: 76962

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)

Upvotes: 19

Related Questions