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 b1329602fcd089356dc1fcae021487832c91c2f8..9871b040d87bbca510124decb3f2b42e9fd15c8e 100644 (file)
--- a/web/seepark_web.py
+++ b/web/seepark_web.py
@@ -1,4 +1,3 @@
-from random import uniform
 import datetime
 import time
 import configparser
@@ -10,12 +9,6 @@ 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)
@@ -47,7 +40,7 @@ config = configparser.ConfigParser()
 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
@@ -86,17 +79,18 @@ def select_sensordata(sensor_id, sensor_type, begin, end, mode):
         )
         duration = (end - begin).total_seconds()
         day = 60 * 60 * 24
-        if duration < day:
+        if duration <= day:
             resolution = resolutions['day']
-        elif duration < 7 * day:
+        elif duration <= 7 * day:
             resolution = resolutions['week']
-        elif duration < 31 * day:
+        elif duration <= 31 * day:
             resolution = resolutions['month']
         else:
             resolution = resolutions['year']
         # TODO: filter out samples from 'result'
-        # something like 
+        # 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()
 
 
@@ -239,36 +233,6 @@ def openweathermap_city(version, cityid):
     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("/")
 def index():
     airvalue, airtime     = currentairtemperature(cityid)