Python sqlalchemy.sql.expression.null() Examples

def unreconciled(db):
        """
        Return a query to match all unreconciled OFXTransactions.

        :param db: active database session to use for queries
        :type db: sqlalchemy.orm.session.Session
        :return: query to match all unreconciled OFXTransactions
        :rtype: sqlalchemy.orm.query.Query
        """
        cutoff_date = datetime(
            RECONCILE_BEGIN_DATE.year, RECONCILE_BEGIN_DATE.month,
            RECONCILE_BEGIN_DATE.day, 0, 0, 0, tzinfo=UTC
        )
        return db.query(OFXTransaction).filter(
            OFXTransaction.reconcile.__eq__(null()),
            OFXTransaction.date_posted.__ge__(cutoff_date),
            OFXTransaction.account.has(reconcile_trans=True),
            OFXTransaction.is_payment.__ne__(True),
            OFXTransaction.is_late_fee.__ne__(True),
            OFXTransaction.is_interest_charge.__ne__(True),
            OFXTransaction.is_other_fee.__ne__(True),
            OFXTransaction.is_interest_payment.__ne__(True)
        ) 

def unreconciled(self):
        """
        Return a query to match all unreconciled Transactions for this account.

        :param db: active database session to use for queries
        :type db: sqlalchemy.orm.session.Session
        :return: query to match all unreconciled Transactions
        :rtype: sqlalchemy.orm.query.Query
        """
        sess = inspect(self).session
        return sess.query(Transaction).filter(
            Transaction.reconcile.__eq__(null()),
            Transaction.date.__ge__(RECONCILE_BEGIN_DATE),
            Transaction.account_id.__eq__(self.id),
            Transaction.date.__le__(dtnow())
        ) 

def test_unreconciled(self):
        Transaction()
        m_db = Mock()
        m_q = Mock(spec_set=Query)
        m_filt = Mock(spec_set=Query)
        m_db.query.return_value = m_q
        m_q.filter.return_value = m_filt
        res = Transaction.unreconciled(m_db)
        assert res == m_filt
        assert len(m_db.mock_calls) == 2
        assert m_db.mock_calls[0] == call.query(Transaction)
        kall = m_db.mock_calls[1]
        assert kall[0] == 'query().filter'
        expected1 = Transaction.reconcile.__eq__(null())
        expected2 = Transaction.date.__ge__(date(2017, 3, 17))
        expected3 = Transaction.account.has(reconcile_trans=True)
        assert len(kall[1]) == 3
        assert str(expected1) == str(kall[1][0])
        assert binexp_to_dict(expected2) == binexp_to_dict(kall[1][1])
        assert str(expected3) == str(kall[1][2]) 

def create_query(cls, sql: str, params: Dict[str, Any]) -> str:
        text_sql: TextClause = text(sql)
        kwargs = {'dialect': cls.DIALECT, 'compile_kwargs': {"literal_binds": True}}
        try:
            return str(
                text_sql.bindparams(
                    **{k: null() if v is None else v for k, v in params.items()}
                ).compile(**kwargs)
            )
        except CompileError as e:
            invalid_param_match = re.match(INVALID_PARAMETER_MESSAGE, e.args[0])
            if invalid_param_match:  # pragma: no cover
                raise BadRequestException(
                    message=f'Cannot find parameter: {invalid_param_match.group(1)}'
                )
            raise  # pragma: no cover
        except ArgumentError as e:
            undefined_param_match = re.match(UNDEFINED_PARAMETER_MESSAGE, e.args[0])
            if undefined_param_match:  # pragma: no cover
                undefined_param: str = undefined_param_match.group(1)
                return cls.create_query(
                    sql, {k: v for k, v in params.items() if k != undefined_param}
                )
            raise  # pragma: no cover 

def getAllExperiments(project, dataset):
    query = session.query(ExpAlchemy)
    query = query.join(ExpAlchemy.features_set)
    query = query.join(FeaturesSetsAlchemy.dataset)
    query = query.outerjoin(ExpAlchemy.parents)
    query = query.filter(DatasetsAlchemy.project == project)
    query = query.filter(DatasetsAlchemy.dataset == dataset)
    query = query.filter(ExpRelationshipsAlchemy.parent_id == null())
    experiments = {}
    for exp in query.all():
        if exp.kind not in experiments:
            experiments[exp.kind] = []
        experiments[exp.kind].append({'name': exp.name, 'id': exp.id})
    for k, v in experiments.items():
        t = [(x['id'], x['name']) for x in v]
        t.sort(key=operator.itemgetter(0), reverse=True)
        experiments[k] = t
    return jsonify(experiments) 

