Python sqlalchemy.func.coalesce() Examples

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() 

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 

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",
            },
        ) 

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) 

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 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
    ) 

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
    ) 

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() 

def test_twelve(self):
        t = self.tables.t
        actual_ts = self.bind.scalar(func.current_timestamp()).replace(
            tzinfo=None
        ) - datetime.datetime(2012, 5, 10, 12, 15, 25)

        self._test(
            func.current_timestamp()
            - func.coalesce(t.c.dtme, func.current_timestamp()),
            {"day": actual_ts.days},
        ) 

def top(self, sess, comment):
        """
        Returns the top users in the meme market
        """
        leaders = sess.query(
            Investor.name,
            func.coalesce(Investor.balance+func.sum(Investment.amount), Investor.balance).label('networth')).\
            outerjoin(Investment, and_(Investor.name == Investment.name, Investment.done == 0)).\
        group_by(Investor.name).\
        order_by(desc('networth')).\
        limit(5).\
        all()

        return comment.reply_wrap(message.modify_top(leaders)) 

def test_insert_inline_kw_defaults(self):
        m = MetaData()
        foo = Table("foo", m, Column("id", Integer))

        t = Table(
            "test",
            m,
            Column("col1", Integer, default=func.foo(1)),
            Column(
                "col2",
                Integer,
                default=select([func.coalesce(func.max(foo.c.id))]),
            ),
        )

        with testing.expect_deprecated_20(
            "The insert.inline parameter will be removed in SQLAlchemy 2.0."
        ):
            stmt = t.insert(inline=True, values={})

        self.assert_compile(
            stmt,
            "INSERT INTO test (col1, col2) VALUES (foo(:foo_1), "
            "(SELECT coalesce(max(foo.id)) AS coalesce_1 FROM "
            "foo))",
        ) 

def test_update_inline_kw_defaults(self):
        m = MetaData()
        foo = Table("foo", m, Column("id", Integer))

        t = Table(
            "test",
            m,
            Column("col1", Integer, onupdate=func.foo(1)),
            Column(
                "col2",
                Integer,
                onupdate=select([func.coalesce(func.max(foo.c.id))]),
            ),
            Column("col3", String(30)),
        )

        with testing.expect_deprecated_20(
            "The update.inline parameter will be removed in SQLAlchemy 2.0."
        ):
            stmt = t.update(inline=True, values={"col3": "foo"})

        self.assert_compile(
            stmt,
            "UPDATE test SET col1=foo(:foo_1), col2=(SELECT "
            "coalesce(max(foo.id)) AS coalesce_1 FROM foo), "
            "col3=:col3",
        ) 

def test_inline_defaults(self):
        m = MetaData()
        foo = Table("foo", m, Column("id", Integer))

        t = Table(
            "test",
            m,
            Column("col1", Integer, default=func.foo(1)),
            Column(
                "col2",
                Integer,
                default=select([func.coalesce(func.max(foo.c.id))]),
            ),
        )

        self.assert_compile(
            t.insert().values({}),
            "INSERT INTO test (col1, col2) VALUES (foo(:foo_1), "
            "(SELECT coalesce(max(foo.id)) AS coalesce_1 FROM "
            "foo))",
        )

        self.assert_compile(
            t.insert().inline().values({}),
            "INSERT INTO test (col1, col2) VALUES (foo(:foo_1), "
            "(SELECT coalesce(max(foo.id)) AS coalesce_1 FROM "
            "foo))",
        ) 

def test_generic_annotation(self):
        fn = func.coalesce("x", "y")._annotate({"foo": "bar"})
        self.assert_compile(fn, "coalesce(:coalesce_1, :coalesce_2)") 

