我们从Python开源项目中，提取了以下50个代码示例，用于说明如何使用sqlalchemy.func.sum()。
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
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)
def getScore(username=None): counts = db.session.query(User.name, func.sum(DailyCompletion.score).label("score")).join( User.points).group_by(User.name).order_by(func.sum(DailyCompletion.score).desc()) if username: counts = counts.filter(User.slackHandle == username) if not counts.count(): return None elif username: return counts.first() else: return counts.all()
def _performance(bot: Bot, update: Update) -> None: """ Handler for /performance. Shows a performance statistic from finished trades :param bot: telegram bot :param update: message update :return: None """ if get_state() != State.RUNNING: send_msg('`trader is not running`', bot=bot) return pair_rates = Trade.session.query(Trade.pair, func.sum(Trade.close_profit).label('profit_sum')) \ .filter(Trade.is_open.is_(False)) \ .group_by(Trade.pair) \ .order_by(text('profit_sum DESC')) \ .all() stats = '\n'.join('{index}.\t<code>{pair}\t{profit:.2f}%</code>'.format( index=i + 1, pair=pair, profit=round(rate * 100, 2) ) for i, (pair, rate) in enumerate(pair_rates)) message = '<b>Performance:</b>\n{}'.format(stats) logger.debug(message) send_msg(message, parse_mode=ParseMode.HTML)
def get_sum_of_rates_in_period(self, start_date, end_date, currency): """ SELECT provider_id, count(*), SUM(rate) FROM "USD_exchange_rates" WHERE date >= '%Y-%m-%d' AND date <= '%Y-%m-%d' GROUP BY provider_id """ return self.db_session\ .query(ExchangeRate.provider_id, func.count(), func.sum(ExchangeRate.rate))\ .filter( and_(ExchangeRate.date >= start_date, ExchangeRate.date <= end_date, ExchangeRate.currency == currency, ExchangeRate.rate.isnot(None)) )\ .group_by(ExchangeRate.provider_id)\ .order_by(ExchangeRate.provider_id)\ .all()
def total(self, cgccpf): res = self.sql_connector.session.query( func.sum(CaptacaoModel.CaptacaoReal).label('total_doado') ).join(InteressadoModel, CaptacaoModel.CgcCpfMecena==InteressadoModel.CgcCpf) res = res.filter(InteressadoModel.CgcCpf.like('%' + cgccpf + '%') )
def single_stat_request(player, code, stat): """Actually retrieves the stat and returns the stat info in an embed""" session = Session() message = "" if code == 1: (table, col, message) = stat_dict[stat] columns = [col] res = session.query(*(getattr(table, column) for column in columns), Account.display_name).join(Account).filter(Account.display_name == player).first() #Returns a tuple containing the stat, but only the first element is defined for some reason. num = truncate_decimals(res[0]) name = res[1] elif code == 2 or code == 3: (table, col, message) = medal_dict[stat] columns = [col] res = session.query(func.sum(*(getattr(table, column) for column in columns))).join(Account).filter(Account.display_name == player).group_by(AccountMedals.id).first() num = float(res[0]) name = player if message != "Activities Entered" and message != "Total Number of Medals" and message != "Total Medal Score": message = f"Total {message} Medals" if code == 3: denominator = session.query(PvPAggregate.activitiesEntered).join(Account).filter(Account.display_name == player).first() act = denominator[0] num = num/act if message != "Activities Entered" and message != "Total Number of Medals" and message != "Total Medal Score": message = f"{message} Medals per Game" elif code == 4: (table, col, message) = character_dict[stat] columns = [col] res = session.query(func.max(*(getattr(table, column) for column in columns)), Account.display_name).join(Account).filter(Account.display_name == player).first() #Returns a tuple containing the stat, but only the first element is defined for some reason. num = truncate_decimals(res[0]) name = res[1] #em = discord.Embed(title = f"{author}{message}{result}", colour=0xADD8E6) em = f"```{message} for {name}: {num}```" return em
def multi_stat_request(players, code, stat): session = Session() data = [] if code == 1: (table, col, message) = stat_dict[stat] columns = [col] res = session.query(*(getattr(table, column) for column in columns), Account.display_name).join(Account).filter(Account.display_name.in_(players)).order_by(Account.display_name).all() data = [(item[1], truncate_decimals(item[0])) for item in res if item[0] is not None] elif code == 2 or code == 3: (table, col, message) = medal_dict[stat] columns = [col] res = session.query(func.sum(*(getattr(table, column) for column in columns)), Account.display_name).join(Account).filter(Account.display_name.in_(players)).group_by(AccountMedals.id).order_by(Account.display_name).all() data = [(item[1], truncate_decimals(item[0])) for item in res if item[0] is not None] if code == 3: num_activities = session.query(PvPAggregate.activitiesEntered).join(Account).filter(Account.display_name.in_(players)).order_by(Account.display_name).all() data = [(res[i][1], truncate_decimals(float(res[i][0])/num_activities[i][0])) for i in range(len(res)) if res[i][0] is not None] elif code == 4: (table, col, message) = pveStatDict[stat] columns = [col] res = session.query(*(getattr(table, column) for column in columns), Account.display_name).join(Account).filter(Account.display_name.in_(players)).order_by(Account.display_name).all() data = [(item[1], truncate_decimals(item[0])) for item in res if item[0] is not None] data = sorted(data, key=lambda x: x[1], reverse=True) if (code == 2 or code == 3) and message != "Activities Entered" and message != "Total Number of Medals" and message != "Total Medal Score": message = f"Total {message} Medals" em = discord.Embed(title = f"{message}", colour=0xADD8E6) if len(data) > 10: data = data[:9] for (name, num) in data: em.add_field(name=name, value=num) return em
def _obs_get_total_size(): from sqlalchemy import func from .file import File from .observation import Observation return db.session.query(func.sum(File.size)).filter(File.obsid == Observation.obsid).as_scalar()
def total_size(self): "The total size (in bytes) of all Files associated with this session." from sqlalchemy import func from .file import File my_obsids = db.session.query(Observation.obsid).filter(Observation.session_id == self.id) return (db.session.query(func.sum(File.size)) .filter(File.obsid.in_(my_obsids)) .scalar())
def total_size(self): "The total size (in bytes) of all Files associated with this observation." from sqlalchemy import func from .file import File return (db.session.query(func.sum(File.size)) .filter(File.obsid == self.obsid) .scalar())
def sum_equally_voted_reputation(self, contribution, value): """return the sum of reputation of evaluators of evaluation.contribution that have evaluated the same value""" equally_voted_rep = DBSession.query(func.sum(User.reputation)).\ join(Evaluation).\ filter(Evaluation.contribution_id == contribution.id).\ filter(Evaluation.value == value).\ one()[0] or 0 return equally_voted_rep
def total_reputation(self): return DBSession.query(func.sum(User.reputation)).filter(User.contract == self).one()[0]
def get_sum_count_being_deleted(rse_id, session=None): """ :param rse_id: The id of the RSE. :param session: The database session in use. :returns: A dictionary with total and bytes. """ none_value = None total, bytes = session.query(func.count(models.RSEFileAssociation.tombstone), func.sum(models.RSEFileAssociation.bytes)).filter_by(rse_id=rse_id).\ filter(models.RSEFileAssociation.tombstone != none_value).\ filter(models.RSEFileAssociation.state == ReplicaState.BEING_DELETED).\ one() return {'bytes': bytes or 0, 'total': total or 0}
def filterDatasetQueryObjectWithRules(queryObject,rules): for rule in rules: field = rule['field'] ruletype = rule['type'] value = rule['value'] queryObject = filterQueryByRule(Dataset,queryObject,field,ruletype,value) return queryObject # Create and run the query for # Group by a column and return the sum for each group
def aggregate(cls, value): return { 'average_age': func.avg(cls.age), 'average_wage': func.avg(cls.wage), 'wage': func.sum(cls.wage), 'jobs': func.count(cls.employee), 'average_establishment_size': func.count(cls.employee) / func.count(distinct(cls.establishment)) }[value]
def aggregate(cls, value): return { 'enrolleds': func.count(), 'entrants': func.sum(cls.entrant), 'graduates': func.sum(cls.graduate), 'average_age': func.avg(cls.age) }[value]
def aggregate(cls, value): return { 'weight': func.sum(cls.weight), 'value': func.sum(cls.value), }[value]
def aggregate(cls, value): return { 'professionals': func.count(), 'other_hours_worked': func.sum(cls.other_hours_worked), 'hospital_hour': func.sum(cls.hospital_hour), 'ambulatory_hour': func.sum(cls.ambulatory_hour), }[value]
def aggregate(cls, value): return { 'beds': func.sum(cls.number_existing_bed), 'number_existing_bed': func.sum(cls.number_existing_bed), 'number_existing_contract': func.sum(cls.number_existing_contract), 'number_sus_bed': func.sum(cls.number_sus_bed), 'number_non_sus_bed': func.sum(cls.number_non_sus_bed), }[value]
def aggregate(cls, value): return { 'value': func.sum(cls.value), 'kg': func.sum(cls.kg), }[value]
def query(table): col1 = literal_column("TIMESTAMP_TRUNC(timestamp, DAY)").label("timestamp_label") col2 = func.sum(table.c.integer) query = ( select([ col1, col2, ]) .where(col1 < '2017-01-01 00:00:00') .group_by(col1) .order_by(col2) ) return query
