

In [ ]:

    
import MySQLdb
import netrc
import pandas
import datetime
import matplotlib.pyplot as plt

%matplotlib inline

login = netrc.netrc().authenticators('lastfm.mysql')
if not login:
    raise netrc.NetrcParseError('No authenticators for lastfm.mysql')
    
mysql = MySQLdb.connect(user=login[0],passwd=login[2],db=login[1], charset='utf8')
cursor = mysql.cursor()

Last execution date and time



In [ ]:

    
today = datetime.datetime.now()
print today.strftime('Generated on the %d %b %Y at %H:%M:%S')

Play count by month



In [ ]:

    
cursor.execute('select * from view_play_count_by_month')
rows = cursor.fetchall()

df = pandas.DataFrame( [[ij for ij in i] for i in rows] )
df.rename(columns={0: 'Month', 1: 'Play count'}, inplace=True)
df = df.sort_values(by='Month', ascending=[0])
df.head(15).T



In [ ]:

    
df['Month'] = pandas.to_datetime(df['Month'], format='%Y-%m')
df = df.sort_values(by='Month', ascending=[1])
my_plot = df.plot(x='Month', title='Play count by month', legend =None, figsize=(16, 10))
my_plot.set_ylabel('Play count')

Play count by day



In [ ]:

    
select = 'select date_format(p.play_date, \'%y-%m-%d\') as day, count(p.play_db_id) as count from play p'
where  = 'where p.play_date > date_add(CURRENT_TIMESTAMP, interval -30 day)'
group_and_order = 'group by day order by day desc'
cursor.execute('%s %s %s' % (select, where, group_and_order))
rows = cursor.fetchall()

df = pandas.DataFrame( [[ij for ij in i] for i in rows] )
df.rename(columns={0: 'Day', 1: 'Play count'}, inplace=True)
df.head(30)
df['Day'] = pandas.to_datetime(df['Day'], format='%y-%m-%d')
my_plot = df.plot(x='Day', title='Play count by day', legend =None, figsize=(16, 10))
my_plot.set_ylabel('Play count')

Recent plays



In [ ]:

    
cursor.execute('select * from view_plays')
rows = cursor.fetchall()

df = pandas.DataFrame( [[ij for ij in i] for i in rows] )
df = df.drop(4, 1)
df.rename(columns={0: 'Track nb', 1: 'Track', 2: 'Artist', 3: 'Album', 5: 'Date'}, inplace=True);
df.head(30)