From 4e84325c15980ade9ccb2c0a37a23e540f41e769 Mon Sep 17 00:00:00 2001 From: Pinapelz Date: Wed, 20 Mar 2024 16:16:03 +0000 Subject: migrate codebase to Postgres SQL and add dotenv --- .env.template | 8 ++ backend/app.py | 66 ++++++++++------- backend/fileutil.py | 16 ---- backend/nijitrack.py | 101 ++++++++++++++++++++----- backend/requirements.txt | 120 ++++++++++++++++++++++++++++++ backend/sql/pg_handler.py | 169 ++++++++++++++++++++++++++++++++++++++++++ backend/sql_table_config.json | 6 +- 7 files changed, 422 insertions(+), 64 deletions(-) create mode 100644 .env.template create mode 100644 backend/sql/pg_handler.py 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/") 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//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" } -- cgit v1.2.3