Exercise Analytics Using Raspberry Pi, MySQL and APIs

Many of the posts I've been doing since the end of 2014 have been about using exercise API data and exercise database data to ensure I keep fit and healthy.  Key examples are:

• Analysing Strava data from my Garmin sports watch
• A MySQL database for exercise.
• Analysing Fitbit data from my Fitbit Charge HR

This has taught me a lot about myself, in particular the need to set myself targets and periodically measure myself against these targets.  Back in early January 2015 I sent myself an email with this set of targets in it:

It's one quarter into the year, it's time to see how I'm doing against the targets.  First I needed to calculate a set of targets for the quarter.  I did this as Year / 4 rounded up to the nearest integer.  This makes the targets:

(Fitbit based Steps - St and Sleep - Sl have been "pro-rated" as I only got the device at the end of January).

So taking each exercise type in turn, here's how I got the data to compare with the targets.

Strava Data

This is cycling, swimming and running data logged using my Garmin sports watch and uploaded to Strava.

For this I wrote a Python script to extract information from the Strava API.  Full code at the bottom of this post but here's some highlights.

A obtained all the data with a single URL request to the Strava API:
urllib2.urlopen('https://www.strava.com/api/v3/activities?access_token=' + StravaToken + '&per_page=200&after=' + TheUnixTime).read()

Here the per_page=200 entity reference means give me 200 records (more than enough for my Strava efforts) and after= means give all records after the specified period (which is defined in Unix time).

The resulting JSON defeated all my ham-fisted attempts to parse using simple methods so I ended up using the json Python module.  This made life a lot easier!  So with:

StravaJSON = json.loads(StravaText)to access the JSON structures.

...and the likes of ...

StravaJSON[i]['type'] to access specific fields.

...it was easy enough to loop through the whole set of records, pick out all the swims, runs and cycles, add together the distances and write the results.  The output was:

pi@raspberrypi ~/exercise $ sudo python strava_v1.py
Swim Count: 13. Swim Distance: 15600.0
Bike Count: 28. Bike Distance: 282693.4
Run Count: 12. Run Distance: 68337.6

(All distances in metres)

Jerks Exercises
Previously I blogged on how I used a MySQL database on my raspberry Pi to log "physical jerks" (e.g. press ups and sits ups).  I communicate these using a simple code sent in a tweet.

Getting all the data is as simple as running an SQL query.  Here's what I got:

mysql> SELECT exercise, SUM(count) FROM exercise where tdate >= "2015-01-01" GROUP BY exercise order by sum(count);
+--------------------+------------+
| exercise           | SUM(count) |
+--------------------+------------+
| Pilates            |         13 |
| Yoga               |         13 |
| Leg Weights        |         16 |
| Hundred Ups        |         19 |
| General Stretching |         23 |
| Foam Rolling       |         25 |
| Squatting          |        112 |
| Arm Raises         |        322 |
| Side Raises        |        322 |
| Clap Press Ups     |        328 |
| Bicep Curls        |        342 |
| Shoulder Press     |        367 |
| Tricep Curls       |        374 |
| Sit Ups            |        789 |
| Abdominal Crunches |       1429 |
| Press Ups          |       1501 |
| Calf Raises        |       1839 |
+--------------------+------------+

Easy!

Fitbit Data
For the sleep data from my Fitbit Charge HR I re-used code that I used for my sleep infographic.  This gave me a per day sleep figure that I simply summed to give me the total sleep for the period.

For steps data (and floors climbed data) I simply modified the sleep code to 1)access the activities resource and 2)pull out the steps and floors data.  Full code below.  Key parts were getting activity data from the API using fitbit-python:

fitbit_data = authd_client._COLLECTION_RESOURCE('activities',DateForAPI)

.and extracting steps and floors from the resulting JSON:

#Get the total steps value
TotalSteps = fitbit_data['summary']['steps']

#Get the total floors value
TotalFloors = fitbit_data['summary']['floors']

Again I simply summed the data from the summary file to give me the single figure I needed.

Overall Result
Here's a table with the overall result.  Green means target met or exceeded, red means not met.





















Observations:

