Saturday, 31 January 2015

Terrific Times Table Test Tool with SL4A

School test practice for my daughter has again given me inspiration for a Geek project (after my previous project to create a spellings test tool).  At my daughter's school they're currently conducting quick-fire times oral table tests where the teacher reads out 50-odd times table questions in quick succession.  In helping my eldest practice for these tests we found:

  1. It's hard to be really random in the questions you ask and you often end up focusing on one times table (e.g. the twelves).
  2. It's hard to keep track of the questions you ask and the associated answers.
  3. It's hard to make sure you are keeping to an appropriate pace.

So I solved this challenge using SL4A, my favourite scripting language for Android handsets.

Using Python and the Android API binding I could implement the following features:

Feature 1: Generate random times table questions using the randint method from the random module:

from random import randint
FirstVal = randint (3,12)
SecondVal = randint (3,12) 

...the 3 and 12 attributes means the random numbers are always within the range 3 to 12 (inclusive).  This means no super easy 1 and 2 times tables.

Feature 2: Form strings to speak and to print to screen and then say the times table:

#Form strings to print and say
SayString = str (FirstVal) + ' times ' + str (SecondVal)
PrintString = str (i) + ': ' + SayString + ' = ' + str (FirstVal * SecondVal)
print PrintString

#Speak the times table

Feature 3: Compute a variable delay after speaking ones times table and then speaking the next. Easier calculations get a shorter delay, harder calculations get a longer delay:

  #Compute a different delay depending on the complexity of the times table
  TheAnswer = FirstVal * SecondVal
  if TheAnswer < 60:
    TheDelay = 3
  elif TheAnswer > 90:
    TheDelay = 5
    TheDelay = 4
  time.sleep (TheDelay)'s arguable that the complexity of a times table is not 100% correlated with how large the number is (e.g. 7 * 8 is harder than 10 * 10) but it's a good general rule of thumb.

Feature 4: Having an on-screen record of all the questions.  These can be used for post-test marking of my daughter's answers:

Here's a video of it in action:

...and here's all the code.  The great things for me is that this tool, which makes life just a little bit easier, took a mere 20 minutes to write, test and debug.  SL4A is epic!

import android
from random import randint
import time

#Create a Droid object and speak a welcome message
droid = android.Android()
droid.ttsSpeak('Hi. get ready for a terrific times table test')

#Sleep - gives time for the TTS application to initiate
time.sleep (10)

#Loop for as many times as required
for i in range (1,51):
  #Form the two values for the times table
  FirstVal = randint (3,12)
  SecondVal = randint (3,12)
  #Form strings to print and say
  SayString = str (FirstVal) + ' times ' + str (SecondVal)
  PrintString = str (i) + ': ' + SayString + ' = ' + str (FirstVal * SecondVal)
  print PrintString
  #Speak the times table
  #Compute a different delay depending on the complexity of the times table
  TheAnswer = FirstVal * SecondVal
  if TheAnswer < 60:
    TheDelay = 3
  elif TheAnswer > 90:
    TheDelay = 5
    TheDelay = 4
  time.sleep (TheDelay)

