Added docker stuff

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