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 c19e9b92e177cfac69d57eadd6f9ba9cca5d0a6a..59e0c8b0264d8d0a0a9067afea2e2f9c1dc82e9c 100644 (file)
--- 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
 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
 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
 
 
 # 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')
 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


@@ -61,7 +55,33 @@ class Sensors(db.Model):
     __tablename__ = 'sensors'
 
 
     __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)
     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)
         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()
 
 
     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 = 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
 
 
     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):
 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')
 
     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':
 
     if format == 'c3':

-        return convert_to_c3(result)
+        return convert_to_c3(result, 'sensor_id', 'timestamp', 'value')

     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 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):
 
 
 def currentwatertemperature(sensorid):


@@ -178,39 +248,23 @@ def sensortype(version, 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(