CODEWITHSUNDEEP
sqlalchemy
Vishakh
Vishakh

Reputation: 1388

Using alias() for 'select as' in SQLAlchemy

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.

Upvotes: 78

Views: 85284

Answers (1)

Sean Vieira
Sean Vieira

Reputation: 159905

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

Upvotes: 166

Related Questions