X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/90b27bb7bd4231c93556537faa5ee6b9a6329291..35b005a713dfc2160b9b887ab1fa37668a0bb504:/web/seepark_web.py diff --git a/web/seepark_web.py b/web/seepark_web.py index c19e9b9..59e0c8b 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -1,19 +1,13 @@ -from random import uniform import datetime import time 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 - - -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 @@ -61,7 +55,33 @@ class Sensors(db.Model): __tablename__ = 'sensors' -def select_sensordata(sensor_id, sensor_type, begin, end, mode): +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) @@ -71,71 +91,121 @@ 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 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 return query.all() -def convert_to_c3(result): +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[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 +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') - 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 -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) - return weatherdata['main']['temp'], datetime.datetime.fromtimestamp(weatherdata['dt']) - except OpenWeatherMapError: - return None, None +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): @@ -178,39 +248,23 @@ def sensortype(version, sensor_type): 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('/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(): - airvalue, airtime = currentairtemperature(apikey, cityid) + airvalue, airtime = currentairtemperature(cityid) watervalue, watertime = currentwatertemperature(mainsensor) return render_template(