migrate codebase to Postgres SQL and add dotenv

7 files changed, 422 insertions, 64 deletions

diff --git a/.env.template b/.env.template
new file mode 100644
index 0000000..860f57d
--- /dev/null
+++ b/.env.template
@@ -0,0 +1,8 @@
+POSTGRES_HOST=
+POSTGRES_USER=
+POSTGRES_PASSWORD=
+POSTGRES_DATABASE=
+HOLODEX_KEY=
+YOUTUBE_API_KEY=
+B2_APP_ID=
+B2_APP_KEY=
\ No newline at end of file
diff --git a/backend/app.py b/backend/app.py
index b29ca17..5e3fa7c 100644
--- a/backend/app.py
+++ b/backend/app.py
@@ -3,15 +3,18 @@ Flask app for serving the static files
 """
 from flask import Flask, send_file, jsonify
 from flask_cors import CORS
-from sql.sql_handler import SQLHandler
+from sql.pg_handler import PostgresHandler
 import fileutil as fs
 import datetime
 import pandas
 from sklearn.linear_model import Ridge
 import numpy as np
+import os
+from dotenv import load_dotenv
+
+load_dotenv()
 
 app = Flask(__name__)
-CONFIG = fs.load_config("config.ini")
 CORS(app)
 
 # Optional setting to use any of the custom options below
@@ -25,27 +28,42 @@ ALL_EXCLUDE_MANUAL_DATA = False
 # For when you only want to serve actual data you collected at those specific endpoints
 INDIVIDUAL_EXCLUDE_MANUAL_DATA = True
 
+def create_database_connection():
+    """
+    Creates a database connection using the environment variables
+    :param: auth_append: str = "" - If you want to use a different set of variables for persisitance of sessions
+    """
+    hostname = os.environ.get("POSTGRES_HOST")
+    user = os.environ.get("POSTGRES_USER")
+    password = os.environ.get("POSTGRES_PASSWORD")
+    database = os.environ.get("POSTGRES_DATABASE")
+    return PostgresHandler(host_name=hostname, username=user, password=password, database=database, port=5432)
+
 @app.route("/")
 def index():
-    return send_file("index.html")
+    try:
+        return send_file("index.html")
+    except Exception as e:
+        return jsonify({"error": str(e)})
 
 @app.route("/api/subscribers")
 def api_subscribers():
-    server = SQLHandler(CONFIG["SQL"]["host"], CONFIG["SQL"]["user"], CONFIG["SQL"]["password"], CONFIG["SQL"]["database"])
-    data = server.execute_query("SELECT * FROM subscriber_data INNER JOIN 24h_historical ON subscriber_data.channel_id = 24h_historical.channel_id ORDER BY subscriber_count DESC")
-    channel_data_list = [{"channel_name":row[3], "profile_pic": row[2], "subscribers": row[4], "sub_org": row[5], "video_count": row[6], "day_diff": int(row[4] - int(row[10]))} for row in data]
-    subscriber_data = {"timestamp": datetime.datetime.now(),"channel_data":channel_data_list}
+    server = create_database_connection()
+    query = 'SELECT sd.*, h.* FROM subscriber_data sd INNER JOIN "24h_historical" h ON sd.channel_id = h.channel_id ORDER BY sd.subscriber_count DESC'
+    data = server.execute_query(query)
+    channel_data_list = [{"channel_name": row[3], "profile_pic": row[2], "subscribers": row[4], "sub_org": row[5], "video_count": row[6], "day_diff": int(row[4] - int(row[10]))} for row in data]
+    subscriber_data = {"timestamp": datetime.datetime.now(), "channel_data": channel_data_list}
     return jsonify(subscriber_data)
 
 @app.route("/api/subscribers/<channel_name>")
 def api_subscribers_channel(channel_name):
-    server = SQLHandler(CONFIG["SQL"]["host"], CONFIG["SQL"]["user"], CONFIG["SQL"]["password"], CONFIG["SQL"]["database"])
-    data = server.execute_query("SELECT * FROM subscriber_data_historical WHERE name = %s AND timestamp > %s", (channel_name, START_DATE))
-    sorted_data = sorted(data, key=lambda row: row[5].strftime("%Y-%m-%d"))
+    server = create_database_connection()
+    query = "SELECT * FROM subscriber_data_historical WHERE name = %s AND timestamp > %s ORDER BY TO_CHAR(timestamp, 'YYYY-MM-DD')"
+    data = server.execute_query(query, (channel_name, START_DATE))
     labels = []
     data_points = []
     seen_dates = set()
-    for row in sorted_data:
+    for row in data:
         date_string = row[5].strftime("%Y-%m-%d")
         if date_string in seen_dates:
             continue
@@ -54,16 +72,15 @@ def api_subscribers_channel(channel_name):
         seen_dates.add(date_string)
     return jsonify({"labels": labels, "datasets": data_points})
 
-
 @app.route("/api/subscribers/<channel_name>/7d")
 def api_subscribers_channel_7d(channel_name):
-    server = SQLHandler(CONFIG["SQL"]["host"], CONFIG["SQL"]["user"], CONFIG["SQL"]["password"], CONFIG["SQL"]["database"])
-    data = server.execute_query("SELECT * FROM subscriber_data_historical WHERE name = %s", (channel_name,))
-    sorted_data = sorted(data, key=lambda row: row[5].strftime("%Y-%m-%d"))
+    server = create_database_connection()
+    query = "SELECT * FROM subscriber_data_historical WHERE name = %s ORDER BY TO_CHAR(timestamp, 'YYYY-MM-DD')"
+    data = server.execute_query(query, (channel_name,))
     labels = []
     data_points = []
     seen_dates = set()
-    for row in sorted_data:
+    for row in data:
         date_string = row[5].strftime("%Y-%m-%d")
         if date_string in seen_dates:
             continue
@@ -81,9 +98,10 @@ def get_channel_information(channel_name):
             return ((subscriber_count // 100000) + 1) * 100000
         else:
             return ((subscriber_count // 1000000) + 1) * 1000000
-    server = SQLHandler(CONFIG["SQL"]["host"], CONFIG["SQL"]["user"], CONFIG["SQL"]["password"], CONFIG["SQL"]["database"])
-    data = server.execute_query("SELECT * FROM subscriber_data WHERE name = %s", (channel_name,))
-    channel_data = {"channel_id":data[0][1],"channel_name":data[0][3], "profile_pic": data[0][2], "subscribers": data[0][4], "sub_org": data[0][5], "video_count": data[0][6]}
+    server = create_database_connection()
+    query = "SELECT * FROM subscriber_data WHERE name = %s"
+    data = server.execute_query(query, (channel_name,))
+    channel_data = {"channel_id": data[0][1], "channel_name": data[0][3], "profile_pic": data[0][2], "subscribers": data[0][4], "sub_org": data[0][5], "video_count": data[0][6]}
     historical_data = server.execute_query("SELECT * FROM subscriber_data_historical WHERE name = %s", (channel_name,))
     current_subscriber_count = data[0][4]
     subscriber_points = []
@@ -123,18 +141,14 @@ def get_channel_information(channel_name):
         channel_data["days_until_next_milestone"] = "N/A"
         channel_data["next_milestone"] = "N/A"
     return jsonify(channel_data)
-
 @app.route("/api/announcement")
 def api_announcement():
-    """
-    Can be used to show a particular message/error on the NEXT interface
-    """
-    announcement_data = {"message": "None", "show_message": False} # stub TODO
+    announcement_data = {"message": "None", "show_message": False}
+    return jsonify(announcement_data)
 
 @app.errorhandler(404)
 def not_found(error):
     return jsonify(error=str(error)), 404
 
-
 if __name__ == "__main__":
-    app.run(debug=True, port=5001)
+    app.run(debug=True)
diff --git a/backend/fileutil.py b/backend/fileutil.py
index c12ae43..325ee0f 100644
--- a/backend/fileutil.py
+++ b/backend/fileutil.py
@@ -58,22 +58,6 @@ def get_local_channels(path: str = "data"):
         return [tuple(row.split(":")) for row in rows]
 
 
-def check_diff_refresh():
-    if not os.path.exists(os.path.join("data", "last_refresh.txt")):
-        with open(
-            os.path.join("data", "last_refresh.txt"), "w", encoding="utf-8") as file:
-            file.write(time.strftime("%Y-%m-%d"))
-            return True
-    with open(os.path.join("data", "last_refresh.txt"), "r", encoding="utf-8") as file:
-        last_refresh = file.read()
-    if last_refresh != time.strftime("%Y-%m-%d"):
-        with open(
-            os.path.join("data", "last_refresh.txt"), "w", encoding="utf-8"
-        ) as file:
-            file.write(time.strftime("%Y-%m-%d"))
-        return True
-
-
 def update_data_files(url: str) -> None:
     # Updates the local txt channel data stored in data folder
     if not os.path.exists(os.path.join("data", "channels.txt")):
diff --git a/backend/nijitrack.py b/backend/nijitrack.py
index 87c256b..f573bda 100644
--- a/backend/nijitrack.py
+++ b/backend/nijitrack.py
@@ -1,41 +1,75 @@
-import time
+from datetime import datetime
 
 import fileutil as fs
-from sql.sql_handler import SQLHandler
+from sql.pg_handler import PostgresHandler
 from webapi.holodex import HolodexAPI
 from webapi.youtube import YouTubeAPI
+from b2sdk.v2 import *
 import graph
 from decorators import *
 import argparse
+import os
+import pytz
+from dotenv import load_dotenv
 
+load_dotenv()
 
-CONFIG = fs.load_config("config.ini")
 DATA_SETTING = fs.load_json_file("sql_table_config.json")
+CONFIG = fs.load_config("config.ini")
 
+def create_database_connection():
+    """
+    Creates a database connection using the environment variables
+    :param: auth_append: str = "" - If you want to use a different set of variables for persisitance of sessions
+    """
+    hostname = os.environ.get("POSTGRES_HOST")
+    user = os.environ.get("POSTGRES_USER")
+    password = os.environ.get("POSTGRES_PASSWORD")
+    database = os.environ.get("POSTGRES_DATABASE")
+    return PostgresHandler(host_name=hostname, username=user, password=password, database=database, port=5432)
 
 @log("Initializing Database")
-def initialize_database(server: SQLHandler):
+def initialize_database(server: PostgresHandler):
     server.create_table(name = CONFIG["TABLES"]["live"], column = DATA_SETTING["LIVE_COLUMNS"])
     server.create_table(name = CONFIG["TABLES"]["historical"], column = DATA_SETTING["HISTORICAL_COLUMNS"])
     server.create_table(name = CONFIG["TABLES"]["daily"], column = DATA_SETTING["DAILY_COLUMNS"])
 
 
 @log("Inserting Live Data into Database")
-def record_subscriber_data(data: list):
+def record_subscriber_data(data: list, force_refresh: bool = False):
     def transform_sql_string(string: str) -> str:
         return string.encode("ascii", "ignore").decode().replace("'", "''")
     def record_diff_data(data_tuple: tuple, refresh_daily: bool):
         if not server.check_row_exists(CONFIG["TABLES"]["daily"], "channel_id", channel_id):
             # data_tuple = (channel_id, pfp, channel_name, sub_count, time.strftime('%Y-%m-%d %H:%M:%S'))
             server.insert_row(CONFIG["TABLES"]["daily"], DATA_SETTING["DAILY_HEADER"], (data_tuple[0], data_tuple[3]))
-            server.insert_row(name = CONFIG["TABLES"]["historical"], column = DATA_SETTING["HISTORICAL_HEADER"], data=data_tuple)
+            server.insert_row(table_name = CONFIG["TABLES"]["historical"], column = DATA_SETTING["HISTORICAL_HEADER"], data=data_tuple)
             return
         elif refresh_daily:
             server.update_row(CONFIG["TABLES"]["daily"], "channel_id", channel_id, "sub_diff", sub_count)
-            server.insert_row(name = CONFIG["TABLES"]["historical"], column = DATA_SETTING["HISTORICAL_HEADER"], data=data_tuple)
+            server.insert_row(table_name = CONFIG["TABLES"]["historical"], column = DATA_SETTING["HISTORICAL_HEADER"], data=data_tuple)
     
+    def check_diff_refresh():
+        last_updated = server.get_most_recently_added_row_time(CONFIG["TABLES"]["historical"])[0]
+        if not last_updated:
+            print("Failed to get the most recently added row time.")
+            return False
+        last_updated = pytz.timezone('US/Pacific').localize(last_updated)
+        utc_now = datetime.now(pytz.timezone('UTC'))
+        pst_now = utc_now.astimezone(pytz.timezone('US/Pacific'))
+        time_diff = pst_now - last_updated
+        if time_diff.days >= 1:
+            return True
+        elif time_diff.days == 0 and time_diff.seconds >= 85800:
+            return True
+        else:
+            print("Skipping Daily Refresh. It has not been a day yet")
+            return False
     exclude_channels = fs.get_excluded_channels()
-    refresh_daily = fs.check_diff_refresh()
+    if force_refresh:
+        refresh_daily = True
+    else:
+        refresh_daily = check_diff_refresh()
     for channel in data:
         channel_id = channel["id"]
         if channel_id in exclude_channels:
@@ -50,33 +84,36 @@ def record_subscriber_data(data: list):
         if sub_org is None:
             sub_org = "Unknown"
         channel_name = transform_sql_string(channel_name)
-        data_tuple = (channel_id, pfp, channel_name, sub_count, sub_org, video_count, time.strftime('%Y-%m-%d %H:%M:%S'))
-        historical_data_tuple = (channel_id, pfp, channel_name, sub_count, time.strftime('%Y-%m-%d %H:%M:%S'))
-        server.insert_row(name = CONFIG["TABLES"]["live"], column = DATA_SETTING["LIVE_HEADER"], data=data_tuple)
+        utc_now = datetime.now(pytz.timezone('UTC'))
+        pst_now = utc_now.astimezone(pytz.timezone('US/Pacific'))
+        formatted_time = pst_now.strftime('%Y-%m-%d %H:%M:%S')
+        data_tuple = (channel_id, pfp, channel_name, sub_count, sub_org, video_count, formatted_time)
+        historical_data_tuple = (channel_id, pfp, channel_name, sub_count, formatted_time)
+        server.insert_row(table_name = CONFIG["TABLES"]["live"], column = DATA_SETTING["LIVE_HEADER"], data=data_tuple)
         record_diff_data(historical_data_tuple, refresh_daily)
 
 
 @log("Running Holodex Generation")
-def holodex_generation(server: SQLHandler):
+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(CONFIG["TABLES"]["live"])
     server.reset_auto_increment(CONFIG["TABLES"]["live"])
-    holodex = HolodexAPI(CONFIG["API"]["holodex"], organization="Phase%20Connect")
+    holodex = HolodexAPI(os.environ.get("HOLODEX_KEY"), organization="Phase%20Connect")
     for organization in holodex_organizations:
         holodex.set_organization(organization)
         subscriber_data = holodex.get_subscriber_data()
-        record_subscriber_data(subscriber_data)
+        record_subscriber_data(subscriber_data, force_refresh)
     return holodex.get_generated_channel_data(), holodex.get_inactive_channels()
 
 @log("Running YouTube Generation")
-def youtube_generation(server: SQLHandler):
+def youtube_generation(server: PostgresHandler):
     """
     Generates the data from the YouTube API
     """
-    ytapi = YouTubeAPI(CONFIG["API"]["youtube"])
+    ytapi = YouTubeAPI(os.environ.get("YOUTUBE_API_KEY"))
     server.clear_table(CONFIG["TABLES"]["live"])
     server.reset_auto_increment(CONFIG["TABLES"]["live"])
     data = ytapi.get_data_all_channels(fs.get_local_channels())
@@ -94,20 +131,46 @@ def combine_excluded_channel_ids(inactive_channel_data: list, excluded_channels:
         channel_ids.append(inactive_channel)
     return channel_ids
 
+def uploadFileToBucket(filepath: str) -> bool:
+    try:
+        info = InMemoryAccountInfo()
+        b2_api = B2Api(info)
+        application_key_id = os.environ.get("B2_APP_ID")
+        application_key = os.environ.get("B2_APP_KEY")
+        file_info = {'how': 'good-file'}
+        b2_api.authorize_account("production", application_key_id, application_key)
+        b2_file_name = "graph.html"
+        bucket = b2_api.get_bucket_by_name("vtuber-rabbit-hole-archive")
+        bucket.upload_local_file(local_file=filepath, file_name=b2_file_name, file_info=file_info)
+        return True
+    except Exception as e:
+        print("An error occured while attempting to upload to B2")
+        print(e)
+        return False;
 
 if __name__ == "__main__":
     parser = argparse.ArgumentParser(description="NijiTrack - A Subscriber Tracker")
     parser.add_argument('--mode', choices=['yt', 'holodex'], help='Specify the data source to use (yt or holodex)')
+    parser.add_argument('--b2', action='store_true', help="Upload graph html to Backblaze B2")
+    parser.add_argument('--ff', action='store_true', help="Force a full refresh of all data (override daily refresh)")
     args = parser.parse_args()
-    server = SQLHandler(CONFIG["SQL"]["host"], CONFIG["SQL"]["user"], CONFIG["SQL"]["password"], CONFIG["SQL"]["database"])
+    server = create_database_connection()
     initialize_database(server)
     if args.mode == 'yt':
         print("Using YouTube API")
         channel_data = youtube_generation(server)
         inactive_channels = fs.get_excluded_channels()
     else:
-        channel_data, inactive_channels = holodex_generation(server)
+        if args.ff:
+            print("Forcing a full refresh")
+            channel_data, inactive_channels = holodex_generation(server, force_refresh=True)
+        else:
+            channel_data, inactive_channels = holodex_generation(server)
     fs.update_excluded_channels(inactive_channels)
     graph_html = graph.plot_subscriber_count_over_time(server, CONFIG["TABLES"]["historical"], exclude_channels=combine_excluded_channel_ids(inactive_channels, fs.get_excluded_channels()))
     with open("index.html", "w", encoding="utf-8") as file:
-        file.write(graph_html)
\ No newline at end of file
+        file.write(graph_html)
+    if args.b2:
+        uploadFileToBucket("index.html")
+    else:
+        print("Skipping B2 Upload")
\ No newline at end of file
diff --git a/backend/requirements.txt b/backend/requirements.txt
index 2099279..a788bc0 100644
--- a/backend/requirements.txt
+++ b/backend/requirements.txt
@@ -1,35 +1,155 @@
+anyio==4.3.0
+argon2-cffi==23.1.0
+argon2-cffi-bindings==21.2.0
+arrow==1.3.0
+asttokens==2.4.1
+async-lru==2.0.4
+attrs==23.2.0
+b2sdk==1.29.0
+Babel==2.14.0
+beautifulsoup4==4.12.3
+bleach==6.1.0
 blinker==1.7.0
 certifi==2023.11.17
+cffi==1.16.0
 charset-normalizer==3.3.2
 click==8.1.7
+colorama==0.4.6
+comm==0.2.1
+contourpy==1.2.0
+cycler==0.12.1
+debugpy==1.8.1
+decorator==5.1.1
+defusedxml==0.7.1
 docopt==0.6.2
+exceptiongroup==1.2.0
+executing==2.0.1
+fastjsonschema==2.19.1
+filelock==3.13.1
 Flask==3.0.0
 Flask-Cors==4.0.0
+fonttools==4.49.0
+fqdn==1.5.1
+fsspec==2024.2.0
+gitdb==4.0.11
+GitPython==3.1.42
 greenlet==3.0.1
+gunicorn==21.2.0
+h11==0.14.0
+httpcore==1.0.4
+httpx==0.27.0
 idna==3.6
+ipykernel==6.29.3
+ipython==8.22.2
+isoduration==20.11.0
 itsdangerous==2.1.2
+jedi==0.19.1
 Jinja2==3.1.2
 joblib==1.3.2
+json5==0.9.20
+jsonpointer==2.4
+jsonschema==4.21.1
+jsonschema-specifications==2023.12.1
+jupyter-events==0.9.0
+jupyter-lsp==2.2.4
+jupyter-server-mathjax==0.2.6
+jupyter_client==8.6.0
+jupyter_core==5.7.1
+jupyter_server==2.13.0
+jupyter_server_terminals==0.5.2
+jupyterlab==4.1.3
+jupyterlab_git==0.50.0
+jupyterlab_pygments==0.3.0
+jupyterlab_server==2.25.3
+kiwisolver==1.4.5
+logfury==1.0.1
 MarkupSafe==2.1.3
+matplotlib==3.8.3
+matplotlib-inline==0.1.6
+mistune==3.0.2
+mpmath==1.3.0
 mysql-connector-python==8.2.0
+nbclient==0.9.0
+nbconvert==7.16.2
+nbdime==4.0.1
+nbformat==5.9.2
+nest-asyncio==1.6.0
+networkx==3.2.1
+notebook_shim==0.2.4
 numpy==1.26.2
+nvidia-cublas-cu12==12.1.3.1
+nvidia-cuda-cupti-cu12==12.1.105
+nvidia-cuda-nvrtc-cu12==12.1.105
+nvidia-cuda-runtime-cu12==12.1.105
+nvidia-cudnn-cu12==8.9.2.26
+nvidia-cufft-cu12==11.0.2.54
+nvidia-curand-cu12==10.3.2.106
+nvidia-cusolver-cu12==11.4.5.107
+nvidia-cusparse-cu12==12.1.0.106
+nvidia-nccl-cu12==2.19.3
+nvidia-nvjitlink-cu12==12.3.101
+nvidia-nvtx-cu12==12.1.105
+overrides==7.7.0
 packaging==23.2
 pandas==2.1.3
+pandocfilters==1.5.1
+parso==0.8.3
 patsy==0.5.3
+pexpect==4.9.0
+pillow==10.2.0
 pip-review==1.3.0
 pipreqs==0.4.13
+platformdirs==4.2.0
 plotly==5.18.0
+prometheus_client==0.20.0
+prompt-toolkit==3.0.43
 protobuf==4.21.12
+psutil==5.9.8
+psycopg2-binary==2.9.9
+ptyprocess==0.7.0
+pure-eval==0.2.2
+pycparser==2.21
+Pygments==2.17.2
+pyparsing==3.1.1
 python-dateutil==2.8.2
+python-dotenv==1.0.1
+python-json-logger==2.0.7
 pytz==2023.3.post1
+PyYAML==6.0.1
+pyzmq==25.1.2
+referencing==0.33.0
 requests==2.31.0
+rfc3339-validator==0.1.4
+rfc3986-validator==0.1.1
+rpds-py==0.18.0
 scikit-learn==1.3.2
 scipy==1.11.4
+seaborn==0.13.2
+Send2Trash==1.8.2
 six==1.16.0
+smmap==5.0.1
+sniffio==1.3.1
+soupsieve==2.5
+stack-data==0.6.3
+sympy==1.12
 tenacity==8.2.3
+terminado==0.18.0
 threadpoolctl==3.2.0
+tinycss2==1.2.1
+tomli==2.0.1
+torch==2.2.1
+tornado==6.4
+tqdm==4.66.1
+traitlets==5.14.1
+triton==2.2.0
+types-python-dateutil==2.8.19.20240106
 typing_extensions==4.8.0
 tzdata==2023.3
+uri-template==1.3.0
 urllib3==2.1.0
+wcwidth==0.2.13
+webcolors==1.13
+webencodings==0.5.1
+websocket-client==1.7.0
 Werkzeug==3.0.1
 yarg==0.1.9
diff --git a/backend/sql/pg_handler.py b/backend/sql/pg_handler.py
new file mode 100644
index 0000000..74a9170
--- /dev/null
+++ b/backend/sql/pg_handler.py
@@ -0,0 +1,169 @@
+import psycopg2
+from psycopg2 import Error
+
+class PostgresHandler:
+    def __init__(self, username: str, password: str, host_name: str, port: int, database: str):
+        db_params = {
+            "dbname": database,
+            "user": username,
+            "password": password,
+            "host": host_name,
+            "port": port
+        }
+        self._connection = psycopg2.connect(**db_params)
+        print("Handler Success")
+    
+    def get_connection(self):
+        return self._connection
+
+    def create_table(self, name: str, column: str):
+        cursor = self._connection.cursor()
+        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}")
+        self._connection.commit()
+        cursor.close()
+
+    def check_row_exists(self, table_name: str, column_name: str, value: str):
+        cursor = self._connection.cursor()
+        query = f'SELECT 1 FROM "{table_name}" WHERE {column_name} = %s'
+        cursor.execute(query, (value,))
+        result = cursor.fetchone()
+        cursor.close()
+
+        if result is not None:
+            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)
+            self._connection.commit()
+            print("Data Inserted:", data)
+        except Error as err:
+            self._connection.rollback()
+            print("Error inserting data")
+            print(err)
+            if "duplicate key" not in str(err).lower():
+                return False
+        return True
+
+    def update_row(self, table_name: str, column: str, value: str, update_column: str, update_value: str):
+        try:
+            cursor = self._connection.cursor()
+            query = f'UPDATE "{table_name}" SET {update_column} = %s WHERE {column} = %s'
+            cursor.execute(query, (update_value, value))
+            self._connection.commit()
+            print("Data Updated:", value, update_value)
+        except Error as e:
+            self._connection.rollback()
+            print(f"Failed to update row from {table_name} WHERE {column} is {value}")
+            print(e)
+            return False
+        return True
+    
+    def get_rows(self, table_name: str, column: str, value: str):
+        try:
+            cursor = self._connection.cursor()
+            query = f'SELECT * FROM "{table_name}" WHERE {column} = %s'
+            cursor.execute(query, (value,))
+            result = cursor.fetchall()
+            return result
+        except Error as e:
+            self._connection.rollback()
+            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"
+        try:
+            cursor = self._connection.cursor()
+            query = f"SELECT * FROM {table_name} WHERE {condition} ORDER BY RANDOM() LIMIT {str(count)}"
+            cursor.execute(query)
+            result = cursor.fetchall()
+            return result
+        except Error as e:
+            self._connection.rollback()
+            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")
+        result = cursor.fetchone()
+        cursor.close()
+        if result is not None:
+            return True
+        else:
+            return False
+    
+    def delete_row(self, table_name: str, column: str, value: str):
+        try:
+            cursor = self._connection.cursor()
+            query = f"DELETE FROM {table_name} WHERE {column} = %s"
+            cursor.execute(query, (value,))
+            self._connection.commit()
+            print("Data Deleted:", value)
+        except Error as e:
+            self._connection.rollback()
+            print(f"Failed to delete row from {table_name} WHERE {column} is {value}")
+            print(e)
+            return False
+        return True
+    
+    def execute_query(self, query: str, data: tuple = None):
+        try:
+            cursor = self._connection.cursor()
+            if data is None:
+                cursor.execute(query)
+            else:
+                cursor.execute(query, data)
+            result = cursor.fetchall()
+            return result
+        except Error as e:
+            self._connection.rollback()
+            print(f"Failed to execute query: {query}")
+            print(e)
+            return False
+    
+    def reset_auto_increment(self, table_name: str):
+        try:
+            cursor = self._connection.cursor()
+            query = f"ALTER SEQUENCE {table_name}_id RESTART WITH 1"
+            cursor.execute(query)
+            self._connection.commit()
+            print("Auto Increment Reset")
+        except Error as e:
+            self._connection.rollback()
+            print(f"Failed to reset auto increment for {table_name}")
+            print(e)
+            return False
+        return True
+    
+    def get_most_recently_added_row_time(self, table_name: str):
+        try:
+            cursor = self._connection.cursor()
+            query = f"SELECT timestamp FROM {table_name} ORDER BY id DESC LIMIT 1"
+            cursor.execute(query)
+            result = cursor.fetchone()
+            return result
+        except Error as e:
+            self._connection.rollback()
+            print(f"Failed to get most recently added row from {table_name}")
+            print(e)
+            return False
+
+    
+    def close_connection(self):
+        self._connection.close()
\ No newline at end of file
diff --git a/backend/sql_table_config.json b/backend/sql_table_config.json
index 0a2ebc3..8bc237f 100644
--- a/backend/sql_table_config.json
+++ b/backend/sql_table_config.json
@@ -1,9 +1,9 @@
 {
-    "LIVE_COLUMNS": "id INT PRIMARY KEY AUTO_INCREMENT, channel_id VARCHAR(255), profile_pic VARCHAR(255), name VARCHAR(255), subscriber_count INT, suborg VARCHAR(255), video_count INT,  timestamp DATETIME",
+    "LIVE_COLUMNS": "id SERIAL PRIMARY KEY, channel_id VARCHAR(255), profile_pic VARCHAR(255), name VARCHAR(255), subscriber_count INT, suborg VARCHAR(255), video_count INT,  timestamp TIMESTAMP",
     "LIVE_HEADER": "channel_id, profile_pic, name, subscriber_count, suborg, video_count, timestamp",
-    "DAILY_COLUMNS": "id INT PRIMARY KEY AUTO_INCREMENT, channel_id VARCHAR(255), sub_diff INT",
+    "DAILY_COLUMNS": "id SERIAL PRIMARY KEY, channel_id VARCHAR(255), sub_diff INT",
     "DAILY_HEADER": "channel_id, sub_diff",
-    "HISTORICAL_COLUMNS": "id INT PRIMARY KEY AUTO_INCREMENT, channel_id VARCHAR(255), profile_pic VARCHAR(255), name VARCHAR(255), subscriber_count INT, timestamp DATETIME",
+    "HISTORICAL_COLUMNS": "id SERIAL PRIMARY KEY, channel_id VARCHAR(255), profile_pic VARCHAR(255), name VARCHAR(255), subscriber_count INT, timestamp TIMESTAMP",
     "HISTORICAL_HEADER": "channel_id, profile_pic, name, subscriber_count, timestamp",
     "HOLODEX_ORGS": "Phase%20Connect"
 }