1 from random import uniform
7 from collections import defaultdict
8 from flask import Flask, render_template, jsonify, request, abort, Response
10 from flask_sqlalchemy import SQLAlchemy, inspect
13 app_path = os.path.dirname(os.path.realpath(__file__))
14 lib_path = os.path.join(app_path, '..')
15 sys.path.append(lib_path)
16 from seeparklib.openweathermap import openweathermap_json, OpenWeatherMapError
19 # https://stackoverflow.com/a/37350445
20 def sqlalchemy_model_to_dict(model):
21 return {c.key: getattr(model, c.key)
22 for c in inspect(model).mapper.column_attrs}
25 class JSONEncoder(flask.json.JSONEncoder):
26 def default(self, object):
27 if isinstance(object, datetime.datetime):
28 return object.isoformat()
29 elif isinstance(object, db.Model):
30 return sqlalchemy_model_to_dict(object)
31 return super().default(object)
34 def parse_datetime(date_str):
35 return datetime.datetime.strptime(date_str, '%Y-%m-%dT%H:%M:%S')
38 def get_sqlalchemy_database_uri(config):
39 user = config.get('database', 'user')
40 pwd = config.get('database', 'password')
41 host = config.get('database', 'hostname')
42 db = config.get('database', 'database')
43 return 'mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db)
46 config = configparser.ConfigParser()
47 config.read(os.environ['SEEPARKINI'])
48 apikey = config.get('openweathermap', 'apikey')
49 cityid = config.get('openweathermap', 'cityid')
50 mainsensor = config.get('temperature', 'mainsensor')
53 app.json_encoder = JSONEncoder
54 app.config['SQLALCHEMY_DATABASE_URI'] = get_sqlalchemy_database_uri(config)
55 app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
60 class Sensors(db.Model):
61 __tablename__ = 'sensors'
64 class OpenWeatherMap(db.Model):
65 __tablename__ = 'openweathermap'
68 def select_sensordata(sensor_id, sensor_type, begin, end, mode):
70 if sensor_id is not None:
71 query = query.filter(Sensors.sensor_id == sensor_id)
72 if sensor_type is not None:
73 query = query.filter(Sensors.value_type == sensor_type)
75 query = query.filter(Sensors.timestamp >= begin)
77 query = query.filter(Sensors.timestamp <= end)
78 if mode == 'consolidated' and begin is None and end is None:
79 # copied from munin/master/_bin/munin-cgi-graph.in
80 # interval in seconds for data points
87 duration = (end - begin).total_seconds()
90 resolution = resolutions['day']
91 elif duration < 7 * day:
92 resolution = resolutions['week']
93 elif duration < 31 * day:
94 resolution = resolutions['month']
96 resolution = resolutions['year']
97 # TODO: filter out samples from 'result'
99 # 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 def select_openweatherdata(cityid, begin, end, mode):
104 query = OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid)
105 if begin is not None:
106 query = query.filter(OpenWeatherMap.datetime >= begin)
108 query = query.filter(OpenWeatherMap.datetime <= end)
109 if mode == 'consolidated' and begin is None and end is None:
110 # copied from munin/master/_bin/munin-cgi-graph.in
111 # interval in seconds for data points
118 duration = (end - begin).total_seconds()
121 resolution = resolutions['day']
122 elif duration < 7 * day:
123 resolution = resolutions['week']
124 elif duration < 31 * day:
125 resolution = resolutions['month']
127 resolution = resolutions['year']
128 # TODO: filter out samples from 'result'
130 # 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;
134 def convert_to_c3(result):
135 c3result = defaultdict(list)
137 c3result[row.sensor_id].append(row.value)
138 dt = row.timestamp.strftime('%Y-%m-%d %H:%M:%S')
139 c3result[row.sensor_id + '_x'].append(dt)
143 def request_arg(key, type, default=None):
144 """Returns the key from the request if available, otherwise the default value.
145 In case type is provided and the key is present, the value is converted by calling type.
146 In other words: Reimplement request.args.get but don't return default value if
147 type raises a ValueError."""
148 if key in request.args:
150 return type(request.args[key])
151 except ValueError as e:
152 abort(Response(str(e), 400))
157 def sensordata(sensor_id=None, sensor_type=None):
158 begin = request_arg('begin', parse_datetime)
159 end = request_arg('end', parse_datetime)
160 mode = request.args.get('mode', 'full')
161 format = request.args.get('format', 'default')
163 result = select_sensordata(sensor_id, sensor_type, begin, end, mode)
166 return convert_to_c3(result)
170 def openweathermapdata(cityid):
171 begin = request_arg('begin', parse_datetime)
172 end = request_arg('end', parse_datetime)
173 mode = request.args.get('mode', 'full')
174 format = request.args.get('format', 'default')
176 result = select_openweatherdata(cityid, begin, end, mode)
179 return convert_to_c3(result)
183 def currentairtemperature(apikey, cityid):
184 """Retruns the tuple temperature, datetime (as float, datetime) in case of success, otherwise None, None."""
186 url, weatherdata = openweathermap_json(apikey, cityid)
187 return weatherdata['main']['temp'], datetime.datetime.fromtimestamp(weatherdata['dt'])
188 except OpenWeatherMapError:
192 def currentwatertemperature(sensorid):
193 result = Sensors.query.filter_by(sensor_id=sensorid).order_by(Sensors.timestamp.desc()).first()
194 return result.value, result.timestamp
197 @app.route('/api/<version>/sensors/')
198 def sensors(version):
199 """List all sensors found in the database"""
200 result = db.session.query(Sensors.sensor_id, Sensors.sensor_name, Sensors.value_type).distinct().all()
201 return jsonify(result)
204 @app.route('/api/<version>/sensor/id/<sensor_id>')
205 def sensorid(version, sensor_id):
206 """Return all data for a specific sensor
209 begin=<datetime>, optional, format like "2018-05-19T21:07:53"
210 end=<datetime>, optional, format like "2018-05-19T21:07:53"
211 mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
212 format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
214 result = sensordata(sensor_id=sensor_id)
215 return jsonify(result)
218 @app.route('/api/<version>/sensor/type/<sensor_type>')
219 def sensortype(version, sensor_type):
220 """Return all data for a specific sensor type
223 begin=<datetime>, optional, format like "2018-05-19T21:07:53"
224 end=<datetime>, optional, format like "2018-05-19T21:07:53"
225 mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
226 format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
228 result = sensordata(sensor_type=sensor_type)
229 return jsonify(result)
232 @app.route('/api/<version>/openweathermap/cities')
233 def openweathermap_cities(version):
234 """List all city IDs found in the database"""
235 result = db.session.query(OpenWeatherMap.cityid).distinct().all()
236 return jsonify(result)
239 @app.route('/api/<version>/openweathermap/city/<cityid>')
240 def openweathermap_city(version, cityid):
241 """List all data found for a city"""
242 result = openweathermapdata(cityid=cityid)
243 return jsonify(result)
246 @app.route('/data/', defaults={'timespan': 1})
247 @app.route("/data/<int:timespan>", methods=['GET'])
249 granularity = 5 * timespan # (every) minute(s) per day
250 samples = 60/granularity * 24 * timespan # per hour over whole timespan
256 start = end - samples * granularity * 60
258 for i in range(int(samples)):
259 s4m.append(uniform(-10,30))
260 s5m.append(uniform(-10,30))
261 s4mt = uniform(start, end)
262 s5mt = uniform(start, end)
263 s4m_x.append(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(s4mt)))
264 s5m_x.append(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(s5mt)))
268 '0316a2193bff_x': s4m_x,
270 '0316a21383ff_x': s5m_x,
278 airvalue, airtime = currentairtemperature(apikey, cityid)
279 watervalue, watertime = currentwatertemperature(mainsensor)
281 return render_template(
284 watervalue=watervalue,