X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/711e3a545b616fe73791c6c6fac8de4e470ac3f9..35b005a713dfc2160b9b887ab1fa37668a0bb504:/web/seepark_web.py diff --git a/web/seepark_web.py b/web/seepark_web.py index 74daa2f..59e0c8b 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -1,18 +1,277 @@ -from flask import Flask, render_template, jsonify +import datetime +import time +import configparser +import os +import sys +from collections import defaultdict +from flask import Flask, render_template, jsonify, request, abort, Response +import flask.json +from flask_sqlalchemy import SQLAlchemy, inspect +from sqlalchemy import func + + +# 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) + + +def parse_datetime(date_str): + return datetime.datetime.strptime(date_str, '%Y-%m-%dT%H:%M:%S') + + +def get_sqlalchemy_database_uri(config): + user = config.get('database', 'user') + pwd = config.get('database', 'password') + host = config.get('database', 'hostname') + db = config.get('database', 'database') + return 'mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db) + + +config = configparser.ConfigParser() +config.read(os.environ['SEEPARKINI']) +apikey = config.get('openweathermap', 'apikey') +cityid = config.get('openweathermap', 'cityid') +mainsensor = config.get('webapp', 'mainsensor') + app = Flask(__name__) +app.json_encoder = JSONEncoder +app.config['SQLALCHEMY_DATABASE_URI'] = get_sqlalchemy_database_uri(config) +app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False +db = SQLAlchemy(app) +db.reflect(app=app) + + +class Sensors(db.Model): + __tablename__ = 'sensors' + + +class OpenWeatherMap(db.Model): + __tablename__ = 'openweathermap' + + +def calc_grouping_resolution(begin, end): + """How many data points should be between the timestamps begin and end?""" + # 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'] + return resolution + + +def select_sensordata(sensor_id, sensor_type, begin, end): + 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) + return query.all() + + +def select_sensordata_grouped(sensor_id, sensor_type, begin, end): + # determine resolution (interval in seconds for data points) + resolution = calc_grouping_resolution(begin, end) + + # Let the database do the grouping. Example in SQL (MySQL): + # 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; + query = db.session.query(func.to_seconds(Sensors.timestamp).op('div')(resolution).label('g'), + func.from_unixtime(func.avg(func.unix_timestamp(Sensors.timestamp))).label('timestamp'), + func.avg(Sensors.value).label('value'), + Sensors.sensor_id, Sensors.value_type, Sensors.sensor_name) + 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) + query = query.filter(Sensors.timestamp >= begin) + query = query.filter(Sensors.timestamp <= end) + query = query.group_by('g', Sensors.sensor_id, Sensors.value_type, Sensors.sensor_name) + return query.all() + + +def select_openweatherdata(cityid, begin, end): + 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) + return query.all() + + +def select_openweatherdata_grouped(cityid, begin, end): + # determine resolution (interval in seconds for data points) + resolution = calc_grouping_resolution(begin, end) + + # Let the database do the grouping. Example in SQL (MySQL): + # 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; + query = db.session.query(func.to_seconds(OpenWeatherMap.datetime).op('div')(resolution).label('g'), + func.from_unixtime(func.avg(func.unix_timestamp(OpenWeatherMap.datetime))).label('datetime'), + func.avg(OpenWeatherMap.temp).label('temp'), + OpenWeatherMap.cityid) + OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid) + query = query.filter(OpenWeatherMap.datetime >= begin) + query = query.filter(OpenWeatherMap.datetime <= end) + query = query.group_by('g', OpenWeatherMap.cityid) + return query.all() + + +def convert_to_c3(result, id, field_x, field_y): + c3result = defaultdict(list) + for row in result: + c3result[str(getattr(row, id))].append(getattr(row, field_y)) + dt = getattr(row, field_x).strftime('%Y-%m-%d %H:%M:%S') + c3result[str(getattr(row, id)) + '_x'].append(dt) + 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_arg('begin', parse_datetime) + end = request_arg('end', parse_datetime) + mode = request.args.get('mode', 'full') + format = request.args.get('format', 'default') + + if mode == 'full': + result = select_sensordata(sensor_id, sensor_type, begin, end) + elif mode == 'consolidated': + if begin is None or end is None: + abort(Response('begin and end have to be set for mode==consolidated', 400)) + result = select_sensordata_grouped(sensor_id, sensor_type, begin, end) + else: + abort(Response('unknown value for mode', 400)) + + if format == 'c3': + return convert_to_c3(result, 'sensor_id', 'timestamp', 'value') + return result + + +def openweathermapdata(cityid): + begin = request_arg('begin', parse_datetime) + end = request_arg('end', parse_datetime) + mode = request.args.get('mode', 'full') + format = request.args.get('format', 'default') + + if mode == 'full': + result = select_openweatherdata(cityid, begin, end) + elif mode == 'consolidated': + if begin is None or end is None: + abort(Response('begin and end have to be set for mode==consolidated', 400)) + result = select_openweatherdata_grouped(cityid, begin, end) + else: + abort(Response('unknown value for mode', 400)) + + if format == 'c3': + return convert_to_c3(result, 'cityid', 'datetime', 'temp') + return result + + +def currentairtemperature(cityid): + result = OpenWeatherMap.query.filter_by(cityid=cityid).order_by(OpenWeatherMap.datetime.desc()).first() + return result.temp, result.datetime + + +def currentwatertemperature(sensorid): + result = Sensors.query.filter_by(sensor_id=sensorid).order_by(Sensors.timestamp.desc()).first() + return result.value, result.timestamp + + +@app.route('/api//sensors/') +def sensors(version): + """List all sensors found in the database""" + result = db.session.query(Sensors.sensor_id, Sensors.sensor_name, Sensors.value_type).distinct().all() + return jsonify(result) + + +@app.route('/api//sensor/id/') +def sensorid(version, sensor_id): + """Return all data for a specific sensor + + URL parameters: + begin=, optional, format like "2018-05-19T21:07:53" + end=, optional, format like "2018-05-19T21:07:53" + 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 = sensordata(sensor_id=sensor_id) + return jsonify(result) + + +@app.route('/api//sensor/type/') +def sensortype(version, sensor_type): + """Return all data for a specific sensor type + + URL parameters: + begin=, optional, format like "2018-05-19T21:07:53" + end=, optional, format like "2018-05-19T21:07:53" + 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 = sensordata(sensor_type=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("/data") -def data(): - data = { - 'water_temperature': [20, 19, 20, 21, 20, 21], - 'water_temperature_x': ['2018-05-20', '2018-05-21', '2018-05-22', '2018-05-23', '2018-05-24', '2018-05-27'], - 'air_temperature': [27, 32, 26, 29, 30, 31], - 'air_temperature_x': ['2018-05-21', '2018-05-22', '2018-05-23', '2018-05-24', '2018-05-25', '2018-05-27'], - } - return jsonify(data) +@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("/") -def hello(): - return render_template('seepark_web.html') +def index(): + airvalue, airtime = currentairtemperature(cityid) + watervalue, watertime = currentwatertemperature(mainsensor) + return render_template( + 'seepark_web.html', + apikey=apikey, + watervalue=watervalue, + watertime=watertime, + airvalue=airvalue, + airtime=airtime, + )