From 70237b5a5d82e8425eb5870a975bde497a6def08 Mon Sep 17 00:00:00 2001 From: Pinapelz Date: Tue, 20 Jun 2023 02:14:34 -0700 Subject: Refactored entire codebase - Replaced most code with HTML templates - Fixed some janky SQL queries - Replaced config.py with ini and json --- sql/graph_builder.py | 128 -------------------------- sql/sql_handler.py | 76 ++++++++++------ sql/table_builder.py | 252 --------------------------------------------------- 3 files changed, 49 insertions(+), 407 deletions(-) delete mode 100644 sql/graph_builder.py delete mode 100644 sql/table_builder.py (limited to 'sql') diff --git a/sql/graph_builder.py b/sql/graph_builder.py deleted file mode 100644 index df108b3..0000000 --- a/sql/graph_builder.py +++ /dev/null @@ -1,128 +0,0 @@ -import plotly.graph_objs as go -import pandas as pd -import mysql.connector -from mysql.connector import errorcode -import warnings -import math -from datetime import datetime, timedelta -import numpy as np - - -def plot_subscriber_count_over_time(host, username, password, database_name, table_name, - gtitle = "Subscriber Count Over Time for Nijisanji Members", - overrideQuery = None, markers = "lines", exclude_channels = []): - warnings.filterwarnings('ignore') # Ignore pandas warning regarding pyodbc - try: - cnx = mysql.connector.connect(user = username, password = password, - host = host, database = database_name) - except mysql.connector.Error as err: - if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: - print("Something is wrong with your user name or password") - elif err.errno == errorcode.ER_BAD_DB_ERROR: - print("Database does not exist") - else: - print(err) - query = f"SELECT name, subscriber_count, timestamp, channel_id FROM {table_name} ORDER by timestamp DESC" if overrideQuery is None else overrideQuery - df = pd.read_sql_query(query, cnx) - groups = df.groupby("name") - fig = go.Figure() - config = dict({'responsive': True, 'displaylogo': False}) - for channel, group in groups: - if len(exclude_channels) != 0 and group['channel_id'].iloc[0] in exclude_channels: - continue - fig.add_trace(go.Scattergl( - x = group["timestamp"], y = group["subscriber_count"], name = channel, mode = markers, - showlegend = True)) - - fig.update_layout( - title = {'text': gtitle, 'x': 0.5, 'xanchor': 'center', - 'yanchor': 'top', 'font': {'family': 'Arial', 'size': 30}}, - xaxis_title = "Timestamp", - yaxis_title = "Subscribers", - legend = dict(font = dict(size = 16), title = dict(text = "Channels")), - height = 950 - ) - return fig.to_html(config = config) - - -def generate_projection(host, username, password, database_name, table_name, curr_subscribers, - timezone = "Pacific Standard Time"): - def get_next_milestone(subscriber_count): - num_digits = len(str(subscriber_count)) - if num_digits <= 4: - milestone_interval = 10000 - else: - milestone_interval = 10 ** (num_digits - 1) - next_milestone = math.ceil( - subscriber_count / milestone_interval) * milestone_interval - - return next_milestone - - warnings.filterwarnings('ignore') # Ignore pandas warning regarding pyodbc - try: - cnx = mysql.connector.connect(user = username, password = password, - host = host, database = database_name) - except mysql.connector.Error as err: - if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: - print("Something is wrong with your user name or password") - elif err.errno == errorcode.ER_BAD_DB_ERROR: - print("Database does not exist") - else: - print(err) - - def create_milestone_card(time_until_milestone, next_milestone, not_enough_data = False, - declining = False): - now = datetime.now() - milestone_date = ( - now + timedelta(seconds = time_until_milestone)).strftime('%Y-%m-%d') - relative_time = now + timedelta(seconds = time_until_milestone) - now - next_milestone_str = "{:,}".format(next_milestone) - if relative_time.days > 1: - relative_time_str = f"In {relative_time.days} days" - elif relative_time.days == 1: - relative_time_str = f"In {relative_time.days} day" - elif relative_time.days < 0: - relative_time_str = f"{-relative_time.days} days ago" - elif not_enough_data: - relative_time_str = "Not enough data" - elif declining: - relative_time_str = "Declining" - else: - relative_time_str = "Today" - card = f""" -
-
Next Milestone
-
{next_milestone_str}
-
-
Estimated Date:
-
{milestone_date}
-
-
-
{relative_time_str}
-
{timezone}
-
-
-
- """ - - return card - - query = f"SELECT name, subscriber_count, timestamp FROM {table_name} WHERE timestamp >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) ORDER BY timestamp DESC" - df = pd.read_sql_query(query, cnx) - df = df.sort_values(by = 'timestamp') - # get the rate of change from first data point to last data point - first_data = df.iloc[0] - last_data = df.iloc[-1] - delta_sub_count = last_data['subscriber_count'] - first_data['subscriber_count'] - delta_time = (last_data['timestamp'] - first_data['timestamp']).total_seconds() - - # Calculate the average rate of change of subscriber_count over time - avg_rate_of_change = delta_sub_count / delta_time - next_milestone = get_next_milestone(curr_subscribers) - if avg_rate_of_change == 0 or math.isnan(avg_rate_of_change) or math.isinf(avg_rate_of_change): - return create_milestone_card(0, next_milestone, not_enough_data = True) - if avg_rate_of_change < 0: - return create_milestone_card(0, next_milestone, declining = True) - time_to_next_milestone = ( - next_milestone - curr_subscribers) / avg_rate_of_change - return create_milestone_card(time_to_next_milestone, next_milestone) diff --git a/sql/sql_handler.py b/sql/sql_handler.py index d349f64..a02db52 100644 --- a/sql/sql_handler.py +++ b/sql/sql_handler.py @@ -12,19 +12,18 @@ class SQLHandler: host_name, user_name, user_password) self._load_database(database_name) - def _create_server_connection(self, host_name: str, user_name: str, user_password: str, exclude=None) -> mysql.connector: + def _create_server_connection(self, host_name: str, user_name: str, user_password: str) -> mysql.connector: connection = None try: - connection = mysql.connector.connect( - host=host_name, - user=user_name, - passwd=user_password - ) + connection = mysql.connector.connect(host=host_name, user=user_name, passwd=user_password) print("MySQL Database connection successful") except Error as err: print(f"Error: '{err}'") return connection + def get_connection(self): + return self.connection + def _create_database(self, cursor: str, database_name: str): try: cursor.execute( @@ -48,75 +47,77 @@ class SQLHandler: print(err) exit(1) - def create_table(self, table_name: str, table_columns: str): + def create_table(self, name: str, column: str): cursor = self.connection.cursor() try: - cursor.execute(f"CREATE TABLE {table_name} ({table_columns})") - print(f"Table {table_name} created successfully") + cursor.execute(f"CREATE TABLE {name} ({column})") + print(f"Table {name} created successfully") except Error as err: print(err) - def insert_data(self, table_name: str, table_columns: str, data: str): + def insert_row(self, name: str, column: str, data: tuple): cursor = self.connection.cursor() try: - cursor.execute( - f"INSERT INTO {table_name} ({table_columns}) VALUES ({data})") + placeholders = ', '.join(['%s'] * len(data)) + query = f"INSERT INTO {name} ({column}) VALUES ({placeholders})" + cursor.execute(query, data) self.connection.commit() - print("Data inserted successfully") + print("Data Inserted:", data) except Error as err: print("Error inserting data") print(err) - def clear_table(self, table_name: str): + + def clear_table(self, name: str): cursor = self.connection.cursor() try: - cursor.execute(f"DELETE FROM {table_name}") + cursor.execute(f"DELETE FROM {name}") self.connection.commit() print("Table cleared successfully") except Error as err: print("Error clearing table") print(err) - def reset_auto_increment(self, table_name: str): + def reset_auto_increment(self, name: str): cursor = self.connection.cursor() try: - cursor.execute(f"ALTER TABLE {table_name} AUTO_INCREMENT = 1") + cursor.execute(f"ALTER TABLE {name} AUTO_INCREMENT = 1") self.connection.commit() print("Table reset successfully") except Error as err: print("Error resetting table") print(err) - def copy_rows_to_new_table(self, table_name: str, new_table_name: str, table_columns: str): + def copy_rows_to_new_table(self, name: str, new_name: str, column: str): cursor = self.connection.cursor() try: cursor.execute( - f"INSERT INTO {new_table_name} ({table_columns}) SELECT {table_columns} FROM {table_name}") + f"INSERT INTO {new_name} ({column}) SELECT {column} FROM {name}") cursor.execute( - f"ALTER TABLE {new_table_name} MODIFY COLUMN id INT AUTO_INCREMENT") + f"ALTER TABLE {new_name} MODIFY COLUMN id INT AUTO_INCREMENT") self.connection.commit() print("Rows copied successfully") except Error as err: print("Error copying rows") print(err) - def drop_table(self, table_name: str): + def drop_table(self, name: str): cursor = self.connection.cursor() try: - cursor.execute(f"DROP TABLE {table_name}") + cursor.execute(f"DROP TABLE {name}") self.connection.commit() print("Table dropped successfully") except Error as err: print("Error dropping table") print(err) - def check_row_exists(self, table_name: str, column_name: str, value: str): + def check_row_exists(self, name: str, column_name: str, value: str): """ Checks if a row exists in a table """ cursor = self.connection.cursor() try: - cursor.execute(f"SELECT * FROM {table_name} WHERE {column_name} = '{value}'") + cursor.execute(f"SELECT * FROM {name} WHERE {column_name} = '{value}'") result = cursor.fetchone() if result: return True @@ -126,15 +127,36 @@ class SQLHandler: print("Error checking row") print(err) - def update_row(self, table_name: str, column_name: str, search_val: str, replace_col:str, new_value: str): + def update_row(self, name: str, column_name: str, search_val: str, replace_col:str, new_value: str): """ Updates a row in a table """ cursor = self.connection.cursor() try: - cursor.execute(f"UPDATE {table_name} SET {replace_col} = '{new_value}' WHERE {column_name} = '{search_val}'") + cursor.execute(f"UPDATE {name} SET {replace_col} = '{new_value}' WHERE {column_name} = '{search_val}'") self.connection.commit() print("Row updated successfully") except Error as err: print("Error updating row") - print(err) \ No newline at end of file + print(err) + + def execute_query(self, query: str): + cursor = self.connection.cursor() + try: + cursor.execute(query) + result = cursor.fetchall() + return result + except Error as err: + print("Error executing query") + print(err) + + def get_query_result(self, query: str): + cursor = self.connection.cursor() + try: + cursor.execute(query) + result = cursor.fetchall() + return result + except Error as err: + print("Error executing query") + print(err) + \ No newline at end of file diff --git a/sql/table_builder.py b/sql/table_builder.py deleted file mode 100644 index e5fbb57..0000000 --- a/sql/table_builder.py +++ /dev/null @@ -1,252 +0,0 @@ -import mysql.connector -from mysql.connector import errorcode - -def sql_to_html_table(host, username, password, database_name, table_name, - diff_table="24h_historical", headers=["Rank", "Liver", "Subscribers", "Difference (24hr)"], root_url="https://nijitracker.com"): - try: - cnx = mysql.connector.connect(user=username, password=password, - host=host, database=database_name) - except mysql.connector.Error as err: - if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: - print("Something is wrong with your user name or password") - elif err.errno == errorcode.ER_BAD_DB_ERROR: - print("Database does not exist") - else: - print(err) - - cursor = cnx.cursor() - query = f"SELECT id, channel_id, name, subscriber_count, timestamp, profile_pic FROM {table_name} ORDER by subscriber_count DESC" - cursor.execute(query) - data = cursor.fetchall() - table = "" - header = "" - header_cols = headers - for col in header_cols: - header += f"" - header += "" - table += header - rank = 1 - for row in data: - table += "" - table += f"" - rank += 1 - for i, col in enumerate(row): - if cursor.description[i][0] == "name": - channel_url = f"{root_url}/stats/{row[2]}" - profile_pic_url = row[5] - table += f"" - elif cursor.description[i][0] == "subscriber_count": - formatted_sub_count = "{:,.0f}".format(int(col)) - table += f"" - elif cursor.description[i][0] == "timestamp": - query = f"SELECT sub_diff FROM {diff_table} WHERE channel_id = '{row[1]}'" - try: - diff_cursor = cnx.cursor() - diff_cursor.execute(query) - diff_data = diff_cursor.fetchall() - old_sub_count = int(diff_data[0][0]) - current_sub_count = int(row[3]) - if old_sub_count > current_sub_count: - difference = f"-{old_sub_count - current_sub_count}" - else: - difference = f"+{current_sub_count - old_sub_count}" - table += f"" - except IndexError: - raise Exception("Are you trying to use a new set of channels?\nPlease delete last_refresh.txt in data folder first!") - elif cursor.description[i][0] not in ["id", "channel_id", "profile_pic"]: - table += f"" - - - table += "" - table += "
{col}
{rank}{col}{formatted_sub_count}{difference}{col}
" - style = "" - style += "" - - - cursor.close() - cnx.close() - return table + style - - -def generate_individual_table(host, username, password, database_name, table_name, param="LIMIT 7"): - try: - cnx = mysql.connector.connect(user=username, password=password, - host=host, database=database_name) - except mysql.connector.Error as err: - if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: - print("Something is wrong with your user name or password") - elif err.errno == errorcode.ER_BAD_DB_ERROR: - print("Database does not exist") - else: - print(err) - - cursor = cnx.cursor() - query = f"SELECT subscriber_count, timestamp FROM {table_name} GROUP BY DATE(timestamp) ORDER by timestamp DESC " + param - - cursor.execute(query) - data = cursor.fetchall() - table = "" - header = "" - header_cols = ["Subscribers", "Timestamp"] - for col in header_cols: - header += f"" - header += "" - table += header - for row in data: - table += "" - for i, col in enumerate(row): - if cursor.description[i][0] == "subscriber_count": - formatted_sub_count = "{:,.0f}".format(int(col)) - table += f"" - else: - table += f"" - table += "" - table += "
{col}
{formatted_sub_count}{col}
" - style = "" - style += "" - - cursor.close() - cnx.close() - return table + style -- cgit v1.2.3