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()