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 calc_grouping_resolution(begin, end):
76 """How many data points should be between the timestamps begin and end?"""
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']
97 def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
98 # determine resolution (interval in seconds for data points)
99 resolution = calc_grouping_resolution(begin, end)
101 # Let the database do the grouping. Example in SQL (MySQL):
102 # 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;
103 query = db.session.query(func.to_seconds(Sensors.timestamp).op('div')(resolution).label('g'),
104 func.min(Sensors.timestamp).label('timestamp'),
105 func.avg(Sensors.value).label('value'),
106 Sensors.sensor_id, Sensors.value_type, Sensors.sensor_name)
107 if sensor_id is not None:
108 query = query.filter(Sensors.sensor_id == sensor_id)
109 if sensor_type is not None:
110 query = query.filter(Sensors.value_type == sensor_type)
111 query = query.filter(Sensors.timestamp >= begin)
112 query = query.filter(Sensors.timestamp <= end)
113 query = query.group_by('g', Sensors.sensor_id, Sensors.value_type, Sensors.sensor_name)
117 def select_openweatherdata(cityid, begin, end):
118 query = OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid)
119 if begin is not None:
120 query = query.filter(OpenWeatherMap.datetime >= begin)
122 query = query.filter(OpenWeatherMap.datetime <= end)
126 def select_openweatherdata_grouped(cityid, begin, end):
127 # determine resolution (interval in seconds for data points)
128 resolution = calc_grouping_resolution(begin, end)
130 # Let the database do the grouping. Example in SQL (MySQL):
131 # 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;
132 query = db.session.query(func.to_seconds(OpenWeatherMap.datetime).op('div')(resolution).label('g'),
133 func.min(OpenWeatherMap.datetime).label('datetime'),
134 func.avg(OpenWeatherMap.temp).label('temp'),
135 OpenWeatherMap.cityid)
136 OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid)
137 query = query.filter(OpenWeatherMap.datetime >= begin)
138 query = query.filter(OpenWeatherMap.datetime <= end)
139 query = query.group_by('g', OpenWeatherMap.cityid)
143 def convert_to_c3(result, id, field_x, field_y):
144 c3result = defaultdict(list)
146 c3result[str(getattr(row, id))].append(getattr(row, field_y))
147 dt = getattr(row, field_x).strftime('%Y-%m-%d %H:%M:%S')
148 c3result[str(getattr(row, id)) + '_x'].append(dt)
152 def request_arg(key, type, default=None):
153 """Returns the key from the request if available, otherwise the default value.
154 In case type is provided and the key is present, the value is converted by calling type.
155 In other words: Reimplement request.args.get but don't return default value if
156 type raises a ValueError."""
157 if key in request.args:
159 return type(request.args[key])
160 except ValueError as e:
161 abort(Response(str(e), 400))
166 def sensordata(sensor_id=None, sensor_type=None):
167 begin = request_arg('begin', parse_datetime)
168 end = request_arg('end', parse_datetime)
169 mode = request.args.get('mode', 'full')
170 format = request.args.get('format', 'default')
173 result = select_sensordata(sensor_id, sensor_type, begin, end)
174 elif mode == 'consolidated':
175 if begin is None or end is None:
176 abort(Response('begin and end have to be set for mode==consolidated', 400))
177 result = select_sensordata_grouped(sensor_id, sensor_type, begin, end)
179 abort(Response('unknown value for mode', 400))
182 return convert_to_c3(result, 'sensor_id', 'timestamp', 'value')
186 def openweathermapdata(cityid):
187 begin = request_arg('begin', parse_datetime)
188 end = request_arg('end', parse_datetime)
189 mode = request.args.get('mode', 'full')
190 format = request.args.get('format', 'default')
193 result = select_openweatherdata(cityid, begin, end)
194 elif mode == 'consolidated':
195 if begin is None or end is None:
196 abort(Response('begin and end have to be set for mode==consolidated', 400))
197 result = select_openweatherdata_grouped(cityid, begin, end)
199 abort(Response('unknown value for mode', 400))
202 return convert_to_c3(result, 'cityid', 'datetime', 'temp')
206 def currentairtemperature(cityid):
207 result = OpenWeatherMap.query.filter_by(cityid=cityid).order_by(OpenWeatherMap.datetime.desc()).first()
208 return result.temp, result.datetime
211 def currentwatertemperature(sensorid):
212 result = Sensors.query.filter_by(sensor_id=sensorid).order_by(Sensors.timestamp.desc()).first()
213 return result.value, result.timestamp
216 @app.route('/api/<version>/sensors/')
217 def sensors(version):
218 """List all sensors found in the database"""
219 result = db.session.query(Sensors.sensor_id, Sensors.sensor_name, Sensors.value_type).distinct().all()
220 return jsonify(result)
223 @app.route('/api/<version>/sensor/id/<sensor_id>')
224 def sensorid(version, sensor_id):
225 """Return all data for a specific sensor
228 begin=<datetime>, optional, format like "2018-05-19T21:07:53"
229 end=<datetime>, optional, format like "2018-05-19T21:07:53"
230 mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
231 format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
233 result = sensordata(sensor_id=sensor_id)
234 return jsonify(result)
237 @app.route('/api/<version>/sensor/type/<sensor_type>')
238 def sensortype(version, sensor_type):
239 """Return all data for a specific sensor type
242 begin=<datetime>, optional, format like "2018-05-19T21:07:53"
243 end=<datetime>, optional, format like "2018-05-19T21:07:53"
244 mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
245 format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
247 result = sensordata(sensor_type=sensor_type)
248 return jsonify(result)
251 @app.route('/api/<version>/openweathermap/cities')
252 def openweathermap_cities(version):
253 """List all city IDs found in the database"""
254 result = db.session.query(OpenWeatherMap.cityid).distinct().all()
255 return jsonify(result)
258 @app.route('/api/<version>/openweathermap/city/<cityid>')
259 def openweathermap_city(version, cityid):
260 """List all data found for a city"""
261 result = openweathermapdata(cityid=cityid)
262 return jsonify(result)
267 airvalue, airtime = currentairtemperature(cityid)
268 watervalue, watertime = currentwatertemperature(mainsensor)
270 return render_template(
273 watervalue=watervalue,