X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/d52005ab2b813753c3e2206fd4dbc8dcae7e9852..ca2ff71b9a5b9d5efcdec8ec56c43c74f0818f4f:/web/seepark_web.py?ds=sidebyside diff --git a/web/seepark_web.py b/web/seepark_web.py index 3ca0b51..3ee9d94 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -4,12 +4,15 @@ 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(cur_path, '..') +sys.path.append(lib_path) from seeparklib.openweathermap import openweathermap_json, OpenWeatherMapError @@ -24,41 +27,45 @@ def parse_datetime(date_str): return datetime.datetime.strptime(date_str, '%Y-%m-%dT%H:%M:%S') -app = Flask(__name__) -app.json_encoder = JSONEncoder +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') - -def open_engine(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] +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' + + # https://stackoverflow.com/a/37350445 + def to_dict(self): + return {c.key: getattr(self, c.key) + for c in inspect(self).mapper.column_attrs} + +def select_sensordata(initial_where): + query = Sensors.query.filter(initial_where) + begin = request.args.get('begin', None, parse_datetime) + end = request.args.get('end', None, parse_datetime) + if begin is not None: + query = query.filter(Sensors.timestamp >= begin) + if end is not None: + query = query.filter(Sensors.timestamp <= end) + result = query.all() mode = request.args.get('mode', 'full') if mode == 'consolidated': @@ -87,17 +94,14 @@ def select_sensordata(initial_where, initial_sql_args): format = request.args.get('format', 'default') if format == 'c3': - c3result = dict() + c3result = defaultdict(list) 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 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 + else: + return [row.to_dict() for row in result] def currentairtemperature(apikey, cityid): @@ -110,21 +114,15 @@ def currentairtemperature(apikey, cityid): 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 +135,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 = select_sensordata(Sensors.sensor_id == sensor_id) return jsonify(result) @@ -151,7 +149,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 = select_sensordata(Sensors.value_type == sensor_type) return jsonify(result) @@ -188,8 +186,7 @@ def data(timespan): @app.route("/") def index(): airvalue, airtime = currentairtemperature(apikey, cityid) - 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',