In a
previous posting I described how I used Twitter, Python and MySQL to capture and log my exercise habits (what I like to call physical jerks).
What I've learnt about myself over the years is that to keep exercising regularly I need:
- Gratification, i.e. something to say "well done" when I've done some exercise (a la Strava Kudos).
- Having some fun data to play with.
- Nagging, i.e. something to keep telling me to do my exercises.
- Targets
I talked about
gratification in my last Jerks post. When I do exercise, I tweet, my Raspberry Pi picks this up and sends me a Twitter DM to say "well done". Example:
When it comes to
fun data, it's a virtuous circle. I exercise more and get fitter, I get more fun data. I want more fun data, I exercise more and get fitter. At the time of writing the database looks something like this:
mysql> SELECT exercise, SUM(count) FROM exercise GROUP BY exercise order by sum(count);
+--------------------+------------+
| exercise | SUM(count) |
+--------------------+------------+
| Yoga | 7 |
| Pilates | 7 |
| Leg Weights | 10 |
| Hundred Ups | 11 |
| Foam Rolling | 16 |
| General Stretching | 16 |
| Squatting | 55 |
| Side Raises | 169 |
| Arm Raises | 169 |
| Bicep Curls | 176 |
| Shoulder Press | 182 |
| Tricep Curls | 239 |
| Clap Press Ups | 263 |
| Sit Ups | 335 |
| Abdominal Crunches | 578 |
| Press Ups | 872 |
| Calf Raises | 1384 |
+--------------------+------------+
17 rows in set (0.13 sec)
When it comes to nagging, this is what I've been working on recently. I decided to create a Python script that would periodically email with details of:
- Jerks I've done today
- Jerks I did yesterday
- Jerks I've done this week
- Jerks I've done this month
- Jerks I've done this year
- All time Jerks
The SQL for this is pretty basic, (similar to that laid out above but with date parameters). The first thing I needed to do was be able to look at today's date and calculate some other dates as offsets to it (i.e. date yesterday, date of start of week, date of start of year). Here's an example for start of week from the GetADate function (full code below):
elif (DateType == DateFirstDayOfMonth):
now = datetime.now()
DayOfMonth = int(now.strftime("%d"))
DayDelta = DayOfMonth - 1
FirstDateOfMonth = now - timedelta(days=DayDelta)
return FirstDateOfMonth.strftime("%Y-%m-%d")
This uses the "%d" attribute for strftime to return the number associated with the day of month. e.g. would return 24 for today, the 24th of January. It then uses the timedelta method (imported from datetime) with an offset of the day number minus 1 (so 23 in my example) to calculate the date of the first day of the month. This is then returned to be used in the SQL.
The email I create is formed from HTML so there's a function (CreateHTMLTable) that takes an SQL cursor as an attribute and forms a heading plus HTML table. It does this no matter how many columns or rows in the SQL response. This results in a HTML segment, albeit with no indentation.
I send the email using methods from the smtplib module. There's plenty of examples of how to do this on the interweb. I used
this one from Stack Overflow that shows how to create HTML and text emails. The full code is shown below in the SendEmail function and is pretty self-explanatory. What I did find is that when I tried to use my Gmail and Outlook.com accounts to send the email, these providers did not "like" me using this method to send. Gmail blocked it out-right, telling me to lower my privacy settings to continue (which I didn't). Outlook.com kept asking me to re-authenticate my account which was a pain. I ended up using an old, unused email account from my ISP which seems to less restrictions. (It's re-assuring that Google and Microsoft have implemented these feature).
So a cron job runs the script every hour (from 8am to 10pm at weekends and 6pm to 10pm on weekdays). The email comes to my smartphone and, as I'm basically addicted to it, I pick it up pretty quickly. The first email of the day is often something like this which is a big insulting nag to do something:
...but then I get emails like this which is like "get in, did more today than yesterday":
Then I get a series of interesting summaries like these:
...and these:
Followed by a reminder of the short codes for the TUI:
So that just leaves the targets. I set these at the start of the year:
...but currently have to manually compare actuals with targets. Sounds like another Geek Dad project to create a nagging capability that includes targets...
Full code listing:
#V1 - First version with exercise table summary
#V2 - Second version with HTML tables and lookup summary
#V3 - Added more summaries and a def to create tables
#V4 - Finished the summaries and formatting changes
#Sends a summary email of Jerks exercise
from datetime import datetime, timedelta
import smtplib
import MySQLdb
#MIME multipart stuff
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
#Email Constants
smtpserver = 'SMTP Server Here'
AUTHREQUIRED = 1 # if you need to use SMTP AUTH set to 1
smtpuser = 'SMTP Username Here' # for SMTP AUTH, set SMTP username here
smtppass = 'SMTP Password Here' # for SMTP AUTH, set SMTP password here
RECIPIENTS = 'Receiving address Here'
SENDER = 'Sending address Here'
#Database related contents
dbMainTable = "exercise"
dbLookupTable = "lookup"
#Date manipulation related constants
DateToday = 'DateToday'
DateYesterday = 'DateYesterday'
DateFirstDayOfWeek = 'DateFirstDayOfWeek'
DateFirstDayOfMonth = 'DateFirstDayOfMonth'
DateFirstDayOfYear = 'DateFirstDayOfYear'
#Gets a initial timestampt for emails
def GetDateTime():
#Get the time, format it and return it
now = datetime.now()
return str(now.date()) + ' ' + str(now.strftime("%H:%M:%S"))
#Send an email
def SendEmail(TheSubject,TheMessage):
#Form the message
#msg = "To:%s\nFrom:%s\nSubject: %s\n\n%s" % (RECIPIENTS, SENDER, TheSubject, TheMessage)
#Start forming the MIME Multipart message
msg = MIMEMultipart('alternative')
msg['Subject'] = TheSubject
msg['From'] = SENDER
msg['To'] = RECIPIENTS
# Record the MIME types of both parts - text/plain and text/html.
#part1 = MIMEText(text, 'plain')
part2 = MIMEText(TheMessage, 'html')
# Attach parts into message container.
# According to RFC 2046, the last part of a multipart message, in this case
# the HTML message, is best and preferred.
#msg.attach(part1)
msg.attach(part2)
#print msg
#Do the stuff to send the message
server = smtplib.SMTP(smtpserver,587)
server.ehlo()
#server.starttls()
server.ehlo()
server.login(smtpuser,smtppass)
server.set_debuglevel(1)
server.sendmail(SENDER, [RECIPIENTS], msg.as_string())
server.quit()
#Creates a string with a HTML table and a heading based upon parameters sent
def CreateHTMLTable(InDBResult, InTitle):
try:
#Start with the heading
OutString = '<H2>' + InTitle + '</H2>\r\n'
#See if there is anything to write
if (InDBResult.rowcount > 0):
#Add the table opening tag
OutString = OutString + '<table border="1">\n\r'
#Loop through each of the database rows, adding table rows
for row in InDBResult:
#New table row
OutString = OutString + '<tr>\r\n'
#Add the table elements
for DBElement in row:
OutString = OutString + '<td>' + str(DBElement) + '</td>\r\n'
#Close the table row
OutString = OutString + '</tr>\r\n'
#Close the table tag
OutString = OutString + '</table>\r\n'
#Return the result
return OutString
else:
OutString = OutString + '<p>No database results for this time period. Come on Jerk!</p>\r\n'
return OutString
except:
return 'Error creating HTML table.\r\n'
#Returns a date based upon the parameter supplied
def GetADate(DateType):
#try:
if (DateType == DateToday): #Just get and return todays date
#Get the time, format it and return it
now = datetime.now()
return now.strftime("%Y-%m-%d")
elif (DateType == DateYesterday):
now = datetime.now()
TheDateYesterday = now - timedelta(days=1)
return TheDateYesterday.strftime("%Y-%m-%d")
elif (DateType == DateFirstDayOfWeek): #The first day of the current week. Sunday is 0. Monday is 1 etc. We want to know how many days from Monday it is
#Find what day of the week it is
now = datetime.now()
DayOfWeek = int(now.strftime("%w")) #Get the number of the day of the week
print 'Day of week ->>' + str(DayOfWeek)
#See what to subtract. Sunday is a special case
if (DayOfWeek == 0):
DayDelta = 6 #Monday was always 6 days ago on a Sunday!
else:
DayDelta = DayOfWeek - 1
print 'Day delta ->>' + str(DayDelta)
DateOfMonday = now - timedelta(days=DayDelta)
print 'Monday was ->>' + str(DateOfMonday)
return DateOfMonday.strftime("%Y-%m-%d")
elif (DateType == DateFirstDayOfMonth):
now = datetime.now()
DayOfMonth = int(now.strftime("%d"))
DayDelta = DayOfMonth - 1
FirstDateOfMonth = now - timedelta(days=DayDelta)
return FirstDateOfMonth.strftime("%Y-%m-%d")
elif (DateType == DateFirstDayOfYear):
now = datetime.now()
DayOfYear = int(now.strftime("%j"))
DayDelta = DayOfYear - 1
FirstDateOfYear = now - timedelta(days=DayDelta)
return FirstDateOfYear.strftime("%Y-%m-%d")
#except:
#return '2014-01-01' #Just returns a default date for before I was a jerk
#%j Day of the year as a zero-padded decimal number.
#%d Day of the month as a zero-padded decimal number.
####################################################################
#Main part of the code
#Database stuff
db = MySQLdb.connect("localhost", "username", "password", "database") #host,user,password,database name
curs=db.cursor()
#Run a query for today
DateForQuery = GetADate(DateToday)
JerksQuery = 'SELECT exercise, SUM(count) FROM exercise WHERE tdate = "' + DateForQuery + '" GROUP BY exercise order by sum(count);'
print JerksQuery
curs.execute (JerksQuery)
#Form the HTML Table for today
OutString = CreateHTMLTable(curs, 'JERKS EXERCISE SUMMARY - TODAY')
#Run a query for yesterday
DateForQuery = GetADate(DateYesterday)
JerksQuery = 'SELECT exercise, SUM(count) FROM exercise WHERE tdate = "' + DateForQuery + '" GROUP BY exercise order by sum(count);'
print JerksQuery
curs.execute (JerksQuery)
#Form the HTML Table for today
OutString = OutString + CreateHTMLTable(curs, 'JERKS EXERCISE SUMMARY - YESTERDAY')
#Run a query for first day of this week
DateForQuery = GetADate(DateFirstDayOfWeek)
JerksQuery = 'SELECT exercise, SUM(count) FROM exercise WHERE tdate >= "' + DateForQuery + '" GROUP BY exercise order by sum(count);'
print JerksQuery
curs.execute (JerksQuery)
#Form the HTML table for this week
OutString = OutString + CreateHTMLTable(curs, 'JERKS EXERCISE SUMMARY - THIS WEEK (Since ' + DateForQuery + ')')
#Run a query for first day of this Month
DateForQuery = GetADate(DateFirstDayOfMonth)
JerksQuery = 'SELECT exercise, SUM(count) FROM exercise WHERE tdate >= "' + DateForQuery + '" GROUP BY exercise order by sum(count);'
print JerksQuery
curs.execute (JerksQuery)
#Form the HTML table for this week
OutString = OutString + CreateHTMLTable(curs, 'JERKS EXERCISE SUMMARY - THIS MONTH (Since ' + DateForQuery + ')')
#Run a query for the first day of this year
DateForQuery = GetADate(DateFirstDayOfYear)
JerksQuery = 'SELECT exercise, SUM(count) FROM exercise WHERE tdate >= "' + DateForQuery + '" GROUP BY exercise order by sum(count);'
print JerksQuery
curs.execute (JerksQuery)
#Form the HTML table for this week
OutString = OutString + CreateHTMLTable(curs, 'JERKS EXERCISE SUMMARY - THIS YEAR (Since ' + DateForQuery + ')')
#Form and run the query for the exercise table - all time
JerksQuery = 'SELECT exercise, SUM(count) FROM exercise GROUP BY exercise order by sum(count);'
curs.execute (JerksQuery)
#Form the HTML Table
OutString = OutString + CreateHTMLTable(curs, 'JERKS EXERCISE SUMMARY - ALL TIME')
#Form and run the query for the lookup table
JerksQuery = 'select * from ' + dbLookupTable + ' order by twoletters;'
#print JerksQuery
curs.execute (JerksQuery)
#Call the def to create a table
OutString = OutString + CreateHTMLTable(curs, 'JERKS EXERCISE SHORT CODES')
#Send the email
SendEmail('Jerks Summary at ' + GetDateTime(), OutString)
