From 9d2c74c059153bffc9ef00a126ee899cae915698 Mon Sep 17 00:00:00 2001 From: jwansek Date: Sat, 8 Jul 2023 19:42:04 +0100 Subject: Added docker stuff --- cron_Dockerfile | 16 ++++ database.py | 209 +++++++++++++++++++++++++++++------------------------ db.env.example | 4 +- devices.py | 52 +++++++++---- docker-compose.yml | 19 +++++ entrypoint.sh | 4 + 6 files changed, 193 insertions(+), 111 deletions(-) create mode 100644 cron_Dockerfile create mode 100644 docker-compose.yml create mode 100644 entrypoint.sh diff --git a/cron_Dockerfile b/cron_Dockerfile new file mode 100644 index 0000000..99a9525 --- /dev/null +++ b/cron_Dockerfile @@ -0,0 +1,16 @@ +FROM ubuntu:latest +MAINTAINER Eden Attenborough "eda@e.email" +ENV TZ=Europe/London +RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone +RUN apt-get update -y +RUN apt-get install -y python3-pip cron +RUN mkdir app +COPY . /app +WORKDIR /app +RUN touch .docker +RUN pip3 install -r requirements.txt + +RUN echo "*/1 * * * * root python3 /app/devices.py nothourly > /proc/1/fd/1 2>/proc/1/fd/2" > /etc/crontab +RUN echo "@hourly root python3 /app/devices.py hourly > /proc/1/fd/1 2>/proc/1/fd/2" >> /etc/crontab +ENTRYPOINT ["bash"] +CMD ["entrypoint.sh"] \ No newline at end of file diff --git a/database.py b/database.py index 51b3ca1..c1d67f3 100644 --- a/database.py +++ b/database.py @@ -1,96 +1,113 @@ -from dataclasses import dataclass -import pymysql -import os - -@dataclass -class PowerDatabase: - host: str = "db" - user: str = "root" - passwd: str = None - db: str = "power" - port: int = 3306 - - def __enter__(self): - if self.passwd is None: - self.passwd = os.environ["MYSQL_ROOT_PASSWORD"] - - try: - self.__connection = self.__get_connection() - except Exception as e: - print(e) - if e.args[0] == 1049: - self.__connection = self.__build_db() - return self - - def __exit__(self, type, value, traceback): - self.__connection.close() - - def __get_connection(self): - return pymysql.connect( - host = self.host, - port = self.port, - user = self.user, - passwd = self.passwd, - charset = "utf8mb4", - database = self.db - ) - - def __build_db(self): - print("Building database...") - self.__connection = pymysql.connect( - host = self.host, - port = self.port, - user = self.user, - passwd = self.passwd, - charset = "utf8mb4", - ) - with self.__connection.cursor() as cursor: - # unsafe: - cursor.execute("CREATE DATABASE %s" % self.db) - cursor.execute("USE %s" % self.db) - - cursor.execute(""" - CREATE TABLE tasmota_devices ( - host VARCHAR(25) NOT NULL PRIMARY KEY, - username VARCHAR(50) NOT NULL, - password VARCHAR(50) NOT NULL - ); - """) - - if "TASMOTA_DEVICES" in os.environ.keys(): - devices = os.environ["TASMOTA_DEVICES"].split(",") - for device in devices: - host, username, password = device.split(":") - cursor.execute("INSERT INTO tasmota_devices VALUES (%s, %s, %s);", (host, username, password)) - - cursor.execute(""" - CREATE TABLE watt_readings ( - host VARCHAR(25) NOT NULL, - `datetime` DATETIME DEFAULT NOW(), - reading DECIMAL(3,1) NOT NULL, - FOREIGN KEY (host) REFERENCES tasmota_devices (host), - PRIMARY KEY (host, `datetime`) - ); - """) - - self.__connection.commit() - return self.__connection - - def get_tasmota_devices(self): - with self.__connection.cursor() as cursor: - cursor.execute("SELECT * FROM tasmota_devices;") - return cursor.fetchall() - - def append_watt_readings(self, host, reading): - cursor.execute("INSERT INTO watt_readings (host, reading) VALUES (%s, %s);", (host, reading)) - -if __name__ == "__main__": - if not os.path.exists(".docker"): - import dotenv - dotenv.load_dotenv(dotenv_path = "db.env") - host = "srv.home" - else: - host = "db" - - with PowerDatabase(host = host) as db: - print(db.get_tasmota_devices()) +from dataclasses import dataclass +import pymysql +import os + +@dataclass +class PowerDatabase: + host: str = "db" + user: str = "root" + passwd: str = None + db: str = "power" + port: int = 3306 + + def __enter__(self): + if self.passwd is None: + self.passwd = os.environ["MYSQL_ROOT_PASSWORD"] + + try: + self.__connection = self.__get_connection() + except Exception as e: + print(e) + if e.args[0] == 1049: + self.__connection = self.__build_db() + return self + + def __exit__(self, type, value, traceback): + self.__connection.commit() + self.__connection.close() + + def __get_connection(self): + return pymysql.connect( + host = self.host, + port = self.port, + user = self.user, + passwd = self.passwd, + charset = "utf8mb4", + database = self.db + ) + + def __build_db(self): + print("Building database...") + self.__connection = pymysql.connect( + host = self.host, + port = self.port, + user = self.user, + passwd = self.passwd, + charset = "utf8mb4", + ) + with self.__connection.cursor() as cursor: + # unsafe: + cursor.execute("CREATE DATABASE %s" % self.db) + cursor.execute("USE %s" % self.db) + + cursor.execute(""" + CREATE TABLE tasmota_devices ( + host VARCHAR(25) NOT NULL PRIMARY KEY, + username VARCHAR(50) NOT NULL, + password VARCHAR(50) NOT NULL + ); + """) + + if "TASMOTA_DEVICES" in os.environ.keys(): + devices = os.environ["TASMOTA_DEVICES"].split(",") + for device in devices: + host, username, password = device.split(":") + cursor.execute("INSERT INTO tasmota_devices VALUES (%s, %s, %s);", (host, username, password)) + + cursor.execute(""" + CREATE TABLE watt_readings ( + host VARCHAR(25) NOT NULL, + `datetime` DATETIME DEFAULT NOW(), + reading FLOAT(24) NOT NULL, + FOREIGN KEY (host) REFERENCES tasmota_devices (host), + PRIMARY KEY (host, `datetime`) + ); + """) + + cursor.execute(""" + CREATE TABLE kwh_readings ( + host VARCHAR(25) NOT NULL, + `datetime` DATETIME DEFAULT NOW(), + reading FLOAT(24) NOT NULL, + FOREIGN KEY (host) REFERENCES tasmota_devices (host), + PRIMARY KEY (host, `datetime`) + ); + """) + + self.__connection.commit() + return self.__connection + + def get_tasmota_devices(self): + with self.__connection.cursor() as cursor: + cursor.execute("SELECT * FROM tasmota_devices;") + return cursor.fetchall() + + def append_watt_readings(self, host, reading): + with self.__connection.cursor() as cursor: + cursor.execute("DELETE FROM watt_readings WHERE `datetime` < DATE_SUB(NOW(), INTERVAL 1 DAY);") + cursor.execute("INSERT INTO watt_readings (host, reading) VALUES (%s, %s);", (host, reading)) + + def append_kwh_readings(self, host, reading): + with self.__connection.cursor() as cursor: + cursor.execute("INSERT INTO kwh_readings (host, reading) VALUES (%s, %s);", (host, reading)) + +if __name__ == "__main__": + if not os.path.exists(".docker"): + import dotenv + dotenv.load_dotenv(dotenv_path = "db.env") + host = "srv.home" + else: + host = "db" + + with PowerDatabase(host = host) as db: + print(db.get_tasmota_devices()) diff --git a/db.env.example b/db.env.example index b7dfa35..3427fc5 100644 --- a/db.env.example +++ b/db.env.example @@ -1,2 +1,2 @@ -MYSQL_ROOT_PASSWORD=********** -TASMOTA_DEVICES=switch.plug:admin:***********,nas.plug:admin:***********,router.plug:admin:************ +MYSQL_ROOT_PASSWORD=********** +TASMOTA_DEVICES=switch.plug:admin:***********,nas.plug:admin:***********,router.plug:admin:************ diff --git a/devices.py b/devices.py index 0b0ba2a..e3c21ae 100644 --- a/devices.py +++ b/devices.py @@ -1,31 +1,57 @@ import tasmotadevicecontroller import database import asyncio +import datetime import json +import sys import os -COUNTER_NAMES = ["Total", "Today"] -GAUGE_NAMES = ["Power", "ApparentPower", "ReactivePower", "Factor", "Voltage", "Current"] -SUMMARY_NAMES = ["TotalStartTime", "Yesterday"] -BOOLEAN_ENUM_NAMES = ["Power"] +if not os.path.exists(os.path.join("/app", ".docker")): + import dotenv + dotenv.load_dotenv(dotenv_path = "db.env") + HOST = "srv.home" +else: + HOST = "db" async def get_energy_for(host, username = None, password = None): device = await tasmotadevicecontroller.TasmotaDevice().connect(host, username, password) energy = await device.sendRawRequest("Status 8") power = await device.getPower() # friendlyname = await device.getFriendlyName() - energy["StatusSNS"]["ENERGY"]["Power"] = power return energy["StatusSNS"]["ENERGY"] # return {"%s_%s" % (status["FriendlyName"], k): v for k, v in status.items()} -async def log_energies_for(db: database.PowerDatabase, host, username, password): - pass +async def poll_watt_for(db: database.PowerDatabase, host, username, password): + power = await get_energy_for(host, username, password) + power = float(power['Power']) + db.append_watt_readings(host, power) + print("'%s' is using %.1fW at %s" % (host, power, datetime.datetime.now())) -if __name__ == "__main__": +async def poll_yesterday_kwh_for(db: database.PowerDatabase, host, username, password): + power = await get_energy_for(host, username, password) + power = float(power['Yesterday']) + db.append_kwh_readings(host, power) + print("'%s' used %.1fkWh yesterday" % (host, power)) + + +def poll_watt_all(): + loop = asyncio.new_event_loop() + asyncio.set_event_loop(loop) + with database.PowerDatabase(host = HOST) as db: + for host, username, password in db.get_tasmota_devices(): + asyncio.run(poll_watt_for(db, host, username, password)) + +def poll_kwh_all(): loop = asyncio.new_event_loop() asyncio.set_event_loop(loop) - try: - print(asyncio.run(get_energy_for("switch.plug", "admin", "securebackdoor"))) - # asyncio.run(get_all_plugs("4u.plug:admin:securebackdoor,switch.plug:admin:securebackdoor,router.plug:admin:securebackdoor,nas.plug:admin:securebackdoor")) - except KeyboardInterrupt: - pass + with database.PowerDatabase(host = HOST) as db: + for host, username, password in db.get_tasmota_devices(): + asyncio.run(poll_yesterday_kwh_for(db, host, username, password)) + + +if __name__ == "__main__": + if sys.argv[1] == "daily": + poll_kwh_all() + else: + poll_watt_all() + diff --git a/docker-compose.yml b/docker-compose.yml new file mode 100644 index 0000000..a752a0f --- /dev/null +++ b/docker-compose.yml @@ -0,0 +1,19 @@ +version: '3' + +services: + cron: + build: + context: . + dockerfile: cron_Dockerfile + image: jwansek/power + env_file: + - ./db.env + external_links: + - mariadb:db + networks: + - db-network + +networks: + db-network: + external: + name: mariadb \ No newline at end of file diff --git a/entrypoint.sh b/entrypoint.sh new file mode 100644 index 0000000..610bf84 --- /dev/null +++ b/entrypoint.sh @@ -0,0 +1,4 @@ +# https://stackoverflow.com/questions/27771781/how-can-i-access-docker-set-environment-variables-from-a-cron-job/35088810#35088810 +printenv | grep -v "no_proxy" >> /etc/environment + +cron -f \ No newline at end of file -- cgit v1.2.3