def albums_by_length(): global albumcount global logcount f.write("{:<30}{:>10}{:>11}{:>10}{:>11}{:>10}{:>11}{:>10}\n".format( "by Length", "Count", "%", "Plays", "%", "Played", "%", "Ratio")) f.write("-" * 105 + "\n") sql = "SELECT ((albumlength/60) DIV 15) * 15 as LengthGroup, COUNT(albumlengths.albumid) as TypeCount, " \ "SUM(album.playcount) as PlayCount, sum(album.played) as Played " \ "FROM albumlengths INNER JOIN albumview as album on albumlengths.albumid = album.albumid " \ "where albumlength < 120*60 and album.albumtypeid<>16 GROUP BY LengthGroup ORDER BY LengthGroup;" results = common.get_results(sql) for r in results: y = int(r[0]) count = int(r[1]) percent = 0 if albumcount == 0 else (count / albumcount) * 100 playcount = int(r[2]) logpercent = 0 if logcount == 0 else (playcount / logcount) * 100 played = int(r[3]) playedpercent = (played / count) * 100 ratio = 0 if played == 0 else playcount / played tstring = "{} - {} min".format(y, y + 14) line = "{:<30}{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10.2f}\n".format( tstring, count, percent, playcount, logpercent, played, playedpercent, ratio) f.write(line) if (y + 15) % 60 == 0: f.write("-" * 105 + "\n") sql = "SELECT ((albumlength/60) DIV 15) * 15 as LengthGroup, COUNT(albumlengths.albumid) as TypeCount, " \ "SUM(album.playcount) as PlayCount, sum(album.played) as Played " \ "FROM albumlengths INNER JOIN album on albumlengths.albumid = album.albumid " \ "where albumlength >= 120*60 and album.albumtypeid<>16;" results = common.get_results(sql) for r in results: y = int(r[0]) count = int(r[1]) percent = 0 if albumcount == 0 else (count / albumcount) * 100 playcount = int(r[2]) logpercent = 0 if logcount == 0 else (playcount / logcount) * 100 played = int(r[3]) playedpercent = (played / count) * 100 ratio = 0 if played == 0 else playcount / played tstring = "{} - {} min".format(y, y + 14) line = "{:<30}{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10.2f}\n".format( "120+ min", count, percent, playcount, logpercent, played, playedpercent, ratio) f.write(line) f.write("\n\n")
def albums_by_type(): global albumcount global logcount f.write("{:<30}{:>10}{:>11}{:>10}{:>11}{:>10}{:>11}{:>10}\n".format( "by Type", "Count", "%", "Plays", "%", "Played", "%", "Ratio")) f.write("-" * 105 + "\n") sql = "SELECT albumtype, COUNT(albumid) as TypeCount, SUM(playcount) as PlayCount, SUM(played) as Played FROM albumview as album inner join albumtype on " \ "album.albumtypeid = albumtype.albumtypeid where album.albumtypeid<>16 GROUP BY albumtype ORDER BY TypeCount desc;" results = common.get_results(sql) for r in results: albumtype = r[0] count = int(r[1]) percent = 0 if albumcount == 0 else (count / albumcount) * 100 playcount = int(r[2]) logpercent = 0 if logcount == 0 else (playcount / logcount) * 100 played = int(r[3]) playedpercent = 0 if count == 0 else (played / count) * 100 ratio = 0 if played == 0 else playcount / played line = "{:<30}{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10.2f}\n".format( albumtype, count, percent, playcount, logpercent, played, playedpercent, ratio) f.write(line) f.write("\n\n")
def top_ten_artists_log(): global albumcount global logcount f.write("{:<30}{:>10}{:>11}{:>10}{:>11}{:>10}{:>11}{:>10}\n".format( "by Plays", "Count", "%", "Plays", "%", "Played", "%", "Ratio")) f.write("-" * 105 + "\n") sql = "select artist.ArtistName, count(album.AlbumID) as Albums, sum(album.PlayCount) as Plays, sum(album.played) as Played " \ "from artist inner join albumartist on artist.artistid = albumartist.artistid " \ "inner join album on album.albumid = albumartist.albumid " \ "where album.albumtypeid<>16 group by artist.ArtistName order by Plays desc limit 10;" results = common.get_results(sql) for r in results: artistname = common.shorten_by_word(r[0], 25) count = int(r[1]) percent = 0 if albumcount == 0 else (count / albumcount) * 100 playcount = int(r[2]) logpercent = 0 if logcount == 0 else (playcount / logcount) * 100 played = int(r[3]) playedpercent = 0 if count == 0 else (played / count) * 100 ratio = 0 if played == 0 else playcount / played line = "{:<30}{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10.2f}\n".format( artistname.upper(), count, percent, playcount, logpercent, played, playedpercent, ratio) f.write(line) f.write("\n\n")
def top_ten_albums_last_28days(): global albumcount global logcount f.write("{:<80}{:>10}{:>10}\n".format("Last 28 Days", "Plays", "Hours")) f.write("-" * 105 + "\n") sql = "select `artist`.`ArtistName` AS `ArtistName`,`album`.`Album` AS `Album`," \ "sum(`albumlengths`.`albumlength`) AS `Points`,count(`log`.`logID`) AS `Plays` " \ "from ((((`artist` join albumartist on artist.artistid = albumartist.artistid inner join `album` " \ "on((`albumartist`.`albumID` = `album`.`albumID`))) " \ "join `albumlengths` on((`album`.`AlbumID` = `albumlengths`.`albumid`))) " \ "join `log` on((`log`.`AlbumID` = `album`.`AlbumID`))) " \ "join `albumtype` on((`album`.`AlbumTypeID` = `albumtype`.`AlbumTypeID`))) " \ "where log.logDate > (DATE_SUB(CURRENT_DATE, INTERVAL 28 DAY)) and album.albumtypeid<>16 " \ "group by `artist`.`ArtistID`,`album`.`AlbumID`,`artist`.`ArtistName`,`album`.`Album` " \ "order by sum(`albumlengths`.`albumlength`) desc limit 10" results = common.get_results(sql) for r in results: artistname = common.shorten_by_word(r[0], 35) title = common.shorten_by_word(r[1], 35) playcount = int(r[3]) playedtime = r[2] / 3600 line = "{:<40}{:<40}{:>10}{:>10.2f}\n".format(artistname.upper(), title, playcount, playedtime) f.write(line) f.write("\n\n")
def weekly_stats(): global albumcount global logcount f.write("{:<30}{:>10}{:>10}{:>20}{:>11}{:>11}\n".format( "by Week", "Count", "Hrs", "Avg. Len (min)", "Missing", "Total")) f.write("-" * 105 + "\n") sql = "select * FROM (SELECT * from listen_perweek where (Y>=2018) OR (Y = YEAR(CURRENT_DATE) AND W<>WEEK(CURRENT_DATE)+1) ORDER BY Y DESC, W DESC LIMIT 26) rr ORDER BY Y,W ASC;" results = common.get_results(sql) for r in results: y = str(r[0]) + "-" + str(r[1]).zfill(2) missing = missing_logs_week(r[0], r[1]) count = int(r[3]) logtime = format_to_HM(r[2]) avg = format_to_MS(((r[2] * 60) / count)) total = count + missing line = "{:<30}{:>10}{:>10}{:>20}{:>11}{:>11}\n".format( y, count, logtime, avg, missing, total) f.write(line) if r[1] % 13 == 0: f.write("-" * 105 + "\n") f.write("\n\n")
def albums_by_year(): global albumcount global logcount global albumsplayed f.write("{:<30}{:>10}{:>11}{:>10}{:>11}{:>10}{:>11}{:>10}\n".format( "by Year of Release", "Count", " ", "Plays", " ", "Played", "%", "Ratio")) f.write("-" * 105 + "\n") sql = "SELECT yearreleased, COUNT(albumid) as TypeCount, SUM(playcount) as PlayCount, Sum(played) as Played FROM albumview as album " \ "WHERE yearreleased >= 2018 and album.albumtypeid<>16 GROUP BY yearreleased ORDER BY yearreleased;" results = common.get_results(sql) for r in results: y = int(r[0]) count = int(r[1]) percent = 0 if albumcount == 0 else (count / albumcount) * 100 playcount = int(r[2]) logpercent = 0 if logcount == 0 else (playcount / logcount) * 100 played = int(r[3]) playedpercent = (played / count) * 100 ratio = 0 if played == 0 else playcount / played if y % 10 == 0: f.write("-" * 105 + "\n") line = "{:<30}{:>10}{:>21}{:>21}{:>10.2f}%{:>10.2f}\n".format( y, count, playcount, played, playedpercent, ratio) f.write(line) f.write("\n\n")
def thisweek_stats(): global albumcount global logcount f.write("{:<30}{:>10}{:>10}{:>20}{:>11}{:>11}\n".format( "This Week", "Count", "Hrs", "Avg. Len (min)", "Missing", "Total")) f.write("-" * 105 + "\n") sql = "select `log`.`logDate`, sum(`albumlengths`.`albumlength`) / 3600 AS `time`, count(`log`.`logID`) AS `logcount` " \ "from `log` inner join `albumlengths` on `log`.`AlbumID` = `albumlengths`.`albumid` inner join album on albumlengths.albumid = album.albumid " \ "where (year(logDate) = year(CURRENT_DATE)) and (week(logDate) = week(CURRENT_DATE)) group by logDate order by logDate" results = common.get_results(sql) for r in results: logdate = r[0].strftime("%Y-%m-%d") missing = missing_logs_date(logdate) count = int(r[2]) logtime = format_to_HM(r[1]) avg = format_to_MS(((r[1] * 60) / count)) total = count + missing line = "{:<30}{:>10}{:>10}{:>20}{:>11}{:>11}\n".format( logdate, count, logtime, avg, missing, total) f.write(line) if r[1] % 13 == 0: f.write("-" * 105 + "\n") f.write("\n\n")
def albums_by_decade(): global albumcount global logcount f.write("{:<30}{:>10}{:>11}{:>10}{:>11}{:>10}{:>11}{:>10}\n".format( "by Decade", "Count", "%", "Plays", "%", "Played", "%", "Ratio")) f.write("-" * 105 + "\n") sql = "SELECT (yearreleased DIV 10) * 10 as Decade, COUNT(albumid) as TypeCount, SUM(playcount) as PlayCount, sum(played) as Played FROM albumview as album " \ "where album.albumtypeid<>16 GROUP BY Decade ORDER BY Decade;" results = common.get_results(sql) for r in results: y = int(r[0]) count = int(r[1]) percent = 0 if albumcount == 0 else (count / albumcount) * 100 playcount = int(r[2]) logpercent = 0 if logcount == 0 else (playcount / logcount) * 100 played = int(r[3]) playedpercent = 0 if count == 0 else (played / count) * 100 ratio = 0 if played == 0 else playcount / played line = "{:<30}{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10.2f}\n".format( y, count, percent, playcount, logpercent, played, playedpercent, ratio) f.write(line) f.write("\n\n")
def annual(outfile): sql = "SELECT DISTINCT artist.artistid, artist.artistname, artist.sortname, album.albumid, album.album " \ "FROM chart_history inner join albumartist on chart_history.albumid = albumartist.albumid " \ "INNER JOIN album on albumartist.albumid = album.albumid " \ "INNER JOIN artist on albumartist.artistid = artist.artistid " \ "where Y <> 0 AND Q = 0 and tyr = 0 " \ "order by artist.sortname, album.album;" artist_list = common.get_results(sql) years = range(2018, date.today().year + 1) yrstring = " ".join(str(y) for y in years) last_initial = "" outfile.write("{:<60}{}\n".format("", yrstring)) for a in artist_list: artistid, artist, sortname, albumid, album = a initial = sortname[0].upper() yranks = [] yrankstr = "" for y in years: yrank = get_y_rank(albumid, y) if yrank is None: yrankstr += "{:>7}".format("-") else: yrankstr += "{:>7}".format(yrank) linestr = "{:<63}{}".format((artist.upper() + ": " + album)[:60], yrankstr) if initial != last_initial: outfile.write("-" * len(linestr) + "\n") last_initial = initial outfile.write(linestr + "\n")
def seasonal(outfile): sql = "SELECT DISTINCT artist.artistid, artist.artistname, artist.sortname " \ "FROM chart_history inner join artist on chart_history.artistid = artist.artistid " \ "where Y <> 0 AND Q <> 0 AND albumid = 0 " \ "order by artist.sortname;" artist_list = common.get_results(sql) years = range(2018, date.today().year + 1) quarters = range(1, 5) yrstring = "" for y in years: for q in quarters: yrstring += " " + "{}Q{}".format(y, q) last_initial = "" outfile.write("{:<60}{}\n".format("", yrstring)) for a in artist_list: artistid, artist, sortname = a initial = sortname[0] yranks = [] yrankstr = "" for y in years: for q in quarters: yrank = get_q_rank(artistid, y, q) if yrank is None: yrankstr += "{:>9}".format("-") else: yrankstr += "{:>9}".format(yrank) linestr = "{:<68}{}".format(artist.upper(), yrankstr) if initial != last_initial: outfile.write("-" * len(linestr) + "\n") last_initial = initial outfile.write(linestr + "\n")
def top_ten_artists_28days(): global albumcount global logcount f.write("{:<30}{:>10}{:>11}\n".format("Last 28 Days", "Plays", "Hours")) f.write("-" * 80 + "\n") sql = "select artist.ArtistName, sum(albumlengths.albumlength)/3600 as hours, count(log.logID) as logged " \ "from log inner join albumlengths on log.AlbumID = albumlengths.AlbumID " \ "inner join album on album.albumid = albumlengths.albumid " \ "inner join albumartist on album.albumid = albumartist.albumid " \ "inner join artist on albumartist.artistid = artist.ArtistID " \ "where log.logDate > (DATE_SUB(CURRENT_DATE, INTERVAL 28 DAY)) and album.albumtypeid<>16 " \ "group by artist.ArtistName order by hours desc limit 10;" results = common.get_results(sql) for r in results: artistname = common.shorten_by_word(r[0], 25) count = int(r[2]) time = r[1] line = "{:<30}{:>10}{:>11.2f}\n".format(artistname.upper(), count, time) f.write(line) f.write("\n\n")
def annual_stats(): global albumcount global logcount f.write("{:<30}{:>10}{:>10}{:>20}{:>11}{:>11}\n".format( "Annual Stats", "Count", "Hrs", "Avg. Len (min)", "Missing", "Total")) f.write("-" * 105 + "\n") sql = "select year(`log`.`logDate`) as Y, sum(`albumlengths`.`albumlength`) / 3600 AS `time`, count(`log`.`logID`) AS `logcount` " \ "from `log` inner join `albumlengths` on `log`.`AlbumID` = `albumlengths`.`albumid` inner join album on albumlengths.albumid = album.albumid " \ "where year(log.logdate) >= 2017 group by Y order by Y" results = common.get_results(sql) for r in results: y = r[0] missing = missing_logs_year(y) count = int(r[2]) logtime = format_to_HM(r[1]) avg = format_to_MS(((r[1] * 60) / count)) total = count + missing line = "{:<30}{:>10}{:>10}{:>20}{:>11}{:>11}\n".format( y, count, logtime, avg, missing, total) f.write(line) if r[1] % 13 == 0: f.write("-" * 105 + "\n") f.write("\n\n")
def get_y_rank(artistid, yr): sql = "SELECT rank FROM chart_history WHERE artistid = {} AND y = {} AND Q = 0 AND albumid = 0;".format( artistid, yr) results = common.get_results(sql) if len(results) == 0: return None else: return results[0][0]
def get_last_run(albumid): results = common.get_results( "SELECT MAX(chartrun) FROM chart_history_rolling WHERE albumid={};". format(albumid)) if results[0][0] is None: return 0 else: return results[0][0]
def get_albums(): sql = "SELECT album.albumid, artistname, album " \ "FROM albumview as album INNER JOIN albumartist on album.albumid = albumartist.albumid " \ "INNER JOIN artist on albumartist.artistid = artist.artistid " \ "WHERE sourceid=4 and albumtypeid<>7 " \ "and recordedtocassette is null " \ "order by SortName, yearreleased, album;" return common.get_results(sql)
def get_total_plays(y): if y > 0: sql = "SELECT SUM(playcount) FROM album where SourceID<>6 and yearreleased={};".format( y) else: sql = "SELECT SUM(playcount) FROM album where SourceID<>6;" results = common.get_results(sql) return results[0][0]
def get_last_rank(artistid, chart_date): last_week = chart_date - timedelta(days=7) results = common.get_results("SELECT rank FROM chart_history_rolling " "WHERE artistid={} and chartdate='{}';".format(artistid, last_week.strftime("%Y-%m-%d"))) if results is None or len(results) == 0: return None else: return results[0][0]
def get_data(query, condition=None): sql = "SELECT ArtistName, Album, Points, Plays from {} ".format(query) if condition is not None: sql += "WHERE {} ".format(condition) sql += "LIMIT 100;".format(query.lower()) return common.get_results(sql)
def get_media_count(physical): if physical: criteria = "<= 3" else: criteria = "BETWEEN 4 and 5" sql = "SELECT COUNT(albumid) as AlbumCount, sum(played) as Played FROM albumview as album where SourceID {};".format( criteria) results = common.get_results(sql) return results[0][0], results[0][1]
def albums_played_multi_times_in_single_day(): sql = "select distinct artistcredit, album from multiple_plays_per_day;" results = common.get_results(sql) last_log_date = None for r in results: artistname = common.shorten_by_word(r[0], 35) album = common.shorten_by_word(r[1], 35) line = "{:<40}{:<40}\n".format(artistname.upper(), album) f.write(line) f.write("\n\n")
def get_total_time(y): if y > 0: sql = "SELECT SUM(tracklength) FROM tracklengths INNER JOIN album on album.albumid = tracklengths.albumid " \ "INNER JOIN log on log.albumid = album.albumid " \ "where bonustrack = 0 and yearreleased={};".format(y) else: sql = "SELECT SUM(tracklength) FROM tracklengths INNER JOIN album on album.albumid = tracklengths.albumid " \ "INNER JOIN log on log.albumid = album.albumid " \ "where bonustrack = 0;" results = common.get_results(sql) return results[0][0]
def run(): conn = common.conn thresh_date = (date.today() - timedelta(weeks=13)).strftime("%Y-%m-%d") sql = "Select * from (select 'Added' as Status, cast(album.dateadded as date) as `Date`, album.artistcredit as Artist, " \ "album.album as Title from albumview as album " \ "UNION " \ "select 'Played' as status, min(log.logdate) as `Date`, album.artistcredit as Artist, album.album as title " \ "from log inner join albumview as album on album.albumid = log.albumid " \ "group by log.albumid) x " \ f"where `Date` >= '{thresh_date}' " \ "order by `Date`, Status, Artist, Title;" results = common.get_results(sql) outfile = os.path.join(common.basedir, "Growth Report.txt") out = io.open(outfile, "w", encoding='utf-8') last_date = date(1990, 1, 1) progress = 0 positive = 0 neutral = 0 negative = 0 overall = 0 for r in results: status, dt, artist, album = r if dt > last_date: dts = dt.strftime("%Y-%b-%d") if last_date != date(1990, 1, 1): out.write(f"\n\tProgress: {progress}\n") out.write(f"\n{dts}\n\n") last_date = dt positive += 1 if progress > 0 else 0 neutral += 1 if progress == 0 else 0 negative += 1 if progress < 0 else 0 overall += progress progress = 0 out.write(f"\t{status.upper()}\t{artist}: {album}\n") progress += 1 if status == 'Played' else -1 out.write(f"\nPositive Progress:\t{positive}\n") out.write(f"Neutral Progress:\t{neutral}\n") out.write(f"Negative Progress:\t{negative}\n") out.write(f"\n13 Week Progress:\t{overall}\n") out.flush() out.close()
def get_plays_in_week(artistid, chart_date): startofweek = (chart_date - timedelta(weeks=13) + timedelta(days=1)).strftime("%Y-%m-%d") endofweek = chart_date.strftime("%Y-%m-%d") results = common.get_results( "SELECT count(*) as logcount " "FROM log INNER JOIN albumartist ON albumartist.albumid = log.albumid " "INNER JOIN albumview ON albumartist.albumid = albumview.albumid " f"WHERE albumartist.artistid = {artistid} and log.logdate between '{startofweek}' and '{endofweek}';" ) return results[0][0]
def run(): conn = common.conn seperator = "\n\n" + ("-" * 120) + "\n" results = common.get_results( "SELECT artist.artistid, artist.artistname, chr.chartdate, chr.rank, chr.chartrun " "FROM artist INNER JOIN chart_history_rolling chr " "ON artist.artistid = chr.artistid " "ORDER BY artist.sortname, chr.chartdate;") out = io.open(str(Path.home()) + "\Charts\Rolling Artist Stats.txt", "w", encoding='utf-8') last_artist = "" last_run = 0 last_rank = 99 for r in results: artistid, artist, chartdate, rank, run = r indicator = "" if artist != last_artist: out.write(seperator) out.write(artist.upper() + "\n") last_artist = artist last_run = 0 last_rank = 99 if run > last_run: out.write("\n") indicator = "N" if last_run == 0 else "R" last_run = run last_rank = 99 if rank == 1: indicator += "*" elif rank < last_rank and indicator == "": indicator = "+" plays = get_plays_in_week(artistid, chartdate) linestr = "\t\t{:<25}{:>5}{:>5}{:>10}{}\n".format( chartdate.strftime("%Y-%b-%d"), rank, indicator, "", "*" * plays if plays > 0 else "") out.write(linestr) last_rank = rank out.flush() out.close()
def albums_played_last_14days(): sql = "select artistcredit, album, logdate from log_history where logdate > (DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY))" results = common.get_results(sql) last_log_date = None for r in results: artistname = common.shorten_by_word(r[0], 35) album = common.shorten_by_word(r[1], 35) logdate = r[2] if logdate != last_log_date: f.write("\n" + ("-" * 5) + " " + str(logdate) + " " + ("-" * 88) + "\n\n") last_log_date = logdate line = "{:<40}{:<40}\n".format(artistname.upper(), album) f.write(line) f.write("\n\n")
def top_ten_artists_time_played(): global albumcount global logcount f.write("{:<31}{:>9}{:>11}\n".format("by Time Played", "Plays", "Hours")) f.write("-" * 80 + "\n") sql = "select artistname, plays, points / 3600 as hours from chart_artist_alltime order by hours desc limit 10;" results = common.get_results(sql) for r in results: artistname = common.shorten_by_word(r[0], 25) count = int(r[1]) time = r[2] line = "{:<30}{:>10}{:>11.2f}\n".format(artistname.upper(), count, time) f.write(line) f.write("\n\n")
def albums_added_last_14days(): sql = "select artistcredit, album, datepurchased, source from recent_additions where source <> 'Digital' and " \ "datepurchased > (DATE_SUB(CURRENT_DATE, INTERVAL 28 DAY))" results = common.get_results(sql) last_log_date = None for r in results: artistname = common.shorten_by_word(r[0], 35) album = common.shorten_by_word(r[1], 35) datepurchased = r[2] source = r[3] if datepurchased != last_log_date: f.write("\n" + ("-" * 5) + " " + str(datepurchased) + " " + ("-" * 88) + "\n\n") last_log_date = datepurchased line = "{:<40}{:<40}{:<10}\n".format(artistname.upper(), album, source) f.write(line) f.write("\n\n")
def albums_requiring_purchase(): global albumcount global logcount f.write("{:<80}{:>10}\n".format("Requiring Purchase", "Plays")) f.write("-" * 105 + "\n") sql = "select artistname, title, count(*) as Plays from log_reimport lr where lr.input is null and lr.requires_rerip is null " \ "group by artistname, title having Plays >= 3 order by plays desc, artistname, title LIMIT 10" results = common.get_results(sql) for r in results: artistname = common.shorten_by_word(r[0], 35) title = common.shorten_by_word(r[1], 35) playcount = int(r[2]) line = "{:<40}{:<40}{:>10}\n".format(artistname.upper(), title, playcount) f.write(line) f.write("\n\n")
def albums_requiring_rerip(): global albumcount global logcount f.write("{:<80}{:>10}\n".format("Requiring Re-Rip/Re-Import", "Plays")) f.write("-" * 105 + "\n") sql = "select artistcredit, album, playcount FROM albumview as album where TBA=1 " \ "order by playcount desc, artistcredit, album; " results = common.get_results(sql) for r in results: artistname = common.shorten_by_word(r[0], 35) title = common.shorten_by_word(r[1], 35) playcount = int(r[2]) line = "{:<40}{:<40}{:>10}\n".format(artistname.upper(), title, playcount) f.write(line) f.write("\n\n")
def top_ten_streams_bycount(): global albumcount global logcount f.write("{:<80}{:>10}{:>10}\n".format("Streams", "Plays", "Hours")) f.write("-" * 105 + "\n") sql = "select artistname, album, Plays, Points from chart_streamed_alltime order by Plays desc LIMIT 10" results = common.get_results(sql) for r in results: artistname = common.shorten_by_word(r[0], 35) title = common.shorten_by_word(r[1], 35) playcount = int(r[2]) playedtime = r[3] / 3600 line = "{:<40}{:<40}{:>10}{:>10.2f}\n".format(artistname.upper(), title, playcount, playedtime) f.write(line) f.write("\n\n")