From ed1e2cbe77f93307f0f3bff736e14695a6c59163 Mon Sep 17 00:00:00 2001 From: Pinapelz Date: Thu, 4 May 2023 16:01:14 -0700 Subject: Fresh initial commit to remove large history --- sql/table_builder.py | 252 +++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 252 insertions(+) create mode 100644 sql/table_builder.py (limited to 'sql/table_builder.py') 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 = "" + 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