X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/7089107ff2449e6d62cf0d16bca58a1823ffaf4b..07f6fd2c6041d6203380a60771ce1cffdc2f68a7:/web/seepark_web.py diff --git a/web/seepark_web.py b/web/seepark_web.py index c0fd440..e8b9da1 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -4,19 +4,30 @@ import time import configparser import os import sys +from collections import defaultdict from flask import Flask, render_template, jsonify, request import flask.json -from sqlalchemy import create_engine -import requests +from flask_sqlalchemy import SQLAlchemy, inspect -sys.path.append('..') + +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) @@ -24,107 +35,119 @@ def parse_datetime(date_str): return datetime.datetime.strptime(date_str, '%Y-%m-%dT%H:%M:%S') -app = Flask(__name__) -app.json_encoder = JSONEncoder -config = configparser.ConfigParser() -config.read(os.environ['SEEPARKINI']) -apikey = config.get('openweathermap', 'apikey') -cityid = config.get('openweathermap', 'cityid') - - -def open_engine(config): +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') - engine = create_engine('mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db), echo=False) - return engine - - -def select_sensordata(initial_where, initial_sql_args): - engine = open_engine(config) - with engine.connect() as conn: - where = [initial_where] - sql_args = [initial_sql_args] - begin = None - end = None - if 'begin' in request.args: - where.append('timestamp>=%s') - begin = request.args.get('begin', None, parse_datetime) - sql_args.append(begin) - if 'end' in request.args: - where.append('timestamp<=%s') - end = request.args.get('end', None, parse_datetime) - sql_args.append(end) - sql = 'select * from sensors where {} order by id'.format(' and '.join(where)) - cursor = conn.execute(sql, *sql_args) - result = [dict(row) for row in cursor] + return 'mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db) - mode = request.args.get('mode', 'full') - if mode == 'consolidated': - if begin is None or end is None: - pass - else: - # copied from munin/master/_bin/munin-cgi-graph.in - 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' - # like loop over results and skip if timestamp(n+1)-timestamp(n)= 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 mean(temperature) from sensors where ... group by mod(timestamp, resolution) + # func.avg(...) + # + # from https://stackoverflow.com/questions/4342370/grouping-into-interval-of-5-minutes-within-a-time-range + # SELECT + # timestamp, -- not sure about that + # name, + # count(b.name) + # FROM time a, id + # WHERE … + # GROUP BY + # UNIX_TIMESTAMP(timestamp) DIV 300, name + 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 sensordata(sensor_id=None, sensor_type=None): + begin = request.args.get('begin', None, parse_datetime) + end = request.args.get('end', None, 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': - c3result = dict() - for row in result: - if not row['sensor_id'] in c3result: - c3result[row['sensor_id']] = list() - c3result[row['sensor_id']].append(row['value']) - if not row['sensor_id'] + '_x' in c3result: - c3result[row['sensor_id'] + '_x'] = list() - dt = row['timestamp'].strftime('%Y-%m-%d %H:%M:%S') - c3result[row['sensor_id'] + '_x'].append(dt) - result = c3result + return convert_to_c3(result) return result def currentairtemperature(apikey, cityid): """Retruns the tuple temperature, datetime (as float, datetime) in case of success, otherwise None, None.""" try: - weatherdata = openweathermap_json(apikey, cityid) + url, weatherdata = openweathermap_json(apikey, cityid) return weatherdata['main']['temp'], datetime.datetime.fromtimestamp(weatherdata['dt']) except OpenWeatherMapError: return None, None def currentwatertemperature(sensorid): - engine = open_engine(config) - with engine.connect() as conn: - cursor = conn.execute('select value, timestamp from sensors where sensor_id=%s order by timestamp desc limit 1', sensorid) - result = [dict(row) for row in cursor] - return result[0]['value'], result[0]['timestamp'].strftime('%Y-%m-%d %H:%M') + 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""" - engine = open_engine(config) - with engine.connect() as conn: - cursor = conn.execute('select distinct sensor_id, sensor_name, value_type from sensors') - result = [dict(row) for row in cursor] - return jsonify(result) + result = db.session.query(Sensors.sensor_id, Sensors.sensor_name, Sensors.value_type).distinct().all() + return jsonify(result) @app.route('/api//sensor/id/') @@ -137,7 +160,7 @@ def sensorid(version, sensor_id): 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 = select_sensordata('sensor_id=%s', sensor_id) + result = sensordata(sensor_id=sensor_id) return jsonify(result) @@ -151,7 +174,7 @@ def sensortype(version, sensor_type): 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 = select_sensordata('value_type=%s', sensor_type) + result = sensordata(sensor_type=sensor_type) return jsonify(result) @@ -188,9 +211,7 @@ def data(timespan): @app.route("/") def index(): airvalue, airtime = currentairtemperature(apikey, cityid) - airvalue = "{:.1f}".format(airvalue) if isinstance(airvalue, float) else airvalue - watervalue, watertime = currentwatertemperature('0316a21383ff') # config? mainwatertemp? - watervalue = "{:.1f}".format(watervalue) if isinstance(watervalue, float) else watervalue + watervalue, watertime = currentwatertemperature(mainsensor) return render_template( 'seepark_web.html',