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())
)
python类null()的实例源码
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)http://stackoverflow.com/questions/13712381/how-to-turn-on-pragma-foreign-keys-on-in-sqlalchemy-migration-script-or-conf
for setting foreign keys in sqlite:
```
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 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_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_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]
