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"| {col} | "
header += "
"
table += header
rank = 1
for row in data:
table += ""
table += f"| {rank} | "
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" {col} | "
elif cursor.description[i][0] == "subscriber_count":
formatted_sub_count = "{:,.0f}".format(int(col))
table += f"{formatted_sub_count} | "
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"{difference} | "
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"{col} | "
table += "
"
table += "
"
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"| {col} | "
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"| {formatted_sub_count} | "
else:
table += f"{col} | "
table += "
"
table += "
"
style = ""
style += ""
cursor.close()
cnx.close()
return table + style