def get_todays_electricity():
return Electricity.query.with_entities(
(func.max(Electricity.meter_280) - func.min(Electricity.meter_280)).label(
'todays_export'), (func.max(Electricity.meter_180) - func.min(Electricity.meter_180)).label(
'todays_import')).filter(
func.strftime('%Y-%m-%d', Electricity.created_at) == datetime.now().strftime('%Y-%m-%d')).group_by(
func.strftime('%Y-%m-%d', Electricity.created_at)).first()
def get_last_n_days_import(n):
return (Electricity.query.
with_entities(func.strftime('%Y-%m-%dT00:00:00', Electricity.created_at).label('created_at'),
(func.max(Electricity.meter_180) - func.min(Electricity.meter_180))
.label('electricity_import'))
.filter(Electricity.created_at > (datetime.now() - timedelta(days=n)))
.group_by(func.strftime('%Y-%m-%d', Electricity.created_at))
.all())
def year_report_by_month_json(year):
by_year = func.strftime('%Y', Item.date) == year
expenses = db.session.query(func.sum(Item.amount), func.strftime('%Y-%m', Item.date)).filter(by_year).filter(Item.is_expense == True).group_by(func.strftime('%Y-%m', Item.date)).all()
cols = [{'id':'', 'label':'Month', 'type':'string'}, {'id':'', 'label':'Expense', 'type':'number'}]
rows = []
for (expense, month) in expenses:
rows.append({'c': [{'v': month}, {'v': expense}]})
return jsonify(cols=cols, rows=rows)
def get_yearly_data(year):
"""
:param year: year of the data
:return: returns an array of monthly energy for a given year
"""
return PVData.query.with_entities((func.max(PVData.total_energy) - func.min(PVData.total_energy)).label(
'total_energy')).filter(
func.strftime('%Y', PVData.created_at) == str(year)).group_by(
func.strftime('%Y-%m', PVData.created_at)).all()
def stravenky(cls,month,card_number):
narok=0
form = db.session.query( func.strftime('%Y-%m-%d', cls.time).label("date"),func.max(func.strftime('%H:%M', cls.time)).label("Max"),\
func.min(func.strftime('%H:%M', cls.time)).label("Min"),( func.max(cls.time) - func.min(cls.time)).label("Rozdil"))\
.filter(func.strftime('%Y-%m', cls.time) == month).filter(cls.card_number == card_number).group_by(func.strftime('%Y-%m-%d', cls.time)).all()
for n in form:
if n.rozdil >= 3:
narok = narok + 1
return narok
def get_yearly_series():
"""Returns a list of yearly generated energy for past years
:return: list of yearly generated energy for past years
"""
return (PVData.query
.with_entities(func.strftime('%Y', PVData.created_at).label('year'),
(func.max(PVData.total_energy) - func.min(PVData.total_energy)).label('yearly_output'))
.group_by(func.strftime('%Y', PVData.created_at))
.all())
def tbl_insdata(od, do):
# data = db.session.query( func.strftime('%Y-%m', Card.time).label("time")).filter_by(card_number=current_user.card_number).group_by(func.strftime('%Y-%m', Card.time))
if od == 0 and do == 0:
data = db.session.query(Card.id, Card.card_number, func.strftime('%Y-%m', Card.time).label("time")).all()
else:
data = db.session.query(Card.id, Card.card_number, func.strftime('%Y-%m', Card.time).label("time")).slice(od,
do)
pole = ['id', 'time', 'card_number']
result = [{col: getattr(d, col) for col in pole} for d in data]
return jsonify(data=result)
def get_daily_pellets_consumption_last_n_days(n=30):
"""
Returns an array with the daily pellets consumption for the last n days.
"""
end_date = datetime.utcnow()
start_date = (end_date - timedelta(days=n)).strftime('%Y-%m-%d')
return Kessel.query.with_entities((func.max(Kessel.pellets_total) - func.min(Kessel.pellets_total)).label('pellets_consumption'),
func.strftime('%Y-%m-%d', Kessel.created_at).label('created_at')).filter(
Kessel.created_at >= start_date).filter(
Kessel.created_at <= end_date).group_by(func.strftime('%Y-%m-%d', Kessel.created_at)).all()
def get_daily_operating_hours_last_n_days(n=30):
"""
Returns an array with the daily operating hours for the last n days.
"""
end_date = datetime.utcnow()
start_date = (end_date - timedelta(days=n)).strftime('%Y-%m-%d')
return Kessel.query.with_entities((func.max(Kessel.operating_hours) - func.min(Kessel.operating_hours)).label('operating_hours'),
func.strftime('%Y-%m-%d', Kessel.created_at).label('created_at')).filter(
Kessel.created_at >= start_date).filter(
Kessel.created_at <= end_date).group_by(func.strftime('%Y-%m-%d', Kessel.created_at)).all()
def mesicni_vypis_alluser(mesic):
# form=Card.find_by_number(current_user.card_number)
# form = db.session.query(Card.time).filter_by(card_number=current_user.card_number)
form = db.session.query(func.strftime('%Y-%m-%d', Card.time).label("date"),
func.max(func.strftime('%H:%M', Card.time)).label("Max"), \
func.min(func.strftime('%H:%M', Card.time)).label("Min"),
(func.max(Card.time) - func.min(Card.time)).label("Rozdil")) \
.filter(func.strftime('%Y-%-m', Card.time) == mesic).group_by(func.strftime('%Y-%m-%d', Card.time))
# .group_by([func.day(Card.time)])
return render_template("auth/mesicni_vypisy.tmpl", form=form, user=current_user)
def get_last_n_days(n):
"""Returns a list of daily yields
:param n: number of last days
:return: list of daily yields
"""
return (PVData.query.
with_entities(func.strftime('%Y-%m-%dT00:00:00', PVData.created_at).label('created_at'),
func.max(PVData.daily_energy).label('daily_energy'))
.filter(PVData.created_at > (datetime.now() - timedelta(days=n)))
.group_by(func.strftime('%Y-%m-%d', PVData.created_at))
.all())
def monthly_hours(self):
qs = (
Worktime.query
.with_entities(func.strftime('%Y-%m', Worktime.day).label('month'),
func.sum(Worktime.hours))
.filter_by(project=self)
.group_by(func.strftime('%Y-%m', Worktime.day))
.all()
)
qs.sort(key=lambda a: a[0], reverse=True)
return qs
def get_7_day_max_energy_series(current_date):
"""
:param current_date: pivot date of the energy series
:return: theoretical maximum energy series ± 3 days around current date
"""
return PVData.query.with_entities(func.strftime('%H:%M:00', PVData.created_at).label('pvdata_created_at'),
func.max(PVData.current_power).label('pv_max')).filter(
PVData.created_at >= (current_date - timedelta(days=3)).strftime('%Y-%m-%d')).filter(
PVData.created_at <= (current_date + timedelta(days=3)).strftime('%Y-%m-%d')).filter(
PVData.current_power > 0).group_by(
func.strftime('%H:%M:00', PVData.created_at)).all()
def report():
year = func.strftime('%Y', Item.date)
years = db.session.query(year).group_by(year).all()
month = func.strftime('%Y-%m', Item.date)
months = db.session.query(month).group_by(month).all()
results = []
for m in months:
tokens = m[0].split('-')
results.append((tokens[0], tokens[1]))
return render_template('report.html', years=years, months=results)
def example1():
"""
func.strftime practice:
seleft dob that month = day (in this case, dob = Jan 1th)
"""
result = ses.query(Student.student_id, Student.dob) \
.filter(func.strftime("%m", Student.dob) == func.strftime("%d", Student.dob)) \
.all()
for row in result:
print(row)
def main():
session = create_session()
date_format = '%Y-%m-%W'
results = session.query(
func.strftime(date_format, Message.sent),
func.sum(Message.polarity),
func.count(Message.id),
func.strftime('%m-%d-%Y', Message.sent),
).join(Contact).filter(
and_(
Contact.name == "Me",
Message.polarity != 0,
Message.subjectivity >= .9,
)
).group_by(
func.strftime(date_format, Message.sent),
).order_by(
Message.sent.asc()
).all()
# width = .35
axis_every = 10 * 1
x_axis_labels = [
x[1][3] for x in enumerate(results) if x[0] % axis_every == 0]
x_axis = [x[0] for x in enumerate(results)]
y_axis = [x[1]/x[2] for x in results]
colors = map(lambda v: {True: 'g', False: 'r'}[v > 0], y_axis)
#y_axis = map(abs, y_axis)
print "Samples: {}".format(len(x_axis))
plt.rcParams["figure.figsize"][0] = 21.0
plt.rcParams["figure.figsize"][1] = 9.0
plt.rcParams["figure.autolayout"] = True
plt.bar(x_axis, y_axis, linewidth=0, color=colors)
plt.grid(b=True, which='major', linestyle='-')
plt.title('Positive or Negative calculated sentiment of sent text messages per week')
plt.ylabel("Calculated percentage of sentiment per message")
plt.xlabel("Date")
plt.xticks(
np.arange(min(x_axis), max(x_axis), axis_every),
x_axis_labels,
rotation=35,
horizontalalignment='center',
)
plt.xlim([min(x_axis) - 0.5, max(x_axis) + 0.5])
plt.ylim([min(y_axis) - .1, max(y_axis) + .1])
print plt.rcParams["figure.figsize"]
plt.savefig('output.png')
def account_expenses(account, period):
''' total amount expendited during that period '''
total = session.query(func.sum(Operation.amount))\
.filter_by(account=account)\
.filter(func.strftime('%Y-%m-%d', Operation.invoice_date)\
> period.start_on.strftime('%Y-%m-%d'))\
.filter(func.strftime('%Y-%m-%d', Operation.invoice_date)\
< period.end_on.strftime('%Y-%m-%d'))\
.scalar()
if total:
return total
else:
return 0
def list_events(self):
out = []
session = self.session_mgr()
for row in session.query(
HALDB.Event.type, HALDB.Event.src, HALDB.Event.msg, HALDB.Event.severity,
func.strftime('%Y-%m-%d', HALDB.Event.created_on), func.count()) \
.group_by(HALDB.Event.type, HALDB.Event.src, HALDB.Event.msg, HALDB.Event.severity,
func.strftime('%Y-%m-%d', HALDB.Event.created_on)) \
.having(func.count() > 0) \
.order_by(desc(func.strftime('%Y-%m-%d', HALDB.Event.created_on))) \
.all():
out.append({'source': row[1], 'type': row[0], 'msg': row[2], 'count': row[5],
'severity': row[3], 'period': row[4]})
self.session_mgr.remove()
return out
def monthly_list_by_category(year, month, category):
c = Category.query.filter_by(name=category).first()
by_month = func.strftime('%Y-%m', Item.date) == '%s-%s' % (year, month)
items = db.session.query(Item).filter(Item.is_expense == True).filter(by_month).filter_by(category=c).all()
expense = db.session.query(func.sum(Item.amount)).filter(Item.is_expense == True).filter(by_month).first()[0]
return render_template('monthly_list_by_category.html', items=items, year=year, month=month, category=category, total=expense)
def check_session(username, key):
"""Check a username and key session data combination."""
stmt = select([
users.c.username,
func.sum(
func.strftime('%s', 'now') - sessions.c.change
).label("last_check")
]).select_from(
sessions.join(users, users.c.id == sessions.c.user)
).where(
sessions.c.key == key
)
conn = engine.connect()
result = conn.execute(stmt)
row = result.fetchone()
conn.close()
if row is None or username != row['username']:
return False, False
if row['last_check'] > config.SESSION_EXPIRE:
destroy_session(username, key)
return True, False
update_session(username, key)
return True, True
def now_playing():
if engine.name == 'sqlite':
query = User.query.join(Track).filter(func.strftime('%s', now()) - func.strftime('%s', User.last_play_date) < Track.duration * 2)
elif engine.name == 'postgresql':
query = User.query.join(Track).filter(func.date_part('epoch', func.now() - User.last_play_date) < Track.duration * 2)
else:
query = User.query.join(Track).filter(func.timediff(func.now(), User.last_play_date) < Track.duration * 2)
return request.formatter({
'nowPlaying': {
'entry': [ dict(
u.last_play.as_subsonic_child(request.user).items() +
{ 'username': u.name, 'minutesAgo': (now() - u.last_play_date).seconds / 60, 'playerId': 0 }.items()
) for u in query ]
}
})
def filtering_query(query, args):
model = Transaction
if args['date_start'] and args['date_end']:
query = query.filter(model.date >= args['date_start'])
query = query.filter(model.date <= args['date_end'])
else:
month = args['month'] or datetime.today().strftime('%Y-%m')
query = query.filter(
sql_func.strftime('%Y-%m', model.date) == month
)
if args['negative'] == 't':
query = query.filter(model.cost < 0)
if args['positive'] == 't':
query = query.filter(model.cost > 0)
if args['cost_le']:
query = query.filter(model.cost <= args['cost_le'])
if args['cost_ge']:
query = query.filter(model.cost >= args['cost_ge'])
if args['text']:
query = query.filter(or_(
model.main_line.like('%%%s%%' % word.replace('%', r'\%'))
for word in args['text'].upper().split()
))
if args['ordering']:
query = query.order_by(*args['ordering'].split(','))
else:
query = query.order_by(model.date)
return query
def find_player(self, soft_match=False, **kwargs):
id_clauses = [ (getattr(Player, k) == kwargs[k])
for k in Player.id_fields()
if (k in kwargs and kwargs[k] != '' and kwargs[k] is not None) ]
matches = []
# first try to match just on id clauses
if len(id_clauses) > 0:
matches = self.query(Player).filter(or_(*id_clauses)).all()
# if nothing, then try to match on first name/last name AND if the
# player has an MLB ID AND a birthdate AND the birthdate is after 1974
if soft_match and len(matches) == 0:
name_clauses = [ (func.lower(getattr(Player, k)) == kwargs[k])
for k in ['last_name', 'first_name']
if (k in kwargs and kwargs[k] != '' and kwargs[k] is not None) ]
if len(name_clauses) > 0:
matches = self.query(Player).filter(and_(*name_clauses),
Player.mlb_id != None,
Player.birthdate != None,
func.strftime('%Y', Player.birthdate) > '1974').all()
return matches
def data(request):
"""Return server side data."""
# defining columns
# - explicitly cast date to string, so string searching the date
# will search a date formatted equal to how it is presented
# in the table
columns = [
ColumnDT(User.id),
ColumnDT(User.name),
ColumnDT(Address.description),
ColumnDT(func.strftime('%d-%m-%Y', User.birthday)),
ColumnDT(User.age)
]
# defining the initial query depending on your purpose
# - don't include any columns
# - if you need a join, also include a 'select_from'
query = DBSession.query().\
select_from(User).\
join(Address).\
filter(Address.id > 4)
# instantiating a DataTable for the query and table needed
rowTable = DataTables(request.GET, query, columns)
# returns what is needed by DataTable
return rowTable.output_result()
def get_list_query(self):
cls = self.cls
query = (
cls.query
.join(cls.transaction)
.join(cls.member)
.order_by(Transaction.date.desc())
)
args = query_parser.parse_args()
if args['name']:
arg = args['name'].replace('%',r'\%')
query = query.filter(or_(
Transaction.name.ilike('%%%s%%' % arg),
cls.name.ilike('%%%s%%' % arg),
))
if args['payment_type_id'] is not None:
query = query.filter(cls.payment_type_id == args['payment_type_id'])
if args['budget_id'] is not None:
query = query.filter(cls.budget_id == args['budget_id'])
if args['month'] is not None:
query = query.filter(
sql_func.strftime('%Y-%m', cls.date) == args['month']
)
if args['member_id'] is not None:
query = query.filter(cls.member_id == args['member_id'])
return query.limit(20)
def month_worktimes(self, month):
return (
Worktime.query
.filter_by(user=self)
.filter(func.strftime('%Y-%m', Worktime.day) == month)
.order_by(Worktime.day.desc())
)
def get_last_years_energy():
"""
:return: total energy yielded in the previous year
"""
current_year = datetime.now().year
return PVData.query.with_entities(PVData.total_energy).filter(
func.strftime('%Y', PVData.created_at) == str(current_year - 1)).order_by(PVData.id.desc()).first()
def year_report_by_category_json(year):
by_year = func.strftime('%Y', Item.date) == year
expenses = db.session.query(func.sum(Item.amount), Category.name).filter(Item.category_id == SubCategory.id).filter(SubCategory.parent_id == Category.id).filter(by_year).filter(Item.is_expense == True).group_by(SubCategory.parent_id).all()
cols = [{'id':'', 'label':'Category', 'type':'string'}, {'id':'', 'label':'Expense', 'type':'number'}]
rows = []
for (expense, category) in expenses:
rows.append({'c': [{'v': category}, {'v': expense}]})
return jsonify(cols=cols, rows=rows)
def report_by_year(year):
by_year = func.strftime('%Y', Item.date) == year
expense = db.session.query(func.sum(Item.amount)).filter(Item.is_expense == True).filter(by_year).first()[0]
income = db.session.query(func.sum(Item.amount)).filter(Item.is_expense == False).filter(by_year).first()[0]
expenses_by_category = db.session.query(func.sum(Item.amount), Category.name).filter(Item.category_id == SubCategory.id).filter(SubCategory.parent_id == Category.id).filter(by_year).filter(Item.is_expense == True).group_by(SubCategory.parent_id).all()
return render_template('report_year.html', expense=expense, income=income, total=income - expense, year=year, expenses_by_category=expenses_by_category)
def month_worktimes(self, month):
return (
Worktime.query
.filter_by(project=self)
.filter(func.strftime('%Y-%m', Worktime.day) == month)
.order_by(Worktime.day.asc())
.all()
)
def single_date(start):
#sel min, max, avg based on a date
sel = [
Measurement.date,
func.min(Measurement.tobs),
func.max(Measurement.tobs),
func.round(func.avg(Measurement.tobs))
]
#set up query for the above and use strftime to allow input of date and all dates after
results = session.query(*sel).filter(
func.strftime("%Y-%m-%d", Measurement.date) >= start).group_by(
Measurement.date).all()
#close session
session.close()
#similar to above, set up empty list and create dict to go in it
date_temp_stats = []
#for loop
for result in results:
date_dict = {}
date_dict["Date"] = result[0]
date_dict["Min Temp"] = result[1]
date_dict["Max Temp"] = result[2]
date_dict["Avg Temp"] = result[3]
date_temp_stats.append(date_dict)
#close session
#session.close()
#make into list
#date_stats_list = list(np.ravel(date_stats))
#jsonify
return jsonify(date_temp_stats)
def daterange(start_date, end_date):
Measurement = Base.classes.measurement
start_date = '2017-03-01'
end_date = '2017-03-15'
# generate list of dates for selected period
sel = [
func.min(Measurement.tobs),
func.avg(Measurement.tobs),
func.max(Measurement.tobs)
]
results = session.query(*sel).filter(func.strftime("%Y-%m-%d", Measurement.date) >= start_date).\
filter(Measurement.date <= end_date).group_by(Measurement.date).all()
date_list = []
for tdate in results:
date_list.append(tdate)
return jsonify(date_list)
# try:
# (start_date)
# (end_date)
# except ValueError:
# return f"The date {date1} or {date2} is not in the correct format or is invalid"
results=session.query(func.min(Measurement.tobs).label('min'),\
func.avg(Measurement.tobs).label('avg')\
, func.max(Measurement.tobs).label('max')).\
filter(Measurement.date >= start_date).filter(Measurement.date <= end_date).all()
#Unravel the results
daterange_stats = []
for result in results:
result_dict = {}
result_dict["min"] = result.min
result_dict["avg"] = result.avg
result_dict["max"] = result.max
daterange_stats.append(result_dict)
return jsonify(daterange_stats)
def start_end(start, end):
#Return a json list of the minimum temperature, the average temperature, and the max temperature for a given start
start_date = datetime.datetime.strptime(start, "%Y-%m-%d")
end_date = datetime.datetime.strptime(end, "%Y-%m-%d")
start_month_day = str(start_date.month) + "-" + str(start_date.day)
end_month_day = str(end_date.month) + "-" + str(end_date.day)
# Retrieve data based on the month and day
results = session.query(func.strftime("%m-%d", Measurement.date),
Measurement.tobs).all()
import pandas as pd
date1 = []
tobs1 = []
for row in results:
date1.append(row[0])
tobs1.append(row[1])
tobs_df = pd.DataFrame({"date": date1, "tobs": tobs1})
tobs_df = tobs_df[(tobs_df['date'] >= start_month_day)
& (tobs_df['date'] <= end_month_day)]
TMIN = tobs_df['tobs'].min()
TMAX = tobs_df['tobs'].max()
TAVG = tobs_df['tobs'].mean()
tobs_dict = {"TMIN": int(TMIN), "TMAX": int(TMAX), "TAVG": TAVG}
return jsonify(tobs_dict)
def daily_normals(self, date):
# Open new session
session = Session(bind=self.engine)
"""Daily Normals.
Args:
date (str): A date string in the format '%m-%d'
Returns:
A list of tuples containing the daily normals, tmin, tavg, and tmax
"""
sel = [
func.min(self.tbl_Measurement.tobs),
func.avg(self.tbl_Measurement.tobs),
func.max(self.tbl_Measurement.tobs)
]
qry_daily_normals = session.query(*sel).filter(
func.strftime("%m-%d", self.tbl_Measurement.date) == date).all()
# Close session
session.close()
return qry_daily_normals
def start(start):
# Query final date
final_date_query = session.\
query(func.max(func.strftime("%Y-%m-%d", Measurement.date))).\
all()
# Set max date
max_date = final_date_query[0][0]
# `calc_temps` will accept start date and end date in the format '%Y-%m-%d'
# and return the minimum, average, and maximum temperatures for that range of dates
temps = calc_temps(start, max_date)
# Create a [] to store minimum, average, and maximum temperatures
return_list = []
# Create a {} to hold the start and end dates
date_dict = {'start_date': start, 'end_date': max_date}
# Append []
return_list.append(date_dict)
return_list.append({'Observation': 'TMIN', 'Temperature': temps[0][0]})
return_list.append({'Observation': 'TAVG', 'Temperature': temps[0][1]})
return_list.append({'Observation': 'TMAX', 'Temperature': temps[0][2]})
# Close session
session.close()
# Return json object
return jsonify(return_list)
def start(start):
# Return a JSON list of the minimum, average, and maximum temperatures from start date to last date in the database.
print("Received request for temperature data for a particular start date")
session = Session(bind=engine)
# First we find the last date in the database
final_date_query = session.query(func.max(func.strftime(f"%Y-%m-%d", Measurement.date))).all()
max_date = final_date_query[0][0]
# Get the temperatures
temps = calc_temps(start, max_date)
# Create a list
return_list = []
date_dict = {'start_date': start, 'end_date': max_date}
return_list.append(date_dict)
return_list.append({'Observation': 'TMIN', 'Temperature': temps[0][0]})
return_list.append({'Observation': 'TAVG', 'Temperature': temps[0][1]})
return_list.append({'Observation': 'TMAX', 'Temperature': temps[0][2]})
session.close()
return jsonify(return_list)
def start(start):
#session open
session = Session(engine)
# calc_temps second argument from start date to the max date
max_date_query = session.query(
func.max(func.strftime("%Y-%m-%d", Measurement.date))).all()
max_date = max_date_query[0][0]
tobs = calc_temps(start, max_date)
# close session
session.close()
# min, avg, max list
tobs_list = []
start_date_dict = {'start_date': start, 'end_date': max_date}
tobs_list.append(start_date_dict)
tobs_list.append({'Hawaii': 'Minimum', 'Temperature': tobs[0][0]})
tobs_list.append({'Hawaii': 'Average', 'Temperature': tobs[0][1]})
tobs_list.append({'Hawaii': 'Maximum', 'Temperature': tobs[0][2]})
return jsonify(tobs_list)
def tobsinfo_start_end(start, end):
# daterange = session.query(Measurement.date).all()
try:
if start and end: #in daterange:
# start = func.strftime('%Y-%m-%d', 'start')
sel = [
func.min(Measurement.tobs),
func.avg(Measurement.tobs),
func.max(Measurement.tobs)
]
calcs = session.query(*sel).filter(
func.strftime('%Y-%m-%d',
Measurement.date).between(start, end)).one()
return (f"<h2> Temperature informtion from {start} to {end}</h2>"
f"Minimum temperature: {calcs[0]}<br>"
f"Average temperature: {round(calcs[1],2)}<br>"
f"Maximum temperature: {round(calcs[2],2)}<br>")
except:
abort(404)
def tobs():
session = Session(engine)
# Find the most recent date in the data set.
last_measure_date = session.query(func.max(Measurement.date)).scalar()
# Get the date of one year ago from the last measured date in the population
one_year_ago = dt.datetime.strptime(last_measure_date,
'%Y-%m-%d') - dt.timedelta(days=365)
# Perform a query to retrieve the data and precipitation scores
results = session.query(Measurement.tobs).\
filter(func.strftime("%Y-%m-%d", Measurement.date) >= one_year_ago).\
order_by(Measurement.date).all()
session.close()
# Flatten list of lists into a single list
temp_list = []
for row in results:
temp_list.append(row[0])
return jsonify(temp_list)
def temp_stats_start(start):
print("<Start> API request received.")
"""Return a JSON list of the minimum, average, and maximum temperatures from the start date until
the end of the database."""
# Retrieve First and Last dates
final_date_query = session.query(
func.max(func.strftime("%Y-%m-%d", Measurement.date))).all()
max_date = final_date_query[0][0]
# Retrieve temps
temps = calc_temps(start, max_date)
# Create list
start_data_list = []
date_dict = {'start_date': start, 'end_date': max_date}
start_data_list.append(date_dict)
start_data_list.append({'tobs': 'TMIN', 'Temperature': temps[0][0]})
start_data_list.append({'tobs': 'TAVG', 'Temperature': temps[0][1]})
start_data_list.append({'tobs': 'TMAX', 'Temperature': temps[0][2]})
return jsonify(start_data_list)
def precipitation():
# Create our session (link) from Python to the DB
session = Session(engine)
maxdate = session.query(
func.max(func.strftime("%Y-%m-%d", Measurement.date))).all()
tmpdate = list(np.ravel(maxdate))[0]
tmpdate = dt.datetime.strptime(tmpdate, '%Y-%m-%d')
year = dt.timedelta(days=365)
begdate = tmpdate - year
# enddate = tmpdate
# Query all measurements
results = session.query(Measurement.date, Measurement.prcp)\
.filter(Measurement.date >begdate)\
.group_by(Measurement.date)\
.order_by(Measurement.date).all()
session.close()
rainDict = {}
for result in results:
rainDict.update({result.date: result.prcp})
return jsonify(rainDict)
def start(start):
sel = [
Measurement.date,
func.min(Measurement.tobs),
func.avg(Measurement.tobs),
func.max(Measurement.tobs)
]
start_date = session.query(*sel)\
.filter(func.strftime("%Y-%m-%d", Measurement.date) >= dt.date(2016, 8, 23))\
.group_by(Measurement.date)\
.all()
dates = []
for result in start_date:
date_dict = {}
date_dict["Date"] = result[0]
date_dict["Low Temp"] = result[1]
date_dict["Avg Temp"] = result[2]
date_dict["High Temp"] = result[3]
dates.append(date_dict)
return jsonify(dates)
def start(start):
session = Session(engine)
#query
date = session.query(func.max(func.strftime("%Y-%m-%d",
Measurement.date))).all()
maxdate = date[0][0]
#temp
def temps(start_date, end_date):
return session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date >= start_date).filter(Measurement.date <= end_date).all()
temperature = temps(start, maxdate)
#dictionary
empty = []
date_dict = {'start_date': start, 'end_date': maxdate}
empty.append(date_dict)
empty.append({'Observation': 'TMIN', 'Temperature': temperature[0][0]})
empty.append({'Observation': 'TAVG', 'Temperature': temperature[0][1]})
empty.append({'Observation': 'TMAX', 'Temperature': temperature[0][2]})
return jsonify(empty)
session.close()
def start(start):
"""Return a JSON list of the minimum, average, and maximum temperatures from the start date until
the end of the database."""
print("Received start date api request.")
#First we find the last date in the database
final_date_query = session.query(
func.max(func.strftime("%Y-%m-%d", Measurement.date))).all()
max_date = final_date_query[0][0]
#get the temperatures
temps = calc_temps(start, max_date)
#create a list
return_list = []
date_dict = {'start_date': start, 'end_date': max_date}
return_list.append(date_dict)
return_list.append({'Observation': 'TMIN', 'Temperature': temps[0][0]})
return_list.append({'Observation': 'TAVG', 'Temperature': temps[0][1]})
return_list.append({'Observation': 'TMAX', 'Temperature': temps[0][2]})
return jsonify(return_list)
def start(start):
"""Return JSON when given start only, calculate TMIN, TAVG, and TMAX
for all dates greater than and equal to the start date."""
print("Received start request.")
#Find the last date in the database
last_date_q = session.query(
func.max(func.strftime("%Y-%m-%d", Measurement.date))).all()
db_end_date = last_date_q[0][0]
#Get Temperatures
temps = calc_temps(start, db_end_date)
#Create list of Temperature Observations
obsv_list = []
date_dict = {'start_date': start, 'end_date': db_end_date}
obsv_list.append(date_dict)
obsv_list.append({'Observation': 'TMIN', 'Temperature': temps[0][0]})
obsv_list.append({'Observation': 'TAVG', 'Temperature': temps[0][1]})
obsv_list.append({'Observation': 'TMAX', 'Temperature': temps[0][2]})
return jsonify(obsv_list)
def start_avg(start):
#create datetime object from <start>
start_date_str = start.split('-')
start_year_int = int(start_date_str[0])
start_month_int = int(start_date_str[1])
start_day_int = int(start_date_str[2])
start_date = dt.date(start_year_int, start_month_int, start_day_int)
session = Session(engine)
#create datetime object from last date in data
last_date = session.query(func.strftime(
"%Y-%m-%d",
Measurement.date)).order_by(Measurement.date.desc()).first()
end_date = last_date[0].split('-')
end_year_int = int(end_date[0])
end_month_int = int(end_date[1])
end_day_int = int(end_date[2])
end_date = dt.date(end_year_int, end_month_int, end_day_int)
#create list of date between start and last date
date_list = []
date = start_date
while date != end_date:
date_list.append(date)
date = date + dt.timedelta(days=1)
date_list.append(end_date)
#formatted list for query
formated_date_list = []
for date in date_list:
date = date.strftime('%Y-%m-%d')
formated_date_list.append(date)
temp_list = []
#loop through formatted date list to query for average, max, and min temps each date
for date in formated_date_list:
result= session.query(Measurement.date,func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date==date).all()
temps = result[0]
temp_list.append(temps)
return jsonify(temp_list)
def timeline_json_date(date):
"""Return a list of temperature for a global."""
# Perform the sql query
query = db.session.query(
GlobalTemperatureByMajorCity.date,
GlobalTemperatureByMajorCity.latitude,
GlobalTemperatureByMajorCity.longitude,
GlobalTemperatureByMajorCity.city,
GlobalTemperatureByMajorCity.country,
func.avg(GlobalTemperatureByMajorCity.land_average_temperature).label('avg_land_temperature')
).filter(
GlobalTemperatureByMajorCity.date == date
).group_by(
GlobalTemperatureByMajorCity.city,
GlobalTemperatureByMajorCity.country,
func.strftime('%Y', GlobalTemperatureByMajorCity.date)
)
features = []
for row in query.all():
feature = {
'location': {
'latitude': row.latitude,
'longitude': row.longitude,
},
"average_temperature": row.avg_land_temperature,
"name": row.city + ', ' + row.country,
"time": row.date,
}
features.append(feature)
return jsonify(features)
def start(start_date):
# Create our session (link) from Python to the DB
session = Session(engine)
# Query Temperature Data minimum temperature, the average temperature, and the max temperature
# for a given start or start-end range.
selection = [Measurement.date, func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)]
results = (session.query(*selection)
.filter(func.strftime("%Y-%m-%d", Measurement.date) >= start_date)
.group_by(Measurement.date)
.all())
session.close()
#Return the JSON representation of your dictionary
dates = []
for result in results:
date_dict = {}
date_dict["Date"] = result[0]
date_dict["Low Temperature"] = result[1]
date_dict["Avg. Temperature"] = result[2]
date_dict["High Temperature"] = result[3]
dates.append(date_dict)
return jsonify(dates)
def daily_normals(start):
"""Returns a json list of daily normals when given a start date"""
# Calculate the daily normals. Normals are the averages for min, avg, and max temperatures.
daily_calc = [func.min(Measurement.tobs),\
func.avg(Measurement.tobs),\
func.max(Measurement.tobs)]
daily_query = session.query(Measurement.date,*daily_calc).\
filter(func.strftime("%Y-%m-%d", Measurement.date) >= start).\
group_by(Measurement.date)
# Convert query results into a dictionary
daily_data = []
for daily_normals in daily_query:
(t_date, t_min, t_avg, t_max) = daily_normals
norms_dict = {}
norms_dict["Date"] = t_date
norms_dict["Temp Min"] = t_min
norms_dict["Temp Avg"] = t_avg
norms_dict["Temp Max"] = t_max
daily_data.append(norms_dict)
# Return a json list of daily normals
return jsonify(daily_data)
def start(startDate):
# Temperature data available from start date to 2017-08-23 by using filter
# group_by date
sel = [
Measurement.date,
func.min(Measurement.tobs),
func.avg(Measurement.tobs),
func.max(Measurement.tobs)
]
results = (session.query(*sel).filter(
func.strftime("%Y-%m-%d", Measurement.date) >= startDate).group_by(
Measurement.date).all())
# appending dictionaries with "Date","TMIN","TAVG","TMAX" to the list "date_list"
date_list = []
for result in results:
date_dict = {}
date_dict["Date"] = result[0]
date_dict["TMIN"] = result[1]
date_dict["TAVG"] = result[2]
date_dict["TMAX"] = result[3]
date_list.append(date_dict)
# jsonifying the date_list
return jsonify(date_list)
def precipitation():
last_date = session.query(Measurement.date).order_by(
Measurement.date.desc()).first()
for date in last_date:
sep_date = date.split('-')
year = int(sep_date[0])
month = int(sep_date[1])
day = int(sep_date[2])
first_date = dt.date(year, month, day) - dt.timedelta(days=365)
precip_year = session.query(Measurement.date, Measurement.prcp).\
filter(func.strftime(Measurement.date)>= first_date).\
order_by(Measurement.date).all()
prcp = []
for precip in precip_year:
dict = {}
dict["date"] = precip.date
dict["prcp"] = precip.prcp
prcp.append(dict)
return jsonify(prcp)
def tempertature():
last_date = session.query(Measurement.date).order_by(
Measurement.date.desc()).first()
for date in last_date:
sep_date = date.split('-')
year = int(sep_date[0])
month = int(sep_date[1])
day = int(sep_date[2])
first_date = dt.date(year, month, day) - dt.timedelta(days=365)
temp_year = session.query(Measurement.date, Measurement.tobs).\
filter(func.strftime(Measurement.date)>= first_date).\
order_by(Measurement.date).all()
temp = []
for temperature in temp_year:
dict = {}
dict["date"] = temperature.date
dict["temp"] = temperature.tobs
temp.append(dict)
return jsonify(temp)
def perciptation():
session = Session(engine)
lastDate = session.query(Measurements.date).order_by(
Measurements.date.desc()).first()
for row in lastDate:
date = dt.datetime.strptime(row, "%Y-%m-%d")
sel = [Measurements.date, func.sum(Measurements.prcp)]
data = session.query(*sel).filter(func.strftime("%Y-%m-%d)", Measurements.date) >= str((date - dt.timedelta(days=365)))).\
group_by(Measurements.date).all()
session.close()
returnList = []
for row in data:
dateDict = {}
dateDict["date"] = row.date
dateDict["prcp"] = row[1]
returnList.append(dateDict)
return jsonify(returnList)
def tobs():
# Create our session (link) from Python to the DB
session = Session(engine)
# Query all temperature
qry_active_station = session.query(
Measurement.station, func.count(Measurement.station)).group_by(
Measurement.station).order_by(
func.count(Measurement.station).desc()).all()
session.close()
most_active = qry_active_station[0][0]
session = Session(engine)
maxdate = session.query(
func.max(func.strftime("%Y-%m-%d", Measurement.date))).all()
tmpdate = list(np.ravel(maxdate))[0]
tmpdate = dt.datetime.strptime(tmpdate, '%Y-%m-%d')
year = dt.timedelta(days=365)
begdate = tmpdate - year
session.close()
session = Session(engine)
results = session.query(Measurement.date, Measurement.tobs).\
filter(Measurement.date >= begdate).\
filter(Measurement.station == most_active).all()
session.close()
qry_results = list(np.ravel(results))
return jsonify(qry_results)
def start_date_temps(start):
"""Fetch the tobs for dates >= start date or a 404 if not."""
"""TMIN, TAVG, and TMAX for a list of dates.
Args:
start_date (string): A date string in the format %Y-%m-%d
#end_date (string): A date string in the format %Y-%m-%d
Returns:
TMIN, TAVE, and TMAX
"""
# Create our session (link) from Python to the DB
session = Session(engine)
sel = [
measurement.date,
func.min(measurement.tobs),
func.avg(measurement.tobs),
func.max(measurement.tobs)
]
start_temp_results = (session.query(*sel)\
.filter(func.strftime("%Y-%m-%d", measurement.date) >= start)\
.group_by(measurement.date).all())
session.close()
# Create a dictionary from the row data
start_temps = []
for result in start_temp_results:
start_temps_dict = {}
start_temps_dict["Date"] = result[0]
start_temps_dict["Low Temp"] = result[1]
start_temps_dict["Average Temp"] = result[2]
start_temps_dict["High Temp"] = result[3]
start_temps.append(start_temps_dict)
return jsonify(start_temps)
def start(startDate):
# Return a JSON list of the min Temperature, avg Temperature, and max Temperature
# for a given start. When given the start only, calculate those data for all dates
# greater than and equal to the start date.
session = Session(engine)
# Query start day
results = session.query(Measurement.date, func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(func.strftime("%Y-%m-%d", Measurement.date) >=startDate).\
group_by(Measurement.date).all()
session.close()
return_list = []
for date, tmin, tavg, tmax in results:
dateDict = {}
dateDict["date"] = date
dateDict["Low Temp"] = tmin
dateDict["Avg Temp"] = tavg
dateDict["High Temp"] = tmax
return_list.append(dateDict)
return jsonify(return_list)
def start_end(start=None, end=None):
session = Session(engine)
# * Return a JSON list of the minimum temperature, the average temperature, and the max temperature for a given start or start-end range.
# * When given the start and the end date, calculate the `TMIN`, `TAVG`, and `TMAX` for dates between the start and end date inclusive.
sel = [
func.min(Measurement.tobs),
func.avg(Measurement.tobs),
func.max(Measurement.tobs)
]
before_start = pd.DataFrame(columns=('Date', 'Min Temp', 'Avg Temp',
'Max Temp'))
before_start['Date'] = pd.date_range(start=start, end=end)
j = 0
for i in before_start['Date'].astype(str):
data = session.query(*sel).filter(
func.strftime("%Y-%m-%d", Measurement.date) == i).all()
before_start['Min Temp'][j] = data[0][0]
before_start['Avg Temp'][j] = data[0][1]
before_start['Max Temp'][j] = data[0][2]
j += 1
session.close()
results = before_start.to_json(orient='index', date_format='iso')
return jsonify(results)
def start_end_date(start, end):
"""Fetch the minimum temperature, the average temperature, and
the max temperature whose start date and end date matches
the path variable supplied by the user"""
# Create our session (link) from Python to the DB
session = Session(engine)
# Query to calculate the min, avg, and max Temperatures for given dates in the format %Y-%m-%d
# When given the start and the end date, calculate the TMIN, TAVG, and TMAX for dates between the start and end date inclusive
# From active station USC00519281
results = session.query(Measurement.date, func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.station == 'USC00519281').\
filter(func.strftime("%Y-%m-%d", Measurement.date) >= start).\
filter(Measurement.date <= end).all()
#For loop and if statement for results query where if user api route dates equals query dates
for result in results:
search_date1 = start
search_date2 = end
if search_date1 == start and search_date2 == end:
#Return a JSON list of the minimum temperature, the average temperature, and the max temperature for a given start-end range
return jsonify(result)
def precip():
# Create our session (link) from Python to the DB
session = Session(engine)
# Find the date of the last day recorded in the data
last_day = session.query(Measurement.date).order_by(
Measurement.date.desc()).first()
#Convert last day to an object so I can find a year previous
ld_object = datetime.strptime(last_day[0], '%Y-%m-%d')
# Calculate the date 1 year ago from the last data point in the database
year_ago = ld_object - dt.timedelta(days=366)
results = session.query(Measurement.date, Measurement.prcp).filter(
func.strftime("%Y-%m-%d", Measurement.date) >= year_ago).all()
session.close()
# Create a dictionary from the row data and append to a list of all_passengers
date_precip_dict = {}
for date, prcp in results:
date_precip_dict[date] = prcp
return jsonify(date_precip_dict)
def start_date(date):
"""Start Date.
Args:
date (str): A date string in the format '%m-%d'
Returns:
A list of tuples containing the daily normals, tmin, tavg, and tmax
"""
sel = [
func.min(precip.tobs),
func.avg(precip.tobs),
func.max(precip.tobs)
].all()
results = session.query(*sel).filter(
func.strftime("%m-%d", precip.date) >= date).all()
print(start_date(date))
session.close()
#
all_temp = []
for date, tobs in results:
temp_dict = {}
temp_dict["date"] = date
temp_dict["tobs"] = tobs
all_temp.append(temp_dict)
for x in all_temp:
search_term = x["date"]
if search_term == start:
return jsonify(session.query(func.min(measurement.tobs), func.max(measurement.tobs), func.avg(measurement.tobs)).\
filter(measurement.date >= start).all())
return jsonify({"error": "Date not found."}), 404
return jsonify(varstart)
