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 de719ba0022292d79d7c296ee6660321e19a47ae..59e0c8b0264d8d0a0a9067afea2e2f9c1dc82e9c 100644 (file)
--- a/web/seepark_web.py
+++ b/web/seepark_web.py
@@ -1,25 +1,27 @@
-from random import uniform

 import datetime
 import time
 import configparser
 import os
 import sys
 from collections import defaultdict
 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
 import flask.json

-from flask_sqlalchemy import SQLAlchemy
+from flask_sqlalchemy import SQLAlchemy, inspect
+from sqlalchemy import func

 
 
 
 

-app_path = os.path.dirname(os.path.realpath(__file__))
-lib_path = os.path.join(cur_path, '..')
-sys.path.append(lib_path)
-from seeparklib.openweathermap import openweathermap_json, OpenWeatherMapError
+# 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()
 
 
 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)
 
 
         return super().default(object)
 
 


@@ -39,7 +41,7 @@ config = configparser.ConfigParser()
 config.read(os.environ['SEEPARKINI'])
 apikey = config.get('openweathermap', 'apikey')
 cityid = config.get('openweathermap', 'cityid')
 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
 
 app = Flask(__name__)
 app.json_encoder = JSONEncoder


@@ -53,59 +55,157 @@ class Sensors(db.Model):
     __tablename__ = 'sensors'
 
 
     __tablename__ = 'sensors'
 
 

-def select_sensordata(initial_where):
-    query = Sensors.query.filter(initial_where)
-    begin = request.args.get('begin', None, parse_datetime)
-    end = request.args.get('end', None, parse_datetime)
+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)
+    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 begin is not None:
         query = query.filter(Sensors.timestamp >= begin)
     if end is not None:
         query = query.filter(Sensors.timestamp <= end)

-    result = query.all()
-
+    return query.all()
+
+
+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[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_arg('begin', parse_datetime)
+    end = request_arg('end', parse_datetime)

     mode = request.args.get('mode', 'full')
     mode = request.args.get('mode', 'full')

-    if mode == 'consolidated':
+    format = request.args.get('format', 'default')
+
+    if mode == 'full':
+        result = select_sensordata(sensor_id, sensor_type, begin, end)
+    elif mode == 'consolidated':

         if begin is None or end is None:
         if begin is None or end is None:

-            pass
-        else:
-            # 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']
-            # TODO: filter out samples from 'result'
-            # like loop over results and skip if timestamp(n+1)-timestamp(n)<resolution
+            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')
+    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')
     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':
     if format == 'c3':

-        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)
-        result = c3result
+        return convert_to_c3(result, 'cityid', 'datetime', 'temp')

     return result
 
 
     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 currentairtemperature(cityid):
+    result = OpenWeatherMap.query.filter_by(cityid=cityid).order_by(OpenWeatherMap.datetime.desc()).first()
+    return result.temp, result.datetime

 
 
 def currentwatertemperature(sensorid):
 
 
 def currentwatertemperature(sensorid):


@@ -130,8 +230,8 @@ def sensorid(version, sensor_id):
     mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
     format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
     """
     mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
     format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
     """

-    result = select_sensordata(Sensors.sensor_id == sensor_id)
-    return jsonify([row._asdict() for row in result])
+    result = sensordata(sensor_id=sensor_id)
+    return jsonify(result)

 
 
 @app.route('/api/<version>/sensor/type/<sensor_type>')
 
 
 @app.route('/api/<version>/sensor/type/<sensor_type>')


@@ -144,43 +244,27 @@ def sensortype(version, sensor_type):
     mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
     format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
     """
     mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
     format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
     """

-    result = select_sensordata(Sensors.value_type == sensor_type)
+    result = sensordata(sensor_type=sensor_type)

     return jsonify(result)
 
 
     return jsonify(result)
 
 

-@app.route('/data/', defaults={'timespan': 1})
-@app.route("/data/<int:timespan>", 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/<version>/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/<version>/openweathermap/city/<cityid>')
+def openweathermap_city(version, cityid):
+    """List all data found for a city"""
+    result = openweathermapdata(cityid=cityid)
+    return jsonify(result)

 
 
 @app.route("/")
 def index():
 
 
 @app.route("/")
 def index():

-    airvalue, airtime     = currentairtemperature(apikey, cityid)
+    airvalue, airtime     = currentairtemperature(cityid)

     watervalue, watertime = currentwatertemperature(mainsensor)
 
     return render_template(
     watervalue, watertime = currentwatertemperature(mainsensor)
 
     return render_template(