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
__tablename__ = 'openweathermap'
-def select_sensordata(sensor_id, sensor_type, begin, end, mode):
+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 = 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()
+
+
+def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
+ # determine resolution (interval in seconds for data points)
+ # 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']
+
+ # 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.min(Sensors.timestamp), func.mean(Sensors.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()
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')