• I nailed all the "counting" targets
• I missed all the harder qualitative targets (e.g. cycling and running speed).
• Some counting targets I only just sneaked in (e.g. Yoga and Pilates).  This is generally stuff I don't like doing.
• Some counting targets I exceeded by a fair amount (e.g. press ups).  These are things I like doing!

So I need to find a way to beat the speed targets I set.  How can technology help me with that??

(I've also added a new target for floors climbed based upon my Q1 daily average).

Strava - Code

import urllib2
import json

#Constants - For Strava
StravaToken = '<Key Here>'


#From http://www.onlineconversion.com/unix_time.htm
TheUnixTime = '1420070400'

#Access the Strava API using a URL
StravaText = urllib2.urlopen('https://www.strava.com/api/v3/activities?access_token=' + StravaToken + '&per_page=200&after=' + TheUnixTime).read()
#print StravaText

#Parse the output to get all the information.  Set up some variables
SwimCount = 0
SwimDistance = 0
RunCount = 0
RunDistance = 0
BikeCount = 0
BikeDistance = 0

#See how many Stravas there are.  Count the word 'name' as there's one per record
RecCount = StravaText.count('name')

#Load the string as a JSON to parse
StravaJSON = json.loads(StravaText)

#Loop through each one
for i in range(0,RecCount):
  #See what type it was and process accordingly
  if (StravaJSON[i]['type'] == 'Swim'):
    SwimCount = SwimCount + 1
    SwimDistance = SwimDistance + StravaJSON[i]['distance']
  elif (StravaJSON[i]['type'] == 'Ride'):
    BikeCount = BikeCount + 1
    BikeDistance = BikeDistance + StravaJSON[i]['distance']
  elif (StravaJSON[i]['type'] == 'Run'):
    RunCount = RunCount + 1
    RunDistance = RunDistance + StravaJSON[i]['distance']

#Print results
print 'Swim Count: ' + str(SwimCount) + '. Swim Distance: ' + str(SwimDistance)
print 'Bike Count: ' + str(BikeCount) + '. Bike Distance: ' + str(BikeDistance)
print 'Run Count: ' + str(RunCount) + '. Run Distance: ' + str(RunDistance)


Fitbit - Code

import fitbit
from datetime import datetime, timedelta
import time

#Constants
CLIENT_KEY = '<Yours Here>'
CLIENT_SECRET = '<Yours Here>'
USER_KEY = '<Yours Here>'
#USER_KEY = '<Yours Here>'
USER_SECRET = '<Yours Here>'

#The first date I used Fitbit
FirstFitbitDate = '2015-01-27'

#Determine how many days to process for.  First day I ever logged was 2015-01-27
def CountTheDays():
  #See how many days there's been between today and my first Fitbit date.
  now = datetime.now()                                         #Todays date
  FirstDate = datetime.strptime(FirstFitbitDate,"%Y-%m-%d")    #First Fitbit date as a Python date object

  #Calculate difference between the two and return it
  return abs((now - FirstDate).days)

#Produce a date in yyyy-mm-dd format that is n days before today's date (where n is a passed parameter)
def ComputeADate(DaysDiff):
  #Get today's date
  now = datetime.now()

  #Compute the difference betwen now and the day difference paremeter passed
  DateResult = now - timedelta(days=DaysDiff)
  return DateResult.strftime("%Y-%m-%d")

#Get a client
authd_client = fitbit.Fitbit(CLIENT_KEY, CLIENT_SECRET, resource_owner_key=USER_KEY, resource_owner_secret=USER_SECRET)

#Find out how many days to compute for
DayCount = CountTheDays()

#Open a file to write the output - minute by minute and summary
SummaryFileToWrite = '/home/pi/exercise/' + 'summary_' + datetime.now().strftime("%Y-%m-%d") + '.csv'
SummaryFile = open(SummaryFileToWrite,'w')

#Process each one of these days stepping back in the for loop and thus stepping up in time
for i in range(DayCount,-1,-1):
  #Get the date to process
  DateForAPI = ComputeADate(i)

  #Tell the user what is happening
  print 'Processing this date: ' + DateForAPI

  #Get sleep
  fitbit_data = authd_client._COLLECTION_RESOURCE('activities',DateForAPI)

  #Get the total steps value
  TotalSteps = fitbit_data['summary']['steps']

  #Get the total floors value
  TotalFloors = fitbit_data['summary']['floors']

  #Write a log of summary data
  SummaryFile.write(DateForAPI + ',' + str(TotalSteps) + ',' + str(TotalFloors) + ',' '\r\n')

  #Wait a bit (for API rate limit)
  time.sleep(1.1)

#We're now at the end of the loop.  Close the file
SummaryFile.close()

Raspberry Pi - Python - MySQL - Cron Jobs and Physical Jerks #2

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)

