from random import uniform import datetime import time import configparser import os import sys from collections import defaultdict from flask import Flask, render_template, jsonify, request, abort, Response import flask.json from flask_sqlalchemy import SQLAlchemy, inspect app_path = os.path.dirname(os.path.realpath(__file__)) lib_path = os.path.join(app_path, '..') sys.path.append(lib_path) from seeparklib.openweathermap import openweathermap_json, OpenWeatherMapError # https://stackoverflow.com/a/37350445 def sqlalchemy_model_to_dict(model): return {c.key: getattr(model, c.key) for c in inspect(model).mapper.column_attrs} class JSONEncoder(flask.json.JSONEncoder): def default(self, object): if isinstance(object, datetime.datetime): return object.isoformat() elif isinstance(object, db.Model): return sqlalchemy_model_to_dict(object) return super().default(object) def parse_datetime(date_str): return datetime.datetime.strptime(date_str, '%Y-%m-%dT%H:%M:%S') def get_sqlalchemy_database_uri(config): user = config.get('database', 'user') pwd = config.get('database', 'password') host = config.get('database', 'hostname') db = config.get('database', 'database') return 'mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db) config = configparser.ConfigParser() config.read(os.environ['SEEPARKINI']) apikey = config.get('openweathermap', 'apikey') cityid = config.get('openweathermap', 'cityid') mainsensor = config.get('temperature', 'mainsensor') app = Flask(__name__) app.json_encoder = JSONEncoder app.config['SQLALCHEMY_DATABASE_URI'] = get_sqlalchemy_database_uri(config) app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False db = SQLAlchemy(app) db.reflect(app=app) class Sensors(db.Model): __tablename__ = 'sensors' class OpenWeatherMap(db.Model): __tablename__ = 'openweathermap' def select_sensordata(sensor_id, sensor_type, begin, end, mode): query = Sensors.query 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) if begin is not None: query = query.filter(Sensors.timestamp >= begin) if end is not None: query = query.filter(Sensors.timestamp <= end) if mode == 'consolidated' and begin is None and end is 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; return query.all() def select_openweatherdata(cityid, begin, end, mode): query = OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid) if begin is not None: query = query.filter(OpenWeatherMap.datetime >= begin) if end is not None: query = query.filter(OpenWeatherMap.datetime <= end) if mode == 'consolidated' and begin is None and end is 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; return query.all() def convert_to_c3(result): c3result = defaultdict(list) for row in result: c3result[row.sensor_id].append(row.value) dt = row.timestamp.strftime('%Y-%m-%d %H:%M:%S') c3result[row.sensor_id + '_x'].append(dt) return c3result def request_arg(key, type, default=None): """Returns the key from the request if available, otherwise the default value. In case type is provided and the key is present, the value is converted by calling type. In other words: Reimplement request.args.get but don't return default value if type raises a ValueError.""" if key in request.args: try: return type(request.args[key]) except ValueError as e: abort(Response(str(e), 400)) else: return default def sensordata(sensor_id=None, sensor_type=None): begin = request_arg('begin', parse_datetime) end = request_arg('end', parse_datetime) mode = request.args.get('mode', 'full') format = request.args.get('format', 'default') result = select_sensordata(sensor_id, sensor_type, begin, end, mode) if format == 'c3': return convert_to_c3(result) return result def openweathermapdata(cityid): begin = request_arg('begin', parse_datetime) end = request_arg('end', parse_datetime) mode = request.args.get('mode', 'full') format = request.args.get('format', 'default') result = select_openweatherdata(cityid, begin, end, mode) if format == 'c3': return convert_to_c3(result) return result def currentairtemperature(cityid): result = OpenWeatherMap.query.filter_by(cityid=cityid).order_by(OpenWeatherMap.datetime.desc()).first() return result.temp, result.datetime def currentwatertemperature(sensorid): result = Sensors.query.filter_by(sensor_id=sensorid).order_by(Sensors.timestamp.desc()).first() return result.value, result.timestamp @app.route('/api//sensors/') def sensors(version): """List all sensors found in the database""" result = db.session.query(Sensors.sensor_id, Sensors.sensor_name, Sensors.value_type).distinct().all() return jsonify(result) @app.route('/api//sensor/id/') def sensorid(version, sensor_id): """Return all data for a specific sensor URL parameters: begin=, optional, format like "2018-05-19T21:07:53" end=, optional, format like "2018-05-19T21:07:53" mode=, optional. return all rows (default) or with lower resolution (for charts) format=, optional. return result as returned by sqlalchemy (default) or formatted for c3.js """ result = sensordata(sensor_id=sensor_id) return jsonify(result) @app.route('/api//sensor/type/') def sensortype(version, sensor_type): """Return all data for a specific sensor type URL parameters: begin=, optional, format like "2018-05-19T21:07:53" end=, optional, format like "2018-05-19T21:07:53" mode=, optional. return all rows (default) or with lower resolution (for charts) format=, optional. return result as returned by sqlalchemy (default) or formatted for c3.js """ result = sensordata(sensor_type=sensor_type) return jsonify(result) @app.route('/api//openweathermap/cities') def openweathermap_cities(version): """List all city IDs found in the database""" result = db.session.query(OpenWeatherMap.cityid).distinct().all() return jsonify(result) @app.route('/api//openweathermap/city/') def openweathermap_city(version, cityid): """List all data found for a city""" result = openweathermapdata(cityid=cityid) return jsonify(result) @app.route('/data/', defaults={'timespan': 1}) @app.route("/data/", methods=['GET']) def data(timespan): granularity = 5 * timespan # (every) minute(s) per day samples = 60/granularity * 24 * timespan # per hour over whole timespan s4m = [] s4m_x = [] s5m = [] s5m_x = [] end = time.time() start = end - samples * granularity * 60 for i in range(int(samples)): s4m.append(uniform(-10,30)) s5m.append(uniform(-10,30)) s4mt = uniform(start, end) s5mt = uniform(start, end) s4m_x.append(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(s4mt))) s5m_x.append(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(s5mt))) data = { '0316a2193bff': s4m, '0316a2193bff_x': s4m_x, '0316a21383ff': s5m, '0316a21383ff_x': s5m_x, } return jsonify(data) @app.route("/") def index(): airvalue, airtime = currentairtemperature(cityid) watervalue, watertime = currentwatertemperature(mainsensor) return render_template( 'seepark_web.html', apikey=apikey, watervalue=watervalue, watertime=watertime, airvalue=airvalue, airtime=airtime, )