remove. dangerous table deletion. adjust pg_handler insert row to update on channel_id conflict

3 files changed, 40 insertions, 28 deletions

diff --git a/nijitrack.py b/nijitrack.py
index 13e5ef9..fd2c76b 100644
--- a/nijitrack.py
+++ b/nijitrack.py
@@ -55,7 +55,7 @@ def record_subscriber_data(data: list, force_refresh: bool = False):
         elif refresh_daily:
             server.update_row(DATA_SETTING["TABLE_DAILY"], "channel_id", channel_id, "sub_diff", sub_count)
             server.insert_row(table_name = DATA_SETTING["TABLE_HISTORICAL"], column = DATA_SETTING["HISTORICAL_HEADER"], data=data_tuple)
-    
+
     def check_diff_refresh():
         last_updated = server.get_most_recently_added_row_time(DATA_SETTING["TABLE_HISTORICAL"])
         if last_updated is None or len(last_updated) == 0 or not last_updated[0]:
@@ -107,7 +107,6 @@ def holodex_generation(server: PostgresHandler, force_refresh: bool = False):
     Generates the data from the Holodex API
     """
     holodex_organizations = DATA_SETTING["HOLODEX_ORGS"].split(",")
-    server.clear_table(DATA_SETTING["TABLE_LIVE"])
     holodex = HolodexAPI(os.environ.get("HOLODEX_KEY"), organization="Phase%20Connect")
     for organization in holodex_organizations:
         holodex.set_organization(organization)
@@ -179,4 +178,4 @@ if __name__ == "__main__":
     if args.b2:
         uploadFileToBucket("index.html")
     else:
-        print("Skipping B2 Upload")
\ No newline at end of file
+        print("Skipping B2 Upload")
diff --git a/requirements.txt b/requirements.txt
index 19a6864..aa680cc 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -53,13 +53,12 @@ pexpect==4.9.0
 pickleshare==0.7.5
 pillow==10.4.0
 pip-review==1.3.0
-pipreqs==0.5.0
+pipreqs==0.4.13
 platformdirs==4.3.6
 plotly==5.24.1
 prompt_toolkit==3.0.48
 protobuf==5.28.2
-psycopg2==2.9.9
-psycopg2-binary==2.9.9
+psycopg2-binary==2.9.10
 ptyprocess==0.7.0
 pure_eval==0.2.3
 Pygments==2.18.0
diff --git a/sql/pg_handler.py b/sql/pg_handler.py
index b528a26..68ce82c 100644
--- a/sql/pg_handler.py
+++ b/sql/pg_handler.py
@@ -12,7 +12,7 @@ class PostgresHandler:
         }
         self._connection = psycopg2.connect(**db_params)
         self.print_to_debug("Connected to database successfully")
-    
+
     def get_connection(self):
         return self._connection
 
@@ -21,7 +21,7 @@ class PostgresHandler:
         cursor.execute(f'CREATE TABLE IF NOT EXISTS "{name}" ({column})')
         self._connection.commit()
         cursor.close()
-    
+
     def clear_table(self, name: str):
         cursor = self._connection.cursor()
         cursor.execute(f"DELETE FROM {name}")
@@ -39,22 +39,36 @@ class PostgresHandler:
             return True
         else:
             return False
-    
+
     def insert_row(self, table_name, column, data):
         try:
-            cursor  = self._connection.cursor()
-            placeholders = ', '.join(['%s'] * len(data))
-            query = f'INSERT INTO "{table_name}" ({column}) VALUES ({placeholders})'
-            cursor.execute(query, data)
+            cursor = self._connection.cursor()
+            if table_name == "subscriber_data":
+                column_list = column.split(", ")
+                try:
+                    channel_id_index = column_list.index("channel_id")
+                    channel_id_value = data[channel_id_index]
+                    cursor.execute(f'DELETE FROM "{table_name}" WHERE channel_id = %s', (channel_id_value,))
+                    placeholders = ', '.join(['%s'] * len(data))
+                    query = f'INSERT INTO "{table_name}" ({column}) VALUES ({placeholders})'
+                    cursor.execute(query, data)
+
+                    print(f"Replaced data for channel_id: {channel_id_value}")
+                except ValueError:
+                    placeholders = ', '.join(['%s'] * len(data))
+                    query = f'INSERT INTO "{table_name}" ({column}) VALUES ({placeholders})'
+                    cursor.execute(query, data)
+            else:
+                placeholders = ', '.join(['%s'] * len(data))
+                query = f'INSERT INTO "{table_name}" ({column}) VALUES ({placeholders})'
+                cursor.execute(query, data)
             self._connection.commit()
-            print("Data Inserted:", data)
+            return True
         except Error as err:
             self._connection.rollback()
-            print("Error inserting data")
+            print("Error inserting or updating data")
             print(err)
-            if "duplicate key" not in str(err).lower():
-                return False
-        return True
+            return False
 
     def update_row(self, table_name: str, column: str, value: str, update_column: str, update_value: str):
         try:
@@ -69,7 +83,7 @@ class PostgresHandler:
             print(e)
             return False
         return True
-    
+
     def get_rows(self, table_name: str, column: str, value: str):
         try:
             cursor = self._connection.cursor()
@@ -82,7 +96,7 @@ class PostgresHandler:
             print(f"Failed to fetch row from {table_name} WHERE {column} is {value}")
             print(e)
             return False
-    
+
     def get_random_row(self, table_name: str, count: int, condition: str = None):
         if condition is None:
             condition = "1 = 1"
@@ -97,7 +111,7 @@ class PostgresHandler:
             print(f"Failed to select random rows from {table_name}")
             print(e)
             return False
-    
+
     def check_health(self):
         cursor = self._connection.cursor()
         cursor.execute("SELECT 1")
@@ -107,7 +121,7 @@ class PostgresHandler:
             return True
         else:
             return False
-    
+
     def delete_row(self, table_name: str, column: str, value: str):
         try:
             cursor = self._connection.cursor()
@@ -121,7 +135,7 @@ class PostgresHandler:
             print(e)
             return False
         return True
-    
+
     def execute_query(self, query: str, data: tuple = None):
         try:
             cursor = self._connection.cursor()
@@ -136,7 +150,7 @@ class PostgresHandler:
             print(f"Failed to execute query: {query}")
             print(e)
             return False
-    
+
     def reset_auto_increment(self, table_name: str):
         try:
             cursor = self._connection.cursor()
@@ -150,7 +164,7 @@ class PostgresHandler:
             print(e)
             return False
         return True
-    
+
     def get_most_recently_added_row_time(self, table_name: str):
         try:
             cursor = self._connection.cursor()
@@ -163,10 +177,10 @@ class PostgresHandler:
             print(f"Failed to get most recently added row from {table_name}")
             print(e)
             return False
-    
+
     def print_to_debug(self, message: str):
         print("[PostgresHandler] " + message)
 
-    
+
     def close_connection(self):
-        self._connection.close()
\ No newline at end of file
+        self._connection.close()