sqlalchemy joined alias doesn't have columns from both tables

All I want is the count from TableA grouped by a column from TableB, but of course开发者_如何学Python I need the item from TableB each count is associated with. Better explained with code:

TableA and B are Model objects.

I'm trying to follow this syntax as best I can.

Trying to run this query:

sq = session.query(TableA).join(TableB).\
        group_by(TableB.attrB).subquery()

countA = func.count(sq.c.attrA)
groupB = func.first(sq.c.attrB)   

print session.query(countA, groupB).all()     

But it gives me an AttributeError (sq does not have attrB)

I'm new to SA and I find it difficult to learn. (links to recommended educational resources welcome!)

When you make a subquery out of a select statement, the columns that can be accessed from it must be in the columns clause. Take for example a statement like:

select x, y from mytable where z=5

If we wanted to make a subquery, then GROUP BY 'z', this would not be legal SQL:

select * from (select x, y from mytable where z=5) as mysubquery group by mysubquery.z

Because 'z' is not in the columns clause of "mysubquery" (it's also illegal since 'x' and 'y' should be in the GROUP BY as well, but that's a different issue).

SQLAlchemy works the same exact way. When you say query(..).subquery(), or use the alias() function on a core selectable construct, it means you're wrapping your SELECT statement in parenthesis, giving it a (usually generated) name, and giving it a new .c. collection that has only those columns that are in the "columns" clause, just like real SQL.

So here you'd need to ensure that TableB, at least the column you're dealing with externally, is available. You can also limit the columns clause to just those columns you need:

sq = session.query(TableA.attrA, TableB.attrB).join(TableB).\
        group_by(TableB.attrB).subquery()

countA = func.count(sq.c.attrA)
groupB = func.first(sq.c.attrB)   

print session.query(countA, groupB).all()    

Note that the above query probably only works on MySQL, as in general SQL it's illegal to reference any columns that aren't part of an aggregate function, or part of the GROUP BY, when grouping is used. MySQL has a more relaxed (and sloppy) system in this regard.

edit: if you want the results without the zeros:

import collections

letter_count = collections.defaultdict(int)
for count, letter in session.query(func.count(MyClass.id), MyClass.attr).group_by(MyClass.attr):
    letter_count[letter] = count

for letter in ["A", "B", "C", "D", "E", ...]:
    print "Letter %s has %d elements" % letter_count[letter]

note letter_count[someletter] defaults to zero if otherwise not populated.