Python func.coalesce方法代码示例

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def query_for_fact_status_data(table, start_date, end_date, notification_type, service_id):
    query = db.session.query(
        table.template_id,
        table.service_id,
        func.coalesce(table.job_id, '00000000-0000-0000-0000-000000000000').label('job_id'),
        table.key_type,
        table.status,
        func.count().label('notification_count')
    ).filter(
        table.created_at >= start_date,
        table.created_at < end_date,
        table.notification_type == notification_type,
        table.service_id == service_id,
        table.key_type != KEY_TYPE_TEST
    ).group_by(
        table.template_id,
        table.service_id,
        'job_id',
        table.key_type,
        table.status
    )
    return query.all() 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
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) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def get_account_balance(account_id, date_from=None, date_to=None):
    """ get account balance between dates or on particular date
    """
    assert isinstance(account_id, int)
    sum_expr = func.coalesce(func.sum(Cashflow.sum), 0)
    from_cashflows_query = (
        query_account_from_cashflows(account_id)
        .with_entities(sum_expr)
    )
    to_cashflows_query = (
        query_account_to_cashflows(account_id)
        .with_entities(sum_expr)
    )
    return _get_balance(
        from_cashflows_query,
        to_cashflows_query,
        date_from,
        date_to
    ) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def get_subaccount_balance(subaccount_id, date_from=None, date_to=None):
    """ get subaccount balance between dates or on particular date
    """
    assert isinstance(subaccount_id, int)
    sum_expr = func.coalesce(func.sum(Cashflow.sum), 0)
    from_cashflows_query = (
        query_subaccount_from_cashflows(subaccount_id)
        .with_entities(sum_expr)
    )
    to_cashflows_query = (
        query_subaccount_to_cashflows(subaccount_id)
        .with_entities(sum_expr)
    )
    return _get_balance(
        from_cashflows_query,
        to_cashflows_query,
        date_from,
        date_to
    ) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def __init__(self):
        self._subq  = query_cashflows().subquery()
        self._fields = {
            'id': self._subq.c.id,
            '_id': self._subq.c.id,
            'date': self._subq.c.date,
            'account_from_id': self._subq.c.account_from_id,
            'subaccount_from_id': self._subq.c.subaccount_from_id,
            'account_to_id': self._subq.c.account_to_id,
            'subaccount_to_id': self._subq.c.subaccount_to_id,
            'from': func.coalesce(
                self._subq.c.account_from, self._subq.c.subaccount_from, ''
            ).label('from'),
            'to': func.coalesce(
                self._subq.c.account_to, self._subq.c.subaccount_to, ''
            ).label('to'),
            'currency': self._subq.c.currency,
            'account_item': self._subq.c.account_item,
            'sum': self._subq.c.sum.label('sum'),
        }
        self.build_query() 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def test_update_sql_expr(self):
        stmt = insert(self.table).values(
            [{"id": 1, "bar": "ab"}, {"id": 2, "bar": "b"}]
        )
        stmt = stmt.on_duplicate_key_update(
            bar=func.coalesce(stmt.inserted.bar),
            baz=stmt.inserted.baz + "some literal",
        )
        expected_sql = (
            "INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) ON "
            "DUPLICATE KEY UPDATE bar = coalesce(VALUES(bar)), "
            "baz = (concat(VALUES(baz), %s))"
        )
        self.assert_compile(
            stmt,
            expected_sql,
            checkparams={
                "id_m0": 1,
                "bar_m0": "ab",
                "id_m1": 2,
                "bar_m1": "b",
                "baz_1": "some literal",
            },
        ) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def get_device_tokens(login_id):
    '''
    Get device tokens for a given login. Removes duplicates per provider.
    '''
    with session_scope() as session:
        result = session.query(model.Device.platform_id,
                    func.coalesce(model.Device.device_token_new, model.Device.device_token).label('device_token')).\
                    filter(model.Device.login_id == login_id).filter(model.Device.unregistered_ts.is_(None)).all()


    # only return unique device tokens per provider (gcm, apn) to avoid sending duplicates
    devices = set()
    provider_tokens = set()
    for device in sorted(result): # sorting to make unit tests easier
        platform_id, device_token = device
        provider_id = (constants.PLATFORM_BY_PROVIDER.get(platform_id, 0)
                       or platform_id)
        # NOTE: Use unique tokens per *provider* only for known providers,
        #       and unique tokens per *platform* in other cases, since
        #       it is hard to verify providers for custom senders
        provider_token = (provider_id, device_token)
        if provider_token not in provider_tokens:
            devices.add(device)
            provider_tokens.add(provider_token)
    return list(devices) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def upsert_device(login_id, platform_id, device_token, application_version, unregistered_ts=None):
    '''
    Add or update a device entity. Returns new or updated device with relation to login preloaded.
    '''
    with session_scope() as session:
        login = session.query(model.Login).filter(model.Login.id == login_id).one()
        device = session.query(model.Device).\
            filter(model.Device.login == login).\
            filter(model.Device.platform_id == platform_id).\
            filter(func.coalesce(model.Device.device_token_new, model.Device.device_token) == device_token).\
            one_or_none()
        if device is not None:
            device.application_version = application_version
            device.unregistered_ts = unregistered_ts
        else:
            device = model.Device(login=login, platform_id=platform_id, device_token=device_token,
                                  application_version=application_version, unregistered_ts=unregistered_ts)
            session.add(device)
        session.commit()
        session.refresh(device)
        session.refresh(device.login)
    return device 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def release_waiting_requests_per_free_volume(rse_id, volume=None, session=None):
    """
    Release waiting requests if they fit in available transfer volume. If the DID of a request is attached to a dataset, the volume will be checked for the whole dataset as all requests related to this dataset will be released.

    :param rse_id:  The destination RSE id.
    :param volume:  The maximum volume in bytes that should be transfered.
    :param session: The database session.
    """

    dialect = session.bind.dialect.name
    sum_volume_active_subquery = None
    if dialect == 'mysql' or dialect == 'sqlite':
        sum_volume_active_subquery = session.query(func.ifnull(func.sum(models.Request.bytes), 0).label('sum_bytes'))\
                                            .filter(and_(or_(models.Request.state == RequestState.SUBMITTED, models.Request.state == RequestState.QUEUED),
                                                         models.Request.dest_rse_id == rse_id))
    elif dialect == 'postgresql':
        sum_volume_active_subquery = session.query(func.coalesce(func.sum(models.Request.bytes), 0).label('sum_bytes'))\
                                            .filter(and_(or_(models.Request.state == RequestState.SUBMITTED, models.Request.state == RequestState.QUEUED),
                                                         models.Request.dest_rse_id == rse_id))
    elif dialect == 'oracle':
        sum_volume_active_subquery = session.query(func.nvl(func.sum(models.Request.bytes), 0).label('sum_bytes'))\
                                            .filter(and_(or_(models.Request.state == RequestState.SUBMITTED, models.Request.state == RequestState.QUEUED),
                                                         models.Request.dest_rse_id == rse_id))
    sum_volume_active_subquery = sum_volume_active_subquery.subquery()

    grouped_requests_subquery, filtered_requests_subquery = create_base_query_grouped_fifo(rse_id, filter_by_rse='destination', session=session)

    cumulated_volume_subquery = session.query(grouped_requests_subquery.c.name,
                                              grouped_requests_subquery.c.scope,
                                              func.sum(grouped_requests_subquery.c.volume).over(order_by=grouped_requests_subquery.c.oldest_requested_at).label('cum_volume'))\
                                       .filter(grouped_requests_subquery.c.volume <= volume - sum_volume_active_subquery.c.sum_bytes)\
                                       .subquery()

    cumulated_volume_subquery = session.query(filtered_requests_subquery.c.id)\
                                       .join(cumulated_volume_subquery, and_(filtered_requests_subquery.c.dataset_name == cumulated_volume_subquery.c.name, filtered_requests_subquery.c.dataset_scope == cumulated_volume_subquery.c.scope))\
                                       .filter(cumulated_volume_subquery.c.cum_volume <= volume - sum_volume_active_subquery.c.sum_bytes)\
                                       .subquery()

    statement = update(models.Request).where(models.Request.id.in_(cumulated_volume_subquery)).values(state=RequestState.QUEUED)
    amount_released_requests = session.execute(statement).rowcount
    return amount_released_requests 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def test_functional_ix_two(self):
        m1 = MetaData()
        m2 = MetaData()

        t1 = Table(
            "foo",
            m1,
            Column("id", Integer, primary_key=True),
            Column("email", String(50)),
            Column("name", String(50)),
        )
        Index(
            "email_idx",
            func.coalesce(t1.c.email, t1.c.name).desc(),
            unique=True,
        )

        t2 = Table(
            "foo",
            m2,
            Column("id", Integer, primary_key=True),
            Column("email", String(50)),
            Column("name", String(50)),
        )
        Index(
            "email_idx",
            func.coalesce(t2.c.email, t2.c.name).desc(),
            unique=True,
        )

        with assertions.expect_warnings(
            "Skipped unsupported reflection",
            "autogenerate skipping functional index",
        ):
            diffs = self._fixture(m1, m2)
        eq_(diffs, []) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def get_job_count(self):
        today = datetime.datetime.utcnow().date()
        return Job.query.filter(
            Job.contact_list_id == self.id,
            func.coalesce(
                Job.processing_started, Job.created_at
            ) >= today - func.coalesce(ServiceDataRetention.days_of_retention, 7)
        ).outerjoin(
            ServiceDataRetention, and_(
                self.service_id == ServiceDataRetention.service_id,
                func.cast(self.template_type, String) == func.cast(ServiceDataRetention.notification_type, String)
            )
        ).count() 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def fetch_sms_free_allowance_remainder(start_date):
    # ASSUMPTION: AnnualBilling has been populated for year.
    billing_year = get_financial_year_for_datetime(start_date)
    start_of_year = date(billing_year, 4, 1)

    billable_units = func.coalesce(func.sum(FactBilling.billable_units * FactBilling.rate_multiplier), 0)

    query = db.session.query(
        AnnualBilling.service_id.label("service_id"),
        AnnualBilling.free_sms_fragment_limit,
        billable_units.label('billable_units'),
        func.greatest((AnnualBilling.free_sms_fragment_limit - billable_units).cast(Integer), 0).label('sms_remainder')
    ).outerjoin(
        # if there are no ft_billing rows for a service we still want to return the annual billing so we can use the
        # free_sms_fragment_limit)
        FactBilling, and_(
            AnnualBilling.service_id == FactBilling.service_id,
            FactBilling.bst_date >= start_of_year,
            FactBilling.bst_date < start_date,
            FactBilling.notification_type == SMS_TYPE,
        )
    ).filter(
        AnnualBilling.financial_year_start == billing_year,
    ).group_by(
        AnnualBilling.service_id,
        AnnualBilling.free_sms_fragment_limit,
    )
    return query 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def test_orm_expression(dburl):
    with S(dburl, echo=ECHO) as s:
        key = func.coalesce(Book.a,0) + Book.b
        q = s.query(Book).order_by(key, Book.id)
        check_paging_orm(q=q)

        q = s.query(Book).order_by(key.label('sort_by_me'), Book.id)
        check_paging_orm(q=q) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def query_cashflows():
    """get common query for cashflows
    """
    from_account = aliased(Account)
    to_account = aliased(Account)
    from_subaccount = aliased(Subaccount)
    to_subaccount = aliased(Subaccount)
    currency_expr = func.coalesce(
        from_account.currency_id,
        to_account.currency_id,
    )
 
    return (
        DBSession.query(
            Cashflow.id,
            Cashflow.date,
            Cashflow.sum,
            Cashflow.subaccount_from_id,
            Cashflow.subaccount_to_id,
            Cashflow.account_item_id,
            Currency.id.label('currency_id'),
            Currency.iso_code.label('currency'),
            from_account.name.label('account_from'),
            to_account.name.label('account_to'),
            from_subaccount.name.label('subaccount_from'),
            to_subaccount.name.label('subaccount_to'),
            AccountItem.name.label('account_item'),
        )
        .distinct(Cashflow.id)
        .join(AccountItem, Cashflow.account_item)
        .outerjoin(from_subaccount, Cashflow.subaccount_from)
        .outerjoin(to_subaccount, Cashflow.subaccount_to)
        .outerjoin(from_account, from_subaccount.account_id == from_account.id)
        .outerjoin(to_account, to_subaccount.account_id == to_account.id)
        .join(Currency, Currency.id == currency_expr)
    ) 

# 需要导入模块: from sqlalchemy import func [as 别名]
# 或者: from sqlalchemy.func import coalesce [as 别名]
def _filter_payment(self, payment_from, payment_to):
        if payment_from:
            self.query = self.query.filter(
                func.coalesce(self._sum_payments.c.payments, 0) >= payment_from
            )
        if payment_to:
            self.query = self.query.filter(
                func.coalesce(self._sum_payments.c.payments, 0) <= payment_to
            )