def find_close_pose_record(open_record): ''' open_record = volatility_storage object. find sql entry for the same ticket on the same day at hour_close_max or earlier if exists returns matching volatility_storage object or None if not found ''' record_found = False # Next lines are to simplify query = > not entirely necessary search_date = open_record.timestamp.date() search_name = open_record.name search_time = time_close while not record_found: close_record = query_records.filter(and_(Volatility_storage.name == open_record.name, extract('day', Volatility_storage.timestamp) == search_date.day, extract('month', Volatility_storage.timestamp) == search_date.month, extract('hour', Volatility_storage.timestamp) == search_time.hour, extract('minute', Volatility_storage.timestamp) == search_time.minute)).first() if close_record: record_found = True return close_record curr_min = search_time.minute curr_hour = search_time.hour if curr_min >=1: curr_min -= 1 else: curr_min = 59 curr_hour = curr_hour - 1 search_time = search_time.replace(hour=curr_hour, minute=(search_time.minute-1)) if search_time < hour_close_min: # no closing record found return None
def update_archives(arc_month=LAST_MONTH): """Archives packages of a month, by default: last month""" query = db_session.query(SubPac.pkg_name, SubPac.pkg_version, SubPac.pkg_release, SubPac.pkg_arch, SubPac.vendor_name, SubPac.pkg_status, Sub.distro_name, Sub.distro_version, func.min(SubPac.sub_date), func.count('*').label('count') ).join(Sub) arcs = query.filter(extract('month', SubPac.sub_date) == arc_month.month, extract('year', SubPac.sub_date) == arc_month.year ).group_by(extract('month', SubPac.sub_date), extract('year', SubPac.sub_date), SubPac.pkg_name, SubPac.pkg_version, SubPac.pkg_release, SubPac.pkg_arch, SubPac.vendor_name, SubPac.pkg_status, Sub.distro_name, Sub.distro_version).all() pkg_archives = [] for pkg in arcs: pkg_archive = PackageArchive(*pkg) pkg_archive.month = pkg_archive.month.replace(day=1) pkg_archives.append(pkg_archive) db_session.add_all(pkg_archives) db_session.commit()
def get_count_item_for_last_days(trailcam_id, days=7): date_now = datetime.now() date_7_days_ago = date_now - timedelta(days=days) time_group = DBSession.query(extract('months', TrailcamItem.date_original).label('m'), extract('days', TrailcamItem.date_original).label('d'), sa.func.count(TrailcamItem.id)) \ .filter(TrailcamItem.date_original <= date_now) \ .filter(TrailcamItem.date_original >= date_7_days_ago) \ .filter(TrailcamItem.trailcam_id == trailcam_id) \ .group_by('d') \ .group_by('m') \ .order_by(sa.desc('m')) \ .order_by(sa.desc('d')) \ .all() count_by_days = dict( ('{0}-{1}'.format(m, d), count) for m, d, count in time_group) count_for_last_days = [] for i in range(days): date_i_days_ago = date_now - timedelta(days=i) current_key = '{d.month}-{d.day}'.format(d=date_i_days_ago) if current_key in count_by_days: count_for_last_days.append( (date_i_days_ago.isoformat(), count_by_days[current_key])) else: count_for_last_days.append((date_i_days_ago.isoformat(), 0)) return count_for_last_days
def checkCurMonthOrders(userId): session = db.session() now = datetime.now() numOrders = session.query(Orders).filter_by(AccountId=userId) \ .filter(extract('year', Orders.OrderDate) == now.year) \ .filter(extract('month', Orders.OrderDate) == now.month).all() return len(numOrders)
def event_birthday_calendar(self, out, session): out.writerow([ 'Subject', 'Start Date', 'Start Time', 'End Date', 'End Time', 'All Day Event', 'Description', 'Location', 'Private']) is_multiyear = c.EPOCH.year != c.ESCHATON.year is_multimonth = c.EPOCH.month != c.ESCHATON.month query = session.query(Attendee).filter(Attendee.birthdate != None) # noqa: E711 birth_month = extract('month', Attendee.birthdate) birth_day = extract('day', Attendee.birthdate) if is_multiyear: # The event starts in one year and ends in another query = query.filter(or_( or_( birth_month > c.EPOCH.month, birth_month < c.ESCHATON.month), and_( birth_month == c.EPOCH.month, birth_day >= c.EPOCH.day), and_( birth_month == c.ESCHATON.month, birth_day <= c.ESCHATON.day))) elif is_multimonth: # The event starts in one month and ends in another query = query.filter(or_( and_( birth_month > c.EPOCH.month, birth_month < c.ESCHATON.month), and_( birth_month == c.EPOCH.month, birth_day >= c.EPOCH.day), and_( birth_month == c.ESCHATON.month, birth_day <= c.ESCHATON.day))) else: # The event happens entirely within a single month query = query.filter(and_( birth_month == c.EPOCH.month, birth_day >= c.EPOCH.day, birth_day <= c.ESCHATON.day)) for person in query.all(): subject = "%s's Birthday" % person.full_name year_of_birthday = c.ESCHATON.year if is_multiyear: birth_month = person.birthdate.month birth_day = person.birthdate.day if birth_month >= c.EPOCH.month and birth_day >= c.EPOCH.day: year_of_birthday = c.EPOCH.year delta_years = year_of_birthday - person.birthdate.year start_date = person.birthdate + relativedelta(years=delta_years) end_date = start_date all_day = True private = False out.writerow([ subject, start_date, '', end_date, '', all_day, '', '', private ])
def total_tickets(account, year=None, month=None): """ Returns the total number of tickets sold by the account. """ tickets = Session.query(Ticket.id) # filter by year if year is not None: tickets = tickets.filter(extract('year', Ticket.created_at) == year) # filter by month if month is not None: tickets = tickets.filter(extract('month', Ticket.created_at) == month) tickets = tickets.join(TicketOrder, Order).filter(Order.account_id == account.id) return tickets.count()
def parse_recent_games(season, game_type, daysold=None, month=None, day=None, year=None, game_id=None, limit=None): global session, Base, Classes """ season: the NHL season game_type: the NHL game type (2: regular, 3: playoffs) daysold: only process games within N of das month: all games for the month specified day: all games on the day of the month specified year: all games during the year specified game_id: process a specific game limit: only process N number of games """ logger = logging.getLogger("nhlcom") games = Classes.games filters = [games.season == season, games.game_type == game_type] if month: filters.append(extract('month', games.game_date) == month) if day: filters.append(extract('day', games.game_date) == day) if year: filters.append(extract('year', games.game_date) == year) if daysold: then = datetime.today() - timedelta(days=daysold) filters.append(games.game_date >= then) if game_id: filters.append(games.game_id == game_id) query = session.query(games). \ filter(and_(*filters)). \ order_by(games.game_date.asc()). \ limit(limit) count = query.count() logger.log(logging.INFO, 'parsing %s games for season %s (game type %s)' % \ (count, season, game_type)) for i, game in enumerate(query): logger.log(logging.INFO, 'parsing game %s on date %s (%s/%s)' % \ (game.game_id, game.game_date, i+1, count)) save_pbp(reports.PlayByPlay(game.season, game.game_id)) save_faceoffs(reports.Faceoffs(game.season, game.game_id)) save_toi(reports.TimeOnIce(game.season, game.game_id)) save_roster(reports.Rosters(game.season, game.game_id)) save_box(reports.Boxscore(game.season, game.game_id)) save_events(reports.Events(game.season, game.game_id))
def find_high_low_pos(open_record): search_date = open_record.timestamp.date() search_name = open_record.name highest_record_id = sql_session.query(func.max(Volatility_storage.asset_price), Volatility_storage.id).filter(and_(Volatility_storage.name == open_record.name, extract('day', Volatility_storage.timestamp) == search_date.day, extract('month', Volatility_storage.timestamp) == search_date.month)).one() # print type(highest_record_id[1]), highest_record_id[1] highest_record = query_records.filter(Volatility_storage.id == highest_record_id[1]).one() lowest_record_id = sql_session.query(func.min(Volatility_storage.asset_price), Volatility_storage.id).\ filter(and_(Volatility_storage.name == open_record.name, extract('day', Volatility_storage.timestamp) == search_date.day, extract('month', Volatility_storage.timestamp) == search_date.month)).one() lowest_record = query_records.filter(Volatility_storage.id == lowest_record_id[1]).one() return highest_record, lowest_record
def current_priority_expression(cls, collection, last_build): """ Return computed value for packages priority or None if package is not schedulable. :param: collection package's collection. It should either be the Collection class object, or it should be a concrete collection object if all packages have the same collection. This is done as an optimization to avoid adding join on collection table when the collection is known. :param: last_build package's last complete build. As with the previous argument, should be either Build class object or particular last complete build object. :returns: SQLA expression that, when evaluated in the DB, returns the priority """ # if last_build is concrete object, it may be None # packages with no last build should have no priority # (they cannot be scheduled) if not last_build: return null() dynamic_priority = cls.dependency_priority + cls.build_priority # compute time priority seconds = extract('EPOCH', func.clock_timestamp() - last_build.started) a, b = TIME_PRIORITY.inputs # avoid zero/negative values, when time difference too small log_arg = func.greatest(0.000001, seconds / 3600) dynamic_priority += func.greatest(a * func.log(log_arg) + b, -30) # dynamic priority is affected by coefficient dynamic_priority *= collection.priority_coefficient # manual and static priority are not affected by coefficient current_priority = cls.manual_priority + cls.static_priority + dynamic_priority return case( [ # handle unschedulable packages ( # WHEN blocked OR untracked cls.blocked | ~cls.tracked | # OR has running build (cls.last_complete_build_id != cls.last_build_id) | # OR is unresolved (cls.resolved == False) | # OR resolution is not yet done ((cls.resolved == None) & ~cls.skip_resolution) | # OR the collection's buildroot is broken (collection.latest_repo_resolved == False) | # OR the collection's buildroot wasn't resolved yet (collection.latest_repo_resolved == None), # THEN return NULL None, ) ], # ELSE return the computed priority else_=current_priority)
def crimes_per_day_per_year(): crime_count_year=[] for year in range(2003, 2016): crime_count=[] crime_count.append(session.query(Train).filter(Train.day=='Sunday', extract('year', Train.datetime) == year).count()) crime_count.append(session.query(Train).filter(Train.day=='Monday', extract('year', Train.datetime) == year).count()) crime_count.append(session.query(Train).filter(Train.day=='Tuesday', extract('year', Train.datetime) == year).count()) crime_count.append(session.query(Train).filter(Train.day=='Wednesday', extract('year', Train.datetime) == year).count()) crime_count.append(session.query(Train).filter(Train.day=='Thursday', extract('year', Train.datetime) == year).count()) crime_count.append(session.query(Train).filter(Train.day=='Friday', extract('year', Train.datetime) == year).count()) crime_count.append(session.query(Train).filter(Train.day=='Saturday', extract('year', Train.datetime) == year).count()) crime_count_year.append(crime_count) fig = plt.figure() #Sunday = 1, .... Saturday = 7 x=range(1,8) # These are the colors that will be used in the plot color_sequence = ['#1f77b4', '#aec7e8', '#ff7f0e', '#ffbb78', '#2ca02c', \ '#98df8a', '#d62728', '#ff9896', '#9467bd', '#c5b0d5', \ '#8c564b', '#c49c94', '#e377c2', '#f7b6d2', '#7f7f7f', \ '#c7c7c7', '#bcbd22', '#dbdb8d', '#17becf', '#9edae5'] i=0 for cc in crime_count_year: plt.plot(x, cc, 'r--', linewidth=1, color=color_sequence[i]) i+=1 plt.xlabel('Day') plt.ylabel('Count') plt.title(r'Number of Crimes per day') # Tweak spacing to prevent clipping of ylabel plt.subplots_adjust(left=0.15) plt.show()
def get_years_tweets_counts(self, year): """ Returns tweet count for given year :param year: string :return: int """ model = self.get_new_model() tweets_count = self.session.query(model).filter(year == extract('year', model.created_at)).count() return tweets_count
def get_years_tweets_counts(self, year): """ Returns tweet count for given year :param year: string :return: int """ model = self.get_new_model() tweets_count = self.session.query(model).filter( year == extract('year', model.created_at)).count() return tweets_count
def get(self, userid: Optional[int] = None, categoryid: Optional[int] = None, month: Optional[int] = None, year: Optional[int] = None ): """ Get a list of all bills """ query = Bill.query args = request.args if ("userid" in args): query = query.filter_by(user_id=args["userid"]) if ("categoryid" in args): query = query.filter_by(category_id=args["categoryid"]) if "month" in args and "year" not in args: return BadRequest("year not set") if ("year" in args): query = query.filter(extract("year", Bill.date) == args["year"]) if ("month" in args): query = query.filter(extract("month", Bill.date) == args["month"]) return [bil.json() for bil in query.all()]
def parse_recent_games(season, game_type, daysold=None, month=None, day=None, year=None, game_id=None, limit=None): global session, Base, Classes logger = logging.getLogger("nhlcom") games = Classes.games filters = [games.season == season, games.game_type == game_type] if month: filters.append(extract('month', games.game_date) == month) if day: filters.append(extract('day', games.game_date) == day) if year: filters.append(extract('year', games.game_date) == year) if daysold: then = datetime.today() - timedelta(days=daysold) filters.append(games.game_date >= then) if game_id: filters.append(games.game_id == game_id) query = session.query(games). \ filter(and_(*filters)). \ order_by(games.game_date.asc()). \ limit(limit) count = query.count() logger.log(logging.INFO, 'parsing %s games for season %s (game type %s)' % \ (count, season, game_type)) for i, game in enumerate(query): logger.log(logging.INFO, 'parsing game %s on date %s (%s/%s)' % \ (game.game_id, game.game_date, i+1, count)) save_pbp(reports.PlayByPlay(game.season, game.game_id)) save_faceoffs(reports.Faceoffs(game.season, game.game_id)) save_toi(reports.TimeOnIce(game.season, game.game_id)) save_roster(reports.Rosters(game.season, game.game_id)) save_box(reports.Boxscore(game.season, game.game_id)) save_events(reports.Events(game.season, game.game_id))
def in_rotation_window(cls): """ Determines if a certificate is available for rotation based on the rotation policy associated. :return: """ return case( [ (extract('day', cls.not_after - func.now()) <= RotationPolicy.days, True) ], else_=False )
def add_to_query(self, query, objformatter, value=None, op_num=None, negate=False, collection=None, join_cache=None): no_filter = op_num is None if self.tree_rank is None and self.get_field() is None: query, orm_field = objformatter.objformat(query, getattr(models, self.root_table.name), None, join_cache) no_filter = True elif self.is_relationship(): # will be formatting or aggregating related objects if self.get_field().type == 'many-to-one': query, orm_model, table, field = self.build_join(query, self.join_path, join_cache) query, orm_field = objformatter.objformat(query, orm_model, None, join_cache) else: query, orm_model, table, field = self.build_join(query, self.join_path[:-1], join_cache) orm_field = objformatter.aggregate(query, self.get_field(), orm_model, None) else: query, orm_model, table, field = self.build_join(query, self.join_path, join_cache) if self.tree_rank is not None: query, orm_field = handle_tree_field(query, orm_model, table, self.tree_rank, self.tree_field, collection, join_cache) else: orm_field = getattr(orm_model, self.get_field().name) if field.type == "java.sql.Timestamp": # Only consider the date portion of timestamp fields. # This is to replicate the behavior of Sp6. It might # make sense to condition this on whether there is a # time component in the input value. orm_field = sql.func.DATE(orm_field) if field.is_temporal() and self.date_part != "Full Date": orm_field = extract(self.date_part, orm_field) if not no_filter: if isinstance(value, QueryFieldSpec): _, other_field = value.add_to_query(query.reset_joinpoint(), objformatter, join_cache=join_cache) uiformatter = None value = other_field else: uiformatter = field and get_uiformatter(collection, table.name, field.name) value = value op = QueryOps(uiformatter).by_op_num(op_num) f = op(orm_field, value) query = query.filter(sql.not_(f) if negate else f) query = query.reset_joinpoint() return query, orm_field
def fetch_records(resources, year, month): """ Returns the records used for the dataset. """ if not resources.keys(): return [] query = Session().query(Reservation) query = query.filter(Reservation.resource.in_(resources.keys())) if year != 'all': query = query.filter(extract('year', Reservation.start) == int(year)) if month != 'all': query = query.filter(extract('month', Reservation.start) == int(month)) query = query.order_by( Reservation.resource, Reservation.status, Reservation.start, Reservation.email, Reservation.token, ) return query.all()
def start_game(): today = datetime.datetime.now() routine = session.query(Routine).filter( extract('year', Routine.created_at) == today.year, extract('month', Routine.created_at) == today.month, extract('day', Routine.created_at) == today.day).first() msg = "" if not routine: routine = create_routine() is_over = False if today >= datetime.datetime(today.year, today.month, today.day, 22, 30): is_over = True # routine = session.query(Routine).first() list1 = compute_takens(routine) is_missed_num = list1[0] is_taken_num = list1[1] return render_template('take_meds.html', routine=routine, today=today, is_over=is_over, is_missed_num=is_missed_num, is_taken_num=is_taken_num)
def start_game(): today = datetime.datetime.now() routine = session.query(Routine).filter(extract('year', Routine.created_at) == today.year, extract('month', Routine.created_at) == today.month, extract('day', Routine.created_at) == today.day).first() msg = "" if not routine: routine = create_routine() is_over = False if today >= datetime.datetime(today.year, today.month, today.day, 22, 30): is_over = True # routine = session.query(Routine).first() list1 = compute_takens(routine) is_missed_num = list1[0] is_taken_num = list1[1] return render_template('take_meds.html', routine=routine, today=today, is_over=is_over, is_missed_num = is_missed_num, is_taken_num = is_taken_num)
def leaderboard_query(session, start_date, until_date): """ This is, admittedly, a really ugly sql query. Query optimization has not been performed, but it shouldn't be anything more complicated than a few indices. Good luck. """ #start_date = datetime.strptime(start_date, '%Y-%m-%d') #until_date = datetime.strptime(until_date_str, '%Y-%m-%d') subq = session\ .query( Instance, InstanceType, User, case([(Instance.end_date != None, Instance.end_date)], else_=now()).label('stop_date'))\ .join(Instance.user)\ .join(Instance.type)\ .subquery() uptime_column = case( [ (subq.c.created_date > until_date, 0), (subq.c.stop_date < start_date, 0) ], else_=extract('epoch', func.LEAST(subq.c.stop_date, cast(until_date, DateTime)) - func.GREATEST(subq.c.created_date, cast(start_date, DateTime)) ) ) print subq.c subq2 = session.query( subq.c.user_id, sum(case([(uptime_column == 0, 0)], else_=1)).label('instance_count'), #func.count(subq.c.instance_id).label('instance_count'), sum(uptime_column).label('uptime'), sum(uptime_column * subq.c.cpu).label('cpu_seconds') ).group_by(subq.c.user_id).order_by(desc('cpu_seconds')).subquery() q = session.query( subq2.c.user_id, subq2.c.uptime, subq2.c.cpu_seconds, subq2.c.instance_count, User.username, User.is_staff, User.name ).join(User) return q
def _resource_consumption_stats_view(): time_difference_expr = func.sum(KojiTask.finished - KojiTask.started) time_difference = extract('EPOCH', time_difference_expr) time_difference_all = select([time_difference]).select_from(KojiTask) return (select([ Package.name, KojiTask.arch, time_difference_expr.label('time'), cast(time_difference / time_difference_all, Float).label('time_percentage'), ]).select_from( join( join(Package, Build, Package.id == Build.package_id), KojiTask, )).group_by(Package.name, KojiTask.arch))
def _filter(self, **terms): filters = [] for k, v in terms.items(): if not "__" in k: comparison = "exact" else: k, comparison = k.split("__") try: column = getattr(self.model.__table__.c, k) except AttributeError: raise KeyError("Invalid key '%s'" % k) lookups = { "exact": lambda c, v: c == v, "day": lambda c, v: extract('day', c) == v, "month": lambda c, v: extract('month', c) == v, "year": lambda c, v: extract('year', c) == v, "week_date": lambda c, v: extract('dow', c) == v, "in": lambda c, v: c.in_(v), "contains": lambda c, v: c.contains(v), "icontains": lambda c, v: c.ilike("%" + v + "%"), "startswith": lambda c, v: c.startswith(v), "istartswith": lambda c, v: c.ilike(v + "%"), "endswith": lambda c, v: c.endswith(v), "iendswith": lambda c, v: c.ilike("%" + v), "range": lambda c, v: between(c, v[0], v[1]), "isnull": lambda c, v: c == None if v else c != None, } if not comparison in lookups: raise KeyError("Invalid comparison type %s" % comparison) filters.append(lookups[comparison](column, v)) return and_(*filters)
def get_basequery(self): basequery = super(ArchiveView, self).get_basequery() day_kwarg = self.get_day_kwarg() month_kwarg = self.get_month_kwarg() year_kwarg = self.get_year_kwarg() day = self.kwargs.get(day_kwarg) or self.request.args.get(day_kwarg) month = self.kwargs.get(month_kwarg) or self.request.args.get( month_kwarg) year = self.kwargs.get(year_kwarg) or self.request.args.get(year_kwarg) if year is None: raise ValueError, 'feel bad' basequery = basequery.filter( extract('year', self.model.create_time) == int(year)) if month is None: return basequery basequery = basequery.filter( extract('month', self.model.create_time) == int(month)) if day is None: return basequery basequery = basequery.filter( extract('day', self.model.create_time) == int(day)) return basequery
def current_priority_expression(cls, collection, last_build): """ Return computed value for packages priority or None if package is not schedulable. """ # if last_build is concrete object, it may be None # packages with no last build should have no priority # (they cannot be scheduled) if not last_build: return null() dynamic_priority = cls.dependency_priority + cls.build_priority # compute time priority seconds = extract('EPOCH', func.clock_timestamp() - last_build.started) a, b = TIME_PRIORITY.inputs # avoid zero/negative values, when time difference too small log_arg = func.greatest(0.000001, seconds / 3600) dynamic_priority += func.greatest(a * func.log(log_arg) + b, -30) # dynamic priority is affected by coefficient dynamic_priority *= collection.priority_coefficient # manual and static priority are not affected by coefficient current_priority = cls.manual_priority + cls.static_priority + dynamic_priority return case( [ # handle unschedulable packages ( # WHEN blocked OR untracked cls.blocked | ~cls.tracked | # OR has running build (cls.last_complete_build_id != cls.last_build_id) | # OR is unresolved (cls.resolved == False) | # OR resolution is not yet done ((cls.resolved == None) & ~cls.skip_resolution) | # OR the collection's buildroot is broken (collection.latest_repo_resolved == False) | # OR the collection's buildroot wasn't resolved yet (collection.latest_repo_resolved == None), # THEN return NULL None, ) ], # ELSE return the computed priority else_=current_priority)
def get_tweets_for_year(self, year): """ Returns tweet created at given year :param year: string, year :return: list, tweets matching criteria """ model = self.get_new_model() if not 'ALL' == year: tweets = self.session.query(model).filter( year == extract('year', model.created_at)).order_by( model.created_at).limit(ALE_EACH_YEAR_TWEET_LIMIT_COUNT).all() else: tweets = self.session.query(model).order_by(model.created_at).all() print("Retrieved " + str(len(tweets)) + " rows from database for year:" + str(year) + ".") return tweets
def sanitize_build_year(cls) -> sase.Update: """ Query to update build_year with the year in firstuse if build_year is lower than 1940 and higher than 2020. A quick scna of the data showed that 1940 is approximately the lowest build_year found that looks reasonable compared with firstuse. Somewhere it showed that the data files were created in 2018, therefore 2020 is a bit optimistic. All "years" that fall outside of this range are overwritten with the year of firstuse. If firstuse has a diverging year that is not further remedied because there is not anything to quickly test or check against. :return: A sql statement to update the build_year column with the firstuse year """ stmt = sase.update(cls).prefix_with("IGNORE").where(sase.or_( sase.cast(cls.build_year, Integer) < constants.MIN_YEAR, sase.cast(cls.build_year, Integer) > constants.MAX_YEAR) ).values( build_year=sase.cast(sase.extract('year', sase.cast(cls.firstuse, Date)), String) ) return stmt
def _resource_consumption_stats_view(): time_difference_expr = func.sum(KojiTask.finished - KojiTask.started) time_difference = extract('EPOCH', time_difference_expr) time_difference_all = select([time_difference]).select_from(KojiTask) return ( select([ Package.name, KojiTask.arch, time_difference_expr.label('time'), cast(time_difference / time_difference_all, Float).label('time_percentage'), ]) .select_from( join( join(Package, Build, Package.id == Build.package_id), KojiTask, ) ) .group_by(Package.name, KojiTask.arch) )
def get_tweets_for_year(self, year): """ Returns tweet created at given year :param year: string, year :return: list, tweets matching criteria """ model = self.get_new_model() if not 'ALL' == year: tweets = self.session.query(model).filter( year == extract('year', model.created_at)).order_by( model.created_at).limit( ALE_EACH_YEAR_TWEET_LIMIT_COUNT).all() else: tweets = self.session.query(model).order_by(model.created_at).all() print("Retrieved " + str(len(tweets)) + " rows from database for year:" + str(year) + ".") return tweets
def period_expression(self, period: PeriodType, column: any) -> any: """Builds a period expression from a provided period type and for a given column. :param period: the period type :param column: the column :return: the period expression """ if period == PeriodType.DAY: return cast(extract('year', column), String) \ + '-' \ + self.__entity_manager.query_adapter.pad(extract('month', column), '00') \ + '-' \ + self.__entity_manager.query_adapter.pad(extract('day', column), '00') if period == PeriodType.MONTH: return cast(extract('year', column), String) \ + '-' \ + self.__entity_manager.query_adapter.pad(extract('month', column), '00') if period == PeriodType.QUARTER: return cast(extract('year', column), String) \ + '-Q' \ + cast(cast(cast(extract('month', TransactionDbo.date_value) - 1, Integer) / 3 + 1, Integer), String) if period == PeriodType.YEAR: return cast(extract('year', column), String)
def crime_by_time(category): counts=[] for hour in range(0, 24): counts.append(session.query(Train.datetime).filter(Train.category == category, extract('hour', Train.datetime) == hour).count()) fig = plt.figure() #Sunday = 1, .... Saturday = 7 x=range(0,24) # These are the colors that will be used in the plot color_sequence = ['#1f77b4', '#aec7e8', '#ff7f0e', '#ffbb78', '#2ca02c', \ '#98df8a', '#d62728', '#ff9896', '#9467bd', '#c5b0d5', \ '#8c564b', '#c49c94', '#e377c2', '#f7b6d2', '#7f7f7f', \ '#c7c7c7', '#bcbd22', '#dbdb8d', '#17becf', '#9edae5', '#98df8a', '#d62728', '#ff9896', '#9467bd'] i=0 plt.plot(x, counts, 'ro', linewidth=1, color=color_sequence[i]) plt.xlabel('Hour') plt.ylabel('Count') plt.title(r'Number of Crimes per Hour') # Tweak spacing to prevent clipping of ylabel plt.subplots_adjust(left=0.15) plt.show()
def get_events(self, country, year): events = [] for event in self.session.query(Event.longitud,Event.latitud, Event.event_id_cnty,Event.fatalities,Event.notes).filter(extract('year', Event.event_date) == year).filter(Event.country_id == country): feature = {} feature["type"] ="Feature" feature["geometry"] = {"type": "Point", "coordinates": [event.longitud,event.latitud]} feature["properties"] = {"title": event.event_id_cnty, "description": "%s. Fatalities: %s" % (event.notes, event.fatalities), "marker-symbol": "hospital", "marker-color": "#ff4136"} events.append(feature) return events
def func_extract(self, property, tree): return expression.extract(property.field, self.get_selectable(property.property, tree))
def get_events_center(self, country, year): centers = [] for center in self.session.query(func.avg(Event.longitud).label('longitud'),func.avg(Event.latitud).label('latitud')).filter(extract('year', Event.event_date) == year).filter(Event.country_id == country): centers.append(center.longitud) centers.append(center.latitud) if len(centers) == 0: return [23.992075732837595,4.755856763705008] return centers
def insert_headlines(session: Session, dir_nikkei_headline: Path, train_span: Span, valid_span: Span, test_span: Span, logger: Logger) -> None: dests = list(dir_nikkei_headline.glob('*.csv.gz')) + list( dir_nikkei_headline.glob('*.csv')) for dest in dests: with gzip.open(str(dest), mode='rt') if dest.suffix == '.gz' else dest.open( mode='r') as f: N = sum(1 for _ in f) - 1 f.seek(0) reader = csv.reader(f, delimiter=',', quoting=csv.QUOTE_ALL) next(reader) fields = next(reader) t = fields[1] if 'Z' not in t or '+' not in t: t = t + '+0000' t = datetime.strptime(t, NIKKEI_DATETIME_FORMAT).astimezone(JST) first = session \ .query(Headline) \ .filter(extract('year', in_jst(Headline.t)) == t.year) \ .first() if first is not None: return logger.info('start {}'.format(f.name)) f.seek(0) next(reader) headlines = [] for _ in tqdm(range(N)): fields = next(reader) t = fields[1] if 'Z' not in t or '+' not in t: t = t + '+0000' article_id = fields[5] headline = fields[6] isins = None if fields[25] == '' else fields[25].split(':') countries = None if fields[36] == '' else fields[36].split(':') categories = None if fields[37] == '' else fields[37].split( ':') keywords_headline = None if fields[-2] == '' else fields[ -2].split(':') keywords_article = None if fields[-1] == '' else fields[ -1].split(':') try: t = datetime.strptime(t, NIKKEI_DATETIME_FORMAT) except ValueError: message = 'ValueError: {}, {}, {}' logger.info(message.format(t, article_id, headline)) continue if train_span.start <= t and t < train_span.end: phase = Phase.Train.value elif valid_span.start <= t and t < valid_span.end: phase = Phase.Valid.value elif test_span.start <= t and t < test_span.end: phase = Phase.Test.value else: phase = None headlines.append({ 'article_id': article_id, 't': t, 'headline': headline, 'isins': isins, 'countries': countries, 'categories': categories, 'keywords_headline': keywords_headline, 'keywords_article': keywords_article, 'is_used': None, 'phase': phase }) session.execute(Headline.__table__.insert(), headlines) session.commit()
acct_balance = tdAccounts.starting_balance + tdAccounts.ending_balance acct = tdAccounts.assign( ck_acct=case([(tdAccounts.acct_type == "CK", 1)], else_=0), sv_acct=case([(tdAccounts.acct_type == "SV", 1)], else_=0), cc_acct=case([(tdAccounts.acct_type == "CC", 1)], else_=0), ck_bal=case([(tdAccounts.acct_type == "CK", acct_balance.expression)], else_=0), sv_bal=case([(tdAccounts.acct_type == "SV", acct_balance.expression)], else_=0), cc_bal=case([(tdAccounts.acct_type == "CC", acct_balance.expression)], else_=0)) acct.to_pandas().head(10) # Next: Get the transaction information required for the aggregation. Pull out # the quarter the transaction was made. acct_mon = extract('month', tdTransactions.tran_date.expression).expression trans = tdTransactions.assign( q1_trans=case([(acct_mon == "1", 1), (acct_mon == "2", 1), (acct_mon == "3", 1)], else_=0), q2_trans=case([(acct_mon == "4", 1), (acct_mon == "5", 1), (acct_mon == "6", 1)], else_=0), q3_trans=case([(acct_mon == "7", 1), (acct_mon == "8", 1), (acct_mon == "9", 1)], else_=0), q4_trans=case([(acct_mon == "10", 1), (acct_mon == "11", 1), (acct_mon == "12", 1)], else_=0), ) trans.to_pandas().head(10)
def year(cls): return extract('year', cls.date)
def on_get(self, req, resp, year, month): schedules = sess.query(ms.Schedule).filter(extract("year", ms.Schedule.date) == year, extract("month", ms.Schedule.date) == month) resp.status = falcon.HTTP_200 resp.body = json.dumps([schedule.to_dict() for schedule in schedules])
def get_periodic_stats_quarter_hour(session, start, end): quarter_hour_step = func.date_trunc(literal('hour'), StatCallOnQueue.time) + \ (cast(extract('minute', StatCallOnQueue.time), Integer) / 15) * timedelta(minutes=15) return _get_periodic_stat_by_step(session, start, end, quarter_hour_step)
def get_restaurant_people(restaurant_id: int): """ Given the id of the restaurant return the number of people at lunch and dinner """ openings = ( db.session.query(OpeningHours) .filter( OpeningHours.week_day == datetime.today().weekday(), OpeningHours.restaurant_id == restaurant_id, ) .first() ) if openings is None: return [0, 0, 0] tables = ( db.session.query(RestaurantTable) .filter_by(restaurant_id=restaurant_id) .all() ) tables_id = [] for table in tables: tables_id.append(table.id) reservations_l = ( db.session.query(Reservation) .filter( Reservation.table_id.in_(tables_id), extract("day", Reservation.reservation_date) == extract("day", datetime.today()), extract("month", Reservation.reservation_date) == extract("month", datetime.today()), extract("year", Reservation.reservation_date) == extract("year", datetime.today()), extract("hour", Reservation.reservation_date) >= extract("hour", openings.open_lunch), extract("hour", Reservation.reservation_date) <= extract("hour", openings.close_lunch), ) .all() ) reservations_d = ( db.session.query(Reservation) .filter( Reservation.table_id.in_(tables_id), extract("day", Reservation.reservation_date) == extract("day", datetime.today()), extract("month", Reservation.reservation_date) == extract("month", datetime.today()), extract("year", Reservation.reservation_date) == extract("year", datetime.today()), extract("hour", Reservation.reservation_date) >= extract("hour", openings.open_dinner), extract("hour", Reservation.reservation_date) <= extract("hour", openings.close_dinner), ) .all() ) reservations_now = ( db.session.query(Reservation) .filter( Reservation.checkin is True, Reservation.reservation_date <= datetime.now(), Reservation.reservation_end >= datetime.now(), ) .all() ) return [len(reservations_l), len(reservations_d), len(reservations_now)]
def get_educational_year_beginning_at_given_year(year: int) -> EducationalYear: educational_year = EducationalYear.query.filter( extract("year", EducationalYear.beginningDate) == year).one_or_none() if educational_year is None: raise EducationalYearNotFound() return educational_year
def add_to_query(self, query, objformatter, value=None, op_num=None, negate=False, collection=None, join_cache=None): no_filter = op_num is None if self.tree_rank is None and self.get_field() is None: query, orm_field = objformatter.objformat( query, getattr(models, self.root_table.name), None, join_cache) no_filter = True elif self.is_relationship(): # will be formatting or aggregating related objects if self.get_field().type == 'many-to-one': query, orm_model, table, field = self.build_join( query, self.join_path, join_cache) query, orm_field = objformatter.objformat( query, orm_model, None, join_cache) else: query, orm_model, table, field = self.build_join( query, self.join_path[:-1], join_cache) orm_field = objformatter.aggregate(query, self.get_field(), orm_model, None) else: query, orm_model, table, field = self.build_join( query, self.join_path, join_cache) if self.tree_rank is not None: query, orm_field = handle_tree_field(query, orm_model, table, self.tree_rank, self.tree_field, collection, join_cache) else: orm_field = getattr(orm_model, self.get_field().name) if field.type == "java.sql.Timestamp": # Only consider the date portion of timestamp fields. # This is to replicate the behavior of Sp6. It might # make sense to condition this on whether there is a # time component in the input value. orm_field = sql.func.DATE(orm_field) if field.is_temporal() and self.date_part != "Full Date": orm_field = extract(self.date_part, orm_field) if not no_filter: if isinstance(value, QueryFieldSpec): _, other_field = value.add_to_query(query.reset_joinpoint(), objformatter, join_cache=join_cache) uiformatter = None value = other_field else: uiformatter = field and get_uiformatter( collection, table.name, field.name) value = value op = QueryOps(uiformatter).by_op_num(op_num) f = op(orm_field, value) query = query.filter(sql.not_(f) if negate else f) query = query.reset_joinpoint() return query, orm_field
def current_priority_expression(cls, collection, last_build): """ Return computed value for packages priority or None if package is not schedulable. :param: collection package's collection. It should either be the Collection class object, or it should be a concrete collection object if all packages have the same collection. This is done as an optimization to avoid adding join on collection table when the collection is known. :param: last_build package's last complete build. As with the previous argument, should be either Build class object or particular last complete build object. :returns: SQLA expression that, when evaluated in the DB, returns the priority """ # if last_build is concrete object, it may be None # packages with no last build should have no priority # (they cannot be scheduled) if not last_build: return null() dynamic_priority = cls.dependency_priority + cls.build_priority # compute time priority seconds = extract('EPOCH', func.clock_timestamp() - last_build.started) a, b = TIME_PRIORITY.inputs # avoid zero/negative values, when time difference too small log_arg = func.greatest(0.000001, seconds / 3600) dynamic_priority += func.greatest(a * func.log(log_arg) + b, -30) # dynamic priority is affected by coefficient dynamic_priority *= collection.priority_coefficient # manual and static priority are not affected by coefficient current_priority = cls.manual_priority + cls.static_priority + dynamic_priority return case( [ # handle unschedulable packages ( # WHEN blocked OR untracked cls.blocked | ~cls.tracked | # OR has running build (cls.last_complete_build_id != cls.last_build_id) | # OR is unresolved (cls.resolved == False) | # OR resolution is not yet done ((cls.resolved == None) & ~cls.skip_resolution) | # OR the collection's buildroot is broken (collection.latest_repo_resolved == False) | # OR the collection's buildroot wasn't resolved yet (collection.latest_repo_resolved == None), # THEN return NULL None, ) ], # ELSE return the computed priority else_=current_priority )
tdAccounts.acct_type.expression == "CC", acct_balance.expression ) ], else_=0).expression.label("cc_bal") ] acct = DataFrame.from_query(str(select(acct_select_query_column_projection).compile(compile_kwargs={"literal_binds": True}))) # Using to_pandas() for a cleaner display format: acct.to_pandas().head(10) # Next: Get the transaction information required for the aggregation. Pull out # the quarter the transaction was made. trans_select_query_column_projection = [tdTransactions.acct_nbr.expression, tdTransactions.principal_amt.expression, tdTransactions.interest_amt.expression, tdTransactions.tran_id.expression, tdTransactions.tran_date.expression, extract('month', tdTransactions.tran_date.expression).expression.label("acct_mon"), case_when( [ ( or_( text("acct_mon = '1'"), text("acct_mon = '2'"), text("acct_mon = '3'"), ), 1 ) ], else_=0).expression.label("q1_trans"), case_when( [ ( or_( text("acct_mon = '4'"), text("acct_mon = '5'"), text("acct_mon = '6'"), ), 1 )
def inbox(): is_information = request.args.get("is_information") #drop group_id; not used in IZ*ONE PM is_star = request.args.get("is_star", "0") is_unread = request.args.get("is_unread", "0") q = request.args.get("q", "") member_id = request.args.get("member_id", "0") # 0 for everyone user = get_user() page = request.args.get("page", "0") try: page = int(page) if page < 1: page = 1 except ValueError: page = 1 try: member_id = int(member_id) if member_id < 0 or 13 < member_id: member_id = 0 except ValueError: member_id = 0 mails = [] # Search query = user.mails if member_id == 0: query = query.filter(Mail.member_id < 13) else: query = query.filter(Mail.member_id == member_id) if q != "": search_query = parse_search_query(q) if "begin" in search_query: query = query.filter(Mail.datetime >= search_query["begin"]) if "end" in search_query: query = query.filter(Mail.datetime <= search_query["end"]) if "q" in search_query: query = query.filter( or_(Mail.subject.contains(search_query["q"]), Mail.content.contains(search_query["q"]))) if "today" in search_query: today = datetime.now() query = query.filter( and_( extract("month", Mail.datetime) == today.month, extract("day", Mail.datetime) == today.day)) if "reverse" in search_query and search_query["reverse"] == True: query = query.order_by(Mail.id.desc()) else: pasttoday = user.get_config("pasttoday") pasttoday = pasttoday.value if pasttoday else "0" if pasttoday == "1": today = datetime.now() query2 = query.filter( and_( extract("month", Mail.datetime) == today.month, extract("day", Mail.datetime) == today.day)) mails = query2.all() mails += query.all() if is_star != "0" and is_star != "false": mails = [m for m in mails if user.is_star(m.id)] if is_unread != "0" and is_star != "false": mails = [m for m in mails if not user.is_read(m.id)] total = len(mails) mails = mails[(page - 1) * 20:page * 20] result_obj = { "mail_count": len(mails), "page": page, "has_next_page": page * 20 < total, "unread_count": user.m_unreads[member_id] if member_id < 13 else 0, "star_count": user.m_stars[member_id] if member_id < 13 else 0, "mails": generate_mails(mails) } randompm = user.get_config("randompm") randompm = randompm.value if randompm else "0" if member_id == 0 and randompm == "1" and q == "" and ( is_star == "0" or is_star == "false") and (is_unread == "0" or is_unread == "false") and page == 1: result_obj["mail_count"] += 1 result_obj["mails"].insert(0, RANDOM_MAIL) return generate_json(result_obj)
def create_date_filter(self, column): if self.maybe_year: return extract('year', column) == int(self.term) if self.as_date: return column == self.as_date
def aligned_notification_time(cls, date): hour = extract('hour', cls.notification_time) minute = extract('minute', cls.notification_time) add_hours = func.printf('+%d hours', hour) add_minutes = func.printf('+%d minutes', minute) return func.datetime(date, add_hours, add_minutes)
def fetch_monthly_template_usage_for_service(start_date, end_date, service_id): # services_dao.replaces dao_fetch_monthly_historical_usage_by_template_for_service stats = db.session.query( FactNotificationStatus.template_id.label('template_id'), Template.name.label('name'), Template.template_type.label('template_type'), Template.is_precompiled_letter.label('is_precompiled_letter'), extract('month', FactNotificationStatus.bst_date).label('month'), extract('year', FactNotificationStatus.bst_date).label('year'), func.sum( FactNotificationStatus.notification_count).label('count')).join( Template, FactNotificationStatus.template_id == Template.id).filter( FactNotificationStatus.service_id == service_id, FactNotificationStatus.bst_date >= start_date, FactNotificationStatus.bst_date <= end_date, FactNotificationStatus.key_type != KEY_TYPE_TEST, FactNotificationStatus.notification_status != NOTIFICATION_CANCELLED, ).group_by( FactNotificationStatus.template_id, Template.name, Template.template_type, Template.is_precompiled_letter, extract('month', FactNotificationStatus.bst_date).label('month'), extract('year', FactNotificationStatus.bst_date).label('year'), ).order_by(extract('year', FactNotificationStatus.bst_date), extract('month', FactNotificationStatus.bst_date), Template.name) if start_date <= datetime.utcnow() <= end_date: today = get_london_midnight_in_utc(datetime.utcnow()) month = get_london_month_from_utc_column(Notification.created_at) stats_for_today = db.session.query( Notification.template_id.label('template_id'), Template.name.label('name'), Template.template_type.label('template_type'), Template.is_precompiled_letter.label('is_precompiled_letter'), extract('month', month).label('month'), extract('year', month).label('year'), func.count().label('count')).join( Template, Notification.template_id == Template.id, ).filter(Notification.created_at >= today, Notification.service_id == service_id, Notification.key_type != KEY_TYPE_TEST, Notification.status != NOTIFICATION_CANCELLED).group_by( Notification.template_id, Template.hidden, Template.name, Template.template_type, month) all_stats_table = stats.union_all(stats_for_today).subquery() query = db.session.query( all_stats_table.c.template_id, all_stats_table.c.name, all_stats_table.c.is_precompiled_letter, all_stats_table.c.template_type, func.cast(all_stats_table.c.month, Integer).label('month'), func.cast(all_stats_table.c.year, Integer).label('year'), func.cast(func.sum(all_stats_table.c.count), Integer).label('count'), ).group_by( all_stats_table.c.template_id, all_stats_table.c.name, all_stats_table.c.is_precompiled_letter, all_stats_table.c.template_type, all_stats_table.c.month, all_stats_table.c.year, ).order_by(all_stats_table.c.year, all_stats_table.c.month, all_stats_table.c.name) else: query = stats return query.all()
parser = ArgumentParser() parser.add_argument("-y", "--year", help="Year to process data for.") parser.add_argument("-m", "--month", help="Month to process data for.") parser.add_argument("-a", "--account_id", help="Account ID to process data for.") args = parser.parse_args() Session = sessionmaker(bind=get_db_engine()) session = Session() print( f"Clearing out data - account: {args.account_id}, year: {args.year}, month: {args.month}" ) session.query(Transaction).\ filter(extract('year', Transaction.ds) == args.year).\ filter(extract('month', Transaction.ds) == args.month).\ filter(Transaction.account_id == args.account_id).\ delete(synchronize_session=False) bank = re.search(r'(.*?)_(.*?)_(.*?)', args.account_id).group(1) print(f"Loading: {args.account_id}") csv_handler = get_csv_handler(bank=bank)(account_id=args.account_id, data_dir=os.path.join( SPENDY_DATA, args.year, args.month)) for transaction in csv_handler.process_csv(): session.add(transaction)
def xtract(label, expr): return extract('EPOCH', expr).label(label)
def get_contacts_by_birthday_day(address_book_id, day): return ResponseUtil.parse_collection( Session().query(Contact).join(AddressBook).filter( AddressBook.id == address_book_id).filter( extract('day', Contact.birthday) == day).all())