def test_col_prop_builtin_function(self):
        class Foo(object):
            pass

        mapper(
            Foo,
            self.tables.users,
            properties={
                "foob": column_property(
                    func.coalesce(self.tables.users.c.name)
                )
            },
        )

        stmt1 = select(Foo).where(Foo.foob == "somename").order_by(Foo.foob)
        stmt2 = (
            Session()
            .query(Foo)
            .filter(Foo.foob == "somename")
            .order_by(Foo.foob)
            ._final_statement(legacy_query_style=False)
        )

        expected = (
            "SELECT coalesce(users.name) AS coalesce_1, "
            "users.id, users.name FROM users "
            "WHERE coalesce(users.name) = :param_1 "
            "ORDER BY coalesce_1"
        )
        self.assert_compile(stmt1, expected)
        self.assert_compile(stmt2, expected) 

def average_assignment_score(self, assignment_id):
        """Compute the average score for an assignment.

        Parameters
        ----------
        assignment_id : string
            the name of the assignment

        Returns
        -------
        score : float
            The average score

        """

        assignment = self.find_assignment(assignment_id)
        if assignment.num_submissions == 0:
            return 0.0

        score_sum_gradecell = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(GradeCell, Notebook, Assignment)\
            .filter(Assignment.name == assignment_id).scalar()
        score_sum_taskcell = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(TaskCell, Notebook, Assignment)\
            .filter(Assignment.name == assignment_id).scalar()
        score_sum = score_sum_gradecell + score_sum_taskcell
        return score_sum / assignment.num_submissions 

def average_assignment_code_score(self, assignment_id):
        """Compute the average code score for an assignment.

        Parameters
        ----------
        assignment_id : string
            the name of the assignment

        Returns
        -------
        score : float
            The average code score

        """

        assignment = self.find_assignment(assignment_id)
        if assignment.num_submissions == 0:
            return 0.0

        score_sum = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(GradeCell, Notebook, Assignment)\
            .filter(and_(
                Assignment.name == assignment_id,
                Notebook.assignment_id == Assignment.id,
                GradeCell.notebook_id == Notebook.id,
                Grade.cell_id == GradeCell.id,
                GradeCell.cell_type == "code")).scalar()
        return score_sum / assignment.num_submissions 

def average_assignment_written_score(self, assignment_id):
        """Compute the average written score for an assignment.

        Parameters
        ----------
        assignment_id : string
            the name of the assignment

        Returns
        -------
        score : float
            The average written score

        """

        assignment = self.find_assignment(assignment_id)
        if assignment.num_submissions == 0:
            return 0.0

        score_sum = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(GradeCell, Notebook, Assignment)\
            .filter(and_(
                Assignment.name == assignment_id,
                Notebook.assignment_id == Assignment.id,
                GradeCell.notebook_id == Notebook.id,
                Grade.cell_id == GradeCell.id,
                GradeCell.cell_type == "markdown")).scalar()
        return score_sum / assignment.num_submissions 

def average_assignment_task_score(self, assignment_id):
        """Compute the average task score for an assignment.

        Parameters
        ----------
        assignment_id : string
            the name of the assignment

        Returns
        -------
        score : float
            The average task score

        """

        assignment = self.find_assignment(assignment_id)
        if assignment.num_submissions == 0:
            return 0.0

        score_sum = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(TaskCell, Notebook, Assignment)\
            .filter(and_(
                Assignment.name == assignment_id,
                Notebook.assignment_id == Assignment.id,
                TaskCell.notebook_id == Notebook.id,
                Grade.cell_id == TaskCell.id,
                TaskCell.cell_type == "markdown")).scalar()
        return score_sum / assignment.num_submissions 

def average_notebook_score(self, notebook_id: str, assignment_id: str) -> float:
        """Compute the average score for a particular notebook in an assignment.

        Parameters
        ----------
        notebook_id:
            the name of the notebook
        assignment_id:
            the name of the assignment

        Returns
        -------
        score:
            The average notebook score

        """

        notebook = self.find_notebook(notebook_id, assignment_id)
        if notebook.num_submissions == 0:
            return 0.0

        score_sum = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(SubmittedNotebook, Notebook, Assignment)\
            .filter(and_(
                Notebook.name == notebook_id,
                Assignment.name == assignment_id)).scalar()
        return score_sum / notebook.num_submissions 

