Menu
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 
273
We first extract the average value of the percentage column using SQLalchemy's `func. avg()` function. Then we use the `func. sum()` function to get the sum of the values in the percentage column.
All SELECT statements generated by SQLAlchemy ORM are constructed by Query object. It provides a generative interface, hence successive calls return a new Query object, a copy of the former with additional criteria and options associated with it.
Querying Records For this purpose Flask-SQLAlchemy provides a query attribute on your Model class. When you access it you will get back a new query object over all records. You can then use methods like filter() to filter the records before you fire the select with all() or first() .
method sqlalchemy.orm.Query. all() Return the results represented by this Query as a list. This results in an execution of the underlying SQL statement. The Query object, when asked to return either a sequence or iterator that consists of full ORM-mapped entities, will deduplicate entries based on primary key.
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.
70
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
