For sqlalchemy, Who can gently give simple examples of SQL
functions like sum
, average
, min
, max
, for a column (score
in the following as an example).
As for this mapper:
class Score(Base): #... name = Column(String) score= Column(Integer) #...
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.
SQLAlchemy consists of two distinct components, known as the Core and the ORM.
The grouping is done with the group_by() query method, which takes the column to use for the grouping as an argument, same as the GROUP BY counterpart in SQL. The statement ends by calling subquery() , which tells SQLAlchemy that our intention for this query is to use it inside a bigger query instead of on its own.
A generic function is a pre-established Function class that is instantiated automatically when called by name from the func attribute. Note that calling any name from func has the effect that a new Function instance is created automatically, given that name.
See SQL Expression Language Tutorial for the usage. The code below shows the usage:
from sqlalchemy.sql import func qry = session.query(func.max(Score.score).label("max_score"), func.sum(Score.score).label("total_score"), ) qry = qry.group_by(Score.name) for _res in qry.all(): print _res
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