python - 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. I'd be grateful if someone could show me how to do it.

Many thanks!

question from:https://stackoverflow.com/questions/9187530/using-alias-for-select-as-in-sqlalchemy

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()