editorial change: move a def() around

[chrisu/seepark.git] / web / seepark_web.py

diff --git a/web/seepark_web.py b/web/seepark_web.py
index 9871b040d87bbca510124decb3f2b42e9fd15c8e..59e0c8b0264d8d0a0a9067afea2e2f9c1dc82e9c 100644 (file)
--- 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 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
 
 
 # https://stackoverflow.com/a/37350445


@@ -58,7 +59,29 @@ class OpenWeatherMap(db.Model):
     __tablename__ = 'openweathermap'
 
 
     __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)
     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)
         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()
 
 
     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)
     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:
     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
         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')
 
     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')
 
     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')
 
     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')
 
     if format == 'c3':
         return convert_to_c3(result, 'cityid', 'datetime', 'temp')