Raspberry Pi - Python - MySQL - Cron Jobs and Physical Jerks #1

In two previous posts I blogged about how I've linked geekiness and exercise by playing with the Strava API.  This is good for exercise types that Strava covers but doesn't cover the other stuff I like to do like strength and conditioning work (very important now I am entering more mature years).

What I find is that I go through peaks and troughs of doing this sort of exercise.  I have period where I have good intentions and do lots and other periods where I just seem to forget about it.  Hence what I needed is something like Strava that takes a record of what I've done and gives me prompts to do more.

I'm sure there's apps out there that does this sort of thing but that's not the Geek Dad way; I wanted to build my own....

So here was my idea:

1. When I've done some exercise I send a Tweet from my 'phone with some form of short hand notation in it to define what I've done.
2. My Raspberry Pi periodically checks my twitter feed to pick up these Tweets. 
3. My Raspberry Pi logs the exercise data in a database.
4. My Raspberry Pi sends me responses via Twitter to congratulate me for doing the exercise.
5. At a later stage I can analyse the data on a web page.

The TUI (Twitter User Interface) is a little lazy; maybe one day when I get some more time I'll write an app with a decent GUI (Geek User Interface).

I decided to call it "Jerks", as in physical jerks.  The word has no other meaning that I know of... ;-)

To start with  I needed a database.  I decided to use MySQL as it is a core part of a LAMP webserver.  This page provides an excellent tutorial on setting up and using (via Python and PHP) a MySQL database on Raspberry Pi. The only problem I ran into was that my installation process did not prompt me to create a root password.  I followed the steps on this site and managed to resolve this.

To set-up my Jerks database I simply used this MySQL syntax:

mysql > CREATE DATABASE jerks;
mysql USE jerks;

I wanted the method to Tweet in details of the exercises I'd done to be super simple. Hence I decided to use the format - XX N - where XX is a two letter code defining the exercise I'd done and N is the number of repetitions.  So for example:

• Pu 12 - Means I've done 12 press ups.
• Yo 1 - Means I've done 1 Yoga session.

So I needed a look-up database table that could map from these two letter codes to the full exercise name.  This was easy to create:

CREATE TABLE lookup (twoletters TEXT, longform TEXT);

...and add to:

INSERT INTO lookup (twoletters,longform) values("PU","Press Ups");
INSERT INTO lookup (twoletters,longform) values("YO","Yoga");

...meaning I had a table like this after entering lots of insert statements:

mysql> select * from lookup;
+------------+--------------------+
| twoletters | longform           |
+------------+--------------------+
| PU         | Press Ups          |
| CP         | Clap Press Ups     |
| FR         | Foam Rolling       |
| CR         | Calf Raises        |
| PI         | Pilates            |
| YO         | Yoga               |
| SQ         | Squatting          |
| BC         | Bicep Curls        |
| TC         | Tricep Curls       |
| FR         | Front Raises       |
| SR         | Side Raises        |
| GS         | General Stretching |
| LW         | Leg Weights        |
+------------+--------------------+
13 rows in set (0.01 sec)

In terms of playing with Twitter in Python, I used some example code from this awesome tutorial.  This tells you how to sign up for a Twitter developer account, read Tweets and send Tweets.  A quick pydoc twitter.Apito showed me more ways to use the Python Twitter wrapper.

So (after initiating a Twitter object) you can iterate through each of the Tweets using code like this:

# Display all my tweets
for tweet in twitter.statuses.home_timeline():

  print(tweet['text'])

