From b986a4736e1027ea0f74e14f040c8241cb5760f7 Mon Sep 17 00:00:00 2001 From: gregor herrmann Date: Thu, 5 Jul 2018 19:19:57 +0200 Subject: [PATCH 01/16] README: add python3-flask-sqlalchemy (which depends on python3-flask and python3-sqlalchemy) --- web/README | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/web/README b/web/README index aa14624..cb74f0d 100644 --- a/web/README +++ b/web/README @@ -1,4 +1,4 @@ -Needed packages: python3-flask python3-sqlalchemy python3-mysqldb +Needed packages: (python3-flask python3-sqlalchemy) python3-mysqldb python3-flask-sqlalchemy Start with: FLASK_APP=seepark_web.py FLASK_DEBUG=1 SEEPARKINI=~/seewasser.ini flask run -- 2.47.3 From 5a46011332bbb5252b14d5cc1828772b09c3225e Mon Sep 17 00:00:00 2001 From: gregor herrmann Date: Thu, 5 Jul 2018 19:28:21 +0200 Subject: [PATCH 02/16] try to be a bit more clever in finding our libraries appending '..' to sys.path only works when seepark_web.py is started from its directory, otherwise we get: flask.cli.NoAppException: While importing "seepark_web", an ImportError was raised: Traceback (most recent call last): File "/usr/lib/python3/dist-packages/flask/cli.py", line 235, in locate_app __import__(module_name) File "/home/gregoa/src/toastfreeware/seepark/web/seepark_web.py", line 14, in from seeparklib.openweathermap import openweathermap_json, OpenWeatherMapError ModuleNotFoundError: No module named 'seeparklib' --- web/seepark_web.py | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/web/seepark_web.py b/web/seepark_web.py index 065e3f2..f53585e 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -10,7 +10,9 @@ import flask.json from flask_sqlalchemy import SQLAlchemy -sys.path.append('..') +app_path = os.path.dirname(os.path.realpath(__file__)) +lib_path = os.path.join(cur_path, '..') +sys.path.append(lib_path) from seeparklib.openweathermap import openweathermap_json, OpenWeatherMapError -- 2.47.3 From 21ba143aa9998e73cb5d74144aba83addeaa7a7b Mon Sep 17 00:00:00 2001 From: gregor herrmann Date: Thu, 5 Jul 2018 19:37:59 +0200 Subject: [PATCH 03/16] read main(water)sensor from ini file [temperature] mainsensor=0316a21383ff --- web/seepark_web.py | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/web/seepark_web.py b/web/seepark_web.py index f53585e..de719ba 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -39,6 +39,7 @@ config = configparser.ConfigParser() config.read(os.environ['SEEPARKINI']) apikey = config.get('openweathermap', 'apikey') cityid = config.get('openweathermap', 'cityid') +mainsensor = config.get('temperature', 'mainsensor') app = Flask(__name__) app.json_encoder = JSONEncoder @@ -180,7 +181,7 @@ def data(timespan): @app.route("/") def index(): airvalue, airtime = currentairtemperature(apikey, cityid) - watervalue, watertime = currentwatertemperature('0316a21383ff') # config? mainwatertemp? + watervalue, watertime = currentwatertemperature(mainsensor) return render_template( 'seepark_web.html', -- 2.47.3 From ca2ff71b9a5b9d5efcdec8ec56c43c74f0818f4f Mon Sep 17 00:00:00 2001 From: gregor herrmann Date: Thu, 5 Jul 2018 20:19:20 +0200 Subject: [PATCH 04/16] first rather simple implementation of to_dict() for our Sensors object --- web/seepark_web.py | 13 +++++++++---- 1 file changed, 9 insertions(+), 4 deletions(-) diff --git a/web/seepark_web.py b/web/seepark_web.py index de719ba..3ee9d94 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -7,7 +7,7 @@ import sys from collections import defaultdict from flask import Flask, render_template, jsonify, request import flask.json -from flask_sqlalchemy import SQLAlchemy +from flask_sqlalchemy import SQLAlchemy, inspect app_path = os.path.dirname(os.path.realpath(__file__)) @@ -52,6 +52,10 @@ db.reflect(app=app) class Sensors(db.Model): __tablename__ = 'sensors' + # https://stackoverflow.com/a/37350445 + def to_dict(self): + return {c.key: getattr(self, c.key) + for c in inspect(self).mapper.column_attrs} def select_sensordata(initial_where): query = Sensors.query.filter(initial_where) @@ -95,8 +99,9 @@ def select_sensordata(initial_where): c3result[row.sensor_id].append(row.value) dt = row.timestamp.strftime('%Y-%m-%d %H:%M:%S') c3result[row.sensor_id + '_x'].append(dt) - result = c3result - return result + return c3result + else: + return [row.to_dict() for row in result] def currentairtemperature(apikey, cityid): @@ -131,7 +136,7 @@ def sensorid(version, sensor_id): format=, optional. return result as returned by sqlalchemy (default) or formatted for c3.js """ result = select_sensordata(Sensors.sensor_id == sensor_id) - return jsonify([row._asdict() for row in result]) + return jsonify(result) @app.route('/api//sensor/type/') -- 2.47.3 From 9e63b0e1f5dc5740df2fe78a6feaae5a0020733c Mon Sep 17 00:00:00 2001 From: gregor herrmann Date: Thu, 5 Jul 2018 20:38:02 +0200 Subject: [PATCH 05/16] brown paper bag commit: always test after changing variable names (and rebase/fixup commits) --- web/seepark_web.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/web/seepark_web.py b/web/seepark_web.py index 3ee9d94..c966321 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -11,7 +11,7 @@ from flask_sqlalchemy import SQLAlchemy, inspect app_path = os.path.dirname(os.path.realpath(__file__)) -lib_path = os.path.join(cur_path, '..') +lib_path = os.path.join(app_path, '..') sys.path.append(lib_path) from seeparklib.openweathermap import openweathermap_json, OpenWeatherMapError -- 2.47.3 From 5a114b9646f8cd7a3972e3498deaf406905b3393 Mon Sep 17 00:00:00 2001 From: Philipp Spitzer Date: Wed, 18 Jul 2018 22:24:06 +0200 Subject: [PATCH 06/16] Move to_dict to JSONEncoder. --- web/seepark_web.py | 14 +++++++++----- 1 file changed, 9 insertions(+), 5 deletions(-) diff --git a/web/seepark_web.py b/web/seepark_web.py index c966321..fc28fde 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -16,10 +16,18 @@ sys.path.append(lib_path) from seeparklib.openweathermap import openweathermap_json, OpenWeatherMapError +# https://stackoverflow.com/a/37350445 +def sqlalchemy_model_to_dict(model): + return {c.key: getattr(model, c.key) + for c in inspect(model).mapper.column_attrs} + + class JSONEncoder(flask.json.JSONEncoder): def default(self, object): if isinstance(object, datetime.datetime): return object.isoformat() + elif isinstance(object, db.Model): + return sqlalchemy_model_to_dict(object) return super().default(object) @@ -52,10 +60,6 @@ db.reflect(app=app) class Sensors(db.Model): __tablename__ = 'sensors' - # https://stackoverflow.com/a/37350445 - def to_dict(self): - return {c.key: getattr(self, c.key) - for c in inspect(self).mapper.column_attrs} def select_sensordata(initial_where): query = Sensors.query.filter(initial_where) @@ -101,7 +105,7 @@ def select_sensordata(initial_where): c3result[row.sensor_id + '_x'].append(dt) return c3result else: - return [row.to_dict() for row in result] + return result def currentairtemperature(apikey, cityid): -- 2.47.3 From cde63f1c335bde04c2ba155ce8c3ae645f90fa4c Mon Sep 17 00:00:00 2001 From: Philipp Spitzer Date: Wed, 18 Jul 2018 22:56:00 +0200 Subject: [PATCH 07/16] Split function select_sensordata(). --- web/seepark_web.py | 45 ++++++++++++++++++++++++++++----------------- 1 file changed, 28 insertions(+), 17 deletions(-) diff --git a/web/seepark_web.py b/web/seepark_web.py index fc28fde..18bedc9 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -61,17 +61,16 @@ class Sensors(db.Model): __tablename__ = 'sensors' -def select_sensordata(initial_where): - query = Sensors.query.filter(initial_where) - begin = request.args.get('begin', None, parse_datetime) - end = request.args.get('end', None, parse_datetime) +def select_sensordata(sensor_id, sensor_type, begin, end, mode): + query = Sensors.query + if sensor_id is not None: + query = query.filter(Sensors.sensor_id == sensor_id) + if sensor_type is not None: + query = query.filter(Sensors.value_type == sensor_type) if begin is not None: query = query.filter(Sensors.timestamp >= begin) if end is not None: query = query.filter(Sensors.timestamp <= end) - result = query.all() - - mode = request.args.get('mode', 'full') if mode == 'consolidated': if begin is None or end is None: pass @@ -95,17 +94,29 @@ def select_sensordata(initial_where): resolution = resolutions['year'] # TODO: filter out samples from 'result' # like loop over results and skip if timestamp(n+1)-timestamp(n), optional. return all rows (default) or with lower resolution (for charts) format=, optional. return result as returned by sqlalchemy (default) or formatted for c3.js """ - result = select_sensordata(Sensors.sensor_id == sensor_id) + result = sensordata(sensor_id=sensor_id) return jsonify(result) @@ -153,7 +164,7 @@ def sensortype(version, sensor_type): mode=, optional. return all rows (default) or with lower resolution (for charts) format=, optional. return result as returned by sqlalchemy (default) or formatted for c3.js """ - result = select_sensordata(Sensors.value_type == sensor_type) + result = sensordata(sensor_type=sensor_type) return jsonify(result) -- 2.47.3 From 90b27bb7bd4231c93556537faa5ee6b9a6329291 Mon Sep 17 00:00:00 2001 From: Philipp Spitzer Date: Wed, 18 Jul 2018 23:26:47 +0200 Subject: [PATCH 08/16] Prepare consolidation of data. --- web/seepark_web.py | 54 +++++++++++++++++++++++++++------------------- 1 file changed, 32 insertions(+), 22 deletions(-) diff --git a/web/seepark_web.py b/web/seepark_web.py index 18bedc9..c19e9b9 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -71,29 +71,39 @@ def select_sensordata(sensor_id, sensor_type, begin, end, mode): query = query.filter(Sensors.timestamp >= begin) if end is not None: query = query.filter(Sensors.timestamp <= end) - if mode == 'consolidated': - if begin is None or end is None: - pass + if mode == 'consolidated' and begin is None and end is None: + # copied from munin/master/_bin/munin-cgi-graph.in + # interval in seconds for data points + resolutions = dict( + day = 300, + week = 1800, + month = 7200, + year = 86400, + ) + duration = (end - begin).total_seconds() + day = 60 * 60 * 24 + if duration < day: + resolution = resolutions['day'] + elif duration < 7 * day: + resolution = resolutions['week'] + elif duration < 31 * day: + resolution = resolutions['month'] else: - # copied from munin/master/_bin/munin-cgi-graph.in - resolutions = dict( - day = 300, - week = 1800, - month = 7200, - year = 86400, - ) - duration = (end - begin).total_seconds() - day = 60 * 60 * 24 - if duration < day: - resolution = resolutions['day'] - elif duration < 7 * day: - resolution = resolutions['week'] - elif duration < 31 * day: - resolution = resolutions['month'] - else: - resolution = resolutions['year'] - # TODO: filter out samples from 'result' - # like loop over results and skip if timestamp(n+1)-timestamp(n) Date: Wed, 25 Jul 2018 22:03:18 +0200 Subject: [PATCH 09/16] Make script ready for missing visibility information in JSON. --- owm.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/owm.py b/owm.py index c57b242..6f7ba24 100755 --- a/owm.py +++ b/owm.py @@ -37,7 +37,6 @@ def extractweatherdata(w): temp = w['main']['temp'], pressure = w['main']['pressure'], humidity = w['main']['humidity'], - visibility = w['visibility'], weather = w['weather'][0]['description'], sky = w['weather'][0]['main'], windspeed = w['wind']['speed'], @@ -51,6 +50,7 @@ def extractweatherdata(w): data['winddegrees'] = w['wind']['deg'] if 'deg' in w['wind'] else math.nan data['winddirection'] = degToCompass(data['winddegrees']) data['precipitation'] = w['rain']['3h'] if 'rain' in w else math.nan + data['visibility'] = w.get('visibility', math.nan) return data -- 2.47.3 From c76885ae93a85e0abe3ed4362e6cf2f628a239aa Mon Sep 17 00:00:00 2001 From: Philipp Spitzer Date: Wed, 25 Jul 2018 23:09:52 +0200 Subject: [PATCH 10/16] Implement writing openweathermap data to database. --- owm.py | 57 ++++++++++++++++++++++++------------ seeparklib/openweathermap.py | 6 ++-- web/seepark_web.py | 2 +- 3 files changed, 42 insertions(+), 23 deletions(-) diff --git a/owm.py b/owm.py index 6f7ba24..c41c319 100755 --- a/owm.py +++ b/owm.py @@ -13,11 +13,13 @@ import os import csv import datetime import math +import json +from sqlalchemy import create_engine from seeparklib.openweathermap import openweathermap_json -def fromtimestamp(timestamp, format): - return datetime.datetime.fromtimestamp(timestamp).strftime(format) +def fromtimestamp(timestamp): + return datetime.datetime.fromtimestamp(timestamp) # https://stackoverflow.com/questions/7490660/converting-wind-direction-in-angles-to-text-words @@ -31,9 +33,9 @@ def degToCompass(num): def extractweatherdata(w): data = dict( - datetime = w['dt'], - sunrise = w['sys']['sunrise'], - sunset = w['sys']['sunset'], + datetime = fromtimestamp(w['dt']), + sunrise = fromtimestamp(w['sys']['sunrise']), + sunset = fromtimestamp(w['sys']['sunset']), temp = w['main']['temp'], pressure = w['main']['pressure'], humidity = w['main']['humidity'], @@ -43,10 +45,6 @@ def extractweatherdata(w): cloudiness = w['clouds']['all'], ) - data['sunrise_t'] = fromtimestamp(data['sunrise'], '%H:%M:%S') - data['sunset_t'] = fromtimestamp(data['sunset'], '%H:%M:%S') - data['date'] = fromtimestamp(data['datetime'], '%Y-%m-%d') - data['time'] = fromtimestamp(data['datetime'], '%H:%M:%S') data['winddegrees'] = w['wind']['deg'] if 'deg' in w['wind'] else math.nan data['winddirection'] = degToCompass(data['winddegrees']) data['precipitation'] = w['rain']['3h'] if 'rain' in w else math.nan @@ -60,10 +58,10 @@ def write_csv(csv_file, weather_data): with open(csv_file, "a", newline="") as file: writer = csv.writer(file, dialect="excel", delimiter=';') writer.writerow([ - weather_data['date'], - weather_data['time'], - weather_data['sunrise_t'], - weather_data['sunset_t'], + weather_data['datetime'].date(), + weather_data['datetime'].time(), + weather_data['sunrise'].time(), + weather_data['sunset'].time(), "{:.2f}".format(weather_data['temp']), "{:.2f} mm/h".format(weather_data['precipitation']), "{:.1f} km/h {}".format(weather_data['windspeed'], weather_data['winddirection']), @@ -72,6 +70,27 @@ def write_csv(csv_file, weather_data): ]) +def write_db(config, url, weather_json, weather_data): + user = config.get('database', 'user') + pwd = config.get('database','password') + host = config.get('database','hostname') + db = config.get('database','database') + + engine = create_engine('mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db), echo=False) + conn = engine.connect() + row = dict(cityid=config.get('openweathermap', 'cityid'), url=url, result=json.dumps(weather_json)) + row.update(weather_data) + for key, value in row.items(): + if isinstance(value, float) and math.isnan(value): + row[key] = None + sql_columns = list(row.keys()) + sql_values = list(row.values()) + sql = 'insert into openweathermap ({}) values ({})'.format(', '.join(sql_columns), ','.join(['%s'] * len(sql_columns))) + print(sql) + conn.execute(sql, *sql_values) + conn.close() + + def main(configfile, debug): config = configparser.ConfigParser() config.read(configfile) @@ -79,18 +98,18 @@ def main(configfile, debug): cityid = config.get('openweathermap', 'cityid') csvfile = config.get("openweathermap", 'csvfilename') - weather_raw = openweathermap_json(apikey, cityid) + url, weather_json = openweathermap_json(apikey, cityid) if debug: - pprint(weather_raw) - weather = extractweatherdata(weather_raw) + pprint(weather_json) + weather_data = extractweatherdata(weather_json) if debug: - pprint(weather) + pprint(weather_data) - # TODO: # write to db + write_db(config, url, weather_json, weather_data) # write to csv - write_csv(os.path.expanduser(csvfile), weather) + write_csv(os.path.expanduser(csvfile), weather_data) if __name__ == '__main__': diff --git a/seeparklib/openweathermap.py b/seeparklib/openweathermap.py index d350eb6..626f77e 100644 --- a/seeparklib/openweathermap.py +++ b/seeparklib/openweathermap.py @@ -9,12 +9,12 @@ def openweathermap_json(apikey, cityid): """Returns parsed JSON as returned by openweathermap for the given cityid. In case of errors, an OpenWeatherMapError is raised.""" baseurl = 'http://api.openweathermap.org/data/2.5/weather' - query = baseurl + '?units=metric&APPID={}&id={}&lang=de'.format(apikey, cityid) + url = baseurl + '?units=metric&APPID={}&id={}&lang=de'.format(apikey, cityid) try: - response = requests.get(query) + response = requests.get(url) if response.status_code != 200: raise OpenWeatherMapError('Got status code {} ({}).'.format(response.status_code, response.reason)) else: - return response.json() + return url, response.json() except requests.exceptions.RequestException as error: raise OpenWeatherMapError('Request not successful: {}'.format(error)) diff --git a/web/seepark_web.py b/web/seepark_web.py index c19e9b9..e8b9da1 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -132,7 +132,7 @@ def sensordata(sensor_id=None, sensor_type=None): def currentairtemperature(apikey, cityid): """Retruns the tuple temperature, datetime (as float, datetime) in case of success, otherwise None, None.""" try: - weatherdata = openweathermap_json(apikey, cityid) + url, weatherdata = openweathermap_json(apikey, cityid) return weatherdata['main']['temp'], datetime.datetime.fromtimestamp(weatherdata['dt']) except OpenWeatherMapError: return None, None -- 2.47.3 From d95badd413aaa29e936d6225f08d5342f63b48c0 Mon Sep 17 00:00:00 2001 From: gregor herrmann Date: Sat, 28 Jul 2018 00:03:31 +0200 Subject: [PATCH 11/16] remove debug print --- owm.py | 1 - 1 file changed, 1 deletion(-) diff --git a/owm.py b/owm.py index c41c319..c36064f 100755 --- a/owm.py +++ b/owm.py @@ -86,7 +86,6 @@ def write_db(config, url, weather_json, weather_data): sql_columns = list(row.keys()) sql_values = list(row.values()) sql = 'insert into openweathermap ({}) values ({})'.format(', '.join(sql_columns), ','.join(['%s'] * len(sql_columns))) - print(sql) conn.execute(sql, *sql_values) conn.close() -- 2.47.3 From 07f6fd2c6041d6203380a60771ce1cffdc2f68a7 Mon Sep 17 00:00:00 2001 From: Philipp Spitzer Date: Wed, 1 Aug 2018 21:27:17 +0200 Subject: [PATCH 12/16] Ignore warning about duplicate key when inserting openweathermap data. --- owm.py | 10 ++++++++-- 1 file changed, 8 insertions(+), 2 deletions(-) diff --git a/owm.py b/owm.py index c36064f..3265f20 100755 --- a/owm.py +++ b/owm.py @@ -14,7 +14,10 @@ import csv import datetime import math import json +import warnings +import sqlalchemy from sqlalchemy import create_engine +import MySQLdb.cursors from seeparklib.openweathermap import openweathermap_json @@ -85,8 +88,11 @@ def write_db(config, url, weather_json, weather_data): row[key] = None sql_columns = list(row.keys()) sql_values = list(row.values()) - sql = 'insert into openweathermap ({}) values ({})'.format(', '.join(sql_columns), ','.join(['%s'] * len(sql_columns))) - conn.execute(sql, *sql_values) + sql = 'insert ignore into openweathermap ({}) values ({})'.format(', '.join(sql_columns), ','.join(['%s'] * len(sql_columns))) + with warnings.catch_warnings(): + # ignore _mysql_exceptions.Warning: Duplicate entry '3319578-2018-08-01 20:50:00' for key 'cityid_datetime' + warnings.simplefilter("ignore", category=MySQLdb.cursors.Warning) + conn.execute(sql, *sql_values) conn.close() -- 2.47.3 From 7ca34f80f45e96873eca14f00a31d49f06bf31e1 Mon Sep 17 00:00:00 2001 From: Philipp Spitzer Date: Wed, 1 Aug 2018 21:30:34 +0200 Subject: [PATCH 13/16] Add a working SQL example to group measurements into time slots. --- web/seepark_web.py | 13 +------------ 1 file changed, 1 insertion(+), 12 deletions(-) diff --git a/web/seepark_web.py b/web/seepark_web.py index e8b9da1..7f39c15 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -92,18 +92,7 @@ def select_sensordata(sensor_id, sensor_type, begin, end, mode): resolution = resolutions['year'] # TODO: filter out samples from 'result' # something like - # select mean(temperature) from sensors where ... group by mod(timestamp, resolution) - # func.avg(...) - # - # from https://stackoverflow.com/questions/4342370/grouping-into-interval-of-5-minutes-within-a-time-range - # SELECT - # timestamp, -- not sure about that - # name, - # count(b.name) - # FROM time a, id - # WHERE … - # GROUP BY - # UNIX_TIMESTAMP(timestamp) DIV 300, name + # select to_seconds(datetime) DIV (60*60*24) as interval_id, min(datetime), max(datetime), min(temp), avg(temp), max(temp), count(temp) from openweathermap group by interval_id order by interval_id; return query.all() -- 2.47.3 From fa7ce3c6732ce932f01b29bf4e927cdb530bd71f Mon Sep 17 00:00:00 2001 From: Philipp Spitzer Date: Wed, 1 Aug 2018 21:54:39 +0200 Subject: [PATCH 14/16] Create API for openweathermap data. --- web/seepark_web.py | 62 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 62 insertions(+) diff --git a/web/seepark_web.py b/web/seepark_web.py index 7f39c15..4814d0f 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -61,6 +61,10 @@ class Sensors(db.Model): __tablename__ = 'sensors' +class OpenWeatherMap(db.Model): + __tablename__ = 'openweathermap' + + def select_sensordata(sensor_id, sensor_type, begin, end, mode): query = Sensors.query if sensor_id is not None: @@ -96,6 +100,37 @@ def select_sensordata(sensor_id, sensor_type, begin, end, mode): return query.all() +def select_openweatherdata(cityid, begin, end, mode): + query = OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid) + if begin is not None: + query = query.filter(OpenWeatherMap.datetime >= begin) + if end is not None: + query = query.filter(OpenWeatherMap.datetime <= end) + if mode == 'consolidated' and begin is None and end is None: + # copied from munin/master/_bin/munin-cgi-graph.in + # interval in seconds for data points + resolutions = dict( + day = 300, + week = 1800, + month = 7200, + year = 86400, + ) + duration = (end - begin).total_seconds() + day = 60 * 60 * 24 + if duration < day: + resolution = resolutions['day'] + elif duration < 7 * day: + resolution = resolutions['week'] + elif duration < 31 * day: + resolution = resolutions['month'] + else: + resolution = resolutions['year'] + # TODO: filter out samples from 'result' + # something like + # select to_seconds(datetime) DIV (60*60*24) as interval_id, min(datetime), max(datetime), min(temp), avg(temp), max(temp), count(temp) from openweathermap group by interval_id order by interval_id; + return query.all() + + def convert_to_c3(result): c3result = defaultdict(list) for row in result: @@ -118,6 +153,19 @@ def sensordata(sensor_id=None, sensor_type=None): return result +def openweathermapdata(cityid): + begin = request.args.get('begin', None, parse_datetime) + end = request.args.get('end', None, parse_datetime) + mode = request.args.get('mode', 'full') + format = request.args.get('format', 'default') + + result = select_openweatherdata(cityid, begin, end, mode) + + if format == 'c3': + return convert_to_c3(result) + return result + + def currentairtemperature(apikey, cityid): """Retruns the tuple temperature, datetime (as float, datetime) in case of success, otherwise None, None.""" try: @@ -167,6 +215,20 @@ def sensortype(version, sensor_type): return jsonify(result) +@app.route('/api//openweathermap/cities') +def openweathermap_cities(version): + """List all city IDs found in the database""" + result = db.session.query(OpenWeatherMap.cityid).distinct().all() + return jsonify(result) + + +@app.route('/api//openweathermap/city/') +def openweathermap_city(version, cityid): + """List all data found for a city""" + result = openweathermapdata(cityid=cityid) + return jsonify(result) + + @app.route('/data/', defaults={'timespan': 1}) @app.route("/data/", methods=['GET']) def data(timespan): -- 2.47.3 From dbae96a3ccf844c03b456556dc723946030b24ae Mon Sep 17 00:00:00 2001 From: Philipp Spitzer Date: Wed, 1 Aug 2018 23:07:03 +0200 Subject: [PATCH 15/16] Implement handling of ill-formatted datetime values in URL. --- web/seepark_web.py | 24 +++++++++++++++++++----- 1 file changed, 19 insertions(+), 5 deletions(-) diff --git a/web/seepark_web.py b/web/seepark_web.py index 4814d0f..03d5760 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -5,7 +5,7 @@ import configparser import os import sys from collections import defaultdict -from flask import Flask, render_template, jsonify, request +from flask import Flask, render_template, jsonify, request, abort, Response import flask.json from flask_sqlalchemy import SQLAlchemy, inspect @@ -140,9 +140,23 @@ def convert_to_c3(result): return c3result +def request_arg(key, type, default=None): + """Returns the key from the request if available, otherwise the default value. + In case type is provided and the key is present, the value is converted by calling type. + In other words: Reimplement request.args.get but don't return default value if + type raises a ValueError.""" + if key in request.args: + try: + return type(request.args[key]) + except ValueError as e: + abort(Response(str(e), 400)) + else: + return default + + def sensordata(sensor_id=None, sensor_type=None): - begin = request.args.get('begin', None, parse_datetime) - end = request.args.get('end', None, parse_datetime) + begin = request_arg('begin', parse_datetime) + end = request_arg('end', parse_datetime) mode = request.args.get('mode', 'full') format = request.args.get('format', 'default') @@ -154,8 +168,8 @@ def sensordata(sensor_id=None, sensor_type=None): def openweathermapdata(cityid): - begin = request.args.get('begin', None, parse_datetime) - end = request.args.get('end', None, parse_datetime) + begin = request_arg('begin', parse_datetime) + end = request_arg('end', parse_datetime) mode = request.args.get('mode', 'full') format = request.args.get('format', 'default') -- 2.47.3 From 6acfad15e0f6ec9f864a1cf53680f3500f42ff21 Mon Sep 17 00:00:00 2001 From: Philipp Spitzer Date: Wed, 1 Aug 2018 23:12:00 +0200 Subject: [PATCH 16/16] The air temperature is taken from the database now. --- web/seepark_web.py | 12 ++++-------- 1 file changed, 4 insertions(+), 8 deletions(-) diff --git a/web/seepark_web.py b/web/seepark_web.py index 03d5760..a6d3930 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -180,13 +180,9 @@ def openweathermapdata(cityid): return result -def currentairtemperature(apikey, cityid): - """Retruns the tuple temperature, datetime (as float, datetime) in case of success, otherwise None, None.""" - try: - url, weatherdata = openweathermap_json(apikey, cityid) - return weatherdata['main']['temp'], datetime.datetime.fromtimestamp(weatherdata['dt']) - except OpenWeatherMapError: - return None, None +def currentairtemperature(cityid): + result = OpenWeatherMap.query.filter_by(cityid=cityid).order_by(OpenWeatherMap.datetime.desc()).first() + return result.temp, result.datetime def currentwatertemperature(sensorid): @@ -275,7 +271,7 @@ def data(timespan): @app.route("/") def index(): - airvalue, airtime = currentairtemperature(apikey, cityid) + airvalue, airtime = currentairtemperature(cityid) watervalue, watertime = currentwatertemperature(mainsensor) return render_template( -- 2.47.3