本文整理汇总了Python中sqlalchemy.sql.expression.between函数的典型用法代码示例。如果您正苦于以下问题:Python between函数的具体用法?Python between怎么用?Python between使用的例子?那么恭喜您, 这里精选的函数代码示例或许可以为您提供帮助。
在下文中一共展示了between函数的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: check_venue_bookings
def check_venue_bookings( start, end, venue, sitting=None ):
"""
return all sittings (but sitting if given) a venue is booked for
in the period
"""
assert( type(start) == datetime.datetime )
assert( type(end) == datetime.datetime )
session = Session()
b_filter = sql.and_(sql.or_(
sql.between(schema.sittings.c.start_date, start, end),
sql.between(schema.sittings.c.end_date, start, end),
sql.between(start, schema.sittings.c.start_date,
schema.sittings.c.end_date),
sql.between(end, schema.sittings.c.start_date,
schema.sittings.c.end_date)
),
schema.sittings.c.venue_id == venue.venue_id)
if sitting:
if sitting.sitting_id:
b_filter = sql.and_(b_filter,
schema.sittings.c.sitting_id != sitting.sitting_id)
query = session.query(BookedVenue).filter(b_filter)
venues = query.all()
#session.close()
return venues
示例2: get_available_venues
def get_available_venues( start, end, sitting=None ):
"""get all venues that are not booked for a sitting
(but sitting if given)
in the given time period
SQL:
SELECT *
FROM venues
WHERE venues.venue_id NOT IN (SELECT sitting.venue_id
FROM sitting
WHERE (sitting.start_date BETWEEN '2000-01-01' AND '2000-01-02'
OR sitting.end_date BETWEEN '2000-01-01' AND '2000-01-02'
OR '2000-01-01' BETWEEN sitting.start_date AND sitting.end_date
OR '2000-01-02' BETWEEN sitting.start_date AND sitting.end_date)
AND sitting.venue_id IS NOT NULL)
"""
session = Session()
query = session.query(domain.Venue)
b_filter = sql.and_(
sql.or_(
sql.between(schema.sitting.c.start_date, start, end),
sql.between(schema.sitting.c.end_date, start, end),
sql.between(start, schema.sitting.c.start_date,
schema.sitting.c.end_date),
sql.between(end, schema.sitting.c.start_date,
schema.sitting.c.end_date)
),
schema.sitting.c.venue_id != None)
if sitting:
if sitting.sitting_id:
b_filter = sql.and_(b_filter,
schema.sitting.c.sitting_id != sitting.sitting_id)
query = query.filter(sql.not_(schema.venue.c.venue_id.in_(
sql.select( [schema.sitting.c.venue_id] ).where(b_filter) )))
venues = query.all()
return venues
示例3: __call__
def __call__(self, user_ids, session):
"""
Parameters:
user_ids : list of mediawiki user ids to restrict computation to
session : sqlalchemy session open on a mediawiki database
Returns:
{
user id: 1 if they're a rolling new active editor, 0 otherwise
for all cohort users, or all users that have edits in the time period
}
"""
number_of_edits = int(self.number_of_edits.data)
rolling_days = int(self.rolling_days.data)
end_date = self.end_date.data
start_date = end_date - timedelta(days=rolling_days)
newly_registered = session.query(Logging.log_user) \
.filter(Logging.log_type == 'newusers') \
.filter(Logging.log_action == 'create') \
.filter(between(Logging.log_timestamp, start_date, end_date))
filtered_new = self.filter(
newly_registered, user_ids, column=Logging.log_user
).subquery()
rev_user = label('user_id', Revision.rev_user)
ar_user = label('user_id', Archive.ar_user)
count = label('count', func.count())
revisions = session.query(rev_user, count)\
.filter(between(Revision.rev_timestamp, start_date, end_date))\
.filter(Revision.rev_user.in_(filtered_new))\
.group_by(Revision.rev_user)
archived = session.query(ar_user, count)\
.filter(between(Archive.ar_timestamp, start_date, end_date))\
.filter(Archive.ar_user.in_(filtered_new))\
.group_by(Archive.ar_user)
bot_user_ids = session.query(MediawikiUserGroups.ug_user)\
.filter(MediawikiUserGroups.ug_group == 'bot')\
.subquery()
new_edits = revisions.union_all(archived).subquery()
new_edits_by_user = session.query(new_edits.c.user_id)\
.filter(new_edits.c.user_id.notin_(bot_user_ids))\
.group_by(new_edits.c.user_id)\
.having(func.SUM(new_edits.c.count) >= number_of_edits)
metric_results = {r[0]: {self.id : 1} for r in new_edits_by_user.all()}
if user_ids is None:
return metric_results
else:
return {
uid: metric_results.get(uid, self.default_result)
for uid in user_ids
}
示例4: listenerdata
def listenerdata(start, stop):
from rfk.site import app
app.logger.warn(start)
app.logger.warn(stop)
stop = parse_datetimestring(stop)
start = parse_datetimestring(start)
app.logger.warn(start)
app.logger.warn(stop)
ret = {'data': {}, 'shows': []}
streams = Stream.query.all()
for stream in streams:
ret['data'][str(stream.mount)] = []
#just set an initial stating point from before the starting point
stats = stream.statistic.get(stop=start, num=1, reverse=True)
c = 0
for stat in stats:
c = stat.value
if not stats:
c = 0
ret['data'][str(stream.mount)].append((to_timestamp(to_user_timezone(start)), int(c)))
#fill in the actual datapoints
streams = Stream.query.all()
for stream in streams:
stats = stream.statistic.get(start=start, stop=stop)
for stat in stats:
ret['data'][str(stream.mount)].append(
(to_timestamp(to_user_timezone(stat.timestamp)), int(stat.value)))
streams = Stream.query.all()
for stream in streams:
stats = stream.statistic.get(stop=stop, num=1, reverse=True)
for stat in stats:
c = stat.value
if not stats:
c = 0
ret['data'][str(stream.mount)].append((to_timestamp(to_user_timezone(stop)), int(c)))
#get the shows for the graph
shows = Show.query.filter(between(Show.begin, start, stop) \
| between(Show.end, start, stop)).order_by(Show.begin.asc()).all()
for show in shows:
sstart = to_timestamp(to_user_timezone(show.begin))
if show.end:
send = to_timestamp(to_user_timezone(show.end))
else:
send = to_timestamp(to_user_timezone(now()))
ret['shows'].append({'name': show.name,
'b': sstart,
'e': send})
return jsonify(ret)
示例5: __call__
def __call__(self, user_ids, session):
"""
Parameters:
user_ids : list of mediawiki user ids to restrict computation to
session : sqlalchemy session open on a mediawiki database
Returns:
dictionary from user ids to: 1 if they're a rolling active editor, 0 if not
"""
number_of_edits = int(self.number_of_edits.data)
rolling_days = int(self.rolling_days.data)
end_date = self.end_date.data
start_date = end_date - timedelta(days=rolling_days)
rev_user = label("user_id", Revision.rev_user)
ar_user = label("user_id", Archive.ar_user)
count = label("count", func.count())
revisions = (
session.query(rev_user, count)
.filter(between(Revision.rev_timestamp, start_date, end_date))
.group_by(Revision.rev_user)
)
revisions = self.filter(revisions, user_ids, column=Revision.rev_user)
archived = (
session.query(ar_user, count)
.filter(between(Archive.ar_timestamp, start_date, end_date))
.group_by(Archive.ar_user)
)
archived = self.filter(archived, user_ids, column=Archive.ar_user)
bot_user_ids = (
session.query(MediawikiUserGroups.ug_user).filter(MediawikiUserGroups.ug_group == "bot").subquery()
)
edits = revisions.union_all(archived).subquery()
edits_by_user = (
session.query(edits.c.user_id)
.filter(edits.c.user_id.notin_(bot_user_ids))
.group_by(edits.c.user_id)
.having(func.SUM(edits.c.count) >= number_of_edits)
)
metric_results = {r[0]: {self.id: 1} for r in edits_by_user.all()}
if user_ids is None:
return metric_results
else:
return {uid: metric_results.get(uid, self.default_result) for uid in user_ids}
示例6: list_files
def list_files(request):
"""
LIST all the files uploaded in the last 5 hours
"""
from sqlalchemy.sql.expression import between
from datetime import datetime, timedelta
session = DBSession()
now = datetime.now()
# timeframe could be selectable from an interface for instance
# easy to implement: analyze pars or set default if missing...
timeframe = {'hours': 5, 'minutes': 0, 'seconds': 0}
delta = now - timedelta(**timeframe)
result = session.query(MyModel)\
.filter(between(MyModel.timestamp, delta, now))\
.order_by(MyModel.timestamp.desc())
if result.count() > 0:
files = []
for res in result:
print res.path
files.append({'id': res.id,
'file_name': res.name,
'file_size': res.size,
'file_type': res.filetype,
'download_url': 'http://localhost:9000/getfile/%s' % res.id
})
return files
else:
# Could add a No Content (204) response
return []
示例7: getFilter
def getFilter(date):
return sql.or_(
sql.between(date,
schema.group.c.start_date, schema.group.c.end_date),
sql.and_(
schema.group.c.start_date<=date,
schema.group.c.end_date==None))
示例8: top_maps_by_times_played_q
def top_maps_by_times_played_q(cutoff_days, region = None, game_type_cd = None):
"""
Query to retrieve the top maps by the amount of times it was played
during a date range.
Games older than cutoff_days days old are ignored.
"""
# only games played during this range are considered
right_now = datetime.utcnow()
cutoff_dt = right_now - timedelta(days=cutoff_days)
top_maps_q = DBSession.query(Game.map_id, Map.name,
func.count()).\
filter(Map.map_id==Game.map_id).\
filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\
order_by(expr.desc(func.count())).\
group_by(Game.map_id).\
group_by(Map.name)
if region and region != "" and region != "0":
top_maps_q = top_maps_q.filter(Server.region==region).filter(Server.server_id==Game.server_id)
if game_type_cd and game_type_cd != "":
top_maps_q = top_maps_q.filter(Game.game_type_cd == game_type_cd)
return top_maps_q
示例9: recent_games_q
def recent_games_q(server_id=None, map_id=None, player_id=None,
game_type_cd=None, cutoff=None, force_player_id=False):
'''
Returns a SQLA query of recent game data. Parameters filter
the results returned if they are provided. If not, it is
assumed that results from all servers and maps is desired.
The cutoff parameter provides a way to limit how far back to
look when querying. Only games that happened on or after the
cutoff (which is a datetime object) will be returned.
'''
pgstat_alias = aliased(PlayerGameStat, name='pgstat_alias')
recent_games_q = DBSession.query(Game.game_id, GameType.game_type_cd,
Game.winner, Game.start_dt, GameType.descr.label('game_type_descr'),
Server.server_id, Server.name.label('server_name'), Map.map_id,
Map.name.label('map_name'), PlayerGameStat.player_id,
PlayerGameStat.nick, PlayerGameStat.rank, PlayerGameStat.team,
PlayerGameStat.elo_delta).\
filter(Game.server_id==Server.server_id).\
filter(Game.map_id==Map.map_id).\
filter(Game.game_id==PlayerGameStat.game_id).\
filter(Game.game_type_cd==GameType.game_type_cd).\
order_by(expr.desc(Game.game_id))
# the various filters provided get tacked on to the query
if server_id is not None:
recent_games_q = recent_games_q.\
filter(Server.server_id==server_id)
if map_id is not None:
recent_games_q = recent_games_q.\
filter(Map.map_id==map_id)
# Note: force_player_id makes the pgstat row returned be from the
# specified player_id. Otherwise it will just look for a game
# *having* that player_id, but returning the #1 player's pgstat row
if player_id is not None:
if force_player_id:
recent_games_q = recent_games_q.\
filter(PlayerGameStat.player_id==player_id)
else:
recent_games_q = recent_games_q.\
filter(PlayerGameStat.scoreboardpos==1).\
filter(Game.game_id==pgstat_alias.game_id).\
filter(pgstat_alias.player_id==player_id)
else:
recent_games_q = recent_games_q.\
filter(PlayerGameStat.scoreboardpos==1)
if game_type_cd is not None:
recent_games_q = recent_games_q.\
filter(Game.game_type_cd==game_type_cd.lower())
if cutoff is not None:
right_now = datetime.utcnow()
recent_games_q = recent_games_q.\
filter(expr.between(Game.create_dt, cutoff, right_now))
return recent_games_q
示例10: get_unavailable_resources
def get_unavailable_resources(start, end):
"""Get all resources that are booked in the given time period.
"""
assert(type(start) == datetime.datetime)
assert(type(end) == datetime.datetime)
session = Session()
b_filter = sql.or_(
sql.between(schema.sitting.c.start_date, start, end),
sql.between(schema.sitting.c.end_date, start, end),
sql.between(start,
schema.sitting.c.start_date, schema.sitting.c.end_date),
sql.between(end,
schema.sitting.c.start_date, schema.sitting.c.end_date)
)
query = session.query(BookedResources).filter(b_filter)
resources = query.all()
return resources
示例11: detalleNombre
def detalleNombre(self, dia, nombre):
inicio = datetime(dia.year, dia.month, dia.day, 0, 0)
fin = datetime(dia.year, dia.month, dia.day, 23, 59)
return dict(recibos=model.Recibo.query.filter(
model.Recibo.cliente.like(u"%{0}%".format(nombre))
).filter(between(model.Recibo.dia, inicio, fin)).all())
示例12: iplatlondel
def iplatlondel():
# If only google actually supported sessions! Darn them!
# if 'oldolddatetime' in session:
# serverhits=m.ServerHit.query.filter(between(m.ServerHit.insdatetime,session.get('oldolddatetime'),session.get('olddatetime'))).limit(4000).all()
serverhits=m.ServerHit.query.filter(between(m.ServerHit.insdatetime,datetime.datetime.now()-datetime.timedelta(seconds=8),datetime.datetime.now()-datetime.timedelta(seconds=2))).all()
for row in serverhits:
yield {
'del': '<Placemark targetId="A'+str(row.id)+'"></Placemark>'
}
示例13: dia
def dia(self, dia):
"""Muestra los recibos de un dia"""
inicio = datetime(dia.year, dia.month, dia.day, 0, 0)
fin = datetime(dia.year, dia.month, dia.day, 23, 59)
return dict(recibos=model.Recibo.query.filter(
between(model.Recibo.dia, inicio, fin)).all(),
dia=dia)
示例14: get_sittings
def get_sittings(self):
formatter = self.request.locale.dates.getFormatter("date", "full")
session = Session()
query = (
session.query(domain.GroupSitting)
.filter(
sql.and_(
schema.sittings.c.status.in_(get_states("groupsitting", tagged=["public"])),
sql.between(schema.sittings.c.start_date, self.start_date, self.end_date),
)
)
.order_by(schema.sittings.c.start_date)
.options(
eagerload("group"),
# eagerload('sitting_type'),
eagerload("item_schedule"),
eagerload("item_schedule.item"),
)
)
sittings = query.all()
day = u""
day_list = []
s_dict = {}
for sitting in sittings:
sday = formatter.format(sitting.start_date)
if sday != day:
s_list = []
day = sday
if s_dict:
day_list.append(s_dict)
s_dict = {}
if sitting.group.type == "parliament":
_url = url.set_url_context("/business/sittings/obj-%i" % (sitting.sitting_id))
elif sitting.group.type == "committee":
_url = url.set_url_context(
"/business/committees/obj-%i/sittings/obj-%i" % (sitting.group.group_id, sitting.sitting_id)
)
else:
_url = "#"
s_list.append(
{
"start": sitting.start_date.strftime("%H:%M"),
"end": sitting.end_date.strftime("%H:%M"),
"type": sitting.group.type,
"name": sitting.group.short_name,
"url": _url,
"items": self.get_sitting_items(sitting),
}
)
s_dict["day"] = day
s_dict["sittings"] = s_list
else:
if s_dict:
day_list.append(s_dict)
return day_list
示例15: on_message
def on_message(self, message):
command = message.split('|')[0]
query = '|'.join(message.split('|')[1:])
if command == 'criterion':
criterion = query.split('|')[0]
value = '|'.join(query.split('|')[1:])
if criterion == 'date':
try:
value = datetime.strptime(
value.replace('+', ' '), '%Y-%m-%d %H:%M:%S')
except ValueError:
try:
value = datetime.strptime('%Y-%m-%d')
except ValueError:
value = datetime.now()
filter_ = between(Visit.date,
value.date(),
value.date() + timedelta(days=1))
elif criterion in (
'referrer', 'asn', 'browser_name', 'site',
'browser_version', 'browser_name_version', 'query'):
filter_ = getattr(Visit, criterion).ilike('%%%s%%' % value)
else:
filter_ = func.lower(
getattr(Visit, criterion)) == value.lower()
query = (self.db
.query(Visit)
.filter(filter_))
dialect = query.session.bind.dialect
compiler = SQLCompiler(dialect, query.statement)
compiler.compile()
self.count = 0
self.stop = 20
self.state = 'start'
self.execute(compiler.string, compiler.params)
elif command == 'more':
if self.state == 'paused':
self.stop += 20
self.state = 'executing'
self.cursor.execute(
'FETCH FORWARD 1 FROM visit_cur;')
elif command == '/status':
for i, conn in enumerate(adb._pool):
if conn.busy():
self.write_message(
'INFO|Connection %d is busy: '
'Executing? %s Closed? %d Status? %s (%d)' % (
i, conn.connection.isexecuting(),
conn.connection.closed,
conn.connection.get_transaction_status(),
conn.connection.get_backend_pid()))
else:
self.write_message('INFO|Connection %d is free' % i)