A quick check of the Twitter Developer page showed that this call would result in only the last 20 Tweets being returned so it's not a massive overhead.

So (after checking that a Tweet is a Jerks Tweet by looking for the pattern of two space delimited parts, first part two letters, second part a number) I needed to create a way to check whether a Tweet was a new one or an old one.  I decided to use another database table and to log the unique Tweet ID that is  returned from the Twitter API.  Overall I needed a table to show:

• The date I did the exercise.
• The time I did the exercise.
• The associated Tweet ID.
• The exercise.
• The count of the exercise.

So I used this syntax to create this table:
CREATE TABLE exercise (tdate DATE, ttime TIME, tweet_id TEXT, exercise TEXT, count NUMERIC);

So for every Tweet that came in I could run an SQL query to see if the Tweet ID was in the table and, if not, add a new row to the table with an INSERT statement.

Then to finish the job I could send a Twitter direct message back to me to give me a warm and glowing feeling that I'd done some good to my creaking body.  So my Twitter feed and direct messages looks something like this:

Finally I needed to automate the Python script to get  it to periodically check the Twitter feed, add to the database and direct message me.  Previously I've used endless While loops but this time I fancied using a cron job (as I've never done it before).  After struggling with the crontab command, this excellent page gave me an excellent alternative that worked using a file in/etc/cron.d

So the script works very nicely and after a few days the database is filling up nicely:

mysql> select * from exercise;
+------------+----------+--------------------+--------------------+-------+
| tdate      | ttime    | tweet_id           | exercise           | count |
+------------+----------+--------------------+--------------------+-------+
| 2014-12-10 | 21:33:40 | 542794300512077440 | Press Ups          |     5 |
| 2014-12-10 | 19:51:30 | 542768590521259395 | Clap Press Ups     |    12 |
| 2014-12-09 | 21:46:47 | 542435212346121218 | Foam Rolling       |     1 |
| 2014-12-09 | 21:30:53 | 542431214136039808 | Press Ups          |    10 |
| 2014-12-08 | 19:21:32 | 542036123435369729 | Foam Rolling       |     1 |
| 2014-12-06 | 18:42:07 | 541300987675000064 | Clap Press Ups     |    12 |
| 2014-12-06 | 18:39:29 | 541300987683332864 | Calf Raises        |    12 |
| 2014-12-06 | 18:37:52 | 541302456700659328 | Press Ups          |    12 |
| 2014-12-10 | 21:55:04 | 542790864539329920 | Yoga               |     1 |
| 2014-12-11 | 08:12:05 | 542954961987842176 | Calf Raises        |    40 |
| 2014-12-11 | 21:59:04 | 543161234896574465 | Press Ups          |     5 |
| 2014-12-11 | 22:00:07 | 543163346444444632 | Clap Press Ups     |    12 |
| 2014-12-11 | 22:06:37 | 543164980787878298 | Press Ups          |     4 |
| 2014-12-12 | 07:44:40 | 543310451141212378 | Press Ups          |     3 |
| 2014-12-12 | 09:03:04 | 543330181834567898 | Press Ups          |     1 |
| 2014-12-12 | 09:02:50 | 543330124222222211 | Press Ups          |     1 |
| 2014-12-12 | 07:50:11 | 543311856755551132 | Press Ups          |    10 |
| 2014-12-12 | 18:40:31 | 543475502015523554 | Squatting          |     1 |
| 2014-12-12 | 18:34:33 | 543412342452578571 | Calf Raises        |    12 |
| 2014-12-12 | 18:58:36 | 543480055554545456 | Squatting          |     1 |

+------------+----------+--------------------+--------------------+-------+

20 rows in set (0.00 sec)

Here's all the code (minus sensitive bits of course):

import os
from twitter import *
import MySQLdb
from datetime import datetime
# go to https://dev.twitter.com/apps/new to create your own
# CONSUMER_KEY and CONSUMER_SECRET
# Note that you need to set the access level to read and write
# for this script to work (Found in the settings tab once you
# have created a new application)
# pydoc twitter.Apito get all the twitter documentation
CONSUMER_KEY = "Your_key_here"
CONSUMER_SECRET = "Your_secret_here"

