Sum fields in sqlAlchemy

问题:

I recently upgraded to the most recent version of sqlalchemy and some of my code no longer works. I'm having difficulty finding how to fix it and could use a hand.

Previously the query appeared as so.

self.db.query(Drive).filter(Drive.package_id==package.package_id)\
    .filter(Drive.wipe_end!=None).sum(Drive.wipe_end - Drive.wipe_start)

this worked before to retrieve the sum of some durations but now I get the following error:

'Query' object has no attribute 'sum'

Any googling I do gets me information that is several years old.

回答1:

I believe you need the sum() function in the "func" package:

from sqlalchemy import func
cursor = self.db.query(func.sum(Drive.wipe_end - Drive.wipe_start)).filter(Drive.package_id==package.package_id).filter(Drive.wipe_end!=None)
total = cursor.scalar()

回答2:

In SQLAlchemy 1.1.13 (released 3 August 2017), the syntax for using sum() is this:

from sqlalchemy import func
from apps.mystuff.models import MyModel

some_value = "hello"

result = MyModel.query.with_entities(
             func.sum(MyModel.MyColumn).label("mySum")
         ).filter_by(
             MyValue=some_value
         ).first()

# Depending on the column datatype, it's either int:
print(result.mySum)

# Or if the data is decimal/float:
print(float(result.mySum))

The main difference compared to the original answer is that instead of:
query(func.sum())

The syntax has changed to this starting from version 0.6.5:
query.with_entities(func.sum())

http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.with_entities