X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/3dc2b087a5e3cb7952392cb00a4469508af6e019..35b005a713dfc2160b9b887ab1fa37668a0bb504:/web/seepark_web.py diff --git a/web/seepark_web.py b/web/seepark_web.py index 5263174..59e0c8b 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -1,46 +1,277 @@ -from flask import Flask, render_template, jsonify -from random import uniform +import datetime import time import configparser import os -app = Flask(__name__) +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'); - -@app.route('/data/', defaults={'timespan': 1}) -@app.route("/data/", methods=['GET']) -def data(timespan): - - granularity = 5 * timespan # (every) minute(s) per day - samples = 60/granularity * 24 * timespan # per hour over whole timespan - s4m = [] - s4m_x = [] - s5m = [] - s5m_x = [] - end = time.time() - start = end - samples * granularity * 60 - - for i in range(int(samples)): - s4m.append(uniform(-10,30)) - s5m.append(uniform(-10,30)) - s4mt = uniform(start, end) - s5mt = uniform(start, end) - s4m_x.append(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(s4mt))) - s5m_x.append(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(s5mt))) - - data = { - '0316a2193bff': s4m, - '0316a2193bff_x': s4m_x, - '0316a21383ff': s5m, - '0316a21383ff_x': s5m_x, - } - - return jsonify(data) +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('/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 index(): - return render_template('seepark_web.html', apikey=apikey) + airvalue, airtime = currentairtemperature(cityid) + watervalue, watertime = currentwatertemperature(mainsensor) + return render_template( + 'seepark_web.html', + apikey=apikey, + watervalue=watervalue, + watertime=watertime, + airvalue=airvalue, + airtime=airtime, + )