def where(self, date, intervals):
"""
Generates a WHERE clause
Args:
date: the end date
intervals: intervals
Returns: a clause for filtering the from_obj to be between the date and
the greatest interval
"""
# upper bound
w = "{date_column} < '{date}'".format(
date_column=self.date_column, date=date)
# lower bound (if possible)
if 'all' not in intervals:
greatest = "greatest(%s)" % str.join(
",", ["interval '%s'" % i for i in intervals])
min_date = "'{date}'::date - {greatest}".format(date=date, greatest=greatest)
w += "AND {date_column} >= {min_date}".format(
date_column=self.date_column, min_date=min_date)
if self.input_min_date is not None:
w += "AND {date_column} >= '{bot}'::date".format(
date_column=self.date_column, bot=self.input_min_date)
return ex.text(w)
python类text()的实例源码
def get_ping_aggregates(self, template):
now = datetime.utcnow()
if template == '30m':
a = now - timedelta(days=7)
agg_date = sqle.text("datetime((strftime('%s', time) / 1800) * 1800, 'unixepoch')")
else:
raise Exception()
cnt = sqlf.count(Ping.id)
q = sql.select([
agg_date,
sqlf.sum(Ping.response_time) / cnt,
sqlf.sum(Ping.users) / cnt,
sqlf.sum(Ping.statuses) / cnt,
sqlf.sum(Ping.connections) / cnt,
])
q = q.where((Ping.time >= a) & (Ping.instance_id == self.id))
q = q.group_by(agg_date)
q = q.order_by(Ping.id)
Nt = namedtuple('PingAgg', ['time', 'response_time', 'users', 'statuses', 'connections'])
r = db.session.execute(q)
return [Nt(*t) for t in r]
def get_uptime_aggregates(self, template):
now = datetime.utcnow()
if template == '30m':
a = now - timedelta(days=7)
agg_date = sqle.text("datetime((strftime('%s', time) / 1800) * 1800, 'unixepoch')")
else:
raise Exception()
cnt = sqlf.count(Ping.id)
q = sql.select([
agg_date,
Ping.state,
sqlf.sum(Ping.response_time) / cnt,
])
q = q.where((Ping.time >= a) & (Ping.instance_id == self.id))
q = q.group_by(agg_date, Ping.state)
q = q.order_by(Ping.id)
Nt = namedtuple('PingUptimeAgg', ['time', 'state', 'response_time'])
r = db.session.execute(q)
return [Nt(*t) for t in r]
def test_pk_default(self):
Table(
'simple_items', self.metadata,
Column('id', INTEGER, primary_key=True, server_default=text('uuid_generate_v4()'))
)
assert self.generate_code() == """\
# coding: utf-8
from sqlalchemy import Column, Integer, text
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
metadata = Base.metadata
class SimpleItem(Base):
__tablename__ = 'simple_items'
id = Column(Integer, primary_key=True, server_default=text("uuid_generate_v4()"))
"""
def get_updated_rse_counters(total_workers, worker_number, session=None):
"""
Get updated rse_counters.
:param total_workers: Number of total workers.
:param worker_number: id of the executing worker.
:param session: Database session in use.
:returns: List of rse_ids whose rse_counters need to be updated.
"""
query = session.query(models.UpdatedRSECounter.rse_id).\
distinct(models.UpdatedRSECounter.rse_id)
if total_workers > 0:
if session.bind.dialect.name == 'oracle':
bindparams = [bindparam('worker_number', worker_number),
bindparam('total_workers', total_workers)]
query = query.filter(text('ORA_HASH(rse_id, :total_workers) = :worker_number', bindparams=bindparams))
elif session.bind.dialect.name == 'mysql':
query = query.filter('mod(md5(rse_id), %s) = %s' % (total_workers + 1, worker_number))
elif session.bind.dialect.name == 'postgresql':
query = query.filter('mod(abs((\'x\'||md5(rse_id))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number))
results = query.all()
return [result.rse_id for result in results]
def get_updated_account_counters(total_workers, worker_number, session=None):
"""
Get updated rse_counters.
:param total_workers: Number of total workers.
:param worker_number: id of the executing worker.
:param session: Database session in use.
:returns: List of rse_ids whose rse_counters need to be updated.
"""
query = session.query(models.UpdatedAccountCounter.account, models.UpdatedAccountCounter.rse_id).\
distinct(models.UpdatedAccountCounter.account, models.UpdatedAccountCounter.rse_id)
if total_workers > 0:
if session.bind.dialect.name == 'oracle':
bindparams = [bindparam('worker_number', worker_number),
bindparam('total_workers', total_workers)]
query = query.filter(text('ORA_HASH(CONCAT(account, rse_id), :total_workers) = :worker_number', bindparams=bindparams))
elif session.bind.dialect.name == 'mysql':
query = query.filter('mod(md5(concat(account, rse_id)), %s) = %s' % (total_workers + 1, worker_number))
elif session.bind.dialect.name == 'postgresql':
query = query.filter('mod(abs((\'x\'||md5(concat(account, rse_id)))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number))
return query.all()
def get_db_time():
""" Gives the utc time on the db. """
s = session.get_session()
try:
storage_date_format = None
if s.bind.dialect.name == 'oracle':
query = select([text("sys_extract_utc(systimestamp)")])
elif s.bind.dialect.name == 'mysql':
query = select([text("utc_timestamp()")])
elif s.bind.dialect.name == 'sqlite':
query = select([text("datetime('now', 'utc')")])
storage_date_format = '%Y-%m-%d %H:%M:%S'
else:
query = select([func.current_date()])
for now, in s.execute(query):
if storage_date_format:
return datetime.strptime(now, storage_date_format)
return now
finally:
s.remove()
def update(self, id, user_id, ingredient_id, coefficient):
"""Update a record in the database with new values"""
query = text("""
UPDATE tblUserPreference SET
user_id=:user_id,
ingredient_id=:ingredient_id,
coefficient=:coefficient
WHERE
id=:id
""")
result = self.execute(query,
id=id,
user_id=user_id,
ingredient_id=ingredient_id,
coefficient=coefficient)
db.session.commit()
return result.rowcount > 0
def test_fetchone(self):
"""It gets a single row"""
query = text("""
SELECT id, name FROM tblExample
WHERE id=:id;
""")
# Expect one record to be returned
result = self.dao.fetchone(query, id=1)
self.assertDictEqual(result, {'id': 1, 'name': 'Foo'})
# Expect None to be returned if
result = self.dao.fetchone(query, id=-1)
self.assertEqual(result, None)
# Expect one record to be returned even if multiple rows
result = self.dao.fetchone('SELECT id, name FROM tblExample ORDER BY id')
self.assertDictEqual(result, {'id': 1, 'name': 'Foo'})
def test_fetchmany(self):
"""It gets many rows"""
query = text("""
SELECT id, name FROM tblExample
ORDER BY id
""")
# Expect multiple records to be returned
result = self.dao.fetchmany(query)
self.assertEqual(len(result), 3)
self.assertDictEqual(result[0], {'id': 1, 'name': 'Foo'})
self.assertDictEqual(result[1], {'id': 2, 'name': 'Bar'})
self.assertDictEqual(result[2], {'id': 3, 'name': 'Baz'})
# Expect rows to be limited by MAX_RESULTS_SIZE
self.dao.MAX_RESULTS_SIZE = 2
result = self.dao.fetchmany(query)
self.assertEqual(len(result), 2)
def seed_identifier(cls): # @NoSelf
'''returns data_identifier if the latter is not None, else net.sta.loc.cha by querying the
relative channel and station'''
# Needed note: To know what we are doing in 'sel' below, please look:
# http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html#correlated-subquery-relationship-hybrid
# Notes
# - we use limit(1) cause we might get more than one
# result. Regardless of why it happens (because we don't join or apply a distinct?)
# it is relevant for us to get the first result which has the requested
# network+station and location + channel strings
# - the label(...) at the end makes all the difference. The doc is, as always, unclear
# http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.label
dot = text("'.'")
sel = select([concat(Station.network, dot, Station.station, dot,
Channel.location, dot, Channel.channel)]).\
where((Channel.id == cls.channel_id) & (Station.id == Channel.station_id)).limit(1).\
label('seedidentifier')
return case([(cls.data_identifier.isnot(None), cls.data_identifier)],
else_=sel)
def get_player(self, account_id=None, steam_id=None, real_name=None):
query = Database.session.query(Player)
if account_id:
return query.filter(Player.account_id == account_id).first()
elif steam_id:
return query.filter(Player.steam_id == steam_id).first()
elif real_name: # recommended to be optimized by full-text search.
return query.filter(or_(text('real_name like :real_name'), text('persona_name like :real_name'))).params(
real_name="%" + real_name + "%").limit(LIMIT_DATA).all()
else:
raise ValueError('Account id or Steam id or real name must be specified!')
def get_match_summary_aggregate(self, match_id):
return Database.session.query(MatchHero.account_id,
func.sum(text('match_heroes.player_win')).label('player_win'),
func.count(MatchHero.player_win).label('matches')). \
filter(MatchHero.match_id >= match_id). \
group_by(MatchHero.account_id). \
all()
def get_match_hero_summary_aggregate(self, match_id):
return Database.session.query(MatchHero.account_id,
MatchHero.hero_id,
func.sum(text('match_heroes.player_win')).label('player_win'),
func.count(MatchHero.player_win).label('matches')). \
filter(MatchHero.match_id >= match_id). \
group_by(MatchHero.account_id). \
group_by(MatchHero.hero_id). \
all()
def get_match_item_summary_aggregate(self, match_id):
return Database.session.query(MatchItem.account_id,
MatchItem.item_id,
func.sum(text('match_items.player_win')).label('player_win'),
func.count(MatchItem.player_win).label('matches')). \
filter(MatchItem.match_id >= match_id). \
group_by(MatchItem.account_id). \
group_by(MatchItem.item_id). \
all()
def __init__(self, aggregates, groups, from_obj, state_table,
state_group=None, prefix=None, suffix=None, schema=None):
"""
Args:
aggregates: collection of Aggregate objects.
from_obj: defines the from clause, e.g. the name of the table. can use
groups: a list of expressions to group by in the aggregation or a dictionary
pairs group: expr pairs where group is the alias (used in column names)
state_table: schema.table to query for comprehensive set of state_group entities
regardless of what exists in the from_obj
state_group: the group level found in the state table (e.g., "entity_id")
prefix: prefix for aggregation tables and column names, defaults to from_obj
suffix: suffix for aggregation table, defaults to "aggregation"
schema: schema for aggregation tables
The from_obj and group expressions are passed directly to the
SQLAlchemy Select object so could be anything supported there.
For details see:
http://docs.sqlalchemy.org/en/latest/core/selectable.html
Aggregates will have {collate_date} in their quantities substituted with the date
of aggregation.
"""
self.aggregates = aggregates
self.from_obj = make_sql_clause(from_obj, ex.text)
self.groups = groups if isinstance(groups, dict) else {str(g): g for g in groups}
self.state_table = state_table
self.state_group = state_group if state_group else "entity_id"
self.prefix = prefix if prefix else str(from_obj)
self.suffix = suffix if suffix else "aggregation"
self.schema = schema
def remove_unicode_prefixes(text):
return unicode_re.sub(r"\1\2\1", text)
def remove_unicode_prefixes(text):
return text
def test_indexes_class(self):
simple_items = Table(
'simple_items', self.metadata,
Column('id', INTEGER, primary_key=True),
Column('number', INTEGER),
Column('text', VARCHAR)
)
simple_items.indexes.add(Index('idx_number', simple_items.c.number))
simple_items.indexes.add(Index('idx_text_number', simple_items.c.text, simple_items.c.number))
simple_items.indexes.add(Index('idx_text', simple_items.c.text, unique=True))
assert self.generate_code() == """\
# coding: utf-8
from sqlalchemy import Column, Index, Integer, String
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
metadata = Base.metadata
class SimpleItem(Base):
__tablename__ = 'simple_items'
__table_args__ = (
Index('idx_text_number', 'text', 'number'),
)
id = Column(Integer, primary_key=True)
number = Column(Integer, index=True)
text = Column(String, unique=True)
"""
def get_updated_dids(total_workers, worker_number, limit=100, blacklisted_dids=[], session=None):
"""
Get updated dids.
:param total_workers: Number of total workers.
:param worker_number: id of the executing worker.
:param limit: Maximum number of dids to return.
:param blacklisted_dids: Blacklisted dids to filter.
:param session: Database session in use.
"""
query = session.query(models.UpdatedDID.id,
models.UpdatedDID.scope,
models.UpdatedDID.name,
models.UpdatedDID.rule_evaluation_action)
if total_workers > 0:
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_dids = query.order_by(models.UpdatedDID.created_at).limit(limit).all()
filtered_dids = [did for did in fetched_dids if (did.scope, did.name) not in blacklisted_dids]
if len(fetched_dids) == limit and len(filtered_dids) == 0:
return get_updated_dids(total_workers=total_workers,
worker_number=worker_number,
limit=None,
blacklisted_dids=blacklisted_dids,
session=session)
else:
return filtered_dids
else:
return [did for did in query.order_by(models.UpdatedDID.created_at).all() if (did.scope, did.name) not in blacklisted_dids]
def get_rules_beyond_eol(date_check, worker_number, total_workers, session):
"""
Get rules which have eol_at before a certain date.
:param date_check: The reference date that should be compared to eol_at.
:param worker_number: id of the executing worker.
:param total_workers: Number of total workers.
:param session: Database session in use.
"""
query = session.query(models.ReplicationRule.scope,
models.ReplicationRule.name,
models.ReplicationRule.rse_expression,
models.ReplicationRule.locked,
models.ReplicationRule.id,
models.ReplicationRule.eol_at,
models.ReplicationRule.expires_at).\
filter(models.ReplicationRule.eol_at < date_check)
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)))
return [rule for rule in query.all()]
def get_expired_rules(total_workers, worker_number, limit=100, blacklisted_rules=[], session=None):
"""
Get expired rules.
:param total_workers: Number of total workers.
:param worker_number: id of the executing worker.
:param limit: Maximum number of rules to return.
:param backlisted_rules: List of blacklisted rules.
:param session: Database session in use.
"""
query = session.query(models.ReplicationRule.id, models.ReplicationRule.rse_expression).filter(models.ReplicationRule.expires_at < datetime.utcnow(),
models.ReplicationRule.locked == False,
models.ReplicationRule.child_rule_id == None).\
with_hint(models.ReplicationRule, "index(rules RULES_EXPIRES_AT_IDX)", 'oracle').\
order_by(models.ReplicationRule.expires_at) # NOQA
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_expired_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 list_bad_replicas_history(limit=10000, thread=None, total_threads=None, session=None):
"""
List the bad file replicas history. Method only used by necromancer
:param limit: The maximum number of replicas returned.
:param thread: The assigned thread for this necromancer.
:param total_threads: The total number of threads of all necromancers.
:param session: The database session in use.
"""
query = session.query(models.BadReplicas.scope, models.BadReplicas.name, models.BadReplicas.rse_id).\
filter(models.BadReplicas.state == BadFilesStatus.BAD)
if total_threads and (total_threads - 1) > 0:
if session.bind.dialect.name == 'oracle':
bindparams = [bindparam('thread_number', thread), bindparam('total_threads', total_threads - 1)]
query = query.filter(text('ORA_HASH(name, :total_threads) = :thread_number', bindparams=bindparams))
elif session.bind.dialect.name == 'mysql':
query = query.filter(text('mod(md5(name), %s) = %s' % (total_threads - 1, thread)))
elif session.bind.dialect.name == 'postgresql':
query = query.filter(text('mod(abs((\'x\'||md5(name))::bit(32)::int), %s) = %s' % (total_threads - 1, thread)))
query = query.limit(limit)
bad_replicas = {}
for scope, name, rse_id in query.yield_per(1000):
if rse_id not in bad_replicas:
bad_replicas[rse_id] = []
bad_replicas[rse_id].append({'scope': scope, 'name': name})
return bad_replicas
def get_replica_atime(replica, session=None):
"""
Get the accessed_at timestamp for a replica. Just for testing.
:param replicas: List of dictionaries {scope, name, rse_id, path}
:param session: Database session to use.
:returns: A datetime timestamp with the last access time.
"""
if 'rse_id' not in replica:
replica['rse_id'] = get_rse_id(rse=replica['rse'], session=session)
return session.query(models.RSEFileAssociation.accessed_at).filter_by(scope=replica['scope'], name=replica['name'], rse_id=replica['rse_id']).\
with_hint(models.RSEFileAssociation, text="INDEX(REPLICAS REPLICAS_PK)", dialect_name='oracle').one()[0]
def list_expired_temporary_dids(rse, limit, worker_number=None, total_workers=None,
session=None):
"""
List expired temporary DIDs.
:param rse: the rse name.
:param limit: The maximum number of replicas returned.
:param worker_number: id of the executing worker.
:param total_workers: Number of total workers.
:param session: The database session in use.
:returns: a list of dictionary replica.
"""
rse_id = get_rse_id(rse, session=session)
is_none = None
query = session.query(models.TemporaryDataIdentifier.scope,
models.TemporaryDataIdentifier.name,
models.TemporaryDataIdentifier.path,
models.TemporaryDataIdentifier.bytes).\
with_hint(models.TemporaryDataIdentifier, "INDEX(tmp_dids TMP_DIDS_EXPIRED_AT_IDX)", 'oracle').\
filter(case([(models.TemporaryDataIdentifier.expired_at != is_none, models.TemporaryDataIdentifier.rse_id), ]) == rse_id)
if worker_number and total_workers and total_workers - 1 > 0:
if session.bind.dialect.name == 'oracle':
bindparams = [bindparam('worker_number', worker_number - 1), bindparam('total_workers', total_workers - 1)]
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 - 1)))
elif session.bind.dialect.name == 'postgresql':
query = query.filter(text('mod(abs((\'x\'||md5(path))::bit(32)::int), %s) = %s' % (total_workers - 1, worker_number - 1)))
return [{'path': path,
'rse': rse,
'rse_id': rse_id,
'scope': scope,
'name': name,
'bytes': bytes}
for scope, name, path, bytes in query.limit(limit)]
def get_by_id(self, id):
"""Retrieve a record from the database by ID"""
query = text("""
SELECT id, name FROM tblBreakfast
WHERE id=:id;
""")
return self.fetchone(query, id=id)
def list_all(self):
"""Get all records from the database"""
query = text("""
SELECT id, name FROM tblBreakfast;
""")
return self.fetchall(query)
def get_by_id(self, id):
"""Retrieve a record from the database by ID"""
query = text("""
SELECT id, name FROM tblIngredient
WHERE id=:id;
""")
return self.fetchone(query, id=id)
def list_all(self):
"""Get all records from the database"""
query = text("""
SELECT id, name FROM tblIngredient;
""")
return self.fetchall(query)
def get_by_id(self, id):
"""Retrieve a record from the database by ID"""
query = text("""
SELECT id, first_name, last_name FROM tblUser
WHERE id=:id;
""")
return self.fetchone(query, id=id)
