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)
