X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/dbae96a3ccf844c03b456556dc723946030b24ae..35b005a713dfc2160b9b887ab1fa37668a0bb504:/web/seepark_web.py diff --git a/web/seepark_web.py b/web/seepark_web.py index 03d5760..59e0c8b 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -1,4 +1,3 @@ -from random import uniform import datetime import time import configparser @@ -8,12 +7,7 @@ from collections import defaultdict from flask import Flask, render_template, jsonify, request, abort, Response import flask.json from flask_sqlalchemy import SQLAlchemy, inspect - - -app_path = os.path.dirname(os.path.realpath(__file__)) -lib_path = os.path.join(app_path, '..') -sys.path.append(lib_path) -from seeparklib.openweathermap import openweathermap_json, OpenWeatherMapError +from sqlalchemy import func # https://stackoverflow.com/a/37350445 @@ -47,7 +41,7 @@ config = configparser.ConfigParser() config.read(os.environ['SEEPARKINI']) apikey = config.get('openweathermap', 'apikey') cityid = config.get('openweathermap', 'cityid') -mainsensor = config.get('temperature', 'mainsensor') +mainsensor = config.get('webapp', 'mainsensor') app = Flask(__name__) app.json_encoder = JSONEncoder @@ -65,7 +59,29 @@ class OpenWeatherMap(db.Model): __tablename__ = 'openweathermap' -def select_sensordata(sensor_id, sensor_type, begin, end, mode): +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) @@ -75,68 +91,61 @@ 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' 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 select_openweatherdata(cityid, begin, end, mode): +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) - 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): +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[row.sensor_id].append(row.value) - dt = row.timestamp.strftime('%Y-%m-%d %H:%M:%S') - c3result[row.sensor_id + '_x'].append(dt) + 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 @@ -160,10 +169,17 @@ def sensordata(sensor_id=None, sensor_type=None): mode = request.args.get('mode', 'full') format = request.args.get('format', 'default') - result = select_sensordata(sensor_id, sensor_type, begin, end, mode) + 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) + return convert_to_c3(result, 'sensor_id', 'timestamp', 'value') return result @@ -173,20 +189,23 @@ def openweathermapdata(cityid): mode = request.args.get('mode', 'full') format = request.args.get('format', 'default') - result = select_openweatherdata(cityid, begin, end, mode) + 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) + return convert_to_c3(result, 'cityid', 'datetime', 'temp') 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): @@ -243,39 +262,9 @@ def openweathermap_city(version, cityid): 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) - - @app.route("/") def index(): - airvalue, airtime = currentairtemperature(apikey, cityid) + airvalue, airtime = currentairtemperature(cityid) watervalue, watertime = currentwatertemperature(mainsensor) return render_template(