def execute_sql_get_list(name, s): print(f"execute_sql_get_list: ({name}) - {s}") try: if WRITE_CSV and name is not None: cursor_csv = DATABASE.cursor() cursor_csv.execute(s) filename = f"./CSV/{name}.csv" print(f'Write CSV file: {filename}') os.makedirs(os.path.dirname(filename), exist_ok=True) with open(filename, "w", newline='', encoding="utf-8") as csv_file: csv_writer = csv.writer(csv_file, delimiter=';') # write headers csv_writer.writerow( [f'"{i[0]}"' for i in cursor_csv.description]) for row in cursor_csv.fetchall(): csv_writer.writerow([f'"{r}"' for r in row]) except Exception as e: print(f"execute_sql_get_list - Exception when writing to CSV: {e}") try: c = DATABASE.cursor() c.execute(s) except Exception as e: print(f"execute_sql_get_list - Exception when executing {s}: {e}") return [] rows = c.fetchall() print(f"execute_sql_get_list: ({name}) - length of list: {len(rows)}") return rows
def execute_sql(s): c = DATABASE.cursor() try: c.execute(s) DATABASE.commit() except Exception as e: print(f"Exception when running {s}: {e}")
def execute_many_sql(s, values): c = DATABASE.cursor() try: c.executemany(s, values) DATABASE.commit() except Exception as e: print(f"Exception when running {s}: with values {values} : {e}")
def database_get_years(): c = DATABASE.cursor() c.execute( ''' SELECT DISTINCT(strftime('%Y', timestamp, 'unixepoch')) FROM lastfm_scrobbles ''' ) return c.fetchall()
def internal_get_recent_tracks(number, mode): try: max_date = get_max_scrobble_time() min_date = get_min_scrobble_time() if mode == UPDATE_MODE.GET_FIRST_SCROBBLE: if min_date == 0: recent_tracks = LASTFM_API.get_user( LASTFM_DATA['username']).get_recent_tracks(limit=number, time_from=0) else: recent_tracks = LASTFM_API.get_user( LASTFM_DATA['username']).get_recent_tracks( limit=number, time_from=0, time_to=min_date) elif mode == UPDATE_MODE.UPDATE_DATABASE: recent_tracks = LASTFM_API.get_user( LASTFM_DATA['username']).get_recent_tracks(limit=number, time_from=max_date + 1) count = 0 values = [] except Exception as e: print(e) raise for i, track in enumerate(recent_tracks): count += 1 try: artist = str(track.track.artist) title = str(track.track.title) values.append((artist, title, track.album, track.timestamp)) except Exception as e: print(e) raise try: c = DATABASE.cursor() print(f"Number of scrobbles added to database: {len(values)}") c.executemany( "INSERT INTO lastfm_scrobbles(artist, title, album, timestamp) VALUES(?,?,?,?)", values) DATABASE.commit() except Exception as e: print(e) raise print(f"Count: {count}") return recent_tracks
def execute_sql_get_value(s, params): c = DATABASE.cursor() try: c.execute(s, params) except Exception as e: print(f"Exception when running {s} {params}: {e}") return [] row = c.fetchone() return row
def get_min_scrobble_time(): c = DATABASE.cursor() c.execute(''' SELECT min(timestamp) FROM lastfm_scrobbles ''') data = c.fetchone()[0] if data is None: return 0 else: print(f"First scrobble: {datetime.fromtimestamp(data)}") return int(data)
def database_get_top_artists(limit=0): c = DATABASE.cursor() # All artists if limit == 0: print(f"Get all the top artists") c.execute("SELECT DISTINCT(artist) " "FROM lastfm_scrobbles " "ORDER BY artist") # Limit it to a certain number else: print(f"Get the top {limit} artists") c.execute(f"SELECT artist, count(*) " f"FROM lastfm_scrobbles " f"GROUP BY artist " f"ORDER BY count(*) DESC " f"LIMIT {limit}") return c.fetchall()
def database_get_top_tracks(limit=0): c = DATABASE.cursor() # All tracks if limit == 0: print(f"Get all the top tracks") c.execute(f"SELECT artist, title, count(*) " f"FROM lastfm_scrobbles " f"GROUP BY artist, title " f"ORDER BY count(*) DESC") # Limit it to a certain number else: print(f"Get the top {limit} tracks") c.execute(f"SELECT artist, title, count(*) " f"FROM lastfm_scrobbles " f"GROUP BY artist, title " f"ORDER BY count(*) DESC " f"LIMIT {limit}") return c.fetchall()
def database_get_top_albums(limit=0): c = DATABASE.cursor() # All tracks if limit == 0: print("Get all the top albums") c.execute(f"SELECT album, count(*) " f"FROM lastfm_scrobbles " f"WHERE album IS NOT NULL " f"GROUP BY album " f"ORDER BY count(*) DESC") # Limit it to a certain number else: print(f"Get the top {limit} albums") c.execute(f"SELECT album, count(*) " f"FROM lastfm_scrobbles " f"WHERE album IS NOT NULL " f"GROUP BY album " f"ORDER BY count(*) DESC " f"LIMIT {limit}") return c.fetchall()