Endless Playlist Management System

from datetime import datetime import sqlite3 import os import html def get_rows(conn, sql, params=None): """Helper to fetch all rows for a query.""" with conn: cur = conn.cursor() if params: cur.execute(sql, params) else: cur.execute(sql) return cur.fetchall() def write_html_file(file_path, file_name, html_content): """Helper to write HTML to file.""" with open(os.path.join(file_path, file_name), 'w', encoding="utf-8") as fp: fp.write(html_content) def build_table(headers, rows, link_cols=None, nowrap_cols=None): """Builds a Bootstrap-styled HTML table.""" link_cols = link_cols or [] nowrap_cols = nowrap_cols or [] table = '' for header in headers: table += f"" table += "" for idx, row in enumerate(rows, 1): bgcolor = "#ffffff" if idx % 2 else "#dfdfdf" table += f'' for col_idx, cell in enumerate(row): cell_content = html.escape(str(cell)) td_attrs = "" if col_idx in link_cols: cell_content = f'{cell_content}' if col_idx in nowrap_cols: td_attrs = ' nowrap' table += f"{cell_content}" table += "" table += "

{html.escape(header)}
{idx}

" return table def build_header(date_time, unplayed): """Builds the HTML header and navigation.""" header1 = ( '

' ) header2 = ( '

' ) head = ( "Endless Playlist Management System" "" "" ) body1 = ( '

' '

' ) body2 = f'

Remaining Unplayed Tracks = {unplayed}

' body3 = '

' return head + header1 + header2, body1, body2, body3 def main(): print('HTML BUILDER v7.0.0') now = datetime.now() date_time = now.strftime('%d-%m-%y @ %H:%M') yearis = now.strftime('%Y') filename = r"J:\Spotty\secrets_and_database\Enormous Feed.db" file_path = r'J:\HGMS\VIKING-WEB' try: conn = sqlite3.connect(filename) # Unplayed tracks count unplayed = str(get_rows(conn, "SELECT COUNT(*) FROM Track_List WHERE QUEUED = 0")[0][0]) main_head, body1, body2, body3 = build_header(date_time, unplayed) # [1] Last Run Additions rows = get_rows(conn, "SELECT * FROM Last_Run_Additions") additiondata = "Last Run Additions: " + date_time body4 = f'

{additiondata}

' headers = ["#", "URI", "TRACK", "ARTIST", "ALBUM"] table = build_table(headers[1:], rows, link_cols=[0]) html_content = main_head + body1 + body2 + body3 + body4 + table + '

' write_html_file(file_path, "enoradditions.html", html_content) # [2] Last Run Deletions rows = get_rows(conn, "SELECT * FROM Last_Run_Deletions") deletiondata = "Last Run Deletions: " + date_time body4 = f'

{deletiondata}

' table = build_table(headers[1:], rows, link_cols=[0]) html_content = main_head + body1 + body2 + body3 + body4 + table + '' write_html_file(file_path, "enordeletions.html", html_content) # [3] Duplicated tracks rows = get_rows(conn, "SELECT * FROM New_Dupe_Tracks WHERE REVIEWED = 0") dupedata = "Duplicated Tracks: " + date_time body4 = f'

{dupedata}

' headers_dupe = ["#", "URI", "TRACK", "ARTIST", "REVIEWED"] table = build_table(headers_dupe[1:], rows, link_cols=[0]) html_content = main_head + body1 + body2 + body3 + body4 + table + '' write_html_file(file_path, "enorduplicates.html", html_content) # [4] URI Changes rows = get_rows(conn, f"SELECT * FROM URI_Change WHERE DATE_OF_CHANGE LIKE ? ORDER by DATE_OF_CHANGE DESC LIMIT 100", (f"%{yearis}%",)) uridata = "URI Changes: " + date_time body4 = f'

{uridata}

' headers_uri = ["#", "DATE", "OLD URI", "NEW URI", "TRACK", "ARTIST"] # row[5]=DATE, row[1]=OLD URI, row[2]=NEW URI, row[3]=TRACK, row[4]=ARTIST table_rows = [[row[5], row[1], row[2], row[3], row[4]] for row in rows] table = build_table(headers_uri[1:], table_rows, link_cols=[1,2], nowrap_cols=[0]) html_content = main_head + body1 + body2 + body3 + body4 + table + '' write_html_file(file_path, "enorurichanges.html", html_content) # [5a] Last_Played_Tracks (EPM) rows = get_rows(conn, f"SELECT * FROM Last_Played_Tracks WHERE PLAY_COUNT != -100 AND LAST_PLAYED LIKE ? ORDER BY LAST_PLAYED DESC LIMIT 100", (f"%{yearis}%",)) epmdata = "Last Played Tracks (EPM): " + date_time body4 = f'

{epmdata}

' headers_played = ["#", "URI", "TRACK", "ARTIST", "PLAYED"] table_rows = [[row[0], row[1], row[2], row[5]] for row in rows] table = build_table(headers_played[1:], table_rows, link_cols=[0], nowrap_cols=[3]) html_content = main_head + body1 + body2 + body3 + body4 + table + '' write_html_file(file_path, "enorplayed-epm.html", html_content) # [5b] Last_Played_Tracks (EXT) rows = get_rows(conn, f"SELECT * FROM Last_Played_Tracks WHERE PLAY_COUNT = -100 AND LAST_PLAYED LIKE ? ORDER BY LAST_PLAYED DESC LIMIT 100", (f"%{yearis}%",)) extdata = "Last Played Tracks (EXT): " + date_time body4 = f'

{extdata}

' table_rows = [[row[0], row[1], row[2], row[5]] for row in rows] table = build_table(headers_played[1:], table_rows, link_cols=[0], nowrap_cols=[3]) html_content = main_head + body1 + body2 + body3 + body4 + table + '' write_html_file(file_path, "enorplayed-ext.html", html_content) # [6] Queue Check rows = get_rows(conn, "SELECT * FROM Queue_check") queuedata = "Queue Errors: " + date_time body4 = f'

{queuedata}

' table = build_table(headers[1:], rows, link_cols=[0]) html_content = main_head + body1 + body2 + body3 + body4 + table + '' write_html_file(file_path, "enorqueue-errors.html", html_content) # [8] Tracks with Unknown Release Dates rows = get_rows(conn, "SELECT * FROM Track_List WHERE RELEASE_DATE = 9999") unknown = "Latest Update: " + date_time body4 = f'

{unknown}

' table = build_table(headers[1:], rows, link_cols=[0]) html_content = main_head + body1 + body2 + body3 + body4 + table + '' write_html_file(file_path, "release-date.html", html_content) # [7] Index Page with Interactive Queue Icons rows = get_rows(conn, "SELECT * FROM Queue_preview") lastrun = "Latest Run: " + date_time body6 = ( '

' '

ENDLESS ENORMOUS PLAYLIST MANAGEMENT SYSTEM

' '

Enormous Queue - 50 Upcoming Tunes

' f'

{lastrun}

' ) html_index = main_head + body6 indurilist, indnamelist, indpiclist, indaudlist = [], [], [], [] for row in rows: indurilist.append(str(row[0])) indnamelist.append(str(row[1])) indpiclist.append(str(row[2])) indaudlist.append(str(row[3])) indexpage = "" indrow = 0 inditemcount = 0 while indrow < 5: indexpage += '

' indrowitem = 0 while indrowitem < 10: indrowcount = indrowitem + inditemcount if indrowcount >= len(indnamelist): break indexpage += ( f'

' f'

' ) indrowitem += 1 indrow += 1 inditemcount += 10 indexpage += '

' indend = '

' html_index += indexpage + indend write_html_file(file_path, "enorindex.html", html_index) except sqlite3.Error as e: print("Database error:", e) finally: if conn: conn.close() print('Completed.') if __name__ == "__main__": main()