Using alias() for 'select as' in SQLAlchemy

Question

Let's say I have a table 'shares' with the following columns:

company    price    quantity Microsoft  100      10 Google     99       5 Google     99       20 Google     101      15 

I'd like to run the equivalent of a SQL statement like this:

select price,         sum(quantity) as num  from shares  where company='Google'  group by price; 

The closest I've come is:

result = (dbsession.query(Shares.price, func.sum(Shares.quantity))          .filter(Shares.company == 'Google')          .group_by(Shares.price)          .all()) 

I'm having trouble with setting up the 'sum(quantity) as num' in sqlalchemy. It appears I need to use alias() but I can't figure out how by looking at the documentation.

Answer 1

You actually want the label method.

result = dbsession.query(Shares.price, \                             func.sum(Shares.quantity).label("Total sold")) \                             .filter(Shares.company== 'Google') \                             .group_by(Shares.price).all()