6 from collections import defaultdict
7 from flask import Flask, render_template, jsonify, request, abort, Response
9 from flask_sqlalchemy import SQLAlchemy, inspect
10 from sqlalchemy import func
13 # https://stackoverflow.com/a/37350445
14 def sqlalchemy_model_to_dict(model):
15 return {c.key: getattr(model, c.key)
16 for c in inspect(model).mapper.column_attrs}
19 class JSONEncoder(flask.json.JSONEncoder):
20 def default(self, object):
21 if isinstance(object, datetime.datetime):
22 return object.isoformat()
23 elif isinstance(object, db.Model):
24 return sqlalchemy_model_to_dict(object)
25 return super().default(object)
28 def parse_datetime(date_str):
29 return datetime.datetime.strptime(date_str, '%Y-%m-%dT%H:%M:%S')
32 def get_sqlalchemy_database_uri(config):
33 user = config.get('database', 'user')
34 pwd = config.get('database', 'password')
35 host = config.get('database', 'hostname')
36 db = config.get('database', 'database')
37 return 'mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db)
40 config = configparser.ConfigParser()
41 config.read(os.environ['SEEPARKINI'])
42 apikey = config.get('openweathermap', 'apikey')
43 cityid = config.get('openweathermap', 'cityid')
44 mainsensor = config.get('webapp', 'mainsensor')
47 app.json_encoder = JSONEncoder
48 app.config['SQLALCHEMY_DATABASE_URI'] = get_sqlalchemy_database_uri(config)
49 app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
54 class Sensors(db.Model):
55 __tablename__ = 'sensors'
58 class OpenWeatherMap(db.Model):
59 __tablename__ = 'openweathermap'
62 def select_sensordata(sensor_id, sensor_type, begin, end):
64 if sensor_id is not None:
65 query = query.filter(Sensors.sensor_id == sensor_id)
66 if sensor_type is not None:
67 query = query.filter(Sensors.value_type == sensor_type)
69 query = query.filter(Sensors.timestamp >= begin)
71 query = query.filter(Sensors.timestamp <= end)
75 def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
76 # determine resolution (interval in seconds for data points)
77 # copied from munin/master/_bin/munin-cgi-graph.in
84 duration = (end - begin).total_seconds()
87 resolution = resolutions['day']
88 elif duration <= 7 * day:
89 resolution = resolutions['week']
90 elif duration <= 31 * day:
91 resolution = resolutions['month']
93 resolution = resolutions['year']
95 # Let the database do the grouping. Example in SQL (MySQL):
96 # 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;
97 query = db.session.query(func.to_seconds(Sensors.timestamp).op('div')(resolution).label('g'),
98 func.min(Sensors.timestamp).label('timestamp'),
99 func.avg(Sensors.value).label('value'),
100 Sensors.sensor_id, Sensors.value_type, Sensors.sensor_name)
101 if sensor_id is not None:
102 query = query.filter(Sensors.sensor_id == sensor_id)
103 if sensor_type is not None:
104 query = query.filter(Sensors.value_type == sensor_type)
105 query = query.filter(Sensors.timestamp >= begin)
106 query = query.filter(Sensors.timestamp <= end)
107 query = query.group_by('g', Sensors.sensor_id, Sensors.value_type, Sensors.sensor_name)
111 def select_openweatherdata(cityid, begin, end, mode):
112 query = OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid)
113 if begin is not None:
114 query = query.filter(OpenWeatherMap.datetime >= begin)
116 query = query.filter(OpenWeatherMap.datetime <= end)
117 if mode == 'consolidated' and begin is not None and end is not None:
118 # copied from munin/master/_bin/munin-cgi-graph.in
119 # interval in seconds for data points
126 duration = (end - begin).total_seconds()
129 resolution = resolutions['day']
130 elif duration < 7 * day:
131 resolution = resolutions['week']
132 elif duration < 31 * day:
133 resolution = resolutions['month']
135 resolution = resolutions['year']
136 # TODO: filter out samples from 'result'
138 # 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;
142 def convert_to_c3(result, id, field_x, field_y):
143 c3result = defaultdict(list)
145 c3result[str(getattr(row, id))].append(getattr(row, field_y))
146 dt = getattr(row, field_x).strftime('%Y-%m-%d %H:%M:%S')
147 c3result[str(getattr(row, id)) + '_x'].append(dt)
151 def request_arg(key, type, default=None):
152 """Returns the key from the request if available, otherwise the default value.
153 In case type is provided and the key is present, the value is converted by calling type.
154 In other words: Reimplement request.args.get but don't return default value if
155 type raises a ValueError."""
156 if key in request.args:
158 return type(request.args[key])
159 except ValueError as e:
160 abort(Response(str(e), 400))
165 def sensordata(sensor_id=None, sensor_type=None):
166 begin = request_arg('begin', parse_datetime)
167 end = request_arg('end', parse_datetime)
168 mode = request.args.get('mode', 'full')
169 format = request.args.get('format', 'default')
172 result = select_sensordata(sensor_id, sensor_type, begin, end)
173 elif mode == 'consolidated':
174 if begin is None or end is None:
175 abort(Response('begin and end have to be set for mode==consolidated', 400))
176 result = select_sensordata_grouped(sensor_id, sensor_type, begin, end)
178 abort(Response('unknown value for mode', 400))
181 return convert_to_c3(result, 'sensor_id', 'timestamp', 'value')
185 def openweathermapdata(cityid):
186 begin = request_arg('begin', parse_datetime)
187 end = request_arg('end', parse_datetime)
188 mode = request.args.get('mode', 'full')
189 format = request.args.get('format', 'default')
191 result = select_openweatherdata(cityid, begin, end, mode)
194 return convert_to_c3(result, 'cityid', 'datetime', 'temp')
198 def currentairtemperature(cityid):
199 result = OpenWeatherMap.query.filter_by(cityid=cityid).order_by(OpenWeatherMap.datetime.desc()).first()
200 return result.temp, result.datetime
203 def currentwatertemperature(sensorid):
204 result = Sensors.query.filter_by(sensor_id=sensorid).order_by(Sensors.timestamp.desc()).first()
205 return result.value, result.timestamp
208 @app.route('/api/<version>/sensors/')
209 def sensors(version):
210 """List all sensors found in the database"""
211 result = db.session.query(Sensors.sensor_id, Sensors.sensor_name, Sensors.value_type).distinct().all()
212 return jsonify(result)
215 @app.route('/api/<version>/sensor/id/<sensor_id>')
216 def sensorid(version, sensor_id):
217 """Return all data for a specific sensor
220 begin=<datetime>, optional, format like "2018-05-19T21:07:53"
221 end=<datetime>, optional, format like "2018-05-19T21:07:53"
222 mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
223 format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
225 result = sensordata(sensor_id=sensor_id)
226 return jsonify(result)
229 @app.route('/api/<version>/sensor/type/<sensor_type>')
230 def sensortype(version, sensor_type):
231 """Return all data for a specific sensor type
234 begin=<datetime>, optional, format like "2018-05-19T21:07:53"
235 end=<datetime>, optional, format like "2018-05-19T21:07:53"
236 mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
237 format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
239 result = sensordata(sensor_type=sensor_type)
240 return jsonify(result)
243 @app.route('/api/<version>/openweathermap/cities')
244 def openweathermap_cities(version):
245 """List all city IDs found in the database"""
246 result = db.session.query(OpenWeatherMap.cityid).distinct().all()
247 return jsonify(result)
250 @app.route('/api/<version>/openweathermap/city/<cityid>')
251 def openweathermap_city(version, cityid):
252 """List all data found for a city"""
253 result = openweathermapdata(cityid=cityid)
254 return jsonify(result)
259 airvalue, airtime = currentairtemperature(cityid)
260 watervalue, watertime = currentwatertemperature(mainsensor)
262 return render_template(
265 watervalue=watervalue,