aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--cron_Dockerfile16
-rw-r--r--database.py209
-rw-r--r--db.env.example4
-rw-r--r--devices.py52
-rw-r--r--docker-compose.yml19
-rw-r--r--entrypoint.sh4
6 files changed, 193 insertions, 111 deletions
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
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-27 21:22:27 +0000