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
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):
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)
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)