Type Hinting Sqlalchemy Query Result

I can't figure out what kind of object a sqlalchemy query returns. entries = session.query(Foo.id, Foo.date).all() The type of each object in entries seems to be sqlalchemy.util._

Solution 1:

I too found it curious that the class couldn't be imported. The answer is pretty long as I've walked you through how I've worked it out, bear with me.

Query.all() calls list() on the Query object itself:

defall(self):
    """Return the results represented by this ``Query`` as a list.
    This results in an execution of the underlying query.
    """returnlist(self)

... where list will be iterating over the object, so Query.__iter__():

def__iter__(self):
    context = self._compile_context()
    context.statement.use_labels = Trueif self._autoflush andnot self._populate_existing:
        self.session._autoflush()
    return self._execute_and_instances(context)

... returns the result of Query._execute_and_instances() method:

def_execute_and_instances(self, querycontext):
    conn = self._get_bind_args(
        querycontext, self._connection_from_session, close_with_result=True
    )

    result = conn.execute(querycontext.statement, self._params)
    return loading.instances(querycontext.query, result, querycontext)

Which executes the query and returns the result of sqlalchemy.loading.instances() function. In that function there is this line which applies to non-single-entity queries:

keyed_tuple = util.lightweight_named_tuple("result", labels)

... and if I stick a print(keyed_tuple) in after that line it prints <class 'sqlalchemy.util._collections.result'>, which is the type that you mention above. So whatever that object is, it's coming from the sqlalchemy.util._collections.lightweight_named_tuple() function:

deflightweight_named_tuple(name, fields):
    hash_ = (name,) + tuple(fields)
    tp_cls = _lw_tuples.get(hash_)
    if tp_cls:
        return tp_cls

    tp_cls = type(
        name,
        (_LW,),
        dict(
            [
                (field, _property_getters[idx])
                for idx, field inenumerate(fields)
                if field isnotNone
            ]
            + [("__slots__", ())]
        ),
    )

    tp_cls._real_fields = fields
    tp_cls._fields = tuple([f for f in fields if f isnotNone])

    _lw_tuples[hash_] = tp_cls
    return tp_cls

So the key part is this statement:

tp_cls = type(
    name,
    (_LW,),
    dict(
        [
            (field, _property_getters[idx])
            for idx, field inenumerate(fields)
            if field isnotNone
        ]
        + [("__slots__", ())]
    ),
)

... which calls the built in type() class which according to the docs:

With three arguments, return a new type object. This is essentially a dynamic form of the class statement.

And this is why you cannot import the class sqlalchemy.util._collections.result - because the class is only constructed at query time. I'd say that the reason for this is that the column names (i.e. the named tuple attributes) aren't known until the query is executed).

From python docs the signature for type is: type(name, bases, dict) where:

The name string is the class name and becomes the __name__ attribute; the bases tuple itemizes the base classes and becomes the __bases__ attribute; and the dict dictionary is the namespace containing definitions for class body and is copied to a standard dictionary to become the __dict__ attribute.

As you can see, the bases argument passed to type() in lightweight_named_tuple() is (_LW,). So any of the dynamically created named tuple types inherit from sqlalchemy.util._collections._LW, which is a class that you can import:

from sqlalchemy.util._collections import _LW

entries = session.query(Foo.id, Foo.date).all()
for entry in entries:
    assertisinstance(entry, _LW)  # True

... so I'm not sure whether it's good form to type your function to an internal class with the leading underscore, but _LW inherits from sqlalchemy.util._collections.AbstractKeyedTuple, which itself inherits from tuple. That's why your current typing of List[Tuple[int, str]] works, because it is a list of tuples. So take your pick, _LW, AbstractKeyedTuple, tuple would all be correct representations of what your function is returning.