Add a sqlalchemy group by/having example

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

diff --git a/web/seepark_web.py b/web/seepark_web.py
index 7f39c15eec9e07140c827e93a5b9151e8198d540..9871b040d87bbca510124decb3f2b42e9fd15c8e 100644 (file)
--- a/web/seepark_web.py
+++ b/web/seepark_web.py
@@ -1,21 +1,14 @@
-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
-
-

 # https://stackoverflow.com/a/37350445
 def sqlalchemy_model_to_dict(model):
     return {c.key: getattr(model, c.key)
 # https://stackoverflow.com/a/37350445
 def sqlalchemy_model_to_dict(model):
     return {c.key: getattr(model, c.key)


@@ -47,7 +40,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,6 +54,10 @@ class Sensors(db.Model):
     __tablename__ = 'sensors'
 
 
     __tablename__ = 'sensors'
 
 

+class OpenWeatherMap(db.Model):
+    __tablename__ = 'openweathermap'
+
+

 def select_sensordata(sensor_id, sensor_type, begin, end, mode):
     query = Sensors.query
     if sensor_id is not None:
 def select_sensordata(sensor_id, sensor_type, begin, end, mode):
     query = Sensors.query
     if sensor_id is not None:


@@ -71,7 +68,39 @@ 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:
+    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):
+    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(
         # copied from munin/master/_bin/munin-cgi-graph.in
         # interval in seconds for data points
         resolutions = dict(


@@ -96,35 +125,58 @@ def select_sensordata(sensor_id, sensor_type, begin, end, mode):
     return query.all()
 
 
     return query.all()
 
 

-def convert_to_c3(result):
+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[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')
 
     result = select_sensordata(sensor_id, sensor_type, begin, end, mode)
 
     if format == 'c3':
     mode = request.args.get('mode', 'full')
     format = request.args.get('format', 'default')
 
     result = select_sensordata(sensor_id, sensor_type, begin, end, mode)
 
     if format == 'c3':

-        return convert_to_c3(result)
+        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')
+
+    result = select_openweatherdata(cityid, begin, end, mode)
+
+    if format == 'c3':
+        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:
-        url, 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):


@@ -167,39 +219,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(