SQLAlchemy equivalent to Django's annotate() method

Question

I'm doing a join like this in SQLAlchemy:

 items = Item.query\
    .outerjoin((ItemInfo, ItemInfo.item_id==Item.id))
 items.add_columns(ItemInfo.count)

This causes SQLAlchemy to return tuples:

 >>> items.first()
 (<Item ...>, 2)

I'd much prefer it if the "count" value would instead be returned as an attribute of the item, i.e. I want to do:

 >>> items.first().count
 2

Is this supported?

Answer 1

Actually, "items.first().count" would work, since the tuple you get back is a named tuple...but guessing you don't want to see items.first().item.foo.

The second way you could do this would be just to run the result of your query() through a function that constructs the kind of result you want:

def process(q):
    for item, count in q:
        item.count = count
        yield count

edit: here is a generalized version:

from sqlalchemy.orm.query import Query

class AnnotateQuery(Query):
    _annotations = ()

    def annotate(self, key, expr):
        q = self.add_column(expr)
        q._annotations = self._annotations + (key, )
        return q

    def __iter__(self):
        if not self._annotations:
            return super(AnnotateQuery, self).__iter__()
        else:
            for row in super(AnnotateQuery, self):
                item, remaining = row[0], row[1:]
                for i, key in enumerate(self._annotations):
                    setattr(item, key, remaining[i])
                yield item


# session usage:

Session = sessionmaker(query_cls=AnnotateQuery)

# query usage:
q = Session.query(Item).outerjoin(...).annotate('count', Item.count)

The third, is that you alter the Item class to support this function. You'd use column_property() to apply a select subquery to your class: http://www.sqlalchemy.org/docs/orm/mapper_config.html#sql-expressions-as-mapped-attributes . If you wanted the loading of the attribute to be conditional, you'd use deferred: http://www.sqlalchemy.org/docs/orm/mapper_config.html#deferred-column-loading .