SQLAlchemy ORM: Why row[0].attribute instead of just row.attribute on select results [duplicate]

Question

Why did the creators of SQLAlchemy decide to return rows from select statements with ORM tables (like User(Base)) in tuples with the first result being what you'd expect the normal result being?

For example, if your query is this:

user = session.execute(select(User)).first()

Why did they decide this:

user_name = user[0].name

is better than this:

user_name = user.name

by default?

Answer 1

I believe this was done to make the API more consistent with the core api. Also this makes a single argument compatible with multiple arguments, ie select(User) and select(User, Company.name).join(User.company) both return namedtuple-like rows. There is some explanation in the docs: rowproxy-is-no-longer-a-proxy-is-now-called-row-and-behaves-like-an-enhanced-named-tuple

added more doc links based on comments

Note that it seems there are a few helper methods to make this usage easier, session.scalar, session.scalars, scalar and scalars() to achieve your intended usage.

Multiple ways to perform this task:

# This is probably the most clear for a single result.
user = session.scalar(select(User))
user = session.scalars(select(User)).first()
user = session.execute(select(User)).scalar()
user = session.execute(select(User)).scalars().first()

# for looping without needing to unpack 1-tuple
for user in session.scalars(select(User)):
    print(user)

Compared to this type of usage

user, = session.execute(select(User)).first()
user, company_name = session.execute(select(User, Company.name).join(User.company)).first()