def _check_already_trained_conf(self):
        already_trained_id = self.exp_conf.already_trained
        model_exp = _get_exp_row(self.session, already_trained_id)
        # Check whether the experiment exists
        if model_exp is None:
            raise ExperimentNotFound(already_trained_id)
        # Check the type of the experiment
        if model_exp.kind == 'Diadem':
            query = self.session.query(DiademExpAlchemy)
            query = query.join(DiademExpAlchemy.exp)
            query = query.join(ExpAlchemy.parents)
            query = query.filter(
                       ExpRelationshipsAlchemy.parent_id == already_trained_id)
            query = query.filter(DiademExpAlchemy.type == 'train')
            query = query.filter(DiademExpAlchemy.fold_id == null())
            query = query.join(DiademExpAlchemy.exp)
            query = query.filter(ExpAlchemy.kind == 'Train')
            res = query.one()
            return res.exp_id
        elif model_exp.kind == 'ActiveLearning':
            return None
        else:
            raise InvalidModelExperimentKind(model_exp.kind)

    # kind in ['train', 'test', 'validation'] 

def report_stats(app: web.Application) -> None:
    stats_monitor = app['stats_monitor']
    await stats_monitor.report_metric(
        GAUGE, 'ai.backend.gateway.coroutines', len(asyncio.Task.all_tasks()))

    all_inst_ids = [
        inst_id async for inst_id
        in app['registry'].enumerate_instances()]
    await stats_monitor.report_metric(
        GAUGE, 'ai.backend.gateway.agent_instances', len(all_inst_ids))

    async with app['dbpool'].acquire() as conn, conn.begin():
        query = (sa.select([sa.func.sum(keypairs.c.concurrency_used)])
                   .select_from(keypairs))
        n = await conn.scalar(query)
        await stats_monitor.report_metric(
            GAUGE, 'ai.backend.gateway.active_kernels', n)

        subquery = (sa.select([sa.func.count()])
                      .select_from(keypairs)
                      .where(keypairs.c.is_active == true())
                      .group_by(keypairs.c.user_id))
        query = sa.select([sa.func.count()]).select_from(subquery.alias())
        n = await conn.scalar(query)
        await stats_monitor.report_metric(
            GAUGE, 'ai.backend.users.has_active_key', n)

        subquery = subquery.where(keypairs.c.last_used != null())
        query = sa.select([sa.func.count()]).select_from(subquery.alias())
        n = await conn.scalar(query)
        await stats_monitor.report_metric(
            GAUGE, 'ai.backend.users.has_used_key', n)

        '''
        query = sa.select([sa.func.count()]).select_from(usage)
        n = await conn.scalar(query)
        await stats_monitor.report_metric(
            GAUGE, 'ai.backend.gateway.accum_kernels', n)
        ''' 

def get_injected_rules(total_workers, worker_number, limit=100, blacklisted_rules=[], session=None):
    """
    Get rules to be injected.

    :param total_workers:      Number of total workers.
    :param worker_number:      id of the executing worker.
    :param limit:              Maximum number of rules to return.
    :param blacklisted_rules:  Blacklisted rules not to include.
    :param session:            Database session in use.
    """

    if session.bind.dialect.name == 'oracle':
        query = session.query(models.ReplicationRule.id).\
            with_hint(models.ReplicationRule, "index(rules RULES_INJECTIONSTATE_IDX)", 'oracle').\
            filter(text("(CASE when rules.state='I' THEN rules.state ELSE null END)= 'I' ")).\
            filter(models.ReplicationRule.state == RuleState.INJECT).\
            order_by(models.ReplicationRule.created_at)
    else:
        query = session.query(models.ReplicationRule.id).\
            with_hint(models.ReplicationRule, "index(rules RULES_INJECTIONSTATE_IDX)", 'oracle').\
            filter(models.ReplicationRule.state == RuleState.INJECT).\
            order_by(models.ReplicationRule.created_at)

    query = filter_thread_work(session=session, query=query, total_threads=total_workers, thread_id=worker_number, hash_variable='name')

    if limit:
        fetched_rules = query.limit(limit).all()
        filtered_rules = [rule for rule in fetched_rules if rule[0] not in blacklisted_rules]
        if len(fetched_rules) == limit and not filtered_rules:
            return get_injected_rules(total_workers=total_workers,
                                      worker_number=worker_number,
                                      limit=None,
                                      blacklisted_rules=blacklisted_rules,
                                      session=session)
        else:
            return filtered_rules
    else:
        return [rule for rule in query.all() if rule[0] not in blacklisted_rules] 

def unreconciled(db):
        """
        Return a query to match all unreconciled Transactions.

        :param db: active database session to use for queries
        :type db: sqlalchemy.orm.session.Session
        :return: query to match all unreconciled Transactions
        :rtype: sqlalchemy.orm.query.Query
        """
        return db.query(Transaction).filter(
            Transaction.reconcile.__eq__(null()),
            Transaction.date.__ge__(RECONCILE_BEGIN_DATE),
            Transaction.account.has(reconcile_trans=True)
        ) 