Saturday, 24 January 2015

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 =
      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 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). 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 =
  return str( + ' ' + 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.

  #print msg

  #Do the stuff to send the message
  server = smtplib.SMTP(smtpserver,587)
  server.sendmail(SENDER, [RECIPIENTS], msg.as_string())

#Creates a string with a HTML table and a heading based upon parameters sent
def CreateHTMLTable(InDBResult, InTitle):
    #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
      OutString = OutString + '<p>No database results for this time period.  Come on Jerk!</p>\r\n'
      return OutString
    return 'Error creating HTML table.\r\n'

#Returns a date based upon the parameter supplied
def GetADate(DateType):
    if (DateType == DateToday):    #Just get and return todays date
      #Get the time, format it and return it
      now =
      return now.strftime("%Y-%m-%d")
    elif (DateType == DateYesterday):
      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 =
      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!
        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 =
      DayOfMonth =  int(now.strftime("%d"))
      DayDelta = DayOfMonth - 1
      FirstDateOfMonth = now - timedelta(days=DayDelta)
      return FirstDateOfMonth.strftime("%Y-%m-%d")
    elif (DateType == DateFirstDayOfYear):
      now =
      DayOfYear =  int(now.strftime("%j"))
      DayDelta = DayOfYear - 1
      FirstDateOfYear = now - timedelta(days=DayDelta)
      return FirstDateOfYear.strftime("%Y-%m-%d")

   #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

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

Sunday, 18 January 2015

Automated Spellings Test Practice with SL4A

A busy morning a week or so ago gave me an idea for a Geek Dad project.  I was rushing around, getting chores done before work when my daughter reminded me I needed to test her on her spellings ahead of a test that day at school.  If only we had an automatic way of doing this that allowed her to practise on her own....

A detailed Ethnographic* study of how we conduct spelling test practises led to these basic requirements :
  • It's possible to "load" this weeks spellings into the system.
  • The system will read out the spellings to my daughter.
  • The system will take voice commands to manage the practise (e.g. repeat existing word, move on to next word, explain a word).
(*I had a quick think about it!).

First a video of it, then an explanation:

I chose to use SL4A, my favourite scripting language for Android phones, to prototype this.  This allows for a decent Python environment and can access the Android API for text to speech and speech to text capabilities.  One day I'll build a full blown application...

Loading the spellings is done by sending a text message to my phone.  The message format has to be "spellings,<child's name>,word 1,word 2,word n".  I then use the smsGetMessages method to read the SMS inbox, find a spellings test related message and read it into a string array.

The prototype starts by reading a welcome message to the user (gathered from the second part of the SMS) and then reading out  the first spelling.  I use the ttsSpeak method to do the text to speech conversion.  The SpeakSomething function takes an Android object and a string array as parameters.  The reason I use a string array rather than one single string is that it allows you to introduce a short pause whilst the system is speaking.  This makes it sound a little more natural.

The prototype then gets into a While loop, prompting the user to speak and interpreting the speech using the recognizeSpeech method which communicates with Google's most excellent speech to text capability.

The following voice commands have been implemented:
  • The "next" command is achieved by maintaining an index of which word in the string array is the current word.  If "next" is heard, the index is incremented and the new word is played.
  • The "repeat" command just maintains the index and re-plays the current word.
  • The "cheat" command just assigns the current word to the string array (rather than appending it).  This has the effect of putting each letter of the word into an individual element of the string array.  The net result of this is that each letter of the word is spelled out individually.
  • The "define" command makes use of a Dictionary API published by Cambridge University Press.  It's free to sign up for an evaluation key and it offers a lightweight JSON API that takes a word as an argument and provides a response with several definitions, examples and links to online media files.  I simply take the first definition and example from the API response and add them to the string array.The API provides for a bunch of other methods that may be useful for future Geek Dad projects...
  • The "exit" command simply ends the script.

  • The recognizeSpeech method is not 100% reliable (not surprising really, what it actually does is pretty epic).  For example it's not great at recognising my 7 year old daughters voice, most likely because it's too "young".  It works better for my 10 year old's voice; I'm guessing because it's more mature.
  • I'd like to be able to pause program execution while the system is speaking.  The API provides a ttsIsSpeaking() method.  However if I test this using while (DroidObject.ttsIsSpeaking() == True): then it doesn't pause at all and if I do while DroidObject.ttsIsSpeaking(): it just pauses forever, even after speech has stopped.  More investigation required....

Full code listing:

#Import statements
import android
import time
import sys
import urllib2
import re

DictionaryURLStart =''
DictionaryURLEnd = '/?format=xml'

#The API Key.  Get this from
ApiKey = '<Your Key Here>'

#Actually does the speaking
def SpeakSomething(DroidObject,TheMessage):
  for i in range(len(TheMessage)):
    while (DroidObject.ttsIsSpeaking() == True):
      print '.'

#Used to trim tags from HTML, XML or similar.  Nabbed this from a Stack Overflow page
def TrimTags(InStr):
    #Uses regular expression module
    cleanr =re.compile('<.*?>')
    cleantext = re.sub(cleanr,'', InStr)
    return cleantext
    return 'Error trimming tags from a string\r\n'

#################Main Part of the code
print 'starting'

#Create a Droid object
droid = android.Android()

#Get the messages - True means only un-read messages
result = droid.smsGetMessages(False)

#Used to check if we found a spellings message
MessageFound = False

#Outer loop, look at each message
for i in result.result:
  #Get the message we'll manipulate
  CurrentMessage = (i['body']).encode('utf-8')

  #print CurrentMessage
  #Split in a delimited way  
  MessageStr = CurrentMessage.split(',')

  #See of the first word is "spellings"
  if (MessageStr[0] == "spellings"):
    #See if we have already found a spellings text
    if MessageFound:
      #Do nothing
      print 'message already found'
      MessageFound = True
      SpellingsMessage = MessageStr
      print SpellingsMessage

print 'end of spellings search loop'

#At this point we should have found a message
if MessageFound:
  #Play a welcome message
  print 'Playing the welcome message'
  MessageToSpeak = []
  MessageToSpeak.append('Hi ' + SpellingsMessage[1])
  MessageToSpeak.append('.  Welcome to your spellings test.')
  print MessageToSpeak
  #Speak the first spelling
  SpellingNumber = 2  
  MessageToSpeak = []
  MessageToSpeak.append('Word number ' + str(SpellingNumber - 1))

  #Now get in a loop  awaiting voice commands
  EndLooping = False  
  while (EndLooping == False):  
    speech = droid.recognizeSpeech('Command',None,None)  
    #See what the user said
    if speech[1] == 'next':
      #Increment thespelling number and speak
      print 'I heard next'
      SpellingNumber = SpellingNumber + 1
      MessageToSpeak = []
      MessageToSpeak.append('Word number ' + str(SpellingNumber - 1))
      MessageToSpeak.append (SpellingsMessage[SpellingNumber])
    elif speech [1] == 'cheat':
      MessageToSpeak = []
      MessageToSpeak = SpellingsMessage [SpellingNumber]
      SpeakSomething (droid,MessageToSpeak)
    elif speech[1] == 'define':
      #Get a definition from the Cambridge dictionary API
      DictionaryURL = DictionaryURLStart + SpellingsMessage[SpellingNumber] + DictionaryURLEnd
      print DictionaryURL
      #Do the API request
      request = urllib2.Request(DictionaryURL, headers={"accessKey" : ApiKey})
      APIResponse = urllib2.urlopen(request).read()
      #Start a string array to hold our definition
      MessageToSpeak = []

      #First of all - Get the first definition in the API response
      StartPos = APIResponse.find('<def>')
      EndPos = APIResponse.find('<\/def>',StartPos)
      PartString = APIResponse[StartPos:EndPos]
      PrintString = TrimTags(PartString)
      MessageToSpeak.append('Definition. ' + PrintString)

      #Now get the first example
      StartPos = APIResponse.find('<eg>')
      EndPos = APIResponse.find('<\/eg>',StartPos)
      PartString = APIResponse[StartPos:EndPos]
      PrintString = TrimTags(PartString)
      MessageToSpeak.append('Example. ' + PrintString)
      SpeakSomething (droid,MessageToSpeak)
    elif speech[1] == 'exit':
      print 'I heard exit'
      MessageToSpeak = []
      MessageToSpeak.append('Thank you very much and goodbye!')
      EndLooping = True
    elif speech[1] == 'repeat':
      #Dont Increment the spelling number and speak
      print 'I heard repeat'
      MessageToSpeak = []
      MessageToSpeak.append('Word number ' + str(SpellingNumber - 1))
      MessageToSpeak.append(SpellingsMessage [SpellingNumber])
      SpeakSomething (droid,MessageToSpeak)
      print 'Unknown command'
      MessageToSpeak = []
      MessageToSpeak.append('That was an unknown command!')

Saturday, 10 January 2015

Raspberry Pi and Strava API #3

In a previous post I described how I've used the Strava API to analyse the results from my High Intensity Interval Training (HIIT) sessions.  I use PHP on a Raspberry Pi webserver to extract, organise and present Strava data.  It's a few weeks on so it's time to see how I'm getting on.

Here's a new chart showing up to date results:

The previous set of results were up to and included 1014-11-27 so the last seven results are new over and above last time.  So what can I tell from this:

  • I'm a lot more consistent.  There's less "spread" between the fastest and slowest interval.
  • I'm pedaling faster, there's a general upwards trend.

To double check whether the my cadence is getting higher, I've calculated an average of averages (I know this is a little imprecise) which is plotted below: no doubt about this, I'm definitely pedaling faster now.  The reason for this is that I'm now watching my cadence as I do the fast intervals.  I do this simply by strapping my sports watch to the handle bars on my bike! I have a target of 130 RPM so just try and keep my cadence on or above that.

But am I getting any fitter?  To look at this I've also plotted the maximum heart rate logged by my sports watch over the course of the HIIT session.  My theory is that as I get fitter I should be able to achieve the same average cadence for a lower heart rate.  Here's how the chart looks:

To me, this just shows I'm working harder because as my average cadence has gone up, so has my maximum heart rate (notwithstanding the last heart rate point which may just be an anomaly).

Next step: More HIIT sessions, more consistent 130 RPM sessions and (hopefully) a lower heart rate...

So add extra colour I thought I'd include a few pictures the"shed of mild discomfort" where I do my training.  As you can see it's a very high tech, snazzy environment.  I'm sure all the Top Pros from Team Sky and Saxo Tinkoff train in environments like this...

View from the outside, not cluttered at all:

View from the saddle.  always nice to have spiders to keep you company when you're training...

Close up of the clutter.  Just enough room to train in, the turbo moves on the wooden floor if I bump about too much!  Occasionally things drop off the shelves and twice I've shifted backwards and the when has rubbed hard against the chest of drawers, creating a lovely burning rubber aroma...