我们从Python开源项目中，提取了以下15个代码示例，用于说明如何使用sqlalchemy.sql.expression.null()。
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 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) )
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 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]) == 3 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)
def withdata(model_column): """Returns a filter argument for returning instances with values of `model_column` NOT *empty* nor *null*. `model_column` type must be STRING or BLOB :param model_column: A valid column name, e.g. an attribute Column defined in some sqlalchemy orm model class (e.g., 'User.data'). **The type of the column must be STRING or BLOB**, otherwise result is undefined. For instance, numeric column with zero as value are *not* empty (as the sql length function applied to numeric returns the number of bytes) :example:
# given a table User, return empty or none via "~" session.query(User.id).filter(~withdata(User.data)).all() # return "valid" columns: session.query(User.id).filter(withdata(User.data)).all() ``` """ return (model_column.isnot(None)) & (func.length(model_column) > 0)
```
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 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 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 band_code(cls): # @NoSelf '''returns the sql expression returning the first letter of the channel field, or NULL if the latter has not length 3''' # return an sql expression matching the last char or None if not three letter channel return substr(cls.channel, 1, 1) # return case([(func.length(cls.channel) == 3, func.substr(cls.channel, 1, 1))], else_=null())
def instrument_code(cls): # @NoSelf '''returns the sql expression returning the second letter of the channel field, or NULL if the latter has not length 3''' # return an sql expression matching the last char or None if not three letter channel return substr(cls.channel, 2, 1) # return case([(func.length(cls.channel) == 3, func.substr(cls.channel, 2, 1))], else_=null())
def orientation_code(cls): # @NoSelf '''returns the sql expression returning the third letter of the channel field, or NULL if the latter has not length 3''' # return an sql expression matching the last char or None if not three letter channel return substr(cls.channel, 3, 1) #return case([(func.length(cls.channel) == 3, func.substr(cls.channel, 3, 1))], else_=null())
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) if session.bind.dialect.name == 'oracle': bindparams = [bindparam('worker_number', worker_number), bindparam('total_workers', total_workers)] query = query.filter(text('ORA_HASH(name, :total_workers) = :worker_number', bindparams=bindparams)) elif session.bind.dialect.name == 'mysql': query = query.filter(text('mod(md5(name), %s) = %s' % (total_workers + 1, worker_number))) elif session.bind.dialect.name == 'postgresql': query = query.filter(text('mod(abs((\'x\'||md5(name))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number))) 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 len(filtered_rules) == 0: 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 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) if session.bind.dialect.name == 'oracle': bindparams = [bindparam('worker_number', worker_number), bindparam('total_workers', total_workers)] query = query.filter(text('ORA_HASH(name, :total_workers) = :worker_number', bindparams=bindparams)) elif session.bind.dialect.name == 'mysql': query = query.filter(text('mod(md5(name), %s) = %s' % (total_workers + 1, worker_number))) elif session.bind.dialect.name == 'postgresql': query = query.filter(text('mod(abs((\'x\'||md5(name))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number))) 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 len(filtered_rules) == 0: 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]
