def Get(icpe, from_date = (datetime.now() - timedelta(days=7)), to_date =
datetime.now()):
power_data = SQL.session.query(PowerModel, \
label('low', func.min(PowerModel.low)),
label('high', func.max(PowerModel.high)),
label('total', func.sum(PowerModel.average)),
label('date', PowerModel.date)).\
join(iCPEModel).\
filter(iCPEModel.mac_address == icpe).\
filter(PowerModel.date > from_date).\
filter(PowerModel.date < to_date).\
group_by(PowerModel.date).all()
if not power_data:
return False
ret_json = {'icpe' : icpe}
ret_json['power'] = []
for data in power_data:
ret_json['power'].append({'date' : str(data.date), 'low' : data.low, 'high' : data.high,
'total' : data.total})
return ret_json
python类sum()的实例源码
def test_counter_works_add_counter(self):
"""Test event listener when adding a task run adds a counter."""
task_run = TaskRunFactory.create()
counters = db.session.query(Counter).filter_by(project_id=task_run.project.id,
task_id=task_run.task.id)\
.order_by(Counter.id).all()
assert len(counters) == 2, counters
for c in counters:
assert c.task_id == task_run.task.id, c
assert c.project_id == task_run.project.id, c
counters = db.session.query(Counter.project_id, Counter.task_id, func.sum(Counter.n_task_runs))\
.filter_by(project_id=task_run.project.id,
task_id=task_run.task.id)\
.group_by(Counter.project_id, Counter.task_id).all()
assert len(counters) == 1, counters
counter = counters[0]
assert counter[1] == 1, counter
def current(*groups):
groups = GroupModel.query.filter(GroupModel.name.in_(*[groups])).all()
if not len(groups):
return False
ret_data = []
for group in groups:
group_data = {}
group_data['name'] = group.name
icpes = [node.icpe.mac_address for node in group.nodes if node.icpe]
min_ago = (datetime.now() - timedelta(hours=0.5))
latest_heat = SQL.session.query(HeatModel,\
label('sum', func.sum(HeatModel.average)),
label('count', func.count(HeatModel.average))).\
join(HeatModel.icpe).\
filter(iCPEModel.mac_address.in_(*[icpes])).\
filter(HeatModel.date > min_ago).first()
if latest_heat.count:
group_data['heat'] = latest_heat.sum / latest_heat.count
else:
group_data['heat'] = 0.0
ret_data.append(group_data)
return ret_data
def latest(icpe):
heat_data = SQL.session.query(HeatModel, \
label('low', func.min(HeatModel.low)),
label('high', func.max(HeatModel.high)),
label('total', func.sum(HeatModel.average)),
label('date', HeatModel.date)).\
join(iCPEModel).\
filter(iCPEModel.mac_address == icpe).\
order_by(HeatModel.date.desc()).\
group_by(HeatModel.date).first()
if not heat_data:
return False
return {'icpe' : icpe, 'date' : str(heat_data.date), 'low' : power_data.low,\
'high' : heat_data.high, 'total' : power_data.total}
def get(icpe, from_date = (datetime.now() - timedelta(days=7)), to_date =
datetime.now()):
heat_data = SQL.session.query(HeatModel, \
label('low', func.min(HeatModel.low)),
label('high', func.max(HeatModel.high)),
label('total', func.sum(HeatModel.average)),
label('date', HeatModel.date)).\
join(iCPEModel).\
filter(iCPEModel.mac_address == icpe).\
filter(HeatModel.date > from_date).\
filter(HeatModel.date < to_date).\
group_by(HeatModel.date).all()
if not heat_data:
return False
ret_json = {'icpe' : icpe}
ret_json['heat'] = []
for data in heat_data:
ret_json['heat'].append({'date' : str(data.date), 'low' : data.low, 'high' : data.high,
'total' : data.total})
return ret_json
def latest(icpe):
power_data = SQL.session.query(PowerModel, \
label('low', func.min(PowerModel.low)),
label('high', func.max(PowerModel.high)),
label('total', func.sum(PowerModel.average)),
label('date', PowerModel.date)).\
join(iCPEModel).\
filter(iCPEModel.mac_address == icpe).\
order_by(PowerModel.date.desc()).\
group_by(PowerModel.date).first()
if not power_data:
return False
return {'icpe' : icpe, 'date' : str(power_data.date), 'low' : power_data.low,\
'high' : power_data.high, 'total' : power_data.total}
def count_water_stock(message):
"""????????????????
:param message: slackbot?????????????class
"""
s = Session()
stock_number, latest_ctime = (
s.query(func.sum(WaterHistory.delta),
func.max(case(whens=((
WaterHistory.delta != 0,
WaterHistory.ctime),), else_=None))).first()
)
if stock_number:
# SQLite????????????????
if not isinstance(latest_ctime, datetime.datetime):
latest_ctime = datetime.datetime.strptime(latest_ctime,
'%Y-%m-%d %H:%M:%S')
message.send('??: {}? ({:%Y?%m?%d?} ??)'
.format(stock_number, latest_ctime))
else:
message.send('??????????')
def search(self, **kwds):
'''Finds entities in the table that satisfy certain criteria.
:param order: Order rows by specified column.
:type order: string
'''
request = Session.query(func.sum(ckanextRequestDataCounters.requests))\
.all()
replied = Session.query(func.sum(ckanextRequestDataCounters.replied))\
.all()
declined_sum = func.sum(ckanextRequestDataCounters.declined)
declined = Session.query(declined_sum).all()
shared = Session.query(func.sum(ckanextRequestDataCounters.shared))\
.all()
counters = {
'requests': request,
'replied': replied,
'declined': declined,
'shared': shared
}
return counters
def search_by_organization(self, **kwds):
'''Finds entities in the table that satisfy certain criteria.
:param order: Order rows by specified column.
:type order: string
'''
request = Session.query(func.sum(ckanextRequestDataCounters.requests))
request = request.filter_by(**kwds).all()
replied = Session.query(func.sum(ckanextRequestDataCounters.replied))
replied = replied.filter_by(**kwds).all()
declined = Session.query(func.sum(ckanextRequestDataCounters.declined))
declined = declined.filter_by(**kwds).all()
shared = Session.query(func.sum(ckanextRequestDataCounters.shared))
shared = shared.filter_by(**kwds).all()
counters = {
'requests': request,
'replied': replied,
'declined': declined,
'shared': shared
}
return counters
def get_user(user_id):
secret = request.headers.get('Authorization')
user = db_session.query(api.models.User).filter(api.models.User.id == user_id). \
filter(api.models.User.secret == secret).one_or_none()
# get koin_count, mission_count_today and mission_count
mission_count = db_session.query(api.models.Solution).filter(api.models.Solution.user_id == user_id)\
.filter(api.models.Solution.valid).count()
mission_count_today = db_session.query(api.models.Solution).filter(api.models.Solution.user_id == user_id)\
.filter(api.models.Solution.valid)\
.filter(cast(api.models.Solution.create_date, Date) == date.today()).count()
koin_count = db_session.query(func.sum(api.models.Solution.koin_count)).filter(api.models.Solution.user_id == user_id).scalar()
logger.debug('user '+str(user_id)+' logged in')
if user:
return user.dump(mission_count=mission_count, mission_count_today=mission_count_today, koin_count=koin_count)
return 'Unauthorized', 401
def num_stale_accounts(sess=None):
"""
Return the number of accounts with stale data.
@TODO This is a hack because I just cannot figure out how to do this
natively in SQLAlchemy.
:return: count of accounts with stale data
:rtype: int
"""
if sess is None:
sess = db_session
return sum(
1 if a.is_stale else 0 for a in sess.query(
Account).filter(Account.is_active.__eq__(True)).all()
)
def budget_account_sum(sess=None):
"""
Return the sum of current balances for all is_budget_source accounts.
:return: Combined balance of all budget source accounts
:rtype: float
"""
if sess is None:
sess = db_session
sum = 0
for acct in sess.query(Account).filter(
Account.is_budget_source.__eq__(True),
Account.is_active.__eq__(True)
):
if acct.balance is not None:
sum += float(acct.balance.ledger)
return sum
def budget_account_unreconciled(sess=None):
"""
Return the sum of unreconciled txns for all is_budget_source accounts.
:return: Combined unreconciled amount of all budget source accounts
:rtype: float
"""
if sess is None:
sess = db_session
sum = 0
for acct in sess.query(Account).filter(
Account.is_budget_source.__eq__(True),
Account.is_active.__eq__(True)
):
sum += acct.unreconciled_sum
return sum
def standing_budgets_sum(sess=None):
"""
Return the sum of current balances of all standing budgets.
:return: sum of current balances of all standing budgets
:rtype: float
"""
if sess is None:
sess = db_session
res = sess.query(func.sum(Budget.current_balance)).filter(
Budget.is_periodic.__eq__(False),
Budget.is_active.__eq__(True)
).all()[0][0]
if res is None:
return 0
return float(res)
def pp_sum(sess=None):
"""
Return the overall allocated sum for the current payperiod minus the
sum of all reconciled Transactions for the pay period.
:return: overall allocated sum for the current pay period minus the sum
of all reconciled Transactions for the pay period.
:rtype: float
"""
if sess is None:
sess = db_session
pp = BiweeklyPayPeriod.period_for_date(dtnow(), sess)
allocated = float(pp.overall_sums['allocated'])
spent = float(pp.overall_sums['spent'])
logger.debug('PayPeriod=%s; allocated=%s; spent=%s',
pp, allocated, spent)
return allocated - spent
def groupByCategoryAndCount(queryObject,columnName,sampleRate=0.2,numCats=False, includeNone=False):
columnObject = getattr(Dataset,columnName)
query = (
queryObject.with_entities(columnObject) # choose only the column we care about
.filter(func.rand() < sampleRate) # grab random sample of rows
.add_columns(func.count(1).label('count')) # add count to response
.group_by(columnName) # group by
.order_by(desc('count')) # order by the largest first
)
if not includeNone:
query = query.filter(columnObject.isnot(None)) # filter out NULLs
# If no numCats is passed in, show all the groups
if numCats:
query = query.limit(numCats) # show the top N results
#TODO maybe: count 'other column' if numCats, where sum counts all but top numCats fields
return (
dict((key,val * (1/sampleRate)) for key, val in # rescale sampled columns to approx. results on full dataset
query.all() # actually run the query
)
)
# Create a query object for a complex "histogram in SQL" query with custom numerical bin sizes
def nr_of_models_alive(self, t=None) -> int:
"""
Number of models still alive.
Parameters
----------
t: int
Population number
Returns
-------
nr_alive: int >= 0 or None
Number of models still alive.
None is for the last population
"""
if t is None:
t = self.max_t
else:
t = int(t)
model_probs = self.get_model_probabilities(t)
return int((model_probs.p > 0).sum())
def get_grading_session_metrics(user_id):
subq = db.session.query(ResponseGrade.session_id.label('session_id'),
func.count(ResponseGrade.id).label(
'responses_graded'),
label('time_grading',
UserGradingSession.ended_on - UserGradingSession.started_on)) \
.join(UserGradingSession) \
.filter(UserGradingSession.user_id == user_id) \
.group_by(ResponseGrade.session_id,
UserGradingSession.started_on,
UserGradingSession.ended_on).subquery()
query = db.session.query(func.count(subq.c.session_id),
func.sum(subq.c.responses_graded),
extract('EPOCH', func.sum(subq.c.time_grading)))
return query.all()[0]
def prob_view_get(hid, pid):
result, ok, problem, homework = checkValid(hid, pid)
if not ok:
return result
form = SubmitForm()
plist = ['rank', 'user name', problem.name, 'Entries', 'last submit time']
result = db.session.query(ProbUserStatic.user_id, func.sum(ProbUserStatic.real_score), func.max(ProbUserStatic.score), func.sum(ProbUserStatic.submit_times), func.max(ProbUserStatic.last_time)). \
filter(ProbUserStatic.prob_id == problem.id, ProbUserStatic.score > 0) \
.group_by(ProbUserStatic.user_id).order_by(func.sum(ProbUserStatic.real_score)).all()
prank = []
result = reversed(result)
for i, res in enumerate(result):
name = User.query.filter_by(id=res[0]).first().name
prank.append([i + 1, name, res[2], res[3], res[4]])
if hid == -1:
return render_template('prob_view.html', problem=problem, form=form, hid=-1, data=problem.data, active='problem', attach = False, plist = plist, prank = prank)
attach = os.path.exists(os.path.join(app.config['UPLOAD_FOLDER'], problem.data.attach))
return render_template('prob_view.html', problem=problem, form=form, hid=hid, data=problem.data, active='homework', attach =attach, plist = plist, prank = prank)
def get_match_summary_aggregate(self, match_id):
return Database.session.query(MatchHero.account_id,
func.sum(text('match_heroes.player_win')).label('player_win'),
func.count(MatchHero.player_win).label('matches')). \
filter(MatchHero.match_id >= match_id). \
group_by(MatchHero.account_id). \
all()
def get_match_hero_summary_aggregate(self, match_id):
return Database.session.query(MatchHero.account_id,
MatchHero.hero_id,
func.sum(text('match_heroes.player_win')).label('player_win'),
func.count(MatchHero.player_win).label('matches')). \
filter(MatchHero.match_id >= match_id). \
group_by(MatchHero.account_id). \
group_by(MatchHero.hero_id). \
all()
def get_match_item_summary_aggregate(self, match_id):
return Database.session.query(MatchItem.account_id,
MatchItem.item_id,
func.sum(text('match_items.player_win')).label('player_win'),
func.count(MatchItem.player_win).label('matches')). \
filter(MatchItem.match_id >= match_id). \
group_by(MatchItem.account_id). \
group_by(MatchItem.item_id). \
all()
def current(group):
group = SQL.session.query(GroupModel).filter(GroupModel.name ==
group).first()
if group is None:
return False
ret_data = []
group_data = {}
group_data['name'] = group.name
group_data['heat'] = 0.0
for node in group.nodes:
if not node.icpe:
continue
node_data = {}
node_data['name'] = node.name
min_ago = (datetime.now() - timedelta(hours=0.5))
latest_heat = SQL.session.query(HeatModel,\
label('sum', func.sum(HeatModel.average)),
label('count', func.count(HeatModel.average))).\
join(HeatModel.icpe).\
filter(iCPEModel.mac_address == node.icpe.mac_address).\
filter(HeatModel.date > min_ago).first()
if latest_heat.count:
node_data['heat'] = latest_heat.sum / latest_heat.count
group_data['heat'] += node_data['heat']
else:
node_data['heat'] = 0.0
ret_data.append(node_data)
ret_data.append(group_data)
return ret_data
def current(group):
group = SQL.session.query(GroupModel).filter(GroupModel.name ==
group).first()
if group is None:
return False
ret_data = []
group_data = {}
group_data['name'] = group.name
group_data['power'] = 0.0
for node in group.nodes:
if not node.icpe:
continue
node_data = {}
node_data['name'] = node.name
min_ago = (datetime.now() - timedelta(hours=0.5))
latest_power = SQL.session.query(PowerModel,\
label('sum', func.sum(PowerModel.average)),
label('count', func.count(PowerModel.average))).\
join(PowerModel.icpe).\
filter(iCPEModel.mac_address == node.icpe.mac_address).\
filter(PowerModel.date > min_ago).first()
if latest_power.count:
node_data['power'] = latest_power.sum / latest_power.count
group_data['power'] += node_data['power']
else:
node_data['power'] = 0.0
ret_data.append(node_data)
ret_data.append(group_data)
return ret_data
def current(icpe, sensor):
sensor = SQL.session.query(SensorModel).\
join(HeatModel.icpe).\
filter(iCPEModel.mac_address == icpe).\
filter(SensorModel.sensor_id == sensor).first()
if sensor is None or sensor.heat is None:
return False
sensor_data = {}
sensor_data['name'] = sensor.name
sensor_data['sensor'] = sensor.sensor_id
sensor_data['icpe'] = sensor.icpe.mac_address
min_ago = (datetime.now() - timedelta(hours=0.5))
latest_heat = SQL.session.query(HeatModel,\
label('sum', func.sum(HeatModel.average)),
label('count', func.count(HeatModel.average))).\
join(HeatModel.icpe).\
join(HeatModel.sensor).\
filter(iCPEModel.mac_address == sensor.icpe.mac_address).\
filter(SensorModel.sensor_id == sensor.sensor_id).\
filter(HeatModel.date > min_ago).first()
if latest_heat.count:
sensor_data['heat'] = latest_heat.sum / latest_heat.count
sensor_data['heat'] += sensor_data['heat']
else:
sensor_data['heat'] = 0.0
return sensor_data
def count_redbull_stock(message):
"""???RedBull????????????
:param message: slackbot?????????????class
"""
s = Session()
q = s.query(func.sum(RedbullHistory.delta).label('stock_number'))
stock_number = q.one().stock_number
if stock_number is None:
stock_number = 0
message.send('??????? {} ?'.format(stock_number))
def show_redbull_history_csv(message):
"""RedBull??????????CSV?????
:param message: slackbot?????????????class
"""
s = Session()
consume_hisotry = (s.query(RedbullHistory)
.filter(RedbullHistory.delta < 0)
.order_by(RedbullHistory.id.desc()))
# func.month??????????groupby count?????
# SQLite??MONTH()???????????Python?????
def grouper(item):
return item.ctime.year, item.ctime.month
ret = []
for ((year, month), items) in groupby(consume_hisotry, grouper):
count = -sum(item.delta for item in items)
ret.append(['{}/{}'.format(year, month), str(count)])
output = StringIO()
w = csv.writer(output)
w.writerows(ret)
param = {
'token': settings.API_TOKEN,
'channels': message.body['channel'],
'title': 'RedBull History Check'
}
requests.post(settings.FILE_UPLOAD_URL,
params=param,
files={'file': output.getvalue()})
def manage_water_stock(message, delta):
"""??????????????
:param message: slackbot?????????????class
:param str delta: POST?????????
User??POST???delta????????????????????
DB??????????????????????
"""
delta = -int(delta)
if not delta:
message.send('0????????')
return
user_id = message.body['user']
s = Session()
s.add(WaterHistory(user_id=user_id, delta=delta))
s.commit()
q = s.query(func.sum(WaterHistory.delta).label('stock_number'))
stock_number = q.one().stock_number
if delta < 0:
message.send('??????????????{}?????????(??: {}?)'
.format(-delta, stock_number))
else:
message.send('??????????????{}????????(??: {}?)'
.format(delta, stock_number))
def index():
conn,curr = sphinx_conn()
totalsql = 'select count(*) from film'
curr.execute(totalsql)
totalcounts = curr.fetchall()
total = int(totalcounts[0]['count(*)'])
sphinx_close(curr,conn)
keywords=Search_Keywords.query.order_by(Search_Keywords.order).limit(6)
form=SearchForm()
today = db.session.query(func.sum(Search_Statusreport.new_hashes)).filter(cast(Search_Statusreport.date, Date) == datetime.date.today()).scalar()
return render_template('index.html',form=form,keywords=keywords,total=total,today=today,sitename=sitename)
def find(self, words):
"""Iterator of all pages containing the words, and their scores."""
ranks = []
for word in words:
# Calculate popularity of each word.
rank = self.db.query(func.sum(Word.count)).filter(
Word.word.like("%%%s%%" % word)).first()[0]
# If any rank is 0, there will be no results anyways
if not rank:
return
ranks.append((rank, word))
ranks.sort()
# Start with the least popular word. Get all pages that contain it.
first_rank, first = ranks[0]
rest = ranks[1:]
first_counts = self.db.query(Word.page, Title.title,
func.sum(Word.count)).\
filter(Word.word.like("%%%s%%" % first)).\
filter(Title.id==Word.page).\
group_by(Word.page)
# Check for the rest of words
for title_id, title, first_count in first_counts:
# Score for the first word
score = float(first_count)/first_rank
for rank, word in rest:
count = self.db.query(func.sum(Word.count)).\
filter(Word.page==title_id).\
filter(Word.word.like("%%%%s%%" % word)).\
first()
if not count:
# If page misses any of the words, its score is 0
score = 0
break
score += float(count)/rank
if score > 0:
yield int(100*score), unicode(title)