#Database related contents
dbMainTable = "exercise"
dbLookupTable = "lookup"

#Used to take a Twitter format date and turn it into a Python date
def ParseTwitterDate(TwitterDt):
  #The Twitter date is like this -> Sat Dec 06 18:42:07 +0000 2014
  #strptime doesn't deal with the +0000 at all well. So I'll just strip it out
  DateToChange = TwitterDt[0:19] + TwitterDt[25:30]
  return datetime.strptime(DateToChange,'%a %b %d %H:%M:%S %Y')

#######################
#This is the main part of the code

#Database stuff
db = MySQLdb.connect("localhost", "jerks", "user", "password")   #host,user,password,database name
curs=db.cursor()

# get full pathname of .twitterdemo_oauth file in the
# home directory of the current user
oauth_filename = os.path.join(os.path.expanduser('~'),'.twitterdemo_oauth')

# get twitter account login info
if not os.path.exists(oauth_filename):
  oauth_dance('Raspberry Pi Twitter Demo', CONSUMER_KEY, CONSUMER_SECRET, oauth_filename)
(oauth_token, oauth_token_secret) = read_token_file(oauth_filename)


# log in to Twitter
auth = OAuth(oauth_token, oauth_token_secret, CONSUMER_KEY, CONSUMER_SECRET)
twitter = Twitter(auth=auth)
# Tweet a new status update
# twitter.statuses.update(status="Hello The World!")
# Display all my tweets
for tweet in twitter.statuses.home_timeline():
  #Jerks tweets are two parts space delimited, two letters + space + a number and from PDW.  Do all these parts
  #First check if it was from the my account
  if tweet['user']['screen_name'] == 'PDW':
    #Get all the parts of the tweet
    TweetParts = tweet['text'].split(' ')
    #Check for all the right components
    if (len(TweetParts) == 2) and (len(TweetParts[0]) == 2) and (TweetParts[1].isdigit()):
      print tweet['text']
      #This means that this is a Jerks tweet.  Need to check whether it's new or old by looking at the database.  We do this by checking the id.  First form the SQL query, then execute it
      JerksQuery = 'SELECT * FROM ' + dbMainTable + ' where tweet_id="' + str(tweet['id']) + '";'
      curs.execute (JerksQuery)
      #Check whether we got anything in response, if not we add to the database
      if (curs.rowcount == 0):
        #Execute a query to add to the database
        print "Adding to the database"
        try:
          #Form the new query then execute it.  First get the date
          MyDateTime = ParseTwitterDate(tweet['created_at'])

          #Now we need to lookup the two letter code from the tweet into it's long form.  We do this in a lookup table
          JerksQuery = 'SELECT * FROM ' + dbLookupTable + ' where twoletters = "' + TweetParts[0].upper() + '";'
          print JerksQuery
          curs.execute (JerksQuery)
          #Check whether we got anything in response, if not we add to the database
          if (curs.rowcount > 0):
            #We've found the long form value of the exercise in the query response.  Add to the insert query
            for LookUpResponse in curs.fetchall():
              LongFormExercise = LookUpResponse[1]

            #Form the query
            JerksQuery = 'INSERT INTO ' + dbMainTable + ' (tdate,ttime,tweet_id,exercise,count) values("' + str(MyDateTime.date()) + '","' + str(MyDateTime.time()) + '"
,"' + str(tweet['id']) + '","' + LongFormExercise + '",'+ TweetParts[1] + ');'
            print JerksQuery
            curs.execute (JerksQuery)
            db.commit()
            print "Data committed"
            MessageToTweet = 'Nice one jerk!  You did ' + str(TweetParts[1]) + ' ' + LongFormExercise
            print MessageToTweet
            twitter.direct_messages.new(user="PDW",text=MessageToTweet)
          else:
            print "Exercise not in lookup table"
            twitter.direct_messages.new(user="PDW",text='Hey jerk! ' + TweetParts[0] + ' is not a code Jerks understands')
        except:
          print "Error: the database is being rolled back"
          db.rollback()
      else:
        print "No new entries to add"