## Create HTML files from MASTER Database Table Data. ## Line 048: [1] Last_Run_Additions. ## Line 079: [2] Last_Run_Deletions. ## Line 109: [3] New_Dupe_Tracks. ## Line 139: [4] URI_Change. ## Line 171: [5a] Last_Played_Tracks (EPM) - Present in the MASTER database. ## Line 201: [5b] Last_Played_Tracks (ETX) - Not yet part of the EPM System. ## Line 232: [6] Queue Errors. (from Queue_check Table). ### NOTE: IMPORTANT ### ### Add your own database name in Line: 42 ### ### (no need for full path if running 'htmlbuilder.py' in same folder) ### from datetime import datetime import sqlite3 from sqlite3 import Error ### HTML BUILDER Creates HTML files from MASTER Database Tables. (22/10/22) print('HTML BUILDER v2.1.8') now = datetime.now() date_time = now.strftime('%d-%m-%y @ %H:%M') ## Format HTML for HEADER, BODY, CSS, TABLE and basic layout details. title = "Endless Playlist Management System" style = "" head = "
" banner = "

ENDLESS PLAYLIST MANAGEMENT SYSTEM

" menu = "" menu0 = "" menu1 = "" menu2 = "" menu3 = "" menu4 = "" menu5 = "" menu6 = "" menu7 = "
MAINADDEDDELETEDDUPLICATESURI CHANGESQUEUE ERRORSPLAYED [EPM]PLAYED [EXT]
" main = title + style + head + banner + menu + menu0 + menu1 + menu2 + menu3 + menu4 + menu5 + menu6 + menu7 footer = "
© Viking Utilities ~ 2022
" conn = None ### filename = "MASTER.db" ### CHANGE HERE ### filename = "C:\Spotty Main\Vi&Cyn Test\Endless Feed.db" ### <== Als db path ### try: conn = sqlite3.connect(filename) ## NOTE: [1] Last Run Additions. sql = ("SELECT * FROM Last_Run_Additions") curr = conn.cursor() curr.execute(sql) rows = curr.fetchall() ## Replicate HTML Header plus data Table for: table = "Last Run Additions - [" + date_time +"]" html = main + "" ## Header Row Labels: html = html + "" ## Set personal preference for alternative row bgcolors (back ground colours). tr = 1 for row in rows: if tr & 1: bgcolor = "#ffffff" else: bgcolor = "#dfdfdf" html = html + "" html = html + "" tr = tr + 1 html = html + footer file = open("additions.html", "w") file.write(html) file.close ##========================================================================== ## NOTE: [2] Last Run Deletions. sql = ("SELECT * FROM Last_Run_Deletions") curr = conn.cursor() curr.execute(sql) rows = curr.fetchall() ## Replicate HTML Header plus data Table for: table = "Last Run Deletions - [" + date_time +"]" html = main + "
" + table + "
#URITRACKARTISTREVIEWED
" + str(tr) + "" + str(row[0]) + "" + str(row[1]) + "" + str(row[2]) + "" + str(row[3]) + "
" ## Header Row Labels: html = html + "" tr = 1 for row in rows: if tr & 1: bgcolor = "#ffffff" else: bgcolor = "#dfdfdf" html = html + "" html = html + "" tr = tr + 1 html = html + footer file = open("deletions.html", "w") file.write(html) file.close ##========================================================================== ## NOTE: [3] Duplicated tracks. sql = ("SELECT * FROM New_Dupe_Tracks WHERE REVIEWED = 0") curr = conn.cursor() curr.execute(sql) rows = curr.fetchall() ## Replicate HTML Header plus data Table for: table = "Duplicated Tracks - [" + date_time +"]" html = main + "
" + table + "
#URITRACKARTISTALBUM
" + str(tr) + "" + str(row[0]) + "" + str(row[1]) + "" + str(row[2]) + "" + str(row[3]) + "
" ## Header Row Labels: html = html + "" tr = 1 for row in rows: if tr & 1: bgcolor = "#ffffff" else: bgcolor = "#dfdfdf" html = html + "" html = html + "" tr = tr + 1 html = html + footer file = open("duplicates.html", "w") file.write(html) file.close ##========================================================================== ## NOTE: [4] URI Changes. sql = ("SELECT * FROM URI_Change ORDER by DATE_OF_CHANGE DESC") curr = conn.cursor() curr.execute(sql) rows = curr.fetchall() ## Replicate HTML Header plus data Table for: table = "URI Changes - [" + date_time +"]" html = main + "
" + table + "
#URITRACKARTISTREVIEWED
" + str(tr) + "" + str(row[0]) + "" + str(row[1]) + "" + str(row[2]) + "" + str(row[3]) + "
" ## Header Row Labels: html = html + "" tr = 1 for row in rows: if tr & 1: bgcolor = "#ffffff" else: bgcolor = "#dfdfdf" html = html + "" html = html + "" html = html + "" html = html + "" tr = tr + 1 html = html + footer file = open("urichanges.html", "w") file.write(html) file.close ##========================================================================== ## NOTE: [5a] Last_Played_Tracks (EPM) sql = ("SELECT * FROM Last_Played_Tracks WHERE PLAY_COUNT != -100 ORDER BY LAST_PLAYED DESC LIMIT 100") curr = conn.cursor() curr.execute(sql) rows = curr.fetchall() ## Replicate HTML Header plus data Table for: table = "Last Played Tracks (EPM) - [" + date_time +"]" html = main + "
" + table + "
#DATEOLD URINEW URITRACKARTIST
" + str(tr) + "" + str(row[0]) + "" + str(row[1]) + "" + str(row[2]) + "" + str(row[3]) + "" + str(row[4]) + "
" ## Header Row Labels: html = html + "" tr = 1 for row in rows: if tr & 1: bgcolor = "#ffffff" else: bgcolor = "#dfdfdf" html = html + "" html = html + "" tr = tr + 1 html = html + footer file = open("played-epm.html", "w") file.write(html) file.close ##========================================================================== ## NOTE: [5b] Last_Played_Tracks (EXT) sql = ("SELECT * FROM Last_Played_Tracks WHERE PLAY_COUNT = -100 ORDER BY LAST_PLAYED DESC") curr = conn.cursor() curr.execute(sql) rows = curr.fetchall() ## Replicate HTML Header plus data Table for: table = "Last Played Tracks (EXT) - [" + date_time +"]" html = main + "
" + table + "
#URITRACKARTISTPLAYED
" + str(tr) + "" + str(row[0]) + "" + str(row[1]) + "" + str(row[2]) + "" + str(row[3]) + "
" ## Header Row Labels: html = html + "" tr = 1 for row in rows: if tr & 1: bgcolor = "#ffffff" else: bgcolor = "#dfdfdf" html = html + "" html = html + "" tr = tr + 1 html = html + footer file = open("played-ext.html", "w") file.write(html) file.close ##========================================================================== ## NOTE: [6] Queue Check. sql = ("SELECT * FROM Queue_check") curr = conn.cursor() curr.execute(sql) rows = curr.fetchall() ## Replicate HTML Header plus data Table for: table = "Queue Errors - [" + date_time +"]" html = main + "
" + table + "
#URITRACKARTISTPLAYED
" + str(tr) + "" + str(row[0]) + "" + str(row[1]) + "" + str(row[2]) + "" + str(row[3]) + "
" ## Header Row Labels: html = html + "" tr = 1 for row in rows: if tr & 1: bgcolor = "#ffffff" else: bgcolor = "#dfdfdf" html = html + "" html = html + "" tr = tr + 1 html = html + footer file = open("queue-errors.html", "w") file.write(html) file.close except Error as e: print(e) finally: if conn: conn.close() print('All done.')
" + table + "
#URITRACKARTISTALBUM
" + str(tr) + "" + str(row[0]) + "" + str(row[1]) + "" + str(row[2]) + "" + str(row[3]) + "