def updates_in_week(cls, day_in_week):
for_year, for_week, week_day = day_in_week.isocalendar()
q = Session.query(cls)
q = q.filter(func.date_part('year', cls.when) == for_year)
q = q.filter(func.date_part('week', cls.when) == for_week)
q = q.order_by(cls.when)
return q
def index(self):
from gviz_data_table import Table
from rockpack.mainsite.services.user.models import User, UserActivity, UserAccountEvent
if request.args.get('activity') == 'activity':
activity_model, activity_date = UserActivity, UserActivity.date_actioned
else:
activity_model, activity_date = UserAccountEvent, UserAccountEvent.event_date
try:
interval_count = int(request.args['interval_count'])
except Exception:
interval_count = 10
interval = request.args.get('interval')
if interval not in ('week', 'month'):
interval = 'week'
cohort = func.date_part(interval, User.date_joined)
cohort_label = func.min(func.date(User.date_joined))
active_interval = (func.date_part(interval, activity_date) - cohort).label('active_interval')
q = readonly_session.query(User).filter(
User.date_joined > LAUNCHDATE, User.refresh_token != '')
if request.args.get('gender') in ('m', 'f'):
q = q.filter(User.gender == request.args['gender'])
if request.args.get('locale') in app.config['ENABLED_LOCALES']:
q = q.filter(User.locale == request.args['locale'])
if request.args.get('age') in ('13-18', '18-25', '25-35', '35-45', '45-55'):
age1, age2 = map(int, request.args['age'].split('-'))
q = q.filter(between(
func.age(User.date_of_birth),
text("interval '%d years'" % age1),
text("interval '%d years'" % age2)
))
active_users = dict(
((c, int(w)), u) for c, w, u in
q.join(
activity_model,
(activity_model.user == User.id) &
(activity_date >= User.date_joined)
).group_by(cohort, active_interval).values(
cohort, active_interval, func.count(func.distinct(activity_model.user))
)
)
table = Table(
[dict(id='cohort', type=date)] +
[dict(id='%s%d' % (interval, i), type=str) for i in range(interval_count)]
)
totals = q.group_by(cohort).order_by(cohort)
for c, l, t in totals.values(cohort, cohort_label, func.count()):
data = []
for i in range(interval_count):
a = active_users.get((c, i), '')
data.append(a and '%d%% (%d)' % (ceil(a * 100.0 / t), a))
table.append([l] + data)
return self.render('admin/retention_stats.html', data=table.encode())
def visit_datetime_op(self, expr):
class_name = type(expr).__name__
input = self._expr_to_sqlalchemy[expr._input]
if class_name in DATE_PARTS_DIC:
if self._sa_engine and self._sa_engine.name == 'mysql':
if class_name == 'UnixTimestamp':
fun = func.unix_timestamp
else:
fun = getattr(func, class_name.lower())
sa_expr = fun(input).cast(types.df_type_to_sqlalchemy_type(expr.dtype))
else:
sa_expr = func.date_part(DATE_PARTS_DIC[class_name], input)\
.cast(types.df_type_to_sqlalchemy_type(expr.dtype))
elif isinstance(expr, Date):
if self._sa_engine and self._sa_engine.name == 'mysql':
sa_expr = func.date(input).cast(types.df_type_to_sqlalchemy_type(expr.dtype))
else:
sa_expr = func.date_trunc('day', input)
elif isinstance(expr, WeekDay):
if self._sa_engine and self._sa_engine.name == 'mysql':
sa_expr = (func.dayofweek(input).cast(types.df_type_to_sqlalchemy_type(expr.dtype)) + 5) % 7
else:
sa_expr = (func.date_part('dow', input).cast(types.df_type_to_sqlalchemy_type(expr.dtype)) + 6) % 7
else:
raise NotImplementedError
self._add(expr, sa_expr)
def getFrequencyPerYear(self):
mensurationPerYear = session\
.query(Mensuration.station.label('station'), func.date_part('year', Mensuration.date).label('year'), func.count(func.date_part('year', Mensuration.date)))\
.group_by(Mensuration.station, func.date_part('year', Mensuration.date))\
.order_by('year')\
.all()\
totals = session \
.query(Mensuration.station.label('station'), func.count(Mensuration.station))\
.group_by(Mensuration.station)\
.order_by('station')\
.all()\
response = []
for row in mensurationPerYear:
for row_2 in totals:
if (row_2[0] == row[0]):
total = row_2[1]
break
response.append({
'station': str(row[0]),
'year': int(row[1]),
'frequency': int(row[2]),
'total': int(total)
})
return response
def get_period_field(period, model):
"""
Returns the SQL Alchemy field to use base on the type
aggregate it is (month vs. year)
:param args: Dictionary
:return: SQLAlchemy Field object
"""
fields = []
fields.append(func.date_part('year', model.date))
if period == 'month':
fields.append(
func.lpad(
expression.cast(
func.date_part('month', model.date), types.String
), 3, '-0'
)
)
else:
fields.append('-01')
fields.append('-01')
return func.concat(*fields).label('date')
def get_today_birthday(self):
today = dt.datetime.today()
users = [
u.to_dict() for u in self.session.query(User).filter(
(func.date_part("month", User.birthday) == today.month)
& (func.date_part("day", User.birthday) == today.day)).all()
]
return json.dumps(users)
def seconds_until_expiry(cls):
current_time = func.current_timestamp()
seconds_left = (
func.date_part('day', cls.expires_on - current_time) * 24 +
func.date_part('hour', cls.expires_on - current_time) * 60 +
func.date_part('minute', cls.expires_on - current_time) * 60 +
func.date_part('second', cls.expires_on - current_time))
return case([(seconds_left > 0, seconds_left)], else_=0)
def query(self, view_kwargs):
user = get_user_from_jwt()
week_number = func.date_part('week', Run.start_time)
year = func.date_part('year', Run.start_time)
query_ = self.session.query(
func.avg(Run.distance).label('average_distance'),
func.avg(Run.duration).label('average_duration'),
func.avg(Run.distance / Run.duration).label('average_speed'),
week_number.label('week_number'),
year.label('year')).filter_by(user_id=user.id).group_by(
year, week_number).order_by(year.desc(), week_number.desc())
return query_
def tweet():
""" Look for statements made on this day, tweet teaser + link
In practice, this is called using a cron job. Cron needs to activate
virtual environment before using this command. This will first find
recent tweets and exclude them from the query. This way, cron can
call the script multiple times in a day and it will not attempt to post
duplicates.
Sample cron:
0 9,12,15,18 * * *
cd ~/webapps/laststatement_org &&
source ~/.virtualenvs/last/bin/activate &&
python -m laststatement.task.manage tweet >>
~/webapps/laststatement_org/instance/cronlog.txt 2>&1
"""
day = datetime.now().strftime('%-m-%-d')
auth = tweepy.OAuthHandler(TWITTER_CONSUMER_KEY, TWITTER_CONSUMER_SECRET)
auth.set_access_token(TWITTER_ACCESS_TOKEN, TWITTER_ACCESS_TOKEN_SECRET)
twitter = tweepy.API(auth)
qry = db.session.query(Offender.execution_num,
Offender.teaser).\
filter(Offender.teaser != None).\
filter(func.date_part('month', Offender.execution_date) + '-' +
func.date_part('day', Offender.execution_date) == day)
# tweepy.user_timeline() returns 20 most recent statuses
recent_teasers = [
r.text.split(' http')[0] for r in twitter.user_timeline()
]
if len(recent_teasers) > 0:
similar = "%|".join(recent_teasers) + "%" # postgres SIMILAR TO syntax
qry = qry.filter(not_(Offender.teaser.op("SIMILAR TO")(similar)))
offender = qry.first()
if offender is not None:
try:
url = "http://laststatement.org/execution/%s" % \
offender.execution_num
twitter.update_status("%s %s" % (offender.teaser, url))
except tweepy.TweepError as e:
print "%s Error: %s (%s)" % (date, e.message[0]['message'],
e.message[0]['code'])
else:
print '%s Notice: No statement for today' % date
def tweet():
""" Look for statements made on this day, tweet teaser + link
In practice, this is called using a cron job. Cron needs to activate
virtual environment before using this command. This will first find
recent tweets and exclude them from the query. This way, cron can
call the script multiple times in a day and it will not attempt to post
duplicates.
Sample cron:
0 9,12,15,18 * * *
cd ~/webapps/laststatement_org &&
source ~/.virtualenvs/last/bin/activate &&
python -m laststatement.task.manage tweet >>
~/webapps/laststatement_org/instance/cronlog.txt 2>&1
"""
day = datetime.now().strftime('%-m-%-d')
auth = tweepy.OAuthHandler(TWITTER_CONSUMER_KEY, TWITTER_CONSUMER_SECRET)
auth.set_access_token(TWITTER_ACCESS_TOKEN, TWITTER_ACCESS_TOKEN_SECRET)
twitter = tweepy.API(auth)
qry = db.session.query(Offender.execution_num,
Offender.teaser).\
filter(Offender.teaser != None).\
filter(func.date_part('month', Offender.execution_date) + '-' +
func.date_part('day', Offender.execution_date) == day)
# tweepy.user_timeline() returns 20 most recent statuses
recent_teasers = [r.text.split(' http')[0]
for r in twitter.user_timeline()]
if len(recent_teasers) > 0:
similar = "%|".join(recent_teasers) + "%" # postgres SIMILAR TO syntax
qry = qry.filter(not_(Offender.teaser.op("SIMILAR TO")(similar)))
offender = qry.first()
if offender is not None:
try:
url = "http://laststatement.org/execution/%s" % \
offender.execution_num
twitter.update_status("%s %s" % (offender.teaser, url))
except tweepy.TweepError as e:
print "%s Error: %s (%s)" % (date,
e.message[0]['message'],
e.message[0]['code'])
else:
print '%s Notice: No statement for today' % date
def __init__(self, cliente):
title = u"(EXPERIMENTAL) Historia de %s" % cliente.nombre
list_header = Columns([
('fixed', 2, Divider()),
('fixed', 3, Text(u"Tip", align='center')),
('fixed', 6, Text(u"Número", align='center')),
('fixed', 3, Text(u"Ven", align='right')),
('fixed', 6, Text(u"Impues", align='right')),
('fixed', 6, Text(u"Descue", align='right')),
('fixed', 9, Text(u"Total".upper(), align='right')),
Divider(),
('fixed', 6, Text(u"Fecha", align='left')),
('fixed', 5, Text(u"Hora", align='left')),
], dividechars=1)
title_row = [('listado.title.important', title), " beta"]
header_row = [
AttrMap(Text(title_row, align='center', wrap='clip'), 'listado.title'),
AttrMap(list_header, 'listado.list_header'),
]
header = Pile(header_row)
key = 'listado.footer.important'
footer = Text([
(key, "+"), "/", (key, "-"), u" expandir/colapsar ",
(key, "ESC"), ",", (key, "ENTER"), ",",
(key, "ESPACIO"), " o ", (key, "F10"),
u" para continuar"], align='right')
query = session.query(Documento.fecha).filter(Documento.cliente==cliente).order_by(Documento.fecha)
months = sorted(session.query(func.date_part("month", Documento.fecha),
func.date_part("year", Documento.fecha)
).filter(Documento.cliente_id==cliente.id).distinct().all(),
key=itemgetter(1, 0))
treestore = TreeListWalker([ClientMonthNode((cliente, date(int(m[1]), int(m[0]), 1))) for m in months])
treebox = TreeListBox(treestore)
self.content = Frame(
AttrMap(treebox, 'listado.body'),
header=header,
footer=AttrMap(footer, 'listado.footer'))
self.__super.__init__(self.content,
height=('relative', 100),
width=('relative', 100),
with_border=False)
def __init__(self, context):
super(PersonsQueryBuilder, self).__init__(context)
self._subq_contacts = query_person_contacts().subquery()
self._subq_passports = query_person_passports().subquery()
self._fields = {
'id': Person.id,
'_id': Person.id,
'name': Person.name,
'birthday': Person.birthday,
'person_category': PersonCategory.name,
'age': case([(
Person.birthday != None,
func.date_part('year', func.age(Person.birthday))
)]),
}
self._simple_search_fields = [
Person.name,
Person.first_name,
Person.last_name,
self._subq_contacts.c.phone,
self._subq_contacts.c.email,
self._subq_contacts.c.skype,
self._subq_passports.c.citizen,
self._subq_passports.c.foreign,
]
self.build_query()
def now_playing():
if engine.name == 'sqlite':
query = session.query(User).join(Track).filter(
func.strftime('%s', now()) -
func.strftime('%s', User.last_play_date) < Track.duration * 2)
elif engine.name == 'postgresql':
query = session.query(User).join(Track).filter(
func.date_part('epoch',
func.now() - User.last_play_date) < Track.duration *
2)
else:
query = session.query(User).join(Track).filter(
func.timediff(func.now(), User.last_play_date) < Track.duration *
2)
return request.formatter({
'nowPlaying': {
'entry': [
dict(
u.last_play.as_subsonic_child(request.user).items() + {
'username': u.name,
'minutesAgo': (now() - u.last_play_date).seconds / 60,
'playerId': 0
}.items()) for u in query
]
}
})
def member_age(self):
"""
Being used in the UI sorting and filtering
:return:member age
"""
return func.date_part("year",
func.age(self.join_date)).label("member_age")
async def get(self):
await self.check_import_exists()
# В задании требуется, чтобы ключами были номера месяцев
# (без ведущих нулей, "01" -> 1).
month = func.date_part('month', citizens_t.c.birth_date)
month = cast(month, Integer).label('month')
query = select([
month,
relations_t.c.citizen_id,
func.count(relations_t.c.relative_id).label('presents')
]).select_from(
relations_t.join(
citizens_t, and_(
citizens_t.c.import_id == relations_t.c.import_id,
citizens_t.c.citizen_id == relations_t.c.relative_id
)
)
).group_by(
month,
relations_t.c.import_id,
relations_t.c.citizen_id
).where(
relations_t.c.import_id == self.import_id
)
rows = await self.pg.fetch(query)
result = {i: [] for i in range(1, 13)}
for month, rows in groupby(rows, key=lambda row: row['month']):
for row in rows:
result[month].append({'citizen_id': row['citizen_id'],
'presents': row['presents']})
return Response(body={'data': result})
async def get_birthdays(import_id: int, database: Database) -> dict:
"""Get number of birthdays by every month for particular import."""
agg_presents = func.count(relations.c.relative).label("presents")
month = func.date_part("month", citizens.c.birth_date)
month = cast(month, Integer).label("month")
query = (select(
[month, relations.c.citizen.label("citizen_id"),
agg_presents]).select_from(
citizens.join(
relations,
and_(
relations.c.import_id == citizens.c.import_id,
relations.c.relative == citizens.c.citizen_id,
),
)).where(relations.c.import_id == import_id).group_by(
month,
relations.c.citizen,
))
res = {str(i): [] for i in range(1, 13)}
async for row in database.iterate(query):
month = str(row[0])
res[month].append({"citizen_id": row[1], "presents": row[2]})
return res
def index_old(self):
from gviz_data_table import Table
from rockpack.mainsite.services.user.models import User, UserActivity
user_count = readonly_session.query(func.count(User.id)).\
filter(User.refresh_token != '').scalar()
header = ('user count', 'max lifetime', 'avg lifetime', 'stddev lifetime',
'max active days', 'avg active days', 'stddev active days')
lifetime = func.date_part('days', func.max(UserActivity.date_actioned) -
func.min(UserActivity.date_actioned)).label('lifetime')
active_days = func.count(func.distinct(func.date(
UserActivity.date_actioned))).label('active_days')
activity = readonly_session.query(UserActivity.user, lifetime, active_days).\
group_by(UserActivity.user)
ctx = {}
for key, having_expr in ('all', None), ('1day', lifetime > 1), ('7day', lifetime > 7):
data = activity.having(having_expr).from_self(
func.count('*'),
func.max(lifetime),
func.avg(lifetime),
func.stddev_samp(lifetime),
func.max(active_days),
func.avg(active_days),
func.stddev_samp(active_days)
).one()
table = Table([
dict(id='metric', type=str),
dict(id='value', type=float),
dict(id='%', type=str),
])
pdata = ('%d%%' % (data[0] * 100 / user_count),) + ('',) * 6
table.extend(zip(*(header, map(float, data), pdata)))
ctx['ret_%s_data' % key] = table.encode()
return self.render('admin/retention_stats_old.html', **ctx)
class ViewAnswersLocalTimeController(RawTableController):
table = ViewAnswersLocalTime
input_fields = ["hour", "activity"]
output_fields = ["Hour", "Activity"]
activity = func.count(ViewAnswersLocalTime.id).label("activity")
hour_part = func.date_part(
'hour', ViewAnswersLocalTime.local_creation_date).label("hour")
def select(self, obj):
return obj.query(self.activity, self.hour_part)
def group(self, obj):
return obj.group_by(self.hour_part)
def order(self, obj):
return obj.order_by(desc(self.activity))
def filter(self, query):
filtered = super(ViewAnswersLocalTimeController, self).filter(query)
return filtered.filter(
ViewAnswersLocalTime.local_creation_date != None)
def paginate(self, query):
return super(ViewAnswersLocalTimeController, self).paginate(query, 24)
def postprocess(self, response):
response = super(ViewAnswersLocalTimeController,
self).postprocess(response)
response["timechart"] = True
response["charttype"] = "timechart"
return response
def reddit_score(self):
s = self.upvotes - self.downvotes
order = func.log(10, func.greatest(func.abs(s), 1))
sign = func.sign(s)
seconds = func.date_part('epoch', self.timestamp) - 1134028003
return func.round(func.cast(sign * order + seconds / 45000, Numeric),
7)
def get_time_fmt(self, st, et):
period = et - st
if period <= datetime.timedelta(days=1):
return 'hour', func.date_part('hour',
self.model.original_create_time)
# return 'hour'
elif datetime.timedelta(days=1) < period < datetime.timedelta(days=31):
# return 'day'
return 'day', func.date_part('day',
self.model.original_create_time)
elif period > datetime.timedelta(days=365):
return 'year', func.date_part('year',
self.model.original_create_time)
else:
return 'month', func.date_part('month',
self.model.original_create_time)
def start():
violations = db.session.query(
Violation.reported_problem,
label('year', func.date_part('year', Violation.date_recieved)),
label('month', func.date_part('month', Violation.date_recieved)),
label('count', func.count(Violation.id))
).group_by(
func.date_part('year', Violation.date_recieved),
func.date_part('month', Violation.date_recieved),
Violation.reported_problem
).order_by(
'year desc',
'month desc',
'count desc'
).all()
months = []
series = defaultdict(dict)
previous_month = None
violation_types = set([])
prep_agg = defaultdict(list)
prep_series = []
for violation in violations:
month = '{}-{}'.format(int(violation.year), int(violation.month))
if not month == previous_month:
months.append(month)
previous_month = month
violation_types.add(violation.reported_problem)
series[month][violation.reported_problem] = violation.count
for month in months[0:3]:
for violation_type in violation_types:
if series[month].get(violation_type):
prep_agg[violation_type].append(series[month][violation_type])
else:
prep_agg[violation_type].append(0)
for key in prep_agg:
prep_series.append({'name': key, 'data': prep_agg[key]})
months = json.dumps(months)
series = json.dumps(prep_series)
return render_template('index.html', months=months, series=series)
async def get_percentiles(conn, import_id):
"""Считаем перентили (Я на постгресе смог посчитать, нно там не совпадает с numpy)"""
citizen_relatives = alias(citizens)
my_genius_join = citizens.outerjoin(relatives, citizens.c.id == relatives.c.relative_id) \
.outerjoin(citizen_relatives, citizen_relatives.c.id == relatives.c.citizen_id)
result = await conn.execute(
select([
citizens.c.town,
func.array_agg(
func.date_part('year', datetime.datetime.utcnow()) -
func.date_part('year', citizens.c.birth_date)).label(
"birthdays"),
]).select_from(my_genius_join).where(
citizens.c.import_id == import_id).group_by(citizens.c.town))
percentiles = await result.fetchall()
return percentiles
def daily_amount_select():
return select([
func.cast(
func.sum(SalesOrderLine.unit_price * SalesOrderLine.quantity) /
func.greatest(
func.cast(
func.date_part(
'DAY',
func.current_date() - Product.create_date),
Integer), 1), Numeric)
]).as_scalar()
def date__time_of_day_filter(table, op, val):
"""Because I couldn't fit it into a one line lambda.
:param table: SQLAlchemy table object
:param op: string op code
:param val: target value to be compared against
:returns: table condition for just the hour"""
column = func.date_part('hour', table.c.point_date)
return getattr(column, field_ops[op])(val)
def baseline(session, variable, month):
"""Returns list of climate baseline data.
:param session: (sqlalchemy.orm.session.Session) database session
:param variable: (string) requested baseline climate variable ('tmax' | 'tmin' | 'precip')
:param month: (int) requested baseline month (1...12)
:return: list of dicts containing station info and the value of the climate baseline variable specified by
`variable`, for the month specified by `month`, for each station in the CRMP database climate baseline dataset
[
{
'network_name': (str) network name,
'station_native_id': (str) station native id,
'station_name': (str) station name,
'lon': (num) station longitude,
'lat': (num) station latitude,
'elevation': (num) station elevation,
'datum': (num) value for variable
},
...
]
"""
db_variable_name = {
'tmax': 'Tx_Climatology',
'tmin': 'Tn_Climatology',
'precip': 'Precip_Climatology',
}
values = session.query(DerivedValue) \
.select_from(DerivedValue) \
.join(Variable, DerivedValue.vars_id == Variable.id) \
.join(Network, Variable.network_id == Network.id) \
.filter(Network.name == pcic_climate_variable_network_name) \
.filter(Variable.name == db_variable_name[variable]) \
.filter(func.date_part('month', DerivedValue.time) == float(month)) \
.subquery()
values_with_station_info = session.query(
Network.name.label('network_name'),
Station.id.label('station_db_id'),
Station.native_id.label('station_native_id'),
History.id.label('history_db_id'),
History.station_name.label('station_name'),
cast(History.lon, Float).label('lon'),
cast(History.lat, Float).label('lat'),
cast(History.elevation, Float).label('elevation'),
values.c.datum.label('datum'),
) \
.select_from(values) \
.join(History, values.c.history_id == History.id) \
.join(Station, History.station_id == Station.id) \
.join(Network, Station.network_id == Network.id)
return dicts_from_rows(values_with_station_info.all())
def thing():
violations = db.session.query(
Violation.reported_problem,
label('year', func.date_part('year', Violation.date_recieved)),
label('month', func.date_part('month', Violation.date_recieved)),
label('count', func.count(Violation.id))).group_by(
func.date_part('year', Violation.date_recieved),
func.date_part('month', Violation.date_recieved),
Violation.reported_problem).order_by('year desc', 'month desc',
'count desc').all()
months = []
series = defaultdict(dict)
previous_month = None
violation_types = set([])
prep_agg = defaultdict(list)
prep_series = []
for violation in violations:
month = '{}-{}'.format(int(violation.year), int(violation.month))
if not month == previous_month:
months.append(month)
previous_month = month
violation_types.add(violation.reported_problem)
series[month][violation.reported_problem] = violation.count
for month in months[0:1]:
for violation_type in violation_types:
if series[month].get(violation_type):
prep_agg[violation_type].append(series[month][violation_type])
else:
prep_agg[violation_type].append(0)
for key in prep_agg:
prep_series.append({'name': key, 'data': prep_agg[key]})
months = json.dumps(months)
series = json.dumps(prep_series)
return render_template('violations/index.html',
months=months,
series=series)
async def bday(self, ctx, *, name: str = None):
"""
`bday` — show users birthday.
До Дня рождения name1 осталось 1 день.
До Дня рождения name2 осталось 11 дней.
`bday all` — return list of all names with birthdays
14.04 name1
22.04 name2
04.05 name3
`bday <name>` — replay with date if found name in db
14.04 or 🤷♂️
"""
def day_of_year(user_object):
return user_object.birth_date - date(user_object.birth_date.year,
1, 1)
async with ctx.typing():
await asyncio.sleep(0.5)
if name and name.casefold() == "all":
users = await User.query.gino.all()
users.sort(key=day_of_year)
message = "🎉🥳🥳🥳🥳🥳🥳🎉:\n"
for user in users:
message += f"{user.user_name}\t{user.month_and_day}\n"
await ctx.send(message)
elif name:
user = await User.query.where(User.user_name.ilike(name)
).gino.first()
await ctx.reply(user.month_and_day if user else "🤷♂️",
mention_author=False)
else:
current_date = datetime.utcnow()
cur_month_users = await User.query.where(
func.date_part("month", User.birth_date) == current_date.month
).gino.all()
if cur_month_users:
for user in cur_month_users:
days_left = user.birth_date.day - current_date.day
await ctx.send(
f"До Дня Рождения **{user.user_name}** осталось {days_left} {correct_day_end(days_left)}."
)
else:
await ctx.send("В этом месяце - никого.")
await ctx.message.delete(delay=delay)
async def bdays_check(self):
if 10 <= datetime.utcnow().hour <= 20:
current_date = datetime.utcnow()
party_duds = await User.query.where(
(func.date_part("month", User.birth_date) == current_date.month) &
(func.date_part("day", User.birth_date) == current_date.day)
).gino.all()
if party_duds:
for dude in party_duds:
user = self.get_user(dude)
channel = self.get_channel(CHANNELS.get("づ｡◕‿‿◕｡づ"))
embed = discord.Embed()
url = random_gif(apikey, "birth day")
embed.set_image(url=url)
async with channel.typing():
await asyncio.sleep(0.10)
await channel.send(f"{user.mention} happy BD, **{user.name}**! We Love you!", embed=embed)
async def get(self):
await self.check_import_exists()
age = func.age(self.CURRENT_DATE, citizens_table.c.birth_date)
age = func.date_part('year', age)
query = select([
citizens_table.c.town,
rounded(func.percentile_cont(0.5).within_group(age)).label('p50'),
rounded(func.percentile_cont(0.75).within_group(age)).label('p75'),
rounded(func.percentile_cont(0.99).within_group(age)).label('p99')
]).select_from(citizens_table).group_by(citizens_table.c.town).where(
citizens_table.c.import_id == self.import_id)
stats = await self.pg.fetch(query)
return Response(body={'data': stats})
def get_device_usage_chart(user_name):
user = db_session.query(User).filter(User.user_name == user_name).first()
activities_duration = []
for result in perdelta(datetime(2016, 04, 01), datetime.today(),
timedelta(days=1)):
activities = db_session.query(Activity).filter(Activity.user_id == user.id).filter(
func.date_part('year', Activity.start_time) == result.date().year).filter(
func.date_part('month', Activity.start_time) == result.date().month).filter(
func.date_part('day', Activity.start_time) == result.date().day)
iPhone_duration = 0
iPad_duration = 0
tv_duration = 0
other_duration = 0
for activity in activities:
duration = abs((activity.end_time - activity.start_time).seconds)
if activity.activity_type == "iPhone":
iPhone_duration += duration
elif activity.activity_type == "iPad":
iPad_duration += duration
elif activity.activity_type == "tv":
tv_duration += duration
else:
other_duration += duration
activities_on_this_day = dict(date=str(result.date()), iPhone_duration=seconds_to_hours_minutes(iPhone_duration),
iPad_duration=seconds_to_hours_minutes(iPad_duration),
tv_duration=seconds_to_hours_minutes(tv_duration),
other_duration=seconds_to_hours_minutes(other_duration))
activities_duration.append(activities_on_this_day)
def now_playing():
if engine.name == 'sqlite':
query = User.query.join(Track).filter(func.strftime('%s', now()) - func.strftime('%s', User.last_play_date) < Track.duration * 2)
elif engine.name == 'postgresql':
query = User.query.join(Track).filter(func.date_part('epoch', func.now() - User.last_play_date) < Track.duration * 2)
else:
query = User.query.join(Track).filter(func.timediff(func.now(), User.last_play_date) < Track.duration * 2)
return request.formatter({
'nowPlaying': {
'entry': [ dict(
u.last_play.as_subsonic_child(request.user).items() +
{ 'username': u.name, 'minutesAgo': (now() - u.last_play_date).seconds / 60, 'playerId': 0 }.items()
) for u in query ]
}
})
def summary(cls):
year = func.date_part('year', CommitteeMeeting.date).label('year')
rows = db.session.query(
cls.member_id,
cls.attendance,
year,
func.count(1).label('cnt')
)\
.select_from(cls)\
.join(CommitteeMeeting)\
.group_by(cls.member_id, cls.attendance, year)\
.order_by(year.desc(), cls.member_id)\
.all()
return rows
def get_date_parts(self, db: Session, *, column: str,
part: str) -> Union[List[int], None]:
"""
GET DATE PARTS
:param db: SQLAlchemy Session
:param column: String representing a Model-column (e.g. "created_at"
:param part: String representing the date parts: "YEAR" / "MONTH" / "DAY"
:return: A sorted list of integers
"""
column_attr = getattr(self.model, column, None)
if not column_attr:
return None
parts = db.query(distinct(func.date_part(part, column_attr)))
if not parts:
return None
parts_sorted_list = sorted([int(part[0]) for part in parts])
return parts_sorted_list
def get_approx_position_as_geojson(self, time=datetime.utcnow(),
filter_charging=True):
positions = []
if self.id != None:
query = Position().queryObject().filter(Position.animal_id
== self.id)
if filter_charging:
query = query.filter(Position.charging == False)
query = query.filter(func.abs(
func.date_part('hour', Position.date - time)) <= 2)
aux = query.order_by(Position.date.desc()).limit(50)
for position in aux:
positions.append(position.geom)
return self.session.scalar(func.ST_AsGeoJson(
func.ST_MinimumBoundingCircle(func.ST_Collect(
array(positions))))) if len(positions) > 1\
else None
def get_approx_position_as_geojson(self,
time=datetime.utcnow(),
filter_charging=True):
positions = []
if self.id != None:
query = Position().queryObject().filter(
Position.animal_id == self.id)
if filter_charging:
query = query.filter(Position.charging == False)
query = query.filter(
func.abs(func.date_part('hour', Position.date - time)) <= 2)
aux = query.order_by(Position.date.desc()).limit(50)
for position in aux:
positions.append(position.geom)
return self.session.scalar(func.ST_AsGeoJson(
func.ST_MinimumBoundingCircle(func.ST_Collect(
array(positions))))) if len(positions) > 1\
else None
async def get_age_statistics(import_id: int, database: Database) -> List[dict]:
"""Get age percentiles by each town."""
age = func.date_part("year", func.age(citizens.c.birth_date)).label("age")
query = (select([
citizens.c.town,
func.percentile_cont(0.5).within_group(age).label("p50"),
func.percentile_cont(0.75).within_group(age).label("p75"),
func.percentile_cont(0.99).within_group(age).label("p99"),
]).where(citizens.c.import_id == import_id).group_by(citizens.c.town))
res = []
async for row in database.iterate(query):
obj = {
"town": row[0],
"p50": row[1],
"p75": row[2],
"p99": row[3],
}
res.append(obj)
return res
async def get_citizens_birthdays(conn, import_id):
"""Получаем пользователей и дни рождения их родственников"""
citizen_relatives = alias(citizens)
my_genius_join = citizens.outerjoin(relatives, citizens.c.id == relatives.c.relative_id) \
.outerjoin(citizen_relatives, citizen_relatives.c.id == relatives.c.citizen_id)
result = await conn.execute(
select([
citizens.c.citizen_id,
func.array_agg(
func.date_part(
'month',
citizen_relatives.c.birth_date)).label("birthdays"),
]).select_from(my_genius_join).where(
citizens.c.import_id == import_id).group_by(citizens.c.citizen_id))
birthdays = await result.fetchall()
return birthdays
async def get_town_age_statistics(db: PG, import_id: int) -> List[Record]:
"""
Возвращает статистику возврастов жителей по городам.
:param db: объект для взаимодействия с БД
:param import_id: идентификатор выгрузки
:return: статистика
"""
age = func.age(CURRENT_DATE, citizens_table.c.birth_date)
age = func.date_part("year", age)
query = (select([
citizens_table.c.town,
rounded(func.percentile_cont(0.5).within_group(age)).label("p50"),
rounded(func.percentile_cont(0.75).within_group(age)).label("p75"),
rounded(func.percentile_cont(0.99).within_group(age)).label("p99"),
]).select_from(citizens_table).group_by(
citizens_table.c.town).where(citizens_table.c.import_id == import_id))
stats = await db.fetch(query)
return stats
async def get_citizen_birthdays_by_months(db: PG,
import_id: int) -> Dict[int, list]:
"""
Возвращает жителей и количество подарков, которые они будут покупать
своим близжашим родственникам, сгруппированных по месяцам.
:param db: объект для взаимодействия с БД
:param import_id: идентификатор выгрузки
:return: статистику по месяцам
"""
month = func.date_part("month", citizens_table.c.birth_date)
month = cast(month, Integer).label("month")
query = (select([
month,
relations_table.c.citizen_id,
func.count(relations_table.c.relative_id).label("presents"),
]).select_from(
relations_table.outerjoin(
citizens_table,
and_(
relations_table.c.import_id == citizens_table.c.import_id,
relations_table.c.relative_id == citizens_table.c.citizen_id,
),
)).group_by(month, relations_table.c.import_id,
relations_table.c.citizen_id).where(
relations_table.c.import_id == import_id))
rows = await db.fetch(query)
result = {str(i): [] for i in range(1, 13)}
for month, rows in groupby(rows, key=lambda row: row["month"]):
for row in rows:
result[str(month)].append({
"citizen_id": row["citizen_id"],
"presents": row["presents"]
})
return result
def get_timeline(user_name, date_str):
user = db_session.query(User).filter(User.user_name == user_name).first()
date_obj = datetime.datetime.strptime(date_str, "%Y-%m-%d")
objects_for_timeline = []
foods = db_session.query(Food).filter(Food.user_id == user.id).filter(
func.date_part('year', Food.timestamp) == date_obj.date().year).filter(
func.date_part('month', Food.timestamp) == date_obj.date().month).filter(
func.date_part('day', Food.timestamp) == date_obj.date().day)
activities = db_session.query(Activity).filter(Activity.user_id == user.id).filter(
func.date_part('year', Activity.start_time) == date_obj.date().year).filter(
func.date_part('month', Activity.start_time) == date_obj.date().month).filter(
func.date_part('day', Activity.start_time) == date_obj.date().day)
for food in foods:
food_dict = food.get_dict()
food_dict['type'] = 'food'
food_dict['date'] = str(food.timestamp.date())
food_dict['time'] = str(food.timestamp.time())
objects_for_timeline.append(food_dict)
for activity in activities:
activity_dict = activity.get_dict()
activity_dict['type'] = 'activity'
activity_dict['date'] = str(activity.start_time.strftime("%Y-%m-%d"))
activity_dict['time'] = str(activity.start_time.strftime("%H:%M"))
time_difference = activity.end_time - activity.start_time
# print time_difference.total_seconds()
duration = seconds_to_hours_minutes_verbal(time_difference.total_seconds())
# print duration
activity_dict['duration'] = duration.strip()
objects_for_timeline.append(activity_dict)
sorted_object_for_timeline = sorted(objects_for_timeline, key=itemgetter('time'))
return sorted_object_for_timeline
def _baseline_4_expressions(self):
for x in range(ITERATIONS):
assert len(list(self.session.query(Zoo))) == 5
assert len(list(self.session.query(Animal))) == ITERATIONS + 12
assert (
len(list(self.session.query(Animal).filter(Animal.Legs == 4)))
== 4
)
assert (
len(list(self.session.query(Animal).filter(Animal.Legs == 2)))
== 5
)
assert (
len(
list(
self.session.query(Animal).filter(
and_(Animal.Legs >= 2, Animal.Legs < 20)
)
)
)
== ITERATIONS + 9
)
assert (
len(list(self.session.query(Animal).filter(Animal.Legs > 10)))
== 2
)
assert (
len(
list(
self.session.query(Animal).filter(Animal.Lifespan > 70)
)
)
== 2
)
assert (
len(
list(
self.session.query(Animal).filter(
Animal.Species.like("L%")
)
)
)
== 2
)
assert (
len(
list(
self.session.query(Animal).filter(
Animal.Species.like("%pede")
)
)
)
== 2
)
assert (
len(
list(
self.session.query(Animal).filter(
Animal.LastEscape != None
)
)
)
== 1
) # noqa
assert (
len(
list(
self.session.query(Animal).filter(
Animal.LastEscape == None
)
)
)
== ITERATIONS + 11
) # noqa
# In operator (containedby)
assert (
len(
list(
self.session.query(Animal).filter(
Animal.Species.like("%pede%")
)
)
)
== 2
)
assert (
len(
list(
self.session.query(Animal).filter(
Animal.Species.in_(("Lion", "Tiger", "Bear"))
)
)
)
== 3
)
# Try In with cell references
class thing(object):
pass
pet, pet2 = thing(), thing()
pet.Name, pet2.Name = "Slug", "Ostrich"
assert (
len(
list(
self.session.query(Animal).filter(
Animal.Species.in_((pet.Name, pet2.Name))
)
)
)
== 2
)
# logic and other functions
name = "Lion"
assert (
len(
list(
self.session.query(Animal).filter(
func.length(Animal.Species) == len(name)
)
)
)
== ITERATIONS + 3
)
assert (
len(
list(
self.session.query(Animal).filter(
Animal.Species.like("%i%")
)
)
)
== ITERATIONS + 7
)
# Test now(), today(), year(), month(), day()
assert (
len(
list(
self.session.query(Zoo).filter(
and_(
Zoo.Founded != None, Zoo.Founded < func.now()
) # noqa
)
)
)
== 3
)
assert (
len(
list(
self.session.query(Animal).filter(
Animal.LastEscape == func.now()
)
)
)
== 0
)
assert (
len(
list(
self.session.query(Animal).filter(
func.date_part("year", Animal.LastEscape) == 2004
)
)
)
== 1
)
assert (
len(
list(
self.session.query(Animal).filter(
func.date_part("month", Animal.LastEscape) == 12
)
)
)
== 1
)
assert (
len(
list(
self.session.query(Animal).filter(
func.date_part("day", Animal.LastEscape) == 21
)
)
)
== 1
)
def _baseline_4_expressions(self):
Zoo = self.metadata.tables['Zoo']
Animal = self.metadata.tables['Animal']
engine = self.metadata.bind
def fulltable(select):
"""Iterate over the full result table."""
return [list(row) for row in engine.execute(select).fetchall()]
for x in range(ITERATIONS):
assert len(fulltable(Zoo.select())) == 5
assert len(fulltable(Animal.select())) == ITERATIONS + 12
assert len(fulltable(Animal.select(Animal.c.Legs == 4))) \
== 4
assert len(fulltable(Animal.select(Animal.c.Legs == 2))) \
== 5
assert len(
fulltable(
Animal.select(
and_(
Animal.c.Legs >= 2,
Animal.c.Legs < 20)))) == ITERATIONS + 9
assert len(fulltable(Animal.select(Animal.c.Legs > 10))) \
== 2
assert len(fulltable(Animal.select(Animal.c.Lifespan
> 70))) == 2
assert len(fulltable(Animal.select(Animal.c.Species.
startswith('L')))) == 2
assert len(fulltable(Animal.select(Animal.c.Species.
endswith('pede')))) == 2
assert len(fulltable(
Animal.select(Animal.c.LastEscape != None))) == 1 # noqa
assert len(
fulltable(Animal.select(
None == Animal.c.LastEscape))) == ITERATIONS + 11 # noqa
# In operator (containedby)
assert len(fulltable(Animal.select(Animal.c.Species.like('%pede%'
)))) == 2
assert len(
fulltable(
Animal.select(
Animal.c.Species.in_(
['Lion', 'Tiger', 'Bear'])))) == 3
# Try In with cell references
class thing(object):
pass
pet, pet2 = thing(), thing()
pet.Name, pet2.Name = 'Slug', 'Ostrich'
assert len(
fulltable(
Animal.select(
Animal.c.Species.in_([pet.Name, pet2.Name])))) == 2
# logic and other functions
assert len(fulltable(Animal.select(Animal.c.Species.like('Slug'
)))) == 1
assert len(fulltable(Animal.select(Animal.c.Species.like('%pede%'
)))) == 2
name = 'Lion'
assert len(
fulltable(
Animal.select(
func.length(
Animal.c.Species) == len(name)))) == ITERATIONS + 3
assert len(
fulltable(
Animal.select(
Animal.c.Species.like('%i%')))) == ITERATIONS + 7
# Test now(), today(), year(), month(), day()
assert len(
fulltable(
Zoo.select(
and_(
Zoo.c.Founded != None, # noqa
Zoo.c.Founded < func.current_timestamp(
_type=Date))))) == 3
assert len(
fulltable(
Animal.select(
Animal.c.LastEscape == func.current_timestamp(
_type=Date)))) == 0
assert len(
fulltable(
Animal.select(
func.date_part(
'year',
Animal.c.LastEscape) == 2004))) == 1
assert len(
fulltable(
Animal.select(
func.date_part(
'month',
Animal.c.LastEscape) == 12))) == 1
assert len(
fulltable(
Animal.select(
func.date_part(
'day',
Animal.c.LastEscape) == 21))) == 1
def opinions_per_month_per_city():
return session.query(func.date_part('month',Opinion.date),City.name, func.count('*')).select_from(Opinion).join(Hotel).join(Address).join(City).group_by(City.name, func.date_part('month',Opinion.date)).all()
def member_age(self):
"""
Being used in the UI sorting and filtering
:return:member age
"""
return func.date_part("year", func.age(self.join_date)).label("member_age")
def sleep_prediction(user_name, today_date_obj=None):
if today_date_obj == None:
today_date_obj = datetime.datetime.today()
else:
today_date_obj = datetime.datetime.strptime(today_date_obj, "%Y-%m-%d")
user = db_session.query(User).filter(User.user_name == user_name).first()
today_activities = db_session.query(Activity).filter(Activity.user_id == user.id).filter(
and_(func.date_part('year', Activity.start_time) == today_date_obj.date().year,
func.date_part('month', Activity.start_time) == today_date_obj.date().month,
func.date_part('day', Activity.start_time) == today_date_obj.date().day))
today_foods = db_session.query(Food).filter(Food.user_id == user.id).filter(
and_(func.date_part('year', Food.timestamp) == today_date_obj.date().year,
func.date_part('month', Food.timestamp) == today_date_obj.date().month,
func.date_part('day', Food.timestamp) == today_date_obj.date().day))
print today_activities.first()
today_activities_ids = [activity.id for activity in today_activities.all()]
today_foods_ids = [food.id for food in today_foods.all()]
activities = db_session.query(Activity).filter(Activity.user_id == user.id).filter(
Activity.id.notin_(today_activities_ids))
print activities.first()
foods = db_session.query(Food).filter(Food.user_id == user.id).filter(Food.id.notin_(today_foods_ids))
activities_objects = {}
for activity in activities:
dict_for_prediction = activity.get_dict_for_export()
time_difference = activity.end_time - activity.start_time
dict_for_prediction['duration_seconds'] = time_difference.total_seconds()
dict_for_prediction['date'] = str(activity.start_time.strftime("%Y-%m-%d"))
print dict_for_prediction
if dict_for_prediction['date'] in activities_objects:
activities_objects[dict_for_prediction['date']].append(dict_for_prediction)
else:
activities_objects[dict_for_prediction['date']] = [dict_for_prediction]
today_sum_activities_duration = 0.0
count = 0
today_activities_average_per_day = 0.0
for activity in today_activities:
dict_for_prediction = activity.get_dict_for_export()
time_difference = activity.end_time - activity.start_time
today_sum_activities_duration += time_difference.total_seconds()
count += 1
if count > 0:
today_activities_average_per_day = float(today_sum_activities_duration / count)
foods_objects = {}
for food in foods:
dict_for_prediction = food.get_dict_for_export()
dict_for_prediction['date'] = str(food.timestamp.date())
if dict_for_prediction['date'] in foods_objects:
foods_objects[dict_for_prediction['date']].append(dict_for_prediction)
else:
foods_objects[dict_for_prediction['date']] = [dict_for_prediction]
today_sum_foods_score = 0.0
today_foods_average_per_day = 0.0
count = 0
for food in today_foods:
dict_for_prediction = food.get_dict_for_export()
today_sum_foods_score += dict_for_prediction['score']
count += 1
if count > 0:
today_foods_average_per_day = float(today_sum_foods_score / count)
activites_average_per_day = {}
foods_average_per_day = {}
count = 0
for date_result in perdelta(datetime.datetime(2016, 04, 01), datetime.datetime.today(),
datetime.timedelta(days=1)):
if date_result.date != today_date_obj.date():
date_str = date_result.strftime("%Y-%m-%d")
print date_str
print date_result
print activities_objects
if date_str in activities_objects:
activites_average_per_day[count] = float(
sum(d['duration_seconds'] for d in activities_objects[date_str])) / len(
activities_objects[date_str])
if date_str in foods_objects:
foods_average_per_day[count] = float(sum(d['score'] for d in foods_objects[date_str])) / len(
foods_objects[date_str])
count += 1
def _baseline_4_expressions(self):
for x in range(ITERATIONS):
assert len(list(self.session.query(Zoo))) == 5
assert len(list(self.session.query(Animal))) == ITERATIONS + 12
assert len(list(self.session.query(Animal).filter(Animal.Legs
== 4))) == 4
assert len(list(self.session.query(Animal).filter(Animal.Legs
== 2))) == 5
assert len(
list(
self.session.query(Animal).filter(
and_(
Animal.Legs >= 2,
Animal.Legs < 20)))) == ITERATIONS + 9
assert len(list(self.session.query(Animal).filter(Animal.Legs
> 10))) == 2
assert len(list(self.session.query(Animal).filter(Animal.Lifespan
> 70))) == 2
assert len(list(self.session.query(Animal).
filter(Animal.Species.like('L%')))) == 2
assert len(list(self.session.query(Animal).
filter(Animal.Species.like('%pede')))) == 2
assert len(list(self.session.query(Animal).filter(Animal.LastEscape
!= None))) == 1
assert len(
list(
self.session.query(Animal).filter(
Animal.LastEscape == None))) == ITERATIONS + 11
# In operator (containedby)
assert len(list(self.session.query(Animal).filter(
Animal.Species.like('%pede%')))) == 2
assert len(
list(
self.session.query(Animal). filter(
Animal.Species.in_(
('Lion', 'Tiger', 'Bear'))))) == 3
# Try In with cell references
class thing(object):
pass
pet, pet2 = thing(), thing()
pet.Name, pet2.Name = 'Slug', 'Ostrich'
assert len(list(self.session.query(Animal).
filter(Animal.Species.in_((pet.Name,
pet2.Name))))) == 2
# logic and other functions
name = 'Lion'
assert len(list(self.session.query(Animal).
filter(func.length(Animal.Species)
== len(name)))) == ITERATIONS + 3
assert len(list(self.session.query(Animal).
filter(Animal.Species.like('%i%'
)))) == ITERATIONS + 7
# Test now(), today(), year(), month(), day()
assert len(
list(
self.session.query(Zoo).filter(
and_(
Zoo.Founded != None,
Zoo.Founded < func.now())))) == 3
assert len(list(self.session.query(Animal).filter(Animal.LastEscape
== func.now()))) == 0
assert len(list(self.session.query(Animal).filter(
func.date_part('year', Animal.LastEscape) == 2004))) == 1
assert len(
list(
self.session.query(Animal). filter(
func.date_part(
'month',
Animal.LastEscape) == 12))) == 1
assert len(list(self.session.query(Animal).filter(
func.date_part('day', Animal.LastEscape) == 21))) == 1
def year(cls):
return func.date_part('year', cls.date_local)
def daily_profit_select():
return select([func.cast(func.sum((SalesOrderLine.unit_price - Product.purchase_price) * SalesOrderLine.quantity)
/ func.greatest(func.cast(func.date_part('DAY', func.current_date() - Product.create_date), Integer), 1), Numeric)]).as_scalar()
def get_where(current_sel):
now = datetime.now()
this_q, this_y = (now.month - 1) // 3 + 1, now.year
return (current_sel.where(func.date_part('QUARTER', SalesOrder.order_date) == this_q)
.where(func.date_part('YEAR', SalesOrder.order_date) == this_y))
def get_where(current_sel):
now = datetime.now()
last_q, last_y = date_util.get_last_quarter(now.month, now.year)
return (current_sel
.where(func.date_part('QUARTER', SalesOrder.order_date) == last_q)
.where(func.date_part('YEAR', SalesOrder.order_date) == last_y))
def make_query(table, raw_query_params):
table_keys = table.columns.keys()
args_keys = raw_query_params.keys()
resp = {
'meta': {
'status': 'error',
'message': '',
},
'objects': [],
}
status_code = 200
query_clauses = []
valid_query = True
if 'offset' in args_keys:
args_keys.remove('offset')
if 'limit' in args_keys:
args_keys.remove('limit')
if 'order_by' in args_keys:
args_keys.remove('order_by')
if 'weather' in args_keys:
args_keys.remove('weather')
for query_param in args_keys:
try:
field, operator = query_param.split('__')
except ValueError:
field = query_param
operator = 'eq'
query_value = raw_query_params.get(query_param)
column = table.columns.get(field)
if field not in table_keys:
resp['meta']['message'] = '"%s" is not a valid fieldname' % field
status_code = 400
valid_query = False
elif operator == 'in':
query = column.in_(query_value.split(','))
query_clauses.append(query)
elif operator == 'within':
geo = json.loads(query_value)
if 'features' in geo.keys():
val = geo['features'][0]['geometry']
elif 'geometry' in geo.keys():
val = geo['geometry']
else:
val = geo
if val['type'] == 'LineString':
shape = asShape(val)
lat = shape.centroid.y
# 100 meters by default
x, y = getSizeInDegrees(100, lat)
val = shape.buffer(y).__geo_interface__
val['crs'] = {"type":"name","properties":{"name":"EPSG:4326"}}
query = column.ST_Within(func.ST_GeomFromGeoJSON(json.dumps(val)))
query_clauses.append(query)
elif operator.startswith('time_of_day'):
if operator.endswith('ge'):
query = func.date_part('hour', column).__ge__(query_value)
elif operator.endswith('le'):
query = func.date_part('hour', column).__le__(query_value)
query_clauses.append(query)
else:
try:
attr = filter(
lambda e: hasattr(column, e % operator),
['%s', '%s_', '__%s__']
)[0] % operator
except IndexError:
resp['meta']['message'] = '"%s" is not a valid query operator' % operator
status_code = 400
valid_query = False
break
if query_value == 'null': # pragma: no cover
query_value = None
query = getattr(column, attr)(query_value)
query_clauses.append(query)
return valid_query, query_clauses, resp, status_code
def _baseline_4_expressions(self):
Zoo = self.metadata.tables["Zoo"]
Animal = self.metadata.tables["Animal"]
engine = self.metadata.bind
def fulltable(select):
"""Iterate over the full result table."""
return [list(row) for row in engine.execute(select).fetchall()]
for x in range(ITERATIONS):
assert len(fulltable(Zoo.select())) == 5
assert len(fulltable(Animal.select())) == ITERATIONS + 12
assert len(fulltable(Animal.select(Animal.c.Legs == 4))) == 4
assert len(fulltable(Animal.select(Animal.c.Legs == 2))) == 5
assert (len(
fulltable(
Animal.select(and_(Animal.c.Legs >= 2,
Animal.c.Legs < 20)))) == ITERATIONS +
9)
assert len(fulltable(Animal.select(Animal.c.Legs > 10))) == 2
assert len(fulltable(Animal.select(Animal.c.Lifespan > 70))) == 2
assert (len(
fulltable(Animal.select(
Animal.c.Species.startswith("L")))) == 2)
assert (len(
fulltable(Animal.select(
Animal.c.Species.endswith("pede")))) == 2)
assert (len(fulltable(
Animal.select(Animal.c.LastEscape != None))) == 1) # noqa
assert (len(fulltable(
Animal.select(None == Animal.c.LastEscape))) == ITERATIONS + 11
) # noqa
# In operator (containedby)
assert (len(
fulltable(Animal.select(
Animal.c.Species.like("%pede%")))) == 2)
assert (len(
fulltable(
Animal.select(
Animal.c.Species.in_(["Lion", "Tiger",
"Bear"])))) == 3)
# Try In with cell references
class thing(object):
pass
pet, pet2 = thing(), thing()
pet.Name, pet2.Name = "Slug", "Ostrich"
assert (len(
fulltable(
Animal.select(Animal.c.Species.in_([pet.Name,
pet2.Name])))) == 2)
# logic and other functions
assert (len(fulltable(Animal.select(
Animal.c.Species.like("Slug")))) == 1)
assert (len(
fulltable(Animal.select(
Animal.c.Species.like("%pede%")))) == 2)
name = "Lion"
assert (len(
fulltable(
Animal.select(func.length(Animal.c.Species) == len(name))))
== ITERATIONS + 3)
assert (len(fulltable(Animal.select(
Animal.c.Species.like("%i%")))) == ITERATIONS + 7)
# Test now(), today(), year(), month(), day()
assert (len(
fulltable(
Zoo.select(
and_(
Zoo.c.Founded != None, # noqa
Zoo.c.Founded < func.current_timestamp(_type=Date),
)))) == 3)
assert (len(
fulltable(
Animal.select(
Animal.c.LastEscape == func.current_timestamp(
_type=Date)))) == 0)
assert (len(
fulltable(
Animal.select(
func.date_part("year", Animal.c.LastEscape) == 2004)))
== 1)
assert (len(
fulltable(
Animal.select(
func.date_part("month", Animal.c.LastEscape) == 12)))
== 1)
assert (len(
fulltable(
Animal.select(
func.date_part("day", Animal.c.LastEscape) == 21))) ==
1)
def get_where(current_sel):
return current_sel.where(func.date_part('YEAR', SalesOrder.order_date) == datetime.now().year)
def get_where(current_sel):
now = datetime.now()
last_m, last_y = date_util.get_last_month(now.month, now.year)
return (current_sel
.where(func.date_part('MONTH', SalesOrder.order_date) == last_m)
.where(func.date_part('YEAR', SalesOrder.order_date) == last_y))
def daily_amount_select():
return select([func.cast(func.sum(SalesOrderLine.unit_price * SalesOrderLine.quantity)
/func.greatest(func.cast(func.date_part('DAY', func.current_date() - Supplier.create_date),Integer), 1), Integer)]).as_scalar()
def year(cls):
return func.date_part('year', cls.takeoff_time)