def test_unreconciled(self):
        m_db = Mock()
        m_q = Mock(spec_set=Query)
        m_filt = Mock(spec_set=Query)
        m_db.query.return_value = m_q
        m_q.filter.return_value = m_filt
        res = OFXTransaction.unreconciled(m_db)
        assert res == m_filt
        assert len(m_db.mock_calls) == 2
        assert m_db.mock_calls[0] == call.query(OFXTransaction)
        kall = m_db.mock_calls[1]
        assert kall[0] == 'query().filter'
        expected1 = OFXTransaction.reconcile.__eq__(null())
        cutoff = datetime(2017, 3, 17, 0, 0, 0, tzinfo=UTC)
        expected2 = OFXTransaction.date_posted.__ge__(cutoff)
        expected3 = OFXTransaction.account.has(reconcile_trans=True)
        assert len(kall[1]) == 8
        assert str(expected1) == str(kall[1][0])
        assert binexp_to_dict(expected2) == binexp_to_dict(kall[1][1])
        assert str(kall[1][2]) == str(expected3)
        assert str(
            OFXTransaction.is_payment.__ne__(True)
        ) == str(kall[1][3])
        assert str(
            OFXTransaction.is_late_fee.__ne__(True)
        ) == str(kall[1][4])
        assert str(
            OFXTransaction.is_interest_charge.__ne__(True)
        ) == str(kall[1][5])
        assert str(
            OFXTransaction.is_other_fee.__ne__(True)
        ) == str(kall[1][6])
        assert str(
            OFXTransaction.is_interest_payment.__ne__(True)
        ) == str(kall[1][7]) 

def __query__(self):
        q = super(NullSelectableSelectFilter, self).__query__()
        arg_name = 'select.{}'.format(self.attribute_name)
        s = self.request_args.get(arg_name, self.default)
        if s == self.NULL:
            q = self.attribute.is_(null())
        elif s == self.NOT_NULL:
            q = self.attribute.isnot(null())
        return q 

def get_next_run_time(self):
        selectable = select([self.jobs_t.c.next_run_time]).\
            where(self.jobs_t.c.next_run_time != null()).\
            order_by(self.jobs_t.c.next_run_time).limit(1)
        next_run_time = self.engine.execute(selectable).scalar()
        return utc_timestamp_to_datetime(next_run_time) 

def get_next_run_time(self):
        selectable = select([self.jobs_t.c.next_run_time]).\
            where(self.jobs_t.c.next_run_time != null()).\
            order_by(self.jobs_t.c.next_run_time).limit(1)
        next_run_time = self.engine.execute(selectable).scalar()
        return utc_timestamp_to_datetime(next_run_time) 

def get_stuck_rules(total_workers, worker_number, delta=600, limit=10, blacklisted_rules=[], session=None):
    """
    Get stuck rules.

    :param total_workers:      Number of total workers.
    :param worker_number:      id of the executing worker.
    :param delta:              Delta in seconds to select rules in.
    :param limit:              Maximum number of rules to select.
    :param blacklisted_rules:  Blacklisted rules to filter out.
    :param session:            Database session in use.
    """
    if session.bind.dialect.name == 'oracle':
        query = session.query(models.ReplicationRule.id).\
            with_hint(models.ReplicationRule, "index(rules RULES_STUCKSTATE_IDX)", 'oracle').\
            filter(text("(CASE when rules.state='S' THEN rules.state ELSE null END)= 'S' ")).\
            filter(models.ReplicationRule.state == RuleState.STUCK).\
            filter(models.ReplicationRule.updated_at < datetime.utcnow() - timedelta(seconds=delta)).\
            filter(or_(models.ReplicationRule.expires_at == null(),
                       models.ReplicationRule.expires_at > datetime.utcnow(),
                       models.ReplicationRule.locked == true())).\
            order_by(models.ReplicationRule.updated_at)  # NOQA
    else:
        query = session.query(models.ReplicationRule.id).\
            with_hint(models.ReplicationRule, "index(rules RULES_STUCKSTATE_IDX)", 'oracle').\
            filter(models.ReplicationRule.state == RuleState.STUCK).\
            filter(models.ReplicationRule.updated_at < datetime.utcnow() - timedelta(seconds=delta)).\
            filter(or_(models.ReplicationRule.expires_at == null(),
                       models.ReplicationRule.expires_at > datetime.utcnow(),
                       models.ReplicationRule.locked == true())).\
            order_by(models.ReplicationRule.updated_at)

    query = filter_thread_work(session=session, query=query, total_threads=total_workers, thread_id=worker_number, hash_variable='name')

    if limit:
        fetched_rules = query.limit(limit).all()
        filtered_rules = [rule for rule in fetched_rules if rule[0] not in blacklisted_rules]
        if len(fetched_rules) == limit and not filtered_rules:
            return get_stuck_rules(total_workers=total_workers,
                                   worker_number=worker_number,
                                   delta=delta,
                                   limit=None,
                                   blacklisted_rules=blacklisted_rules,
                                   session=session)
        else:
            return filtered_rules
    else:
        return [rule for rule in query.all() if rule[0] not in blacklisted_rules]