Changed Data.fetch_table to fetch last 24 hours of data saved in the database.HEADdaemonology

2 files changed, 23 insertions, 22 deletions

diff --git a/RPGH.py b/RPGH.py
index bfbad39..ac1937b 100644
--- a/RPGH.py
+++ b/RPGH.py
@@ -33,7 +33,7 @@ __location__ = os.path.realpath(
 
 # read and extract/format configuration data
 config_f = 'settings_config_parser.cfg'
-config_cl = 'settings_client.cfg'
+config_cl = 'settings_client_local.cfg'
 config_abs = os.path.join(__location__, config_f)
 config_cl_abs = os.path.join(__location__, config_cl)
 cfg = configparser.ConfigParser(interpolation=None)
@@ -44,11 +44,11 @@ cfg_cl.read(config_cl_abs)
 
 class Data:
 
-    def fetch_table(self, table, _min, _max):
+    def fetch_table(self, table):
+        """
+        Fetches last 24 hours of data saved in the database.
+        """
         self.table = table
-        # limits of data to fetch from-to:
-        self._min = _min
-        self._max = _max
         try:
             conn = mysql.connector.connect(**cfg_cl['mysql'])
         except mysql.connector.Error as e:
@@ -64,13 +64,15 @@ class Data:
                 sys.exit()
         cur = conn.cursor()
         sql = (
-            f"SELECT * FROM {self.table} WHERE timestamp BETWEEN %s AND %s"
+            f'SELECT * FROM {self.table} ORDER BY timestamp DESC LIMIT 1;'
         )
-        #sql = (
-        #    f"SELECT * FROM {self.table};"
-        #)
-        cur.execute(sql, (self._min, self._max))
-        #cur.execute(sql)
+        cur.execute(sql)
+        last = cur.fetchall()[0][0]
+        first = last - timedelta(hours=24)
+        sql = (
+            f"SELECT * FROM {self.table} WHERE timestamp BETWEEN '{first}' and '{last}';"
+        )
+        cur.execute(sql)
         rows = cur.fetchall()
         fields = [i[0] for i in cur.description]
         df = pd.DataFrame([[x for x in i] for i in rows])
@@ -110,10 +112,10 @@ class DesignerMainWindow(QtWidgets.QMainWindow, Ui_MainWindow):
         past = _past_p.strftime("%H:%M:%S")
 
 # unpacking temperature data
+        # couldn't it be: temp_fields, temp_data = Data.fetch_table(...
         _temp_data = Data()
-        temp_fields, temp_data  = _temp_data.fetch_table(
-            'temperature', _past, _now
-        )
+        temp_fields, temp_data = _temp_data.fetch_table('temperature')
+        # below results in key error if there's no current data
         timestamp_temp = pd.to_datetime(
             pd.Series(temp_data[0])
         )
@@ -136,8 +138,7 @@ class DesignerMainWindow(QtWidgets.QMainWindow, Ui_MainWindow):
 
 # unpacking light data
         self.light_data = Data()
-        light_fields, light_data = self.light_data.fetch_table(
-            'light', _past, _now)
+        light_fields, light_data = self.light_data.fetch_table('light')
         timestamp_1 = pd.to_datetime(
             pd.Series(light_data[0]), format=timestamp_format
         )
@@ -147,9 +148,7 @@ class DesignerMainWindow(QtWidgets.QMainWindow, Ui_MainWindow):
 
 # unpacking humidity data
         self.hum_data = Data()
-        hum_fields, hum_data = self.hum_data.fetch_table(
-            'humidity', _past, _now
-        )
+        hum_fields, hum_data = self.hum_data.fetch_table('humidity')
         timestamp_2 = pd.to_datetime(
             pd.Series(hum_data[0]), format=timestamp_format
         )
@@ -157,9 +156,7 @@ class DesignerMainWindow(QtWidgets.QMainWindow, Ui_MainWindow):
 
 # unpacking rain data
         self.rain_data = Data()
-        rain_fields, rain_data = self.hum_data.fetch_table(
-            'rain', _past, _now
-        )
+        rain_fields, rain_data = self.hum_data.fetch_table('rain')
         timestamp_3 = pd.to_datetime(
             pd.Series(rain_data[0]), format=timestamp_format
         )
diff --git a/settings_config_parser.cfg b/settings_config_parser.cfg
index f140fc9..e76deb7 100644
--- a/settings_config_parser.cfg
+++ b/settings_config_parser.cfg
@@ -10,6 +10,10 @@ humidity_min = 20
 light_min = 200
 light_max = 100
 
+[water]
+soil_moisture_min = 1
+soil_moisture_max = 10
+
 [hardware_settings]
 read_frequency = 15
 dht_pin_1 = 17