Python sqlalchemy.func.sum() Examples

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 = Decimal('0.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 get_weights(coins, fiat_currency):
    market_cap = {}
    try:
        response = requests.get('https://api.coincap.io/v2/assets')
        assets = response.json()
        coin_data = {}
        for coin in assets['data']:
            coin_data[coin['symbol']] = coin
        for c in coins:
            market_cap[c] = float(coin_data[c]['marketCapUsd'])
    except HTTPError as e:
        print('caught exception when fetching ticker for {} with name={}'
              .format(c, coins[c]['name']))
        raise e

    total_market_cap = sum(market_cap.values())

    weights = {}
    for c in coins:
        weights[c] = market_cap[c] / total_market_cap
    print('coin weights:')
    for w in weights:
        print('  {0}: {1:.4f}'.format(w, weights[w]))
    print()
    return weights 

def running_slots(self, session: Session):
        """
        Get the number of slots used by running tasks at the moment.

        :param session: SQLAlchemy ORM Session
        :return: the used number of slots
        """
        from airflow.models.taskinstance import TaskInstance  # Avoid circular import

        return (
            session
            .query(func.sum(TaskInstance.pool_slots))
            .filter(TaskInstance.pool == self.pool)
            .filter(TaskInstance.state == State.RUNNING)
            .scalar()
        ) or 0 

def queued_slots(self, session: Session):
        """
        Get the number of slots used by queued tasks at the moment.

        :param session: SQLAlchemy ORM Session
        :return: the used number of slots
        """
        from airflow.models.taskinstance import TaskInstance  # Avoid circular import

        return (
            session
            .query(func.sum(TaskInstance.pool_slots))
            .filter(TaskInstance.pool == self.pool)
            .filter(TaskInstance.state == State.QUEUED)
            .scalar()
        ) or 0 

def test_subquery_aliased(self):
        expr = self._case_subquery_aliased()

        s1 = self._get_sqla('star1').alias('t2')
        s2 = self._get_sqla('star2').alias('t1')

        agged = (
            sa.select([s1.c.foo_id, F.sum(s1.c.f).label('total')])
            .group_by(s1.c.foo_id)
            .alias('t0')
        )

        joined = agged.join(s2, agged.c.foo_id == s2.c.foo_id)
        expected = sa.select([agged, s2.c.value1]).select_from(joined)

        self._compare_sqla(expr, expected) 

def test_cte_factor_distinct_but_equal(self):
        expr = self._case_cte_factor_distinct_but_equal()

        alltypes = self._get_sqla('alltypes')

        t2 = alltypes.alias('t2')
        t0 = (
            sa.select([t2.c.g, F.sum(t2.c.f).label('metric')])
            .group_by(t2.c.g)
            .cte('t0')
        )

        t1 = t0.alias('t1')
        table_set = t0.join(t1, t0.c.g == t1.c.g)
        stmt = sa.select([t0]).select_from(table_set)

        self._compare_sqla(expr, stmt) 

def test_aggregate_having(self):
        st = self.sa_star1.alias('t0')

        cases = self._case_aggregate_having()

        metric = F.sum(st.c.f)
        k1 = st.c.foo_id
        expected = [
            sa.select([k1, metric.label('total')])
            .group_by(k1)
            .having(metric > L(10)),
            sa.select([k1, metric.label('total')])
            .group_by(k1)
            .having(F.count('*') > L(100)),
        ]

        for case, ex_sqla in zip(cases, expected):
            self._compare_sqla(case, ex_sqla) 

def setStartScore(self):
		downloads = db.session.query(func.sum(PackageRelease.downloads)). \
				filter(PackageRelease.package_id == self.id).scalar() or 0

		forum_score = 0
		forum_bonus = 0
		topic = self.forums and ForumTopic.query.get(self.forums)
		if topic:
			months = (datetime.datetime.now() - topic.created_at).days / 30
			years  = months / 12
			forum_score = topic.views / max(years, 0.0416) + 80*min(max(months, 0.5), 6)
			forum_bonus = topic.views + topic.posts

		self.score = max(downloads, forum_score * 0.6) + forum_bonus

		if self.getMainScreenshotURL() is None:
			self.score *= 0.8 

def filter(self, qs, value):
        if value in EMPTY_VALUES:
            return qs

        y_column = getattr(self.model, value['y_column'])

        if value['y_func'] == 'count':
            y_func = func.count(y_column)
        elif value['y_func'] == 'sum':
            y_func = func.sum(y_column)
        elif value['y_func'] == 'min':
            y_func = func.min(y_column)
        elif value['y_func'] == 'max':
            y_func = func.max(y_column)
        elif value['y_func'] == 'avg':
            y_func = func.avg(y_column)
        else:
            return qs.filter(sql.false())

        qs = qs.session.query(y_func).one()

        return qs 

def get_points_by_outcome(self, db_session):
        """ Returns how many points are bet on win and how many points
        are bet on lose """

        rows = (
            db_session.query(HSBetBet.outcome, func.sum(HSBetBet.points))
            .filter_by(game_id=self.id)
            .group_by(HSBetBet.outcome)
            .all()
        )

        points = {key: 0 for key in HSGameOutcome}
        for outcome, num_points in rows:
            points[outcome] = num_points

        return points 

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 = pp.overall_sums['allocated']
        spent = pp.overall_sums['spent']
        logger.debug('PayPeriod=%s; allocated=%s; spent=%s',
                     pp, allocated, spent)
        return allocated - spent 

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 res 

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 = Decimal('0.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 += acct.balance.ledger
        return sum 

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 ts_locs_array(
    config: ColumnElement, text: ColumnElement, tsquery: ColumnElement,
) -> ColumnElement:
    options = f"HighlightAll = TRUE, StartSel = {TS_START}, StopSel = {TS_STOP}"
    delimited = func.ts_headline(config, text, tsquery, options)
    parts = func.unnest(func.string_to_array(delimited, TS_START)).alias()
    part = column(parts.name)
    part_len = func.length(part) - len(TS_STOP)
    match_pos = func.sum(part_len).over(rows=(None, -1)) + len(TS_STOP)
    match_len = func.strpos(part, TS_STOP) - 1
    return func.array(
        select([postgresql.array([match_pos, match_len])])
        .select_from(parts)
        .offset(1)
        .as_scalar(),
    )

# When you add a new operator to this, also update zerver/lib/narrow.py 

def fetch_notification_status_for_service_by_month(start_date, end_date, service_id):
    return db.session.query(
        func.date_trunc('month', FactNotificationStatus.bst_date).label('month'),
        FactNotificationStatus.notification_type,
        FactNotificationStatus.notification_status,
        func.sum(FactNotificationStatus.notification_count).label('count')
    ).filter(
        FactNotificationStatus.service_id == service_id,
        FactNotificationStatus.bst_date >= start_date,
        FactNotificationStatus.bst_date < end_date,
        FactNotificationStatus.key_type != KEY_TYPE_TEST
    ).group_by(
        func.date_trunc('month', FactNotificationStatus.bst_date).label('month'),
        FactNotificationStatus.notification_type,
        FactNotificationStatus.notification_status
    ).all() 

def get_strict_matching_sticker_sets(session, context):
    """Get all sticker sets by accumulated score for strict search."""
    strict_subquery = get_strict_matching_query(
        session, context, sticker_set=True
    ).subquery("strict_sticker_subq")

    score = func.sum(strict_subquery.c.score_with_usage).label("score")
    matching_sets = (
        session.query(StickerSet, score)
        .join(strict_subquery, StickerSet.name == strict_subquery.c.name)
        .group_by(StickerSet)
        .order_by(score.desc())
        .limit(8)
        .offset(context.offset)
        .all()
    )

    return matching_sets 

def test_core2(dburl):
    with S(dburl, echo=ECHO) as s:
        sel = select([Book.score]).order_by(Book.id)
        check_paging_core(sel, s)

        sel = select([Book.score]) \
            .order_by(Author.id - Book.id, Book.id) \
            .where(Author.id == Book.author_id)
        check_paging_core(sel, s)

        sel = select([Book.author_id, func.count()]) \
            .group_by(Book.author_id) \
            .order_by(func.sum(Book.popularity))
        check_paging_core(sel, s)

        v = func.sum(func.coalesce(Book.a, 0)) + func.min(Book.b)
        sel = select([Book.author_id, func.count(), v]) \
            .group_by(Book.author_id) \
            .order_by(v)
        check_paging_core(sel, s) 

def fetch_email_usage_for_organisation(organisation_id, start_date, end_date):
    query = db.session.query(
        Service.name.label("service_name"),
        Service.id.label("service_id"),
        func.sum(FactBilling.notifications_sent).label("emails_sent")
    ).select_from(
        Service
    ).join(
        FactBilling, FactBilling.service_id == Service.id,
    ).filter(
        FactBilling.bst_date >= start_date,
        FactBilling.bst_date <= end_date,
        FactBilling.notification_type == EMAIL_TYPE,
        Service.organisation_id == organisation_id,
        Service.restricted.is_(False)
    ).group_by(
        Service.id,
        Service.name,
    ).order_by(
        Service.name
    )
    return query.all() 

def total_amount(self):
        return format_decimal(Decimal(sum(line.total_amount for line in self.lines))) 

def goods_amount(self):
        return (select([func.sum(PurchaseOrderLine.unit_price * PurchaseOrderLine.quantity)])
                .where(self.id == PurchaseOrderLine.purchase_order_id)
                .label('goods_amount')) 

def goods_amount(self):
        return format_decimal(Decimal(sum(line.total_amount for line in self.lines))) 

def total_amount(self):
        return (select([func.sum(ShippingLine.price * ShippingLine.quantity)])
                .where(self.id == ShippingLine.shipping_id).label('total_amount')) 

def total_amount(self):
        return format_decimal(Decimal(sum(line.total_amount for line in self.lines))) 

def test_should_columproperty_convert():
    field = get_field_from_column(column_property(
        select([func.sum(func.cast(id, types.Integer))]).where(id == 1)
    ))

    assert field.type == graphene.Int 

def total_amount(self):
        return format_decimal(Decimal(abs(sum(line.total_amount for line in self.lines)))) 

def get_liability(cls):
        liability = database.db.getSession().query(func.sum(Pool_utxo.amount)).scalar()
        if liability is None:
            liability = 0
        return liability 

def fetch_letter_costs_for_all_services(start_date, end_date):
    query = db.session.query(
        Organisation.name.label("organisation_name"),
        Organisation.id.label("organisation_id"),
        Service.name.label("service_name"),
        Service.id.label("service_id"),
        func.sum(FactBilling.notifications_sent * FactBilling.rate).label("letter_cost")
    ).select_from(
        Service
    ).outerjoin(
        Service.organisation
    ).join(
        FactBilling, FactBilling.service_id == Service.id,
    ).filter(
        FactBilling.service_id == Service.id,
        FactBilling.bst_date >= start_date,
        FactBilling.bst_date <= end_date,
        FactBilling.notification_type == LETTER_TYPE,
    ).group_by(
        Organisation.name,
        Organisation.id,
        Service.id,
        Service.name,
    ).order_by(
        Organisation.name,
        Service.name
    )

    return query.all() 

def num_warns():
    try:
        return SESSION.query(func.sum(Warns.num_warns)).scalar() or 0
    finally:
        SESSION.close() 

def average_retail_price(self):
        from psi.app.models import EnumValues
        return (select([func.sum(InventoryTransactionLine.quantity * InventoryTransactionLine.price) /
                        func.greatest(func.sum(InventoryTransactionLine.quantity), 1)])
                .where(self.id == InventoryTransactionLine.product_id
                       and InventoryTransactionLine.inventory_transaction_id == InventoryTransaction.id
                       and InventoryTransaction.type_id == EnumValues.id
                       and EnumValues.code == const.SALES_OUT_INV_TRANS_TYPE_KEY)
                .label('average_retail_price'))