本文整理汇总了Python中sqlalchemy.sql.func.sum函数的典型用法代码示例。如果您正苦于以下问题:Python sum函数的具体用法?Python sum怎么用?Python sum使用的例子?那么恭喜您, 这里精选的函数代码示例或许可以为您提供帮助。
在下文中一共展示了sum函数的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: calc_level_type_skill
def calc_level_type_skill(self, until=None):
query1 = (
db.session.query(func.count(TriggeredEvent.id), func.sum(TriggeredEvent.given_skill_points).label("sum_1"))
.join(Participation)
.join(LevelInstance)
.join(Level)
.join(Level.level_types)
.filter(Participation.pid == self.pid)
.filter(LevelType.id == self.ltid)
)
query2 = (
db.session.query(func.sum(TriggeredEvent.given_score_points).label("sum_1"))
.join(Participation)
.join(LevelInstance)
.join(Level)
.join(Level.level_types)
.filter(Participation.pid == self.pid)
.filter(LevelType.id == self.ltid)
.group_by(LevelInstance.id)
.order_by(desc("sum_1"))
)
if until:
query1 = query1.filter(TriggeredEvent.timestamp < until)
query2 = query2.filter(TriggeredEvent.timestamp < until)
considered_rows, skill_points = query1.all()[0] if query1.all()[0] else 0
score_points = query2.all()[0][0] if query2.all() else 0
return considered_rows, skill_points, score_points
示例2: get_line_sum
def get_line_sum():
line = int(request.form['line'])
chosen_direction = request.form['direction']
if chosen_direction in ['Inbound','Outbound']:
for result in db.session.query(func.count(Output.ZONE_NUMBER.distinct())).filter_by(LINE_NUMBER = line, DIRECTION = chosen_direction):
length = result[0]
zones, fare, cost = initialize_variables(length)
i = 0
while (i < length):
zones[i] = db.session.query(Output.ZONE_NUMBER.distinct()).filter_by(LINE_NUMBER = line, DIRECTION = chosen_direction)[i][0]
for fare_sum in db.session.query(func.sum(Output.FARE_COLLECTED)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i], DIRECTION = chosen_direction):
fare[i] = fare_sum[0]
for cost_sum in db.session.query(func.sum(Output.TOTAL_OPERATING_COST)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i], DIRECTION = chosen_direction):
cost[i] = cost_sum[0]
i += 1
return zones, fare, cost
else:
for result in db.session.query(func.count(Output.ZONE_NUMBER.distinct())).filter_by(LINE_NUMBER = line):
length = result[0]
zones, fare, cost = initialize_variables(length)
i = 0
while (i < length):
zones[i] = db.session.query(Output.ZONE_NUMBER.distinct()).filter_by(LINE_NUMBER = line)[i][0]
for fare_sum in db.session.query(func.sum(Output.FARE_COLLECTED)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i]):
fare[i] = fare_sum[0]
for cost_sum in db.session.query(func.sum(Output.TOTAL_OPERATING_COST)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i]):
cost[i] = cost_sum[0]
i += 1
return zones, fare, cost
示例3: get_biggest_donations
def get_biggest_donations(cls, limit=None, offset=None):
"""Getter for biggest donations.
Donations from the same person are grouped.
Args:
limit: Maximum number of donations to be returned.
offset: Offset of the result.
Returns:
Tuple with two items. First is total number if donations. Second
is a list of donations sorted by amount with a specified offset.
"""
query = db.session.query(
cls.first_name.label("first_name"),
cls.last_name.label("last_name"),
cls.editor_name.label("editor_name"),
func.max(cls.payment_date).label("payment_date"),
func.sum(cls.amount).label("amount"),
func.sum(cls.fee).label("fee"),
)
query = query.filter(cls.anonymous == False)
query = query.group_by(cls.first_name, cls.last_name, cls.editor_name)
query = query.order_by(desc("amount"))
count = query.count() # Total count should be calculated before limits
if limit is not None:
query = query.limit(limit)
if offset is not None:
query = query.offset(offset)
return count, query.all()
示例4: agg_supply
def agg_supply(timeid):
s_dict = {k: 0 for k in range(1, 14)}
##pull units devoted to production...
if timeid == 1 or session.query(Inventory.id).filter(Inventory.timeid==timeid-1).count() == 0:
result = session.query(Business.industryid, func.sum(Business.produnits)).group_by(Business.industryid).order_by(Business.industryid).all()
for id, produnits in result:
s = produnits * 500
s_dict[id] = s
##commit supply to the database
supply = Supply(timeid=timeid, industryid=id, endunits=s)
session.add(supply)
session.commit()
else:
result = session.query(Business.industryid, func.sum(Business.produnits), func.sum(Inventory.units)).filter(Inventory.timeid==timeid-1, Business.id==Inventory.businessid).group_by(Business.industryid).order_by(Business.industryid).all()
for id, produnits, inventory in result:
##calculate the supply for each record...
s = inventory + produnits * 500
s_dict[id] = s
##commit supply to the database
supply = Supply(timeid=timeid, industryid=id, endunits=s)
session.add(supply)
session.commit()
##return a supply dataframe for program
df = pd.DataFrame(s_dict.values(), index=s_dict.keys(), columns=["supply"])
return df
示例5: get_taxi_data_count
def get_taxi_data_count(color):
if color == 'all':
base_query = session.query(func.sum(TripStats.total_record_cnt))
yellow_count = base_query.filter(TripStats.taxi_type == 2).first()[0]
green_count = base_query.filter(TripStats.taxi_type == 1).first()[0]
return return_json({
"yellow": {
"day": int(yellow_count/365),
"week": int(yellow_count/52),
"month": int(yellow_count/12),
"year": int(yellow_count)
},
"green": {
"day": int(green_count/365),
"week": int(green_count/52),
"month": int(green_count/12),
"year": int(green_count)
}
})
taxi_type = COLOR_CODE[color]
count = session.query(func.sum(TripStats.total_record_cnt)).filter(TripStats.taxi_type == taxi_type).first()[0]
if 'average' in request.args:
if request.args['average'] == 'day':
return return_json({"count": int(count/365), "type": color})
elif request.args['average'] == 'month':
return return_json({"count": int(count/12), "type": color})
elif request.args['average'] == 'week':
return return_json({"count": int(count/52), "type": color})
return return_json({"count": count, "type": color})
示例6: process
def process(self, *args, **kwargs):
self.logtimes()
with make_db(self.db) as db:
try:
nodes = db.query(models.TrNode)
for node in nodes:
r = db.query(
func.sum(models.TrOnline.input_total).label("input_total"),
func.sum(models.TrOnline.output_total).label("output_total")
).filter(
models.TrOnline.account_number == models.TrAccount.account_number,
models.TrAccount.customer_id == models.TrCustomer.customer_id,
models.TrCustomer.node_id == node.id
).first()
if r and all([r.input_total,r.output_total]):
stat = models.TrFlowStat()
stat.node_id = node.id
stat.stat_time = int(time.time())
stat.input_total = r.input_total
stat.output_total = r.output_total
db.add(stat)
# clean expire data
_time = int(time.time()) - (86400 * 2)
db.query(models.TrFlowStat).filter(models.TrFlowStat.stat_time < _time).delete()
db.commit()
logger.info("flow stat task done")
except Exception as err:
db.rollback()
logger.error('flow_stat_job err,%s'%(str(err)))
return self.get_notify_interval()
示例7: _investments
def _investments(self):
today_investments = Investment.query.filter(
cast(Investment.added_at, Date) == date.today(),
Investment.status.in_(
(get_enum('INVESTMENT_PENDING'),
get_enum('INVESTMENT_SUCCESSED'))
)
).order_by("added_at desc").limit(10)
history_investments = db_session.query(
func.date_trunc('day', Investment.added_at),
func.sum(Investment.amount)).group_by(
func.date_trunc('day', Investment.added_at)
).order_by(func.date_trunc('day', Investment.added_at)).all()
total_investments = db_session.query(
func.sum(Investment.amount)).scalar()
today_invest_amount = db_session.query(
func.sum(Investment.amount)).filter(
cast(Investment.added_at, Date) == date.today(),
Investment.status.in_(
(get_enum('INVESTMENT_PENDING'),
get_enum('INVESTMENT_SUCCESSED'))
)
).scalar()
if not today_invest_amount:
today_invest_amount = 0
app.jinja_env.globals['today_invest_amount'] = today_invest_amount
app.jinja_env.globals['today_investments'] = today_investments
app.jinja_env.globals['total_investments'] = total_investments
app.jinja_env.globals['history_investments'] = history_investments
示例8: get_scan_information
def get_scan_information():
if not ValidateClass.check_login():
return redirect(ADMIN_URL + '/index')
if request.method == "POST":
start_time_stamp = request.form.get("start_time_stamp")[0:10]
end_time_stamp = request.form.get("end_time_stamp")[0:10]
start_time_array = datetime.datetime.fromtimestamp(int(start_time_stamp))
end_time_array = datetime.datetime.fromtimestamp(int(end_time_stamp))
if start_time_stamp >= end_time_stamp:
return jsonify(tag="danger", msg="wrong date select.", code=1002)
task_count = CobraTaskInfo.query.filter(
and_(CobraTaskInfo.time_start >= start_time_stamp, CobraTaskInfo.time_start <= end_time_stamp)
).count()
vulns_count = CobraResults.query.filter(
and_(CobraResults.created_at >= start_time_array, CobraResults.created_at <= end_time_array)
).count()
projects_count = CobraProjects.query.filter(
and_(CobraProjects.last_scan >= start_time_array, CobraProjects.last_scan <= end_time_array)
).count()
files_count = db.session.query(func.sum(CobraTaskInfo.file_count).label('files')).filter(
and_(CobraTaskInfo.time_start >= start_time_stamp, CobraTaskInfo.time_start <= end_time_stamp)
).first()[0]
code_number = db.session.query(func.sum(CobraTaskInfo.code_number).label('codes')).filter(
and_(CobraTaskInfo.time_start >= start_time_stamp, CobraTaskInfo.time_start <= end_time_stamp)
).first()[0]
return jsonify(code=1001, task_count=task_count, vulns_count=vulns_count, projects_count=projects_count,
files_count=int(files_count), code_number=int(code_number))
示例9: project_outline
def project_outline(name):
# name=request.args.get('name')
P=models.Projects.query.all()
project=models.Projects.query.filter_by(id=name).first()
G=project.goals.all()
gform=goal_form(request.values)
delete_form=DeleteRow_form()
q_sum = (db.session.query(
Projects.id.label("project_id"),
Goals.id.label("goal_id"),
func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"),
func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"),
func.count(Tasks.id).label("total"),
).join(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id,Goals.id).filter(Projects.id == name) )
if request.method == 'POST' and gform.submit.data:
if gform.validate() == False:
flash('Failed Field validation.')
flash_errors(gform)
return redirect(url_for('project_outline', name=name))
else:
p=models.Goals(goal=gform.goal.data,proj=project)
db.session.add(p)
db.session.commit()
return redirect(url_for('project_outline', name=name))
if request.method == 'POST' and delete_form.submitd.data:
pstratrow = delete_form.row_id.data
pstrat=models.Goals.query.filter_by(id=pstratrow).first()
db.session.delete(pstrat)
db.session.commit()
return redirect(url_for('project_outline',name=name))
# if request.method == 'POST' and delete_form.submit.data:
# delete_row=
return render_template("index_for_goal.html",project=project,G=G,gform=gform,P=P,zipit=zip(G,q_sum),delete_form=delete_form)
示例10: _repayments
def _repayments(self):
today_repayments = Plan.query.filter(
cast(Plan.plan_time, Date) == date.today(),
Plan.status == get_enum('PLAN_PENDING')
).order_by('plan_time desc').limit(10)
today_repay_amount = db_session.query(
func.sum(Plan.amount)
).filter(
cast(Plan.plan_time, Date) == date.today(),
Plan.status == get_enum('PLAN_PENDING')
).scalar()
if not today_repay_amount:
today_repay_amount = 0
total_repay_amount = db_session.query(
func.sum(Plan.amount)
).filter(Plan.status == get_enum('PLAN_PENDING')).scalar()
if not total_repay_amount:
total_repay_amount = 0
app.jinja_env.globals['today_repay_amount'] = today_repay_amount
app.jinja_env.globals['total_repay_amount'] = total_repay_amount
app.jinja_env.globals['today_repayments'] = today_repayments
示例11: allocation
def allocation():
loc = "nav-allocation"
if request.method == "POST" and request.form["action"] == "insert":
#insert new allocation
amount = request.form["amount"]
category_id = request.form["categoryid"]
allocation = Transaction(g.user.id, amount, "Allocation", category_id)
db.session.add(allocation)
db.session.commit()
success = (True if allocation.id != -1 else False)
return render_template("allocation.html", success = success, locid = loc)
elif request.method == "POST" and request.form["action"] == "add":
#show add allocation
return render_template("allocation.html", action = "add", net_income = net_income(), category = Category.query.filter_by(category_type="Expense"), locid = loc)
else:
#show allocation list
allocation_total = 0
qry = db.session.query(Transaction, func.sum(Transaction.amount).label("total_amount")).filter_by(transaction_type="Allocation").filter_by(user_id=g.user.id)
for _res in qry.all():
allocation_total = _res.total_amount
percentage_list = []
qry = db.session.query(Transaction, Category, func.sum(Transaction.amount).label("total_amount")).filter_by(transaction_type="Allocation").filter_by(user_id=g.user.id).join(Category).group_by(Category.description)
for _res in qry.all():
a = { "percent" : int(round(((_res.total_amount / allocation_total) * 100))), "description" : _res.Category.description, "amount" : _res.total_amount }
percentage_list.append(a)
rs = db.session.query(Transaction, Category).filter_by(transaction_type="Allocation").filter_by(user_id=g.user.id).join(Category)
return render_template("allocation.html", action = "show", allocation = rs, percentage_list=percentage_list, locid = loc)
示例12: _columns
def _columns(self, total_row=False):
columns = (
self._column_helper("rations", "male", "st"),
self._column_helper("rations", "female", "st"),
self._column_helper("rations", "male", "sc"),
self._column_helper("rations", "female", "sc"),
self._column_helper("rations", "male", "others"),
self._column_helper("rations", "female", "others"),
self._column_helper("rations", "male", "disabled"),
self._column_helper("rations", "female", "disabled"),
self._column_helper("rations", "male", "minority"),
self._column_helper("rations", "female", "minority"),
self._column_helper("absent", "male"),
self._column_helper("absent", "female"),
self._column_helper("partial", "male"),
self._column_helper("partial", "female"),
self._column_helper("rations", "migrant", "male"),
self._column_helper("rations", "migrant", "female"),
func.count(self.table.c.case_id).filter(
self.table.c.sex == 'M').label("child_count_male"),
func.count(self.table.c.case_id).filter(
self.table.c.sex == 'F').label("child_count_female"),
func.sum(self.table.c.num_rations_distributed).filter(
self.table.c.sex == 'M').label("thr_total_rations_male"),
func.sum(self.table.c.num_rations_distributed).filter(
self.table.c.sex == 'F').label("thr_total_rations_female"),
)
if not total_row:
return (self.table.c.awc_id.label("owner_id"),) + columns
return columns
示例13: child_victimisation_worksheet
def child_victimisation_worksheet(self, wb):
from dexter.models.views import DocumentChildrenView
ws = wb.add_worksheet("child_secondary_victimisation")
rows = self.filter(
db.session.query(
func.sum(
DocumentChildrenView.c.secondary_victim_source == "secondary-victim-source", type_=Integer
).label("secondary_victim_source"),
func.sum(
DocumentChildrenView.c.secondary_victim_identified == "secondary-victim-identified", type_=Integer
).label("secondary_victim_identified"),
func.sum(
DocumentChildrenView.c.secondary_victim_victim_of_abuse == "secondary-victim-abused", type_=Integer
).label("secondary_victim_victim_of_abuse"),
func.sum(
DocumentChildrenView.c.secondary_victim_source_identified_abused
== "secondary-victim-source-identified-abused",
type_=Integer,
).label("secondary_victim_source_identified_abused"),
).join(Document)
).all()
if not rows:
return
d = rows[0]._asdict()
data = [[k, d[k]] for k in sorted(d.keys(), key=len)]
ws.add_table(
0,
0,
len(data),
1,
{"name": "ChildSecondaryVictimisation", "data": data, "columns": [{"header": ""}, {"header": "count"}]},
)
示例14: strategy_outline
def strategy_outline(name,goal):
P=models.Projects.query.all()
project=models.Projects.query.filter_by(id=name).first()
pgoal=models.Goals.query.filter_by(id=goal).first()
S=pgoal.strategies.all()
sform=strategy_form(request.values)
delete_form=DeleteRow_form()
q_sum = (db.session.query(
Projects.id.label("project_id"),
func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"),
func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"),
func.count(Tasks.id).label("total"),
Strategies.id.label("strategy_id"),
Goals.id.label("goal_id"),
).join(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id,Goals.id,Strategies.id).filter(Goals.id == goal) )
if request.method == 'POST' and sform.submit.data:
print sform.validate()
if sform.validate() == False:
flash('Failed Field validation.')
flash_errors(sform)
return redirect(url_for('strategy_outline',name=name,goal=goal))
else:
p=models.Strategies(strategy=sform.strategy.data,goa=pgoal)
db.session.add(p)
db.session.commit()
return redirect(url_for('strategy_outline',name=name,goal=goal))
if request.method == 'POST' and delete_form.submitd.data:
pstratrow = delete_form.row_id.data
pstrat=models.Strategies.query.filter_by(id=pstratrow).first()
db.session.delete(pstrat)
db.session.commit()
return redirect(url_for('strategy_outline',name=name,goal=goal))
return render_template("index_for_strategy.html",project=project,S=S,sform=sform,pgoal=pgoal,P=P,zipit=zip(S,q_sum),delete_form=delete_form)
示例15: execute
def execute(mk_db):
log.msg("start flow stat task..")
db = mk_db()
try:
nodes = db.query(models.SlcNode)
for node in nodes:
r = db.query(
func.sum(models.SlcRadOnline.input_total).label("input_total"),
func.sum(models.SlcRadOnline.output_total).label("output_total")
).filter(
models.SlcRadOnline.account_number == models.SlcRadAccount.account_number,
models.SlcRadAccount.member_id == models.SlcMember.member_id,
models.SlcMember.node_id == node.id
).first()
if r:
stat = models.SlcRadFlowStat()
stat.node_id = node.id
stat.stat_time = int(time.time())
stat.input_total = r.input_total
stat.output_total = r.output_total
db.add(stat)
db.commit()
log.msg("flow stat task done")
except Exception as err:
db.rollback()
log.err(err,'flow_stat_job err')
finally:
db.close()