Python script om P1 slimme meter standen op te slaan in sqlite

p1_python_smart_meter.py

# Python script om P1  weer te geven
# http://domoticx.com/p1-poort-slimme-meter-telegram-uitlezen-met-python/

import re
import os
import serial   # pip install pyserial
import time
import sqlite3
import logging

from datetime import datetime, timedelta

db = '/home/pi/PySmartMeter/database/energie.db'

timestr = time.strftime("%Y%m%d")
folder = "/home/pi/PySmartMeter/logging"
os.makedirs(folder, exist_ok=True)

f = "/home/pi/PySmartMeter/logging/" + str(timestr) + ".log"

log_format = "%(asctime)s :: %(levelname)s :: %(name)s :: %(filename)s :: %(message)s"
# logging.basicConfig(level='DEBUG', format=log_format, datefmt='%d-%b-%y %H:%M:%S')
logging.basicConfig(
    filename=f, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s', level=logging.DEBUG)

# Seriele poort confguratie
ser = serial.Serial()

# DSMR 2.2 > 9600 7E1:
ser.baudrate = 9600
ser.bytesize = serial.SEVENBITS
ser.parity = serial.PARITY_EVEN
ser.stopbits = serial.STOPBITS_ONE

# DSMR 4.0/4.2 > 115200 8N1:
#ser.baudrate = 115200
#ser.bytesize = serial.EIGHTBITS
#ser.parity = serial.PARITY_NONE
#ser.stopbits = serial.STOPBITS_ONE

ser.xonxoff = 0
ser.rtscts = 0
ser.timeout = 12
ser.port = "/dev/ttyUSB0"
ser.close()
i = 0
watt = []
consumption_1 = None
consumption_2 = None
return_1 = None
return_2 = None


def expires(minutes: int = 5):
    future = datetime.now() + timedelta(seconds=minutes*60)
    return int(future.strftime("%s"))


def average(lst):
    return round(int(sum(lst) / len(lst)))


hour_timestamp = expires(60)
five_minutes_timestamp = expires(5)

logging.warning("Start collecting")

while True:
    ser.open()
    checksum_found = False

    while not checksum_found:
        timestamp = int(time.time())

        try:

            ser_data = ser.readline()  # Read in serial line.
            # Strip spaces and blank lines
            ser_data = ser_data.decode('ascii').strip()
        except Exception as e:
            exc_type, exc_obj, exc_tb = sys.exc_info()
            fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
            logging.warning(str(e) + " | " + str(exc_type) +
                            " | " + str(fname) + " | " + str(exc_tb.tb_lineno))
            pass

        try:
            if re.match(r'(?=1-0:1.7.0)', ser_data):  # 1-0:1.7.0 = Actual usage in kW
                kw = ser_data[10:-4]  # Knip het kW gedeelte eruit (0000.54)
                # vermengvuldig met 1000 voor conversie naar Watt (540.0) en rond het af
                watt.append(int(float(kw) * 1000))

            if re.match(r'(?=1-0:1.8.1)', ser_data):
                consumption_1 = ser_data[10:-5]

            if re.match(r'(?=1-0:1.8.2)', ser_data):
                consumption_2 = ser_data[10:-5]

            if re.match(r'(?=1-0:2.8.1)', ser_data):
                return_1 = ser_data[10:-5]

            if re.match(r'(?=1-0:2.8.2)', ser_data):
                return_2 = ser_data[10:-5]

            try:
                conn
            except Exception:
                conn = sqlite3.connect(db)

            try:
                if watt:
                    if timestamp >= five_minutes_timestamp:
                        avg_watt = average(watt)
                        conn.execute("""INSERT INTO consumption (consumption, datetime)
                                    VALUES (?,?)""", [avg_watt, timestamp])
                        conn.commit()
                        logging.warning("Watt saved to Dbase")
                        five_minutes_timestamp = expires(5)
                        watt = []

            except Exception as e:
                exc_type, exc_obj, exc_tb = sys.exc_info()
                fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
                logging.warning(str(e) + " | " + str(exc_type) +
                                " | " + str(fname) + " | " + str(exc_tb.tb_lineno))
                pass

            try:
                if None not in (consumption_1, consumption_2, return_1, return_2):
                    if timestamp >= hour_timestamp:
                        conn.execute("""INSERT INTO totals (consumption_1, consumption_2, return_1, return_2, datetime)
                                    VALUES (?, ?, ? ,? ,?)""", [consumption_1, consumption_2, return_1, return_2, timestamp])
                        conn.commit()
                        logging.warning("Totals saved to Dbase")
                        hour_timestamp = expires(60)
                        consumption_1 = None
                        consumption_2 = None
                        return_1 = None
                        return_2 = None
            except Exception as e:
                exc_type, exc_obj, exc_tb = sys.exc_info()
                fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
                logging.warning(str(e) + " | " + str(exc_type) +
                                " | " + str(fname) + " | " + str(exc_tb.tb_lineno))
                pass

        except Exception as e:
            exc_type, exc_obj, exc_tb = sys.exc_info()
            fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
            logging.warning(str(e) + " | " + str(exc_type) +
                            " | " + str(fname) + " | " + str(exc_tb.tb_lineno))
            pass

        # Check when the exclamation mark is received (end of data)
        if re.match(r'(?=!)', ser_data, 0):
            checksum_found = True

    ser.close()
conn.close()