diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/graph_builder.py | 128 | ||||
| -rw-r--r-- | sql/sql_handler.py | 140 | ||||
| -rw-r--r-- | sql/table_builder.py | 252 |
3 files changed, 520 insertions, 0 deletions
diff --git a/sql/graph_builder.py b/sql/graph_builder.py new file mode 100644 index 0000000..df108b3 --- /dev/null +++ b/sql/graph_builder.py @@ -0,0 +1,128 @@ +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""" + <div style="background-color: #ffffff; padding: 20px; border-radius: 10px; box-shadow: 0 0 10px rgba(0, 0, 0, 0.2); font-family: 'Poppins', sans-serif;"> + <div style="font-size: 24px; font-weight: bold; margin-bottom: 10px;">Next Milestone</div> + <div style="font-size: 18px; margin-bottom: 10px;">{next_milestone_str}</div> + <div style="display: flex; flex-direction: row; justify-content: space-between; margin-bottom: 10px;"> + <div style="font-size: 16px;">Estimated Date:</div> + <div style="font-size: 16px; font-weight: bold;">{milestone_date}</div> + </div> + <div style="display: flex; flex-direction: row; justify-content: space-between; color: #888;"> + <div style="font-size: 16px;">{relative_time_str}</div> + <div style="font-size: 16px;">{timezone}</div> + </div> + </div> + <br> + """ + + 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 new file mode 100644 index 0000000..d349f64 --- /dev/null +++ b/sql/sql_handler.py @@ -0,0 +1,140 @@ +import mysql.connector +from mysql.connector import Error, errorcode + + +class SQLHandler: + def __init__(self, host_name: str, user_name: str, user_password: str, database_name: str): + self.host_name = host_name + self.username = user_name + self.password = user_password + self.database_name = database_name + self.connection = self._create_server_connection( + 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: + connection = None + try: + 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 _create_database(self, cursor: str, database_name: str): + try: + cursor.execute( + f"CREATE DATABASE {database_name} DEFAULT CHARACTER SET 'utf8'") + except Error as err: + print(f"Failed creating database: {err}") + exit(1) + + def _load_database(self, database_name: str): + cursor = self.connection.cursor() + try: + cursor.execute(f"USE {database_name}") + print(f"Database {database_name} loaded successfully") + except Error as err: + print(f"Database {database_name} does not exist") + if err.errno == errorcode.ER_BAD_DB_ERROR: + self._create_database(cursor, database_name) + print(f"Database {database_name} created successfully") + self.connection.database = database_name + else: + print(err) + exit(1) + + def create_table(self, table_name: str, table_columns: str): + cursor = self.connection.cursor() + try: + cursor.execute(f"CREATE TABLE {table_name} ({table_columns})") + print(f"Table {table_name} created successfully") + except Error as err: + print(err) + + def insert_data(self, table_name: str, table_columns: str, data: str): + cursor = self.connection.cursor() + try: + cursor.execute( + f"INSERT INTO {table_name} ({table_columns}) VALUES ({data})") + self.connection.commit() + print("Data inserted successfully") + except Error as err: + print("Error inserting data") + print(err) + + def clear_table(self, table_name: str): + cursor = self.connection.cursor() + try: + cursor.execute(f"DELETE FROM {table_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): + cursor = self.connection.cursor() + try: + cursor.execute(f"ALTER TABLE {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): + cursor = self.connection.cursor() + try: + cursor.execute( + f"INSERT INTO {new_table_name} ({table_columns}) SELECT {table_columns} FROM {table_name}") + cursor.execute( + f"ALTER TABLE {new_table_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): + cursor = self.connection.cursor() + try: + cursor.execute(f"DROP TABLE {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): + """ + Checks if a row exists in a table + """ + cursor = self.connection.cursor() + try: + cursor.execute(f"SELECT * FROM {table_name} WHERE {column_name} = '{value}'") + result = cursor.fetchone() + if result: + return True + else: + return False + except Error as err: + 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): + """ + 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}'") + self.connection.commit() + print("Row updated successfully") + except Error as err: + print("Error updating row") + print(err)
\ No newline at end of file diff --git a/sql/table_builder.py b/sql/table_builder.py new file mode 100644 index 0000000..e5fbb57 --- /dev/null +++ b/sql/table_builder.py @@ -0,0 +1,252 @@ +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 = "<table>" + header = "<tr>" + header_cols = headers + for col in header_cols: + header += f"<th>{col}</th>" + header += "</tr>" + table += header + rank = 1 + for row in data: + table += "<tr>" + table += f"<td>{rank}</td>" + 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"<td><a href='{channel_url}'><img src='{profile_pic_url}' height='50px' width='50px'>{col}</a></td>" + elif cursor.description[i][0] == "subscriber_count": + formatted_sub_count = "{:,.0f}".format(int(col)) + table += f"<td>{formatted_sub_count}</td>" + 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"<td>{difference}</td>" + 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"<td>{col}</td>" + + + table += "</tr>" + table += "</table>" + style = "<style>\ + table {\ + font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;\ + font-size: 16px;\ + border-collapse: separate;\ + border-spacing: 0;\ + width: 100%;\ + max-width: 1570px;\ + margin: 0 auto;\ + background-color: #fff;\ + border-radius: 5px;\ + overflow: hidden;\ + box-shadow: 0px 0px 20px rgba(0, 0, 0, 0.1);\ + }\ + th, td {\ + text-align: left;\ + padding: 12px 15px;\ + font-size: 18px;\ + border-bottom: 1px solid #ddd;\ + }\ + th {\ + background-color: #2a4b71;\ + color: #fff;\ + font-weight: bold;\ + text-transform: uppercase;\ + letter-spacing: 0.03em;\ + }\ + td:nth-child(3), td:nth-child(4) {\ + text-align: left;\ + padding: 12px 15px;\ + font-size: 18px;\ + border-bottom: 1px solid #ddd;\ + }\ + tbody tr:nth-child(even) {\ + background-color: #f2f2f2;\ + }\ + tbody tr:hover {\ + background-color: #ddd;\ + }\ + a {\ + color: #3c8dbc;\ + text-decoration: none;\ + }\ + </style>" + style += "<style>\ + @media screen and (max-width: 1024px) {\ + table {\ + font-size: 14px;\ + }\ + th, td {\ + padding: 8px 10px;\ + font-size: 16px;\ + }\ + }\ + @media screen and (max-width: 768px) {\ + th, td {\ + padding: 5px 8px;\ + font-size: 14px;\ + }\ + }\ + @media screen and (max-width: 600px) {\ + th, td {\ + padding: 3px 5px;\ + font-size: 10px;\ + }\ + }\ + @media screen and (max-width: 400px) {\ + th, td {\ + padding: 2px 4px;\ + font-size: 8px;\ + }\ + }\ + </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 = "<table>" + header = "<tr>" + header_cols = ["Subscribers", "Timestamp"] + for col in header_cols: + header += f"<th>{col}</th>" + header += "</tr>" + table += header + for row in data: + table += "<tr>" + for i, col in enumerate(row): + if cursor.description[i][0] == "subscriber_count": + formatted_sub_count = "{:,.0f}".format(int(col)) + table += f"<td>{formatted_sub_count}</td>" + else: + table += f"<td>{col}</td>" + table += "</tr>" + table += "</table>" + style = "<style>\ + table {\ + font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;\ + font-size: 16px;\ + border-collapse: separate;\ + border-spacing: 0;\ + width: 100%;\ + max-width: 1570px;\ + margin: 0 auto;\ + background-color: #fff;\ + border-radius: 5px;\ + overflow: hidden;\ + box-shadow: 0px 0px 20px rgba(0, 0, 0, 0.1);\ + }\ + th, td {\ + text-align: left;\ + padding: 12px 15px;\ + font-size: 18px;\ + border-bottom: 1px solid #ddd;\ + }\ + th {\ + background-color: #2a4b71;\ + color: #fff;\ + font-weight: bold;\ + text-transform: uppercase;\ + letter-spacing: 0.03em;\ + }\ + td:nth-child(3), td:nth-child(4) {\ + text-align: left;\ + padding: 12px 15px;\ + font-size: 18px;\ + border-bottom: 1px solid #ddd;\ + }\ + tbody tr:nth-child(even) {\ + background-color: #f2f2f2;\ + }\ + tbody tr:hover {\ + background-color: #ddd;\ + }\ + a {\ + color: #3c8dbc;\ + text-decoration: none;\ + }\ + </style>" + style += "<style>\ + @media screen and (max-width: 1024px) {\ + table {\ + font-size: 14px;\ + }\ + th, td {\ + padding: 8px 10px;\ + font-size: 16px;\ + }\ + }\ + @media screen and (max-width: 768px) {\ + th, td {\ + padding: 5px 8px;\ + font-size: 14px;\ + }\ + }\ + @media screen and (max-width: 600px) {\ + th, td {\ + padding: 3px 5px;\ + font-size: 12px;\ + }\ + }\ + @media screen and (max-width: 400px) {\ + th, td {\ + padding: 2px 4px;\ + font-size: 10px;\ + }\ + }\ + </style>" + + cursor.close() + cnx.close() + return table + style |