import os,sys import json import requests import time import sqlite3 from sqlite3 import Error from datetime import date #from secrets import spotify_user_id from refresh import Refresh sys.path.insert(1, 'j:\\Spotty\secrets_and_database') refreshCaller = Refresh() job_done = False os.system('cls') print("Check the artist data for each track in a database") print("") print("V1.0.0 25/11/2023") #print("User: " + spotify_user_id) class Artist_check: def __init__(self): #self.user_id = spotify_user_id self.spotify_token = "" self.here_i_am = os.path.dirname(os.path.abspath(__file__)) self.database = r"j:\Spotty\secrets_and_database\\Missing Enormous Feed.db" self.database2 = r"j:\Spotty\secrets_and_database\\Enormous Feed.db" def do_the_business(self): tic = time.perf_counter() while True: #self.database_checks() #self.get_track_count_from_track_list() #self.get_track_from_track_list() self.verify_artist_changes() if job_done: toc = time.perf_counter() ty_res = time.gmtime(toc - tic) res = time.strftime("%H Hours :%M Minutes :%S Seconds",ty_res) print() print(f"Artist Verify Took {res} Seconds To Add {self.total_records} Records To The artist_verify Table" ) return def database_checks(self): conn = self.create_connection(self.database) cur = conn.cursor() with conn: try: cur.execute("CREATE TABLE IF NOT EXISTS artist_verify (Track_URI text PRIMARY KEY, Track_Name text, Album_Artist_Name text, Artist_Artist_Name text, Artist_Match text);") conn.commit() except Error as e: print(e) def create_connection(self, db_file): # NOTE Connect to the database conn = None conn = sqlite3.connect(db_file) return conn def add_to_db_table(self): #NOTE: Add New Track To Database conn = self.create_connection(self.database) try: tracks = (self.track_uri, self.track_name, self.album_artist_name, self.artist_artist_name, self.artist_match) # NOTE Set the SQL Values sql = ''' INSERT OR IGNORE INTO artist_verify (Track_URI,Track_Name,Album_Artist_Name,Artist_Artist_Name,Artist_Match) VALUES(?,?,?,?,?) ''' cur = conn.cursor() cur.execute(sql, tracks) conn.commit() except sqlite3.IntegrityError as e3: print() print("Add_To_DB") print(e3) return cur.lastrowid def verify_artist_changes(self): avrec = 1 conn = self.create_connection(self.database) cur = conn.cursor() conn_main = self.create_connection(self.database2) cur_main = conn_main.cursor() cur.execute("SELECT COUNT (*) FROM artist_verify") self.total_avrecords = int(cur.fetchone()[0]) cur.execute("SELECT * FROM artist_verify ORDER BY Track_URI ASC") cur_main.execute("SELECT * FROM Track_List") avrows = cur.fetchall() for avrow in avrows: print("Verifying Item " + str(avrec) + " of " + str(self.total_avrecords), end='\r') self.avuri = avrow[0] self.aaname = avrow[3] self.aaname = self.aaname.replace("'","''") self.amatch = int(avrow[4]) try: if self.amatch == 0: cur_main.execute("UPDATE Track_List SET ARTIST_NAME = '" + self.aaname + "' WHERE URI = '" + self.avuri + "'") cur_main.execute("UPDATE Track_List SET Verified = 1 WHERE URI = '" + self.avuri + "'") conn_main.commit() else: cur_main.execute("UPDATE Track_List SET Verified = 1 WHERE URI = '" + self.avuri + "'") conn_main.commit() avrec+=1 except Error as e4: print() print("Add_To_DB") print(self.avuri) print(self.aaname) print(e4) print() avrec+=1 return def get_track_count_from_track_list(self): conn = self.create_connection(self.database) cur = conn.cursor() cur.execute("SELECT COUNT (*) FROM Track_List") self.total_records = int(cur.fetchone()[0]) def get_track_from_track_list(self): global job_done print(str(self.total_records) + " Tracks to check in database") print() rec = 1 conn = self.create_connection(self.database) cur = conn.cursor() cur.execute("SELECT * FROM Track_List ORDER BY ARTIST_NAME ASC") rows = cur.fetchall() for row in rows: if rec > 99: if str(rec)[-2:] == "00": self.call_refresh() print("Working on Item " + str(rec) + " of " + str(self.total_records), end='\r') self.track_id = row[0][14:] self.track_uri = row[0] self.check_track_presence() if self.pres: rec+=1 else: query = "https://api.spotify.com/v1/tracks/{}".format(self.track_id) response = requests.request("get", url=query, headers={"Content-type": "application/json", "Authorization": "Bearer {}".format(self.spotify_token)}) json_data = json.loads(response.text) self.track_name = json_data["name"] self.artist_artist_name = json_data["artists"][0]["name"] self.album_artist_name = json_data["album"]["artists"][0]["name"] if self.artist_artist_name == self.album_artist_name: self.artist_match = 1 else: self.artist_match = 0 rec+=1 if response.status_code == 200: self.add_to_db_table() job_done = True def check_track_presence(self): self.pres = False conn = self.create_connection(self.database) cur = conn.cursor() cur.execute("SELECT * FROM artist_verify WHERE Track_URI = '" + self.track_uri + "'") rows = cur.fetchall() if len(rows) == 1 : self.pres = True return else: return def call_refresh(self): #NOTE Euans 2nd Fix global refreshCaller while True: self.spotify_token = refreshCaller.refresh() if self.spotify_token == -1: print("==============================\n[!] Error refreshing token, retrying in 30 Seconds\n==============================") time.sleep(30) else: break a = Artist_check() a.call_refresh() a.do_the_business()