From 38730e4b9fe34e1496fa06e2f80dd9b7fc5fdbd8 Mon Sep 17 00:00:00 2001 From: gregor herrmann Date: Fri, 23 Nov 2018 00:32:16 +0100 Subject: [PATCH 1/1] merge seewasser.py from master into web --- seewasser.py | 42 +++++++++++++++++++++++++++++++++--------- 1 file changed, 33 insertions(+), 9 deletions(-) diff --git a/seewasser.py b/seewasser.py index ef7f401..be5f149 100755 --- a/seewasser.py +++ b/seewasser.py @@ -9,6 +9,8 @@ import csv import os import configparser from sqlalchemy import create_engine +import warnings +import MySQLdb.cursors #May 27 21:32 /sys/bus/w1/devices/28-0416a1bab9ff #May 27 21:33 /sys/bus/w1/devices/28-0416a1ac66ff @@ -55,11 +57,14 @@ def readsensor(sensor_id): def writesensordatacsv(config, sensor_id, sensor_name, timestamp, value_type, value_raw, value): # Schreiben des csv-files - file = open(os.path.expanduser(config.get("csv", "filename")), "a", newline = "") - writer = csv.writer(file, dialect = "excel") - writer.writerow([timestamp.strftime("%Y-%m-%d %H:%M"), sensor_id, sensor_name, "{:.1f}".format(value)]) + with open(os.path.expanduser(config.get("csv", "filename")), "a", newline = "") as file: + writer = csv.writer(file, dialect = "excel") + writer.writerow([timestamp.strftime("%Y-%m-%d %H:%M"), sensor_id, sensor_name, "{:.1f}".format(value)]) - file.close() + +def readcsvfile(csvfile): + with open(csvfile, "r", newline="") as file: + yield from csv.DictReader(file, dialect = "excel", fieldnames=("timestamp", "sensor_id", "sensor_name", "value")) def writesensordatadb(config, sensor_id, sensor_name, timestamp, value_type, value_raw, value): @@ -71,16 +76,34 @@ def writesensordatadb(config, sensor_id, sensor_name, timestamp, value_type, val engine = create_engine('mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db), echo=False) conn = engine.connect() - conn.execute("insert into sensors (sensor_id, sensor_name, value_type, value_raw, value, timestamp) values (%s,%s,%s,%s,%s,%s)", sensor_id, sensor_name, value_type, value_raw, value, timestamp) + with warnings.catch_warnings(): + # ignore _mysql_exceptions.Warning: Duplicate entry '0316a2193bff-2018-08-29 14:30:00' for key 'sensorid_timestamp' + warnings.simplefilter("ignore", category=MySQLdb.cursors.Warning) + conn.execute("insert ignore into sensors (sensor_id, sensor_name, value_type, value_raw, value, timestamp) values (%s,%s,%s,%s,%s,%s)", sensor_id, sensor_name, value_type, value_raw, value, timestamp) conn.close() -def main(configfile): +def process_csv_file(config, fromcsvfile, value_type, sensors): + # "timestamp", "sensor_id", "sensor_name", "value" + sensors_ids = [sensor_id for sensor_id, sensor_name in sensors] + for record in readcsvfile(fromcsvfile): + timestamp, sensor_id, sensor_name, value = (record["timestamp"], record["sensor_id"], record["sensor_name"], float(record["value"])) + if sensor_id in sensors_ids: + value_raw = None + writesensordatadb(config, sensor_id, sensor_name, timestamp, value_type, value_raw, value) + + +def main(configfile, fromcsvfile): config = configparser.ConfigParser() config.read(configfile) - + sensors = config.items('temperature') value_type = "Wassertemperatur" - for sensor_id, sensor_name in config.items('temperature'): + + if fromcsvfile: + process_csv_file(config, fromcsvfile, value_type, sensors) + return + + for sensor_id, sensor_name in sensors: timestamp, value_raw, value = readsensor(sensor_id) writesensordatacsv(config, sensor_id, sensor_name, timestamp, value_type, value_raw, value) writesensordatadb(config, sensor_id, sensor_name, timestamp, value_type, value_raw, value) @@ -91,6 +114,7 @@ if __name__ == '__main__': default_config_file = os.path.expanduser('~/seewasser.ini') parser = argparse.ArgumentParser(description='Read sensor data') parser.add_argument('--config', default=default_config_file, help='configuration file') + parser.add_argument('--fromcsvfile', help='write values from csv file to database') args = parser.parse_args() - main(args.config) + main(args.config, args.fromcsvfile) -- 2.47.3