X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/92f9f7fe9641e4a7ecc4892194e38d6ff7607f28..21ba143aa9998e73cb5d74144aba83addeaa7a7b:/web/seepark_web.py?ds=sidebyside diff --git a/web/seepark_web.py b/web/seepark_web.py index 9ad59f1..de719ba 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -3,9 +3,17 @@ import datetime 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 +from flask_sqlalchemy import SQLAlchemy + + +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 class JSONEncoder(flask.json.JSONEncoder): @@ -19,40 +27,41 @@ 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') +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) -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] + +class Sensors(db.Model): + __tablename__ = 'sensors' + + +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': @@ -81,27 +90,34 @@ 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) + c3result[row.sensor_id].append(row.value) + dt = row.timestamp.strftime('%Y-%m-%d %H:%M:%S') + c3result[row.sensor_id + '_x'].append(dt) result = c3result 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) + return weatherdata['main']['temp'], datetime.datetime.fromtimestamp(weatherdata['dt']) + except OpenWeatherMapError: + return None, None + + +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""" - 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/') @@ -114,8 +130,8 @@ 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) - return jsonify(result) + result = select_sensordata(Sensors.sensor_id == sensor_id) + return jsonify([row._asdict() for row in result]) @app.route('/api//sensor/type/') @@ -128,7 +144,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) @@ -164,5 +180,14 @@ def data(timespan): @app.route("/") def index(): - return render_template('seepark_web.html', apikey=apikey) - + airvalue, airtime = currentairtemperature(apikey, cityid) + watervalue, watertime = currentwatertemperature(mainsensor) + + return render_template( + 'seepark_web.html', + apikey=apikey, + watervalue=watervalue, + watertime=watertime, + airvalue=airvalue, + airtime=airtime, + )