From 50f62d0b6a17677104f0e4d39c5ff7223c5239b2 Mon Sep 17 00:00:00 2001 From: Dennis Eriksen Date: Mon, 3 Apr 2023 12:23:40 +0200 Subject: adding function to insert state without defining sql --- scripts/common/__init__.py | 34 ++++++++++++++++++++++++++++- scripts/common/queue.py | 8 ++++++- scripts/esphomeapi.py | 54 +++++++++++++++++----------------------------- 3 files changed, 60 insertions(+), 36 deletions(-) diff --git a/scripts/common/__init__.py b/scripts/common/__init__.py index 3c3ab1e..d434a4a 100644 --- a/scripts/common/__init__.py +++ b/scripts/common/__init__.py @@ -3,13 +3,45 @@ import os import sys +import logging +from datetime import datetime QUEUE = bool(os.environ.get('el_QUEUE', False)) name = os.path.splitext(os.path.split(sys.argv[0])[-1])[0] -# Initialize queue +log = logging.getLogger(__name__) +log.setLevel(logging.INFO) +logging.basicConfig(format="%(name)s: %(levelname)s %(message)s") + + +# Use queue or postgres if QUEUE is True: + log.debug('Importing dbi from queue') from .queue import dbi else: + log.debug('Importing dbi from postgres') from .postgres import dbi + + +# Insert state +def statein(sensor, value, device_class, unit, **kwargs): + ''' Insert new state in db ''' + verbose = bool(kwargs['verbose']) if 'verbose' in kwargs else False + + sql = """INSERT INTO states + (sensor_id, + value, + device_class, + unit, + time) + SELECT sensors.id, %s, %s, %s, %s + FROM sensors + WHERE sensors.name = %s;""" + values = (value, + device_class, + unit, + datetime.now(), + sensor) + log.debug('dbi(' + sql + ',' + str(values) + ')') + dbi(sql, values, verbose=verbose) diff --git a/scripts/common/queue.py b/scripts/common/queue.py index cb90423..b4fa369 100644 --- a/scripts/common/queue.py +++ b/scripts/common/queue.py @@ -3,20 +3,26 @@ import os import pickle +import logging from litequeue import SQLQueue QUEUE_DB = os.environ.get('el_QUEUE_db', 'litequeue.db') +log = logging.getLogger(__name__) + # Initialize queue q = SQLQueue(QUEUE_DB, maxsize=None) def dbi(sql,values,**kwargs): ''' insert into queue ''' + log.debug('function dbi()') verbose = bool(kwargs['verbose']) if 'verbose' in kwargs else False + log.debug('Inserting into sqlite-queue') q.put(pickle.dumps([sql, values])) if verbose is True: + log.debug('verbose = True') table = sql.split(' ')[2] num = 1 if isinstance(values, tuple) else len(values) - print("Inserted", num, "item(s) into queue for", table) + print("Inserted " + str(num) + " item(s) into queue for " + str(table)) return True diff --git a/scripts/esphomeapi.py b/scripts/esphomeapi.py index b3c684b..8c79282 100644 --- a/scripts/esphomeapi.py +++ b/scripts/esphomeapi.py @@ -8,7 +8,7 @@ from aioesphomeapi import APIClient, ReconnectLogic, SensorState, APIConnectionE import asyncio import argparse import logging -import colorlog +#import colorlog import zeroconf import common @@ -58,6 +58,7 @@ async def main(args): if type(state) == SensorState and state.missing_state == False: log.debug('function callback(' + str(state) + ')') sensor = sensors[state.key] + value = state.state if 'accuracy_decimals' in sensor: decimals = sensor['accuracy_decimals'] @@ -65,23 +66,8 @@ async def main(args): unit = sensor['unit_of_measurement'] if 'unit_of_measurement' in sensor else '' device_class = sensor['device_class'] if 'device_class' in sensor else '' - sql = """INSERT INTO states - (sensor_id, - value, - device_class, - unit, - time) - SELECT sensors.id, %s, %s, %s, %s - FROM sensors - WHERE sensors.name = %s;""" - values = (value, - device_class, - unit, - datetime.now(), - device['friendly_name']) - log.info(sensor['name'] + ' ' + sensor['device_class'] + ' - ' + str(value) + str(unit)) - common.dbi(sql, values, verbose=True) + common.statein(sensor['name'], value, device_class, unit, verbose=True) async def on_connect() -> None: log.debug('function on_connect()') @@ -126,23 +112,23 @@ async def main(args): if __name__ == "__main__": # Logging - handler = colorlog.StreamHandler() - handler.setFormatter(colorlog.ColoredFormatter( - "%(log_color)s%(levelname)s - %(message)s {%(filename)s:%(lineno)d}", - log_colors={ - 'DEBUG': 'light_black', - 'INFO': 'cyan', - 'WARNING': 'yellow', - 'ERROR': 'red', - 'CRITICAL': 'red,bg_white' - })) - log = colorlog.getLogger(__name__) - log.setLevel(logging.WARNING) - log.addHandler(handler) - - #log = logging.getLogger(__name__) - #log.setLevel(logging.INFO) - #logging.basicConfig(format="%(name)s: %(levelname)s %(message)s") + #handler = colorlog.StreamHandler() + #handler.setFormatter(colorlog.ColoredFormatter( + # "%(log_color)s%(levelname)s - %(message)s {%(filename)s:%(lineno)d}", + # log_colors={ + # 'DEBUG': 'light_black', + # 'INFO': 'cyan', + # 'WARNING': 'yellow', + # 'ERROR': 'red', + # 'CRITICAL': 'red,bg_white' + # })) + #log = colorlog.getLogger(__name__) + #log.setLevel(logging.WARNING) + #log.addHandler(handler) + + log = logging.getLogger(__name__) + log.setLevel(logging.INFO) + logging.basicConfig(format="%(levelname)s - %(message)s {%(filename)s:%(lineno)d}") parser = argparse.ArgumentParser(description="Connect to an esphome-device and access the native api") parser.add_argument( "-a", "--address", help="Address of esp-device to connect to") -- cgit v1.2.3