Q: SQLAlchemy 1.4 (2.0) problem converting results from session.execute(statement).scalars().all() or session.execute(statement).all()

Question

I have this table defined as follows

class Carrier(Base):
    __tablename__ = 'Carrier'
    __table_args__ = {'comment': 'Carrier Code List'}

    Id = Column(Integer, primary_key=True, comment='PK for carrier table')
    Code = Column(String(50), index=True, comment='code to use')
    Description = Column(String(255), comment='description of the code')

To query the table I use the following

with Session(self.sqlengine, future=True) as session:
        statement = select(Carrier)
        #result = session.execute(statement).all()
        result = session.execute(statement).scalars().all()

I can loop through the results as follows

 for row in result:
    print(row.Id, row.Code)

According to the documentation, the following should return

# list of tuples
result = session.execute(statement).all()

# list of first element of each row (i.e. User objects)
result = session.execute(statement).scalars().all()

This is where I am getting confused. I am not getting a list of tuples or the first element of each row. I am getting an address to the instance of the class. For example

It would be nice if it actually returned a tuple or a class with properties as I defined it in the table class but it is not.

I have also failed in all my attempts to convert it to a class that would be useful.

If I should be doing some mapping or converting I have no idea how nor have I been able to find an example. I feel the documentation is not very good either at least for 1.4/2.0

I would appreciate some assistance I know I am missing a nuance on how to convert but I do not know how to proceed.

user_2144390 · Answer

For this setup

with sa.orm.Session(engine, future=True) as session:
    # create test data
    session.add_all([Carrier(Code="Alfa"), Carrier(Code="Bravo")])
    session.commit()
    
    # define our select statement
    statement = sa.select(Carrier)
    
    results = session.execute(statement).all()
    print(results)
    # [(,), (,)]

version 1.4 returns a list of Row objects. They are tuple-like and are represented in the same way as a tuple, surrounded with round brackets: (,).

Using .scalars()

    results = session.scalars(statement).all()
    print(results)
    # [, ]

returns a list of "plain" ("scalar") Carrier objects. Notice that they are not enclosed in ( …,). The .scalars() call "looks inside" each Row object and returns the first element it finds (by default, index=0).

The difference is easier to see with simple scalar values:

    statement = sa.select(Carrier.Id)  # just the .Id column
    
    results = session.execute(statement).all()
    print(results)
    # [(1,), (2,)]
    
    results = session.scalars(statement).all()
    print(results)
    # [1, 2]

Q: SQLAlchemy 1.4 (2.0) problem converting results from session.execute(statement).scalars().all() or session.execute(statement).all()

1 Answers