def average_notebook_written_score(self, notebook_id: str, assignment_id: str) -> float:
        """Compute the average written score for a particular notebook in an
        assignment.

        Parameters
        ----------
        notebook_id:
            the name of the notebook
        assignment_id:
            the name of the assignment

        Returns
        -------
        score:
            The average notebook written score

        """

        notebook = self.find_notebook(notebook_id, assignment_id)
        if notebook.num_submissions == 0:
            return 0.0

        score_sum = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(GradeCell, Notebook, Assignment)\
            .filter(and_(
                Notebook.name == notebook_id,
                Assignment.name == assignment_id,
                Notebook.assignment_id == Assignment.id,
                GradeCell.notebook_id == Notebook.id,
                Grade.cell_id == GradeCell.id,
                GradeCell.cell_type == "markdown")).scalar()
        return score_sum / notebook.num_submissions 

def average_notebook_task_score(self, notebook_id: str, assignment_id: str) -> float:
        """Compute the average task score for a particular notebook in an
        assignment.

        Parameters
        ----------
        notebook_id:
            the name of the notebook
        assignment_id:
            the name of the assignment

        Returns
        -------
        score:
            The average notebook task score

        """

        notebook = self.find_notebook(notebook_id, assignment_id)
        if notebook.num_submissions == 0:
            return 0.0

        score_sum = self.db.query(func.coalesce(func.sum(Grade.score), 0.0))\
            .join(TaskCell, Notebook, Assignment)\
            .filter(and_(
                Notebook.name == notebook_id,
                Assignment.name == assignment_id,
                Notebook.assignment_id == Assignment.id,
                TaskCell.notebook_id == Notebook.id,
                Grade.cell_id == TaskCell.id,
                TaskCell.cell_type == "markdown")).scalar()
        return score_sum / notebook.num_submissions 

def update_canonicals(canonicals):
    '''
    Update canonical data for android devices.
    '''
    global ENGINE
    binding = [{"p_{}".format(k): v for k, v in canonical.items()} for canonical in canonicals]
    device_table = model.metadata.tables['device']
    stmt = update(device_table).\
        values(device_token_new=bindparam('p_new_token')).\
        where(and_(device_table.c.login_id == bindparam('p_login_id'),
                   func.coalesce(device_table.c.device_token_new, device_table.c.device_token) == bindparam('p_old_token')))
    ENGINE.execute(stmt, binding)

    with session_scope() as session:
        query = text('SELECT keep_max_users_per_device( \
                     (:platform_id)::int2, :device_token, (:max_users_per_device)::int2)')
        for canonical in canonicals:
            session.execute(query,
                            {'platform_id': constants.PLATFORM_ANDROID,
                             'device_token': canonical['new_token'],
                             'max_users_per_device': config.max_users_per_device
                            })
            session.execute(query,
                            {'platform_id': constants.PLATFORM_ANDROID_TABLET,
                             'device_token': canonical['new_token'],
                             'max_users_per_device': config.max_users_per_device
                            })
        session.commit() 

def update_unregistered_devices(unregistered):
    '''
    Update data for unregistered Android devices.

    Unregistered device will not receive notifications and will be deleted when number of devices exceeds maximum.
    '''
    global ENGINE
    binding = [{"p_{}".format(k): v for k, v in u.items()} for u in unregistered]
    device_table = model.metadata.tables['device']
    stmt = update(device_table).\
        values(unregistered_ts=func.now()).\
        where(and_(device_table.c.login_id == bindparam('p_login_id'),
                   func.coalesce(device_table.c.device_token_new, device_table.c.device_token) == bindparam('p_device_token')))
    ENGINE.execute(stmt, binding) 

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
            ) 

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, []) 

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() 

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 

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) 

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)
    )