def get_fatalities_center(self, fatalities): centers = [] for center in self.session.query(func.avg(Event.longitud).label('longitud'),func.avg(Event.latitud).label('latitud')).filter(Event.fatalities > fatalities): centers.append(center.longitud) centers.append(center.latitud) if len(centers) == 0: return [23.992075732837595,4.755856763705008] return centers
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 daily_data_expr(flag_expr, value_expr, dry_elev): if dry_elev is None: dry_elev = -1000 flag_expr = expression.case([ (flag_expr == _M, _M), (flag_expr == None, _O), ], else_=_E) flag_expr = expression.case([ (func.avg(value_expr) == None, _M), (func.avg(value_expr) < dry_elev, _D), ], else_=func.max(flag_expr) ) return (flag_expr, func.avg(value_expr))
def statArtistReleaseCount(self): artistAverageCount = self.dbSession.query(func.avg(Artist.releases)) artistMinimumCount = self.dbSession.query(func.min(Artist.releases)).scalar() artistMaximumCount = self.dbSession.query(func.max(Artist.releases)).scalar() artistMaximumId = '' artistMaxName = '' return { 'title': 'Artist Release Count', 'class': 'fa-user', 'average': { 'type': 'string', 'value': artistAverageCount, 'detail': { 'text': '' } }, 'minimum': { 'type': 'string', 'value': artistMinimumCount, 'detail': { 'text': 'Many' } }, 'maximum': { 'type': 'artist', 'value': artistMaximumCount, 'detail': { 'id': artistMaximumId, 'thumbnailUrl': '/images/artist/thumbnail/' + artistMaximumId, 'detailUrl': '/artist/' + artistMaximumId, 'text': artistMaxName } } }
def _filter_stocks(self, stocks): """銘柄を絞り込みます。 絞り込み条件: - 過去の平均出来高が規定の値を上回っている。 Args: stocks: 絞り込み前の銘柄のリスト Returns: 絞り込み後の銘柄のリスト """ filtered_stocks = [] with start_session() as session: for s in stocks: avg_volume = session.query(func.avg(History.volume)) \ .filter_by(symbol=s.symbol).scalar() logger.debug('symbol - avg_volume: {} - {}'.format( s.symbol, avg_volume)) if avg_volume is not None and float(avg_volume) >= self.volume: # 過去の平均出来高が規定値を上回っている場合、絞り込み後のリストに追加します。 filtered_stocks.append(s) return filtered_stocks
def get_programme(programme_id): res = programmes.query.filter(programmes.id == programme_id).order_by(programmes.programme_name).all() for row in res: res_sub = projects.query.filter(projects.programme_id == row.id).order_by(projects.project_name).all() project_array = [] for row_sub in res_sub: average_points_res = sprints.query.with_entities(func.avg(sprints.delivered_points).label('average')).filter(sprints.project_id == row_sub.id).first() if len(average_points_res) > 0: if (average_points_res[0]): average_points = round(average_points_res[0]) else: average_points = 0 else: average_points = 0 sprint_res_sub = sprints.query.filter(sprints.project_id == row_sub.id).filter(sprints.start_date <= str(date.today())).filter(sprints.end_date >= str(date.today())).order_by(sprints.sprint_number.desc()).first() cur_sprint_number = '' cur_sprint_end_date = '' cur_sprint_rag = '' cur_sprint_id = '' if sprint_res_sub: cur_sprint_number = sprint_res_sub.sprint_number cur_sprint_end_date = str(sprint_res_sub.end_date) cur_sprint_rag = sprint_res_sub.sprint_rag cur_sprint_id = sprint_res_sub.id project_array.append({'name': row_sub.project_name, 'id': row_sub.id, 'product_owner': row_sub.product_owner, 'scrum_master': row_sub.scrum_master, 'last_rag': cur_sprint_rag, 'last_sprint': cur_sprint_number, 'last_end_date': cur_sprint_end_date, 'last_sprint_id': cur_sprint_id, 'project_description': row_sub.project_description, 'delivery_manager': row_sub.delivery_manager, 'scrum_tool_link': row_sub.scrum_tool_link, 'average_points': average_points}) res2 = {'name': row.programme_name, 'id': row.id, 'programme_manager': row.programme_manager, 'service_manager': row.service_manager, 'projects': project_array} return Response(json.dumps(res2), mimetype='application/json')
def st_avg(stid,sess = None): sess = sess or Session() #stud = Student.get_st(name,surname, sess) sid = stid #stud.id return sess.query(func.avg(Journal.grade)).\ filter(Journal.student_id==sid).\ scalar()
def move_import(xmlfile, filename, user, request_form): if filename.endswith('.gz'): xmlfile = gzip.GzipFile(fileobj=xmlfile, mode='rb', filename=filename) filename = filename[:-len('.gz')] extension = filename[-4:] import_functions = { '.xml': old_xml_import, '.sml': sml_import, '.gpx': gpx_import, } if extension not in import_functions: flash("unknown fileformat: '%s'" % xmlfile.name, 'error') return import_function = import_functions[extension] move = import_function(xmlfile, user, request_form) if move: move.temperature_avg, = db.session.query(func.avg(Sample.temperature)).filter(Sample.move == move, Sample.temperature > 0).one() stroke_count = 0 for events, in db.session.query(Sample.events).filter(Sample.move == move, Sample.events != None): if 'swimming' in events and events['swimming']['type'] == 'Stroke': stroke_count += 1 if 'swimming' in move.activity: assert stroke_count > 0 if stroke_count > 0: move.stroke_count = stroke_count db.session.commit() return move
def disc_avg(subjectid, sess = None): sess = sess or Session() #subj = Subjects.get_d(subject, sess) sid = subjectid #subj.id return sess.query(func.avg(Journal.grade)).\ filter(Journal.discipline_id==sid).\ scalar()
def _add_missing_mau(self, table_prefix, days_ago): # Locate beginning table tables = self._get_filtered_tables(table_prefix, days_ago+30) first_date = self._date_from_tablename(tables[0].name) grouped_monthly_unique = select([ monthly_rollup.c.date, func.sum(monthly_rollup.c.count).label("count") ]).\ where(monthly_rollup.c.date >= first_date).\ group_by(monthly_rollup.c.date).\ order_by(monthly_rollup.c.date) # Average them over 6 days prior inclding today for MAU's maus = select([ grouped_monthly_unique.c.date, func.avg(grouped_monthly_unique.c.count).over( order_by=text("date ROWS 6 PRECEDING") ).label("mau") ]).\ order_by(grouped_monthly_unique.c.date) results = self._conn.execute(maus).fetchall() if not results: return # Update appropriate rows stmt = daily_stats.update().\ where(daily_stats.c.date == bindparam("update_date")).\ values(mau=bindparam("mau")) self._conn.execute(stmt, [ {"update_date": x.date, "mau": x.mau} for x in results[-days_ago:] ])
def past(): d_type = request.form['data_type'] result = db.session.query(func.avg(Record.value)).filter(Record.data_type == d_type, Record.date >= datetime.utcnow() - timedelta(days=4)) if result[0][0] is not None: return json.dumps({'mean': int(result[0][0])}) #, '0day': datetime.utcnow(), '1day': datetime.utcnow() - timedelta(days=1), , '2day': datetime.utcnow() - timedelta(days=2), , '3day': datetime.utcnow() - timedelta(days=3) }, cls=DecimalEncoder) return jsonify(mean = 0)
def index(self): c.name = config['app_conf']['site_name'] c.title = 'Welcome' c.messages = [] c.session = Session() file_q = c.session.query(File) first_file = file_q.first() if first_file is None: return render('/derived/show/please_scan.html') c.repo_url = first_file.root c.total_size = c.session.query(func.sum(File.size)).one()[0] c.file_count = file_q.count() c.avg_size = c.session.query(func.avg(File.size)).one()[0] res = c.session.execute(select([File.ext, func.count(File.ext)]).group_by(File.ext).order_by(func.count(File.ext).desc())).fetchone() c.ext_string = res[0] c.ext_count = res[1] c.revision = c.session.query(func.max(Revision.id)).one()[0] c.asset_count = c.session.query(Asset).count() c.collection_count = c.session.query(Collection).count() return render('/derived/show/index.html')
def get_average_for_pitches(self, column, pitcher_id=None, pitch_type=None): query = self.session.query(func.avg(column)) if pitcher_id is not None: query = query.filter(entities.Pitch.pitcher == pitcher_id) if pitch_type is not None: query = query.filter(entities.Pitch.pitch_type.like(pitch_type)) return query.one()
def __call__(self, column_clause, cuboid=None): if cuboid and cuboid.fact_count_column is not None: count = func.sum(cuboid.fact_count_column) return case([(count == 0, 0)], else_=( func.sum(column_clause * cuboid.fact_count_column) / cast(count, types.Numeric))) return func.avg(column_clause)
def last_day(self): query = db.session.query( func.round(func.avg(Reading.temperature)), func.round(func.avg(Reading.humidity)), Reading.timestamp )\ .filter_by(channel=self.channel)\ .group_by(func.strftime('%H', Reading.timestamp))\ .order_by(Reading.timestamp.desc())\ .limit(30) return [ { "temperature": r[0], "humidity": r[1], "timestamp": r[2] } for r in query.all() ]
def get_courses(order_by='course_rating', order_direction=DESCENDING, limit=100, offset=0, **kwargs): numeric_columns = { 'course_rating', 'instructor_rating', 'workload' } all_columns = keys | numeric_columns | { 'grade' } exact_keys = { key for key in keys if key in kwargs and kwargs[key] != AVERAGE } group_keys = keys - kwargs.keys() order_by_name = order_by if (order_by in all_columns and kwargs.get(order_by, '') != AVERAGE) else 'course_rating' query = select( [courses.columns[key] for key in group_keys] + [type_coerce(func.avg(courses.columns[key]), Float).label(key) for key in numeric_columns] + [type_coerce(func.avg(case( { 'A': 4.0, 'B': 3.0, 'C': 2.0, 'NR': 0.0 }, value=courses.columns.grade, else_=0.0, )), Float).label('grade')] ).where( and_(*[courses.columns[key] == kwargs[key] for key in exact_keys]) ).group_by( *[courses.columns[key] for key in group_keys] ).order_by( desc(order_by_name) if order_direction == DESCENDING else asc(order_by_name) ).limit(min(100, max(1, limit))).offset(max(0, offset)) results = query.execute().fetchall() dict_result = [] for result in results: item = dict(result.items()) for key in exact_keys: item[key] = kwargs[key] grade = item['grade'] if grade >= 3.5: grade = 'A' elif grade >= 2.5: grade = 'B' elif grade >= 1.5: grade = 'C' else: grade = 'NR' item['grade'] = grade dict_result.append(item) return dict_result return query
def reviews(courseid, page = 1): if courseid < 1: abort(404) course= Course.query.get_or_404(courseid) reviews = Review.query.filter_by(course_id=course.id).order_by(Review.review_date.desc()).paginate(page, POST_PER_PAGE, False) avgrating = (db.session.query(func.avg(models.Review.rating)).filter_by(course_id = course.id)[0])[0] totalreviews= reviews.total return render_template('course-reviews-list.html', user = g.user, reviews= reviews, course =course, totalreviews=totalreviews, avgrating =avgrating)
def resolve_average_result(self, args, context, info): vote_cls = self.get_vote_class() voting_avg = self.db.query(func.avg(getattr(vote_cls, 'vote_value'))).filter_by( vote_spec_id=self.id, tombstone_date=None, idea_id=self.criterion_idea_id).first() # when there is no votes, query.first() equals (None,) avg = voting_avg[0] return avg
def analyze_day(day = "2014-08-22"): hour = func.cast(func.substr(trips.c.joreohitusaika_time,1,2), sa.Integer) cols = [trips.c.tulopysakki, hour, func.avg(trips.c.ohitusaika_ero).label('delay_avg')] new_conds = conditions new_conds.append(trips.c.tapahtumapaiva==day) conds = and_(*new_conds) groupcols = [trips.c.tulopysakki, hour] ts = run(cols, conds, groupcols, n_limit=None) save_multi_json(ts, "../site/hourly_stop_delays_%s.json" % day)
def get_avg_rating(self): avg_rating = db.session\ .query(func.avg(Comment.rating))\ .filter_by(user_id=self.id) if avg_rating[0][0] is None: avg = 0 else: avg = avg_rating[0][0] return "{0:.2f}".format(avg)
def main(): # Get db session. session = sa_session.get_session() # Clear cell tables for t in [sa_cell.cell_habitat_table, sa_cell.cell_table]: session.execute(t.delete()) session.commit() # For each type of cell... for cell_size in ["km100", "km1000"]: print >>sys.stderr, "Processing cells of size '%s'" % cell_size # Initialize list of cells. cells = [] # Get cell ids cell_id_attr = getattr(Habitat, "id_%s" % cell_size) cell_area = func.sum(geo_func.area(func.geography(Habitat.geom))).label("cell_area") cell_depth = func.avg(Habitat.z).label("cell_depth") cell_geom_wkb = geo_func.wkb(func.st_union(Habitat.geom).label("cell_geom")) cell_infos = session.query(cell_id_attr, cell_area, cell_depth, cell_geom_wkb).group_by(cell_id_attr).all() # For each id, create cell and assign habitats. print >>sys.stderr, "Creating cells" cell_counter = 0 for (cell_id, cell_area, cell_depth, cell_geom_wkb) in cell_infos: if (cell_counter % 1000) == 0: print >>sys.stderr, "%s..." % (cell_counter), cell_counter += 1 # Get cell's habitats. cell_habitats = session.query(Habitat).filter(cell_id_attr == cell_id).all() # Format cell's geometry. cell_geom = wkb.loads("%s" % cell_geom_wkb) if cell_geom.geom_type == "Polygon": cell_geom = MultiPolygon([(cell_geom.exterior.coords, cell_geom.interiors)]) cell = Cell( type=cell_size, type_id=cell_id, geom=cell_geom.wkt, area=cell_area, depth=cell_depth, habitats=cell_habitats, ) cells.append(cell) session.add_all(cells) session.commit()
def aggregates(): # Average difference in boarding/alighting boarding_avg = models.Stop.query.\ with_entities(func.avg(models.Stop.boarding).label('average')).all()[0][0] alighting_avg = models.Stop.query.\ with_entities(func.avg(models.Stop.alighting).label('average')).all()[0][0] boarding_sum = models.Stop.query.\ with_entities(func.sum(models.Stop.boarding).label('sum')).all()[0][0] alighting_sum = models.Stop.query.\ with_entities(func.sum(models.Stop.boarding).label('sum')).all()[0][0] diff = boarding_avg - alighting_avg print boarding_avg, alighting_avg, diff, boarding_sum, alighting_sum # routes = models.Route.query.all() # for route in routes: # avg_ridership_per_route = models.Stop.query.\ # with_entities(func.avg(models.Stop.alighting).label('average')).\ # filter(Stop.routes == route.id) # print avg_ridership_per_route # q = db.session.query(models.Route).join((models.Stop, models.Route.stops)) \ # .with_entities(func.avg(models.Stop.boarding).label('avg')).filter(models.Route == '24') routes = models.Route.query.all() avgs = [] for route in routes: b_sum = 0 a_sum = 0 for stop in route.stops: b_sum += stop.boarding a_sum += stop.alighting avgs.append((route, b_sum/len(route.stops.all()), a_sum/len(route.stops.all()))) return render_template("agg.html", title='Aggregates', aggs=[boarding_avg,alighting_avg, diff], route_avgs=avgs)
def get_averages(date): averagesKey = "averages-{}".format(date.day) averageData = cache.get(averagesKey) if averageData is None: averageData = [] averages = db.session.query(Data.time, func.avg(Data.data)).group_by(Data.time).all() for avg in averages: dt = datetime.combine(date, avg[0]) averageData.append({ 'time':dt.isoformat(), 'data':avg[1] }) cache.set(averagesKey, averageData, timeout=28800) # cache will last 8 hours return averageData
def get_avg_choice(vote_spec): vote_cls = vote_spec.get_vote_class() voting_avg = vote_spec.db.query(func.avg(getattr(vote_cls, 'vote_value'))).filter_by( vote_spec_id=vote_spec.id, tombstone_date=None, idea_id=vote_spec.criterion_idea_id).first() # when there is no votes, query.first() equals (None,) avg = voting_avg[0] if avg is None: return None return vote_spec.get_closest_choice(avg)
def reviews(courseid, page = 1): if courseid < 1: abort(404) course= Course.query.get_or_404(courseid) reviews = Review.query.filter_by(course_id=course.id).order_by(Review.review_date.desc()).paginate(page, POST_PER_PAGE_SHORT, False) avgrating = (db.session.query(func.avg(models.Review.rating)).filter_by(course_id = course.id)[0])[0] if avgrating ==None: avgrating=0 avgrating = "{0:.2f}".format(avgrating) totalreviews= reviews.total return render_template('course-reviews-list.html', user = g.user, reviews= reviews, course =course, totalreviews=totalreviews,\ avgrating =avgrating, title=course.course_name)
def _get_avg_duration(self, project, start_period, end_period): avg_duration = db.session.query( func.avg(Build.duration) ).filter( Build.project_id == project.id, Build.date_created >= start_period, Build.date_created < end_period, Build.status == Status.finished, Build.result == Result.passed, ).scalar() or None if avg_duration is not None: avg_duration = float(avg_duration) return avg_duration
def statistics_update(): try: redisapi.hset('sitestatus', 'managed_nodes', Nodedb.get_count()) redisapi.hset('sitestatus', 'system_capacity', session.query( func.sum(Nodedb.core).label('average')).all()[0][0]) redisapi.hset('sitestatus', 'system_utilization', json.dumps(session.query( Perf_System_Load.node_name, func.avg( Perf_System_Load.load_avg_fifteen).label('average') ).group_by('node_name').all())) redisapi.hset('sitestatus', 'user_count', User.get_count()) redisapi.hset('sitestatus', 'registered_master', Masterdb.get_count()) redisapi.hset('sitestatus', 'total_task', 0) redisapi.hset('sitestatus', 'service_level', json.dumps(session.query( Perf_Ping.node_name, func.avg( Perf_Ping.ping_packet_loss).label('average') ).group_by('node_name').all())) redisapi.hset('sitestatus', 'uptime', (datetime.utcnow() - session.query( Masterdb.create_at).first()[0]).days) except Exception as e: logger.warning('error in writing sitestatus ') return {'failed': e} logger.info('Completed in updating site status')
def get_project_stats(self, start_period, end_period): projects_by_id = dict((p.id, p) for p in self.projects) project_ids = projects_by_id.keys() # fetch overall build statistics per project query = db.session.query( Build.project_id, Build.result, func.count(Build.id).label('num'), func.avg(Build.duration).label('duration'), ).join( Source, Source.id == Build.source_id, ).filter( Source.patch_id == None, # NOQA Build.project_id.in_(project_ids), Build.status == Status.finished, Build.result.in_([Result.failed, Result.passed]), Build.date_created >= start_period, Build.date_created < end_period, ).group_by(Build.project_id, Build.result) project_results = {} for project in self.projects: project_results[project] = { 'total_builds': 0, 'green_builds': 0, 'green_percent': None, 'avg_duration': 0, 'link': build_uri('/projects/{0}/'.format(project.slug)), } for project_id, result, num_builds, duration in query: if duration is None: duration = 0 project = projects_by_id[project_id] if result == Result.passed: project_results[project]['avg_duration'] = duration project_results[project]['total_builds'] += num_builds if result == Result.passed: project_results[project]['green_builds'] += num_builds for project, stats in project_results.iteritems(): if stats['total_builds']: stats['green_percent'] = percent(stats['green_builds'], stats['total_builds']) else: stats['green_percent'] = None return project_results
def checks_avg(date_begin, date_end): try: time.strptime(date_begin, '%Y-%m-%d') time.strptime(date_end, '%Y-%m-%d') except ValueError: return "Unknown value" result = Mvc.query.with_entities(func.avg(Mvc.sumcom)).filter( Mvc.datedoc >= date_begin, Mvc.datedoc <= date_end, Mvc.tipdoc == 8, Mvc.kodskl == 1, Mvc.nomwork == 1 ).scalar() return str(result)
def TestRunsQuery(starttime, endtime, category): """Constructs the sqlalchemy query for fetching all pushes in the specified time interval. Input: starttime - start time, UNIX timestamp (in seconds) endtime - end time, UNIX timestamp (in seconds) category - filter by list of builder categories, if not spefified fetches all builder categories Output: query """ bldrs = meta.status_db_meta.tables['builders'] blds = meta.status_db_meta.tables['builds'] stps = meta.status_db_meta.tables['steps'] #construct an aliased subquery that grabs a list of test jobs with 'build' ids, buildernames, total jobtimes, and total actual testing time sq = subquery('a', [blds.c.id, bldrs.c.name.label("name"), func.timestampdiff(text('SECOND'), blds.c.starttime, blds.c.endtime).label("total"), func.sum(func.timestampdiff(text('SECOND'), stps.c.starttime, stps.c.endtime)).label("test")], and_(blds.c.id==stps.c.build_id, blds.c.builder_id==bldrs.c.id, #inner join on builds, builders and steps or_(stps.c.name.like(test_strings_partial), stps.c.name.in_(test_strings)), blds.c.result.in_((1, 0)), # only grab successful jobs not_(bldrs.c.name.in_(exclusions)), bldrs.c.category == category if category else bldrs.c.category.in_(ALL_BRANCHES), #filter by given category blds.c.endtime > blds.c.starttime, blds.c.starttime >= datetime.date.fromtimestamp(starttime).strftime("%Y%m%d"), #filter by daterange blds.c.endtime <= datetime.date.fromtimestamp(endtime).strftime("%Y%m%d"), func.timestampdiff(text('SECOND'), blds.c.starttime, blds.c.endtime) < 86400, # discard massive outliers func.timestampdiff(text('SECOND'), blds.c.starttime, blds.c.endtime) > 10, # jobs taking >1 day, <10s ), group_by=(stps.c.build_id), ) #aggregate data from subquery into a list of averages for each builder (buildername, average total job time, average actual testing time) q = select([sq.c.name, func.avg(sq.c.total).label("total"), func.avg(sq.c.test).label("test")], group_by=(sq.c.name), order_by=( (func.avg(sq.c.test)/func.avg(sq.c.total)).asc() ), ) return q
def success(): if request.method == 'POST': email = request.form["email_name"] height = request.form["height_name"] if db.session.query(Data).filter(Data.email_ == email).count() == 0: data = Data(email, height) db.session.add(data) db.session.commit() #Se ejecuta query en la base datos y se le agrega funcion scalar para que solo retorne el valor del query average_height = round( db.session.query(func.avg(Data.height_)).scalar(), 2) count = db.session.query(Data.height_).count() send_email(email, height, average_height, count) return render_template("success.html") return render_template( "index.html", text="Seems like we've got something from that email address already!")
def success(): if request.method == 'POST': email = request.form["email_name"] height = request.form["height_name"] print(email, height) if db.session.query(Data).filter(Data.email_ == email).count() == 0: data = Data(email, height) db.session.add(data) db.session.commit() average_height = db.session.query(func.avg(Data.height_)).scalar() average_height = round(average_height, 1) count = db.session.query(Data.height_).count() #send_email(email, height, average_height, count) #print(average_height) return render_template("success.html") return render_template( 'index.html', text="Seems like we got something from that email once!")
def success(): if request.method == 'POST': email = request.form["email_name"] height = request.form["height_name"] if db.session.query(Data).filter(Data.email_ == email).count() == 0: data = Data(email, height) db.session.add(data) db.session.commit() average_height = db.session.query(func.avg(Data.height_)).scalar() average_height = round(average_height, 1) count = db.session.query(Data.height_).count() send_email(email, height, average_height, count) return render_template("success.html") return render_template( "index.html", text="Seems like email address already present!")
def parseMatch(data, filteredGameList, filteredPlayerList, defaultElo): match = Match() match_ = data['match'] match.id = match_['id'] match.name = match_['name'] try: match.start_time = datetime.strptime( match_['start_time'], "%Y-%m-%dT%H:%M:%S%z") + timedelta( hours=5, minutes=30) # To convert utc to ist except: match.start_time = datetime.strptime("1900-01-01T00:00:00", "%Y-%m-%dT%H:%M:%S") try: match.end_time = datetime.strptime( match_['end_time'], "%Y-%m-%dT%H:%M:%S%z") + timedelta( hours=5, minutes=30) # To convert utc to ist except: match.end_time = datetime.strptime("2100-12-12T23:59:59", "%Y-%m-%dT%H:%M:%S") users = data['users'] events = data['events'] playerList = parsePlayers(users, filteredPlayerList, defaultElo) gameList, beatmapList = parseGames(events, match.id, filteredGameList, filteredPlayerList) match.players = playerList match.games = gameList if len(beatmapList) > 0: db.session.add_all(beatmapList) db.session.add(match) for game in match.games: ''' calculate average player rating for each game, maybe optimization is needed here? ''' player_ids = [ x[0] for x in db.session.query(Score.player_id).filter( Score.game_id == game.id).all() ] z = Player.query.with_entities( func.avg(Player.elo).label('average')).filter( Player.id.in_(player_ids)).one() #logger.debug(f'average elo : {z}') game.avg_elo = z.average db.session.add(game) db.session.commit()
def get_attrs(self, item_list): apps = { a.id: a for a in App.query.filter( App.id.in_(set(i.app_id for i in item_list)), ) } tasks = { t.id: t for t in Task.query.filter( Task.id.in_(set(i.task_id for i in item_list)), ) } estimatedDurations = dict( db.session.query( Task.app_id, func.avg(Task.date_finished - Task.date_started), ).filter( Task.date_finished > datetime.utcnow() - timedelta(days=7), Task.status == TaskStatus.finished, ).group_by(Task.app_id)) user_ids = set(tasks[d.task_id].user_id for d in item_list) if user_ids: user_map = { u.id: u for u in User.query.filter(User.id.in_(user_ids)) } else: user_map = {} attrs = {} for item in item_list: estimatedDuration = estimatedDurations.get( tasks[item.task_id].app_id) if estimatedDuration: estimatedDuration = estimatedDuration.total_seconds() attrs[item] = { 'app': apps[item.app_id], 'task': tasks[item.task_id], 'user': user_map.get(tasks[item.task_id].user_id), 'estimatedDuration': estimatedDuration, } return attrs
def success(): if request.method == 'POST': email = request.form["email_name"] height = request.form["height_name"] if db.session.query(Data).filter(Data.email_ == email).count() == 0: data = Data(email, height) db.session.add(data) db.session.commit() average_height = db.session.query(func.avg(Data.height_)).scalar() average_height = round(average_height, 2) # round upto 2 decimal points count = db.session.query(Data.height_).count() send_email(email, height, average_height, count) return render_template('success.html') return render_template( 'index.html', text="Seems like we do have that mail<br> address already!")
def success(): if request.method == 'POST': email = request.form["email_name"] height = request.form["height_name"] # finding if email already existed in db if db.session.query(Data).filter(Data.email_ == email).count() == 0: data = Data(email, height) db.session.add(data) db.session.commit() # calculate mean of height by applying func.avg, and extract scalar value average_height = db.session.query(func.avg(Data.height_)).scalar() average_height = round(average_height, 2) count = db.session.query(Data.height_).count() send_email(email, height, average_height, count) print(average_height) return render_template("success.html") return render_template('index.html', text="That email has already existed")
def query_avg_price_period(session, fuel_name, day_from=None, day_to=None) -> Query: day_from, day_to = get_period(day_from, day_to) result = session.query( func.avg(Price.price).label("average_price"), func.date(Price.date_of_price).label("date_of_price"), Fuel.fuel_type, FuelCompany.fuel_company_name).join(Fuel).join(GasStation).join( FuelCompany, FuelCompany.id == GasStation.fuel_company_id).filter( Fuel.fuel_type == fuel_name).filter( func.date(Price.date_of_price).between( day_from, day_to)).group_by( Fuel.fuel_type, func.date(Price.date_of_price), FuelCompany.fuel_company_name).order_by( func.date(Price.date_of_price).desc(), FuelCompany.fuel_company_name) return result
def showallreviews(): reviewresults = [] reviewtitles = [] reviews = Review.query.all() for i in reviews: if i.Title not in reviewtitles: reviewtitles.append(i.Title) for i in reviews: title = i.Title if title in reviewtitles: i.Rating = Review.query.with_entities(func.avg(Review.Rating).label('average')).filter(Review.Title == title).scalar() reviewresults.append({'name' : i.Title, 'rating' : i.Rating }) reviewtitles.remove(title) response = jsonify(reviewresults) return response
def success(): if request.method == 'POST': email = request.form["email-name"] height = request.form["height-name"] if db.session.query(Data).filter(Data.email_==email).count() == 0: data = Data(email, height) db.session.add(data) db.session.commit() average_height = db.session.query(func.avg(Data.height_)).scalar() average_height = round(average_height,2) count = db.session.query(Data.height_).count() send_email(email, height, average_height, count) return render_template('success.html') return render_template('index.html', text = "you have already submitted a query from this tempalte")
def extractedIonChromatogram(self, molid): """Returns extracted ion chromatogram of molecule with id molid""" chromatogram = [] mzqq = self.session.query(func.avg(Fragment.mz)) mzqq = mzqq.filter(Fragment.molid == molid) mzqq = mzqq.filter(Fragment.parentfragid == 0) mzq = mzqq.scalar() precision = 1 + self.session.query(Run.mz_precision).scalar() / 1e6 # fetch max intensity of peaks with mz = mzq+-mzoffset q = self.session.query(Scan.rt, func.max(Peak.intensity)) q = q.outerjoin( Peak, and_(Peak.scanid == Scan.scanid, Peak.mz.between(mzq / precision, mzq * precision))) q = q.filter(Scan.mslevel == 1) for (rt, intens) in q.group_by(Scan.rt).order_by(asc(Scan.rt)): chromatogram.append({'rt': rt, 'intensity': intens or 0}) return chromatogram
def book_api(isbn): if request.method == 'GET': book = db.session.query(Book).filter(Book.isbn == isbn).first() if book is None: return jsonify({"error": "Invalid isbn"}), 404 reviews = db.session.query(func.count(Review.rating).label('count'), func.avg(Review.rating).label('average'))\ .filter(Review.book_id == book.book_id)\ .all() return jsonify({ "title": book.title, "author": book.author, "year": book.year, "isbn": book.isbn, "review_count": reviews[0][0], "average_score": round(reviews[0][1], 1) })
def success(): if request.method == 'POST': email = request.form["email_name"] height = request.form["height_name"] if db.session.query(Data).filter(Data.email == email).count() == 0: data = Data(email, height) db.session.add(data) db.session.commit() avg_height = db.session.query(func.avg(Data.height)).scalar() avg_height = round(avg_height, 1) count = db.session.query(Data.height).count() send_email(email, height, avg_height, count) return render_template("success.html") return render_template( 'index.html', text= "Sorry. It looks like we've got something from that email address already. Please try with another email." )
def success(): if request.method == 'POST': email = request.form["email_name"] height = request.form["height_name"] if db.session.query(Data).filter(Data.email_ == email).count() == 0: d = Data(email, height) db.session.add(d) db.session.commit() average_height = db.session.query(func.avg(Data.height_)).scalar() average_height = round(average_height, 1) count = db.session.query(Data.height_).count() #send_email(email,height,average_height,count) return render_template("success.html") return render_template( "index.html", text="This email address is already registered please enter a new one!!" )
def success(): if request.method == 'POST': email = request.form['email_data'] height = request.form['height_data'] print(email, height) print(db.session.query(Data).filter(Data.email_ == email).count()) if not db.session.query(Data).filter(Data.email_ == email).count(): reg = Data(email, height) db.session.add(reg) db.session.commit() average_height = db.session.query(func.avg(Data.height_)).scalar() average_height = round(average_height, 1) count = db.session.query(Data).count() send_email(email, height, average_height, count) return render_template('success.html') else: return render_template( 'index.html', text="The Entered Email Has Already Been Used!!")
def success(): if request.method == 'POST': email = request.form["email_name"] height = request.form["height_name"] if db.session.query(Data).filter(Data.email_ == email).count() == 0: data = Data(email, height) db.session.add(data) db.session.commit() average_height = db.session.query(func.avg(Data.height_)).scalar() average_height = round(average_height, 1) count = db.session.query(Data.height_).count() send_email(email, height, count, average_height) return render_template("success.html") return render_template( 'index.html', text= "That email address has already been used. Please submit a different email address." )
def success(): if request.method == 'POST': email = request.form["email_name"] height = request.form["height"] if db.session.query(Data).filter(Data.email == email).count() == 0: data = Data(email, height) #Creating instance of the class Data db.session.add(data) db.session.commit() #Session class average_height = db.session.query(func.avg(Data.height)).scalar( ) #Scalar is used to extract the scalar value that is a number average_height = round(average_height, 1) count = db.session.query(Data.height).count send_email(email, height, average_height, count) return render_template("success.html") return render_template( 'index.html', text="Seems like we have got something from that email address already" )
def success(): if request.method=='POST': email = request.form["email_name"] height = request.form["height_name"] if db.session.query(Data).filter(Data.email_==email).count() == 0: data = Data(email, height) db.session.add(data) db.session.commit() average = db.session.query(func.avg(Data.height_)).scalar() average = round(average, 1) count = db.session.query(Data.height_).count() send(email, height, average, count) return render_template("success.html") else: # return render_template("index.html") flash("Seems like your email is already registered !!!", "info") return redirect(url_for("index"))
def success(): if request.method == 'POST': email = request.form['email'] height = request.form['height'] if not db.session.query(Data).filter(Data.username == email).count(): data = Data(email, height) db.session.add(data) db.session.commit() av_height = db.session.query(func.avg(Data.height)).scalar() av_height = round(av_height, 1) count = db.session.query(Data.height).count() send_email(email, av_height, count) return render_template( 'success.html', message='Thank you for submission. You will recieve e-mail ' 'shortly<br>') return render_template( 'index.html', error='Email already exists. Enter another email<br>')
def chart2(): time = int(request.form['time']) res = [ dict(zip(result.keys(), result)) for result in db.session.query( Air.Id.label('id'), func.avg(Air.AQI).label('aqi'), func.max(Air.AQI).label('max')).filter( Air.Time % 100 == time).group_by(Air.Station).all() ] for one in res: maxdate = [ dict(zip(result.keys(), result)) for result in db.session.query(Air.Time).filter( Air.Time % 100 == time, Air.Id == one['id'], Air.AQI == one['max']).all() ] one['Time'] = maxdate[0]['Time'] return jsonify(res)
def done(): if request.method == "POST": input_email = request.form["input_email"] input_salary = request.form["input_salary"] if db.session.query(Data).filter( Data.email_ == input_email).count() == 0: data = Data(input_email, input_salary) db.session.add(data) db.session.commit() average_salary = db.session.query(func.avg(Data.salary_)).scalar() # send_email(input_email, input_salary, average_salary) return render_template("success.html") else: return render_template( "index.html", text= "Seems like you already have something from that email address" )
def success(): if request.method == 'POST': username = request.form['username'] email = request.form['email_name'] height = request.form['height_name'] if db.session.query(Data).filter(Data.email_ == email).count() == 0: data = Data(username, email, height) db.session.add(data) db.session.commit() average_height = db.session.query(func.avg(Data.height_)).scalar() average_height = round(average_height, 1) count = db.session.query(Data.height_).count() send_email(username, email, height, average_height, count) return render_template("success.html") return render_template( "index.html", text="Looks like this email address already exists in our database" )
def get_tasks(id): try: line_of_business = models.LineOfBusiness.query.filter_by(id=id) average_premium=models.Policy.query.with_entities(func.avg(models.Policy.incurred)).filter_by(line_of_business=id). \ filter(models.Policy.incurred>=loss_claim).scalar() num_safe = lambda x: int(average_premium ) if average_premium is not None else 0 return jsonify(["line_of_business"], [e.serialize() for e in line_of_business], [{ "average premium": num_safe(average_premium) }]) except Exception as e: return (str(e))
def success(): if request.method == 'POST': email = request.form['email_name'] height = request.form['height_name'] # print(email,height) if db.session.query(Data).filter(Data.email == email).count() == 0: data = Data(email, height) db.session.add(data) db.session.commit() average_height = db.session.query(func.avg(Data.height)).scalar() average_height = round(average_height, 1) count = db.session.query(Data.height).count() send_email(email, height, average_height, count) return render_template("success.html") return render_template( 'index.html', text="seems like we've something from that email address already!")
def showBeer(country_id, region_id, brewery_id): country = session.query(Country).filter_by(id=country_id).one() region = session.query(Region).filter_by(id=region_id).one() brewery = session.query(Brewery).filter_by(id=brewery_id).one() brewery_creator = getUserInfo(brewery.user_id) beers = session.query(Beer).filter_by(brewery_id=brewery_id).all() if beers: beer_creator = [] num_ratings = [] avg_stars = [] for beer in beers: # Get the creator of the beer and add it to the beer_creator list creator = getUserInfo(beer.user_id).name beer_creator.append(creator) # Get the number of rating per beer and add it to the num_ratings # list ratings = session.query( Rating.num_of_stars).filter_by(beer_id=beer.id).count() num_ratings.append(ratings) # Get the average number of stars per beer and add it to the # avg_stars list stars = session.query(func.avg( Rating.num_of_stars)).filter_by(beer_id=beer.id).all()[0][0] if stars == None: stars = 0 avg_stars.append(stars) return render_template('beer.html', country=country, region=region, brewery=brewery, beers=beers, brewery_creator=brewery_creator, beer_creator=beer_creator, num_ratings=num_ratings, avg_stars=avg_stars) else: return render_template('beer.html', country=country, region=region, brewery=brewery, beers="", brewery_creator=brewery_creator, beer_creator="")
def avg(): movies = Movie.query.all() mylist = [] i = 0 for movie in movies: averageRate = db.session.query(func.avg( Rate.rate).label('average')).filter( Rate.movieid == movie.id).first() if averageRate[0] is not None: mylist.append([movie, averageRate[0]]) def getKey(item): return item[1] mylist.sort(key=getKey, reverse=True) return render_template('index2.html', mylist=mylist)
def success(): if request.method == 'POST': email = request.form["email_name"] height = request.form["height_name"] #to be able to add rows with SQLALchemy we should point to the SQLAlchemy object #which is db in this case. if db.session.query(Data).filter(Data.email_ == email).count( ) == 0: #this line says we input the email only if its unique and not available in db data = Data(email, height) # create object instance of Data class db.session.add(data) db.session.commit() average_height = db.session.query(func.avg(Data.height_)).scalar() average_height = round(average_height, 1) count = db.session.query(Data.height_).count() send_email(email, height, average_height, count) return render_template("success.html") return render_template( "index.html", text="Seems like we've got something from that email address already!")
def success(): if request.method == 'POST': # grab the email input email = request.form["email_name"] height = request.form["height_name"] # If an email address has already been used for the survey if db.session.query(Data).filter(Data.email_ == email).count() == 0: data = Data(email, height) db.session.add(data) db.session.commit() average_height = db.session.query(func.avg(Data.height_)).scalar() average_height = round(average_height, 1) count = db.session.query(Data.height_).count() send_email(email, height, average_height, count) return render_template("success.html") return render_template( "index.html", text="Seems like we've got something from that email address already!")
def average_rates_query(date_from, date_to, origin, destination): destination_port_subquery = _prepare_port_subquery(destination, 'dest') origin_port_subquery = _prepare_port_subquery(origin, 'org') query = db.session.query(t_prices.c.day, func.avg(t_prices.c.price).label('average')) query = query.filter( and_(t_prices.c.day <= date_to, t_prices.c.day >= date_from)) query = query.filter( or_(origin == t_prices.c.orig_code, t_prices.c.orig_code.in_(origin_port_subquery))) query = query.filter( or_(destination == t_prices.c.dest_code, t_prices.c.dest_code.in_(destination_port_subquery))) query = query.group_by(t_prices.c.day) return query
def artwork(artwork_id): artwork = Artwork.query.filter_by(id=artwork_id).first() if artwork is None: return abort(404) artwork_comments = Comment.query.filter_by(artwork_id=artwork_id).order_by( Comment.date.desc()).all() avg = db.session.query(func.avg(Grade.grade).label('average')).filter_by( artwork_id=artwork_id).first() average = avg[0] if average is None: average_grade = 0.0 else: average_grade = round(average, 1) if g.user.is_authenticated: favourited = Favourite.query.filter_by(user_id=g.user.id, artwork_id=artwork_id).first() rated = Grade.query.filter_by(user_id=g.user.id).filter_by( artwork_id=artwork_id).first() if rated is not None: rated_grade = round(float(rated.grade), 1) return render_template('artwork.html', artwork_id=artwork_id, artwork_comments=artwork_comments, artwork=artwork, average_grade=average_grade, favourited=favourited, rated=rated, rated_grade=rated_grade) if rated is None: return render_template('artwork.html', artwork_id=artwork_id, artwork_comments=artwork_comments, artwork=artwork, average_grade=average_grade, favourited=favourited, rated=rated) else: return render_template('artwork.html', artwork_id=artwork_id, artwork_comments=artwork_comments, artwork=artwork, average_grade=average_grade)