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 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)