X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/b44534d53c10d7c9a4462e9b20b420c00152331b..35b005a713dfc2160b9b887ab1fa37668a0bb504:/web/seepark_web.py diff --git a/web/seepark_web.py b/web/seepark_web.py index 9871b04..59e0c8b 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -7,6 +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 +from sqlalchemy import func # https://stackoverflow.com/a/37350445 @@ -58,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) @@ -68,67 +91,59 @@ 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 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; - # seepark_web.db.session.query(func.to_seconds(Sensors.timestamp).op('div')(60*60*24).label('g'), func.min(Sensors.timestamp), func.min(Sensors.value)).group_by('g').all() 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 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_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[getattr(row, id)].append(getattr(row, field_y)) + 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 @@ -154,7 +169,14 @@ 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, 'sensor_id', 'timestamp', 'value') @@ -167,7 +189,14 @@ 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, 'cityid', 'datetime', 'temp')