From 88647d26fe0c38a09cfa39fcb3d09125f9f9e955 Mon Sep 17 00:00:00 2001
From: jwansek <eddie.atten.ea29@gmail.com>
Date: Sat, 7 Oct 2023 17:22:25 +0100
Subject: Added chart data API endpoints
---
database.py | 28 ++++++++++++++++++++++++++--
1 file changed, 26 insertions(+), 2 deletions(-)
(limited to 'database.py')
diff --git a/database.py b/database.py
index 846da4d..59250d0 100644
--- a/database.py
+++ b/database.py
@@ -109,13 +109,37 @@ class PowerDatabase:
with self.__connection.cursor() as cursor:
cursor.execute("INSERT INTO kwh_readings (host, reading) VALUES (%s, %s);", (host, reading))
+ def get_last_plug_readings(self):
+ plugs = [i[0] for i in self.get_tasmota_devices()]
+ with self.__connection.cursor() as cursor:
+ cursor.execute("SELECT host, MAX(datetime) FROM watt_readings WHERE host IN %s GROUP BY host;", (plugs, ))
+ plugtimes = cursor.fetchall()
+
+ readings = []
+ for host, datetime in plugtimes:
+ cursor.execute("SELECT host, datetime, reading FROM watt_readings WHERE host = %s AND datetime = %s;", (host, datetime))
+ readings.append(cursor.fetchone())
+ return readings
+
+ def get_watt_chart(self):
+ with self.__connection.cursor() as cursor:
+ cursor.execute("SELECT DISTINCT host FROM watt_readings;")
+ hosts = [i[0] for i in cursor.fetchall()]
+
+ out = {}
+ for host in hosts:
+ cursor.execute("SELECT datetime, reading FROM watt_readings WHERE host = %s ORDER BY datetime;", (host, ))
+ out[host] = cursor.fetchall()
+
+ return out
+
if __name__ == "__main__":
if not os.path.exists(".docker"):
import dotenv
- dotenv.load_dotenv(dotenv_path = "db.env")
+ dotenv.load_dotenv(dotenv_path = "power.env")
host = "srv.athome"
else:
host = None
with PowerDatabase(host = host) as db:
- print(db.get_tasmota_devices())
+ print(db.get_watt_chart())
--
cgit v1.2.3