sqlalchemy ORM query object returns result of different type depending on context

Question

I'am working with alchemy ORM's Query object. In different context it returns different types.

I have following table in postgres:

select * from test;
 test_id | test_col1 | test_col2 
---------+-----------+-----------
       1 | ABC       | ABC
       2 | XYZ       | XYZ

I have following class in Python3:

class Test (DeclarativeBase):
    __tablename__ = 'test'

    id = sa.Column('test_id', sa.Integer(), primary_key=True)
    col1 = sa.Column('test_col1', sa.String(250), nullable = True)
    col2 = sa.Column('test_col2', sa.String(250), nullable = True)

My queries and results:

In [2]: q1 = session.query(Test)                                                                                                                                                                                                                                                         

In [3]: rows1 = q1.all()                                                                                                                                                                                                                                                                 

In [4]: print(type(rows1[0]))                                                                                                                                                                                                                                                            


In [5]:                                                                                                                                                                                                                                                                                  

In [5]: cte = session.query(Test).cte()                                                                                                                                                                                                                                                  

In [6]: q2 = session.query(cte)                                                                                                                                                                                                                                                          

In [7]: rows2 = q2.all()                                                                                                                                                                                                                                                                 

In [8]: print(type(rows2[0]))                                                                                                                                                                                                                                                            


In [9]:                                                                                                                                                                                                                                                                                  

In [9]: q3 = session.query(Test).filter(Test.col1 == 'ABC')                                                                                                                                                                                                                              

In [10]: rows3 = q3.all()                                                                                                                                                                                                                                                                

In [11]: print(type(rows3[0]))                                                                                                                                                                                                                                                           


In [12]:                                                                                                                                                                                                                                                                                 

In [12]:                                                                                                                                                                                                                                                                                 

In [12]: q4 = session.query(Test).filter(Test.col1 == 'ABC').one()                                                                                                                                                                                                                       

In [13]: print(type(q4))                                                                                                                                                                                                                                                                 


In [14]:                                                                                                                                                                                                                                                                                 

In [14]: result = session.execute(session.query(Test).filter(Test.col1 == 'ABC'))                                                                                                                                                                                                        

In [15]: for row in result: 
    ...:         print (type(row)) 
    ...:

Only type "sqlalchemy.util._collections.result" allows use method _asdict() on row. So, if I call _asdict() in my code, I must explicitly check type, otherwise - I get 'AttributeError' exception.

Is it possible to return only one type from alchemy ORM query and use _asdict() no additional checks?

user_6312218 · Answer

The first example:

session.query(Test)
# <__main__.Test>

is using the declarative orm's query builder. When you stay within the bounds of what it can handle, it is most likely to do what you want e.g. returns an instance of your mapped class.

The second example

cte = session.query(Test).cte()                                                                                                                                                                                                                                                  
session.query(cte)
#

Converts to sql

with foo(
    select * from test
)
select * from foo

Since the cte can contain arbitrary sql, the indirection breaks both the database, and sqlalchemy's knowledge of of the relationship between foo and test. It would be possible for SQLAlchemy to overcome that by checking explicitly if you're selecting all fields from a single table, but that has not been implemented. Instead, it returns a generic type that can handle any shape of data.

The third case using a lower level API session.execute directly. That is intended for executing SQL from text. It is compiling the "smart" ORM query to a "dumb" SQL string that has no knowledge about what your python types are & again, returns a generic type over that lower level API.

The underscore prefix in _asdict() is the author signaling that this is a private method. That means they may change it at any time without notice, so you'll want to avoid it.

You can add a .to_dict() method to your own declarative base class as described in the docs

Here's a reference for how you could implement it

Or, if you're just playing around you can always call .__dict__()

sqlalchemy ORM query object returns result of different type depending on context

1 Answers