X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/f884244feef20d1216cbbbaf123d15d9732758cb..0f86faa33947d135ae246cca70082d32e89a9f5d:/web/seepark_web.py diff --git a/web/seepark_web.py b/web/seepark_web.py index 596d482..00d122f 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -1,17 +1,26 @@ -from random import uniform import datetime import time import configparser import os -from flask import Flask, render_template, jsonify, request +import sys +from collections import defaultdict +from flask import Flask, render_template, jsonify, request, abort, Response import flask.json -from sqlalchemy import create_engine +from flask_sqlalchemy import SQLAlchemy, inspect + + +# 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) @@ -19,87 +28,220 @@ def parse_datetime(date_str): return datetime.datetime.strptime(date_str, '%Y-%m-%dT%H:%M:%S') -app = Flask(__name__) -app.json_encoder = JSONEncoder -config = configparser.ConfigParser() -config.read(os.environ['SEEPARKINI']) -apikey = config.get('openweathermap', 'apikey') - - -def open_engine(config): +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') - engine = create_engine('mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db), echo=False) - return engine + 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 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) + if mode == 'consolidated' and begin is not None and end is not 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 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 not None and end is not 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, id, field_x, field_y): + c3result = defaultdict(list) + for row in result: + c3result[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') + + result = select_sensordata(sensor_id, sensor_type, begin, end, mode) + + 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') + + result = select_openweatherdata(cityid, begin, end, mode) + + 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""" - engine = open_engine(config) - with engine.connect() as conn: - cursor = conn.execute('select distinct sensor_id, sensor_name, value_type from sensors') - result = [dict(row) for row in cursor] - return jsonify(result) + 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/') -def sensor(version, sensor_id): - """Return all sensor data +@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 """ - engine = open_engine(config) - with engine.connect() as conn: - where = ['sensor_id=%s'] - sql_args = [sensor_id] - if 'begin' in request.args: - where.append('timestamp>=%s') - sql_args.append(request.args.get('begin', None, parse_datetime)) - if 'end' in request.args: - where.append('timestamp<=%s') - sql_args.append(request.args.get('end', None, parse_datetime)) - sql = 'select * from sensors where {} order by id'.format(' and '.join(where)) - cursor = conn.execute(sql, *sql_args) - result = [dict(row) for row in cursor] - return jsonify(result) - - -@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) + 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, + )