How to use avg and sum in SQLAlchemy query


Question

I'm trying to return a totals/averages row from my dataset which contains the SUM of certain fields and the AVG of others.

I could do this in SQL via:

SELECT SUM(field1) as SumFld, AVG(field2) as AvgFld 
FROM Rating WHERE url=[url_string]

My attempt to translate this into SQLAlchemy is as follows:

totals = Rating.query(func.avg(Rating.field2)).filter(Rating.url==url_string.netloc)

But this is erroring out with:

TypeError: 'BaseQuery' object is not callable
1
32
8/22/2011 4:42:47 AM

Accepted Answer

You should use something like:

from sqlalchemy.sql import func
session.query(func.avg(Rating.field2).label('average')).filter(Rating.url==url_string.netloc)

You cannot use MyObject.query here, because SqlAlchemy tries to find a field to put result of avg function to, and it fails.

51
7/28/2014 3:32:51 PM

You cannot use MyObject.query here, because SqlAlchemy tries to find a field to put result of avg function to, and it fails.

This isn't exactly true. func.avg(Rating.field2).label('average') returns a Column object (the same type object that it was given to be precise). So you can use it with the with_entities method of the query object.

This is how you would do it for your example:

Rating.query.with_entities(func.avg(Rating.field2).label('average')).filter(Rating.url == url_string.netloc)

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon