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'), func.min(Sensors.timestamp), func.mean(Sensors.value),
98 Sensors.sensor_id, Sensors.value_type, Sensors.sensor_name)
99 if sensor_id is not None:
100 query = query.filter(Sensors.sensor_id == sensor_id)
101 if sensor_type is not None:
102 query = query.filter(Sensors.value_type == sensor_type)
103 query = query.filter(Sensors.timestamp >= begin)
104 query = query.filter(Sensors.timestamp <= end)
105 query = query.group_by('g', Sensors.sensor_id, Sensors.value_type, Sensors.sensor_name)
109 def select_openweatherdata(cityid, begin, end, mode):
110 query = OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid)
111 if begin is not None:
112 query = query.filter(OpenWeatherMap.datetime >= begin)
114 query = query.filter(OpenWeatherMap.datetime <= end)
115 if mode == 'consolidated' and begin is not None and end is not None:
116 # copied from munin/master/_bin/munin-cgi-graph.in
117 # interval in seconds for data points
124 duration = (end - begin).total_seconds()
127 resolution = resolutions['day']
128 elif duration < 7 * day:
129 resolution = resolutions['week']
130 elif duration < 31 * day:
131 resolution = resolutions['month']
133 resolution = resolutions['year']
134 # TODO: filter out samples from 'result'
136 # 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;
140 def convert_to_c3(result, id, field_x, field_y):
141 c3result = defaultdict(list)
143 c3result[str(getattr(row, id))].append(getattr(row, field_y))
144 dt = getattr(row, field_x).strftime('%Y-%m-%d %H:%M:%S')
145 c3result[str(getattr(row, id)) + '_x'].append(dt)
149 def request_arg(key, type, default=None):
150 """Returns the key from the request if available, otherwise the default value.
151 In case type is provided and the key is present, the value is converted by calling type.
152 In other words: Reimplement request.args.get but don't return default value if
153 type raises a ValueError."""
154 if key in request.args:
156 return type(request.args[key])
157 except ValueError as e:
158 abort(Response(str(e), 400))
163 def sensordata(sensor_id=None, sensor_type=None):
164 begin = request_arg('begin', parse_datetime)
165 end = request_arg('end', parse_datetime)
166 mode = request.args.get('mode', 'full')
167 format = request.args.get('format', 'default')
170 result = select_sensordata(sensor_id, sensor_type, begin, end)
171 elif mode == 'consolidated':
172 if begin is None or end is None:
173 abort(Response('begin and end have to be set for mode==consolidated', 400))
174 result = select_sensordata_grouped(sensor_id, sensor_type, begin, end)
176 abort(Response('unknown value for mode', 400))
179 return convert_to_c3(result, 'sensor_id', 'timestamp', 'value')
183 def openweathermapdata(cityid):
184 begin = request_arg('begin', parse_datetime)
185 end = request_arg('end', parse_datetime)
186 mode = request.args.get('mode', 'full')
187 format = request.args.get('format', 'default')
189 result = select_openweatherdata(cityid, begin, end, mode)
192 return convert_to_c3(result, 'cityid', 'datetime', 'temp')
196 def currentairtemperature(cityid):
197 result = OpenWeatherMap.query.filter_by(cityid=cityid).order_by(OpenWeatherMap.datetime.desc()).first()
198 return result.temp, result.datetime
201 def currentwatertemperature(sensorid):
202 result = Sensors.query.filter_by(sensor_id=sensorid).order_by(Sensors.timestamp.desc()).first()
203 return result.value, result.timestamp
206 @app.route('/api/<version>/sensors/')
207 def sensors(version):
208 """List all sensors found in the database"""
209 result = db.session.query(Sensors.sensor_id, Sensors.sensor_name, Sensors.value_type).distinct().all()
210 return jsonify(result)
213 @app.route('/api/<version>/sensor/id/<sensor_id>')
214 def sensorid(version, sensor_id):
215 """Return all data for a specific sensor
218 begin=<datetime>, optional, format like "2018-05-19T21:07:53"
219 end=<datetime>, optional, format like "2018-05-19T21:07:53"
220 mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
221 format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
223 result = sensordata(sensor_id=sensor_id)
224 return jsonify(result)
227 @app.route('/api/<version>/sensor/type/<sensor_type>')
228 def sensortype(version, sensor_type):
229 """Return all data for a specific sensor type
232 begin=<datetime>, optional, format like "2018-05-19T21:07:53"
233 end=<datetime>, optional, format like "2018-05-19T21:07:53"
234 mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
235 format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
237 result = sensordata(sensor_type=sensor_type)
238 return jsonify(result)
241 @app.route('/api/<version>/openweathermap/cities')
242 def openweathermap_cities(version):
243 """List all city IDs found in the database"""
244 result = db.session.query(OpenWeatherMap.cityid).distinct().all()
245 return jsonify(result)
248 @app.route('/api/<version>/openweathermap/city/<cityid>')
249 def openweathermap_city(version, cityid):
250 """List all data found for a city"""
251 result = openweathermapdata(cityid=cityid)
252 return jsonify(result)
257 airvalue, airtime = currentairtemperature(cityid)
258 watervalue, watertime = currentwatertemperature(mainsensor)
260 return render_template(
263 watervalue=watervalue,