BBC iPlayer alerter – Part 2: Data storage

Before reading this article you should read my first post of this series: BBC iPlayer alerter – Part 1: JSON processing before you dive into this one.

You will remember that for step 2 of my plan to automate detection of new BBC iPlayer shows, I had to compare the shows detected in the current run through the JSON. This obviously means that all the shows that were on last week had to be stored somewhere, so they could be compared for changes (new show would not have been there last week; also shows that are no longer current will not be there).

The obvious place to store all the information is within a database, and, as the application is sitting on a Raspberry Pi, MySQL is the best choice.

Predictably, I wrote a test program for reading from and writing to a MySQL database from Python. For this, I used the pymysql library, which is excellent:


import pymysql

conn = pymysql.connect(host='', unix_socket='/var/run/mysqld/mysqld.sock', user='username', passwd='pwd', database='your_database')

cur = conn.cursor()

cur.execute("SELECT * FROM your_table")

for row in cur:


What took me longest to get this working is the socket in the pymysql.connect() function. The usual port (3306) did not work, for reasons not entirely clear to me. In the end I had to put in unix_socket=’/var/run/mysqld/mysqld.sock’, as you can see above.

In a similar script for testing the insert, it took me a while to realise conn.commit() had to be called for it to work.

BBC iPlayer alerter – Part 1: JSON processing

As I am unable to read during my morning commute, I have taken to listening to audiobooks to fill the time usefully.

A great source of material to listen to are the shows on BBC Radio 4 Extra. I have a technique for getting the iPlayer content onto my generic mp3 player, but I found trawling through the lists of upcoming programmes to earmark those I wanted to watch rather laborious. 

To automate this process, I wanted to get an email on a weekly basis telling me of new upcoming shows. I would then just need to read through the handful of new shows that had been added, as opposed to the entire list. The only computer that I keep on all the time is my Raspberry Pi, so I decided a Python script run using a weekly cron job would meet the case.

I split the problem into 3 basic steps (hence splitting the posts for the project into 3 parts):

  1. Automatically read all the current shows
  2. Compare the list of current shows to those of last week
  3. Send an email of the newly detected shows to me

To accomplish point number 1, I discovered that the main list of shows I browsed regularly (Drama) was available in JSON format by appending .json to the url:

Resulting in the raw information about all the current shows. A small sample is below:

{"type":"series","pid":"b01pvbbs","title":"David Constantine - Tea at the Midland","short_synopsis":"Short stories from one of the London Evening Standard's Books of the Year 2012.","image":{"filename":"tea-at-the-midland_midland-hotel-morecambe_140113_s_get.jpg"},"is_available":true},{"type":"series","pid":"b00yjr30","title":"Dick Francis - Dead on Red","short_synopsis":"Simmering resentment brings a high class hit-man over from France to target a jockey","image":{"filename":"dick_francins_dead_on_red_0211_s_get.jpg"},"is_available":true},{"type":"brand","pid":"b01ms5v9","title":"Dickens Confidential","short_synopsis":"Before writing his novels, Charles Dickens worked as editor on a newspaper...","is_available":true}

The next thing to do was to process this using a Python script.

Here is the first test:


from urllib import request
import json
url = ""
response = request.urlopen(url)

encoding = response.headers.get_content_charset()
json_object = json.loads('utf-8'))

programmes = json_object['category_slice']['programmes']

for program in programmes:
print (program['title'])
print (program['short_synopsis'])

At this stage, the titles and details of the radio shows are printed on the screen.

I will tackle the use and storage of this data in the next post.

Update a MySQL column trimming a constant number of characters

Will remove the first 4 characters. E.g. ABCD1234, ABCD1235 -> 1234, 1235:

UPDATE table_name
SET title = RIGHT(column_name, CHAR_LENGTH(column_name) - 4)
WHERE clause;

Display your Twitter feed from a website

I have put up several posts on how to display recent Tweets in a webpage with PHP. However, Twitter has switched to it’s API v1.1 from today, so all of my previous articles have become obsolete. The easiest thing to do is to use Twitters own widget: If you log in with your Twitter account, there is a simple wizard that generates some html that can be pasted directly into your webpage.

Identify a (web) link in a string with PHP

If you want to process some text and output the same text with HTML links automatically created for you, then look no further:

$newstring = preg_replace('!(http|ftp|scp)(s)?://[a-zA-Z0-9.?&_/]+!', "<a href="\" target='_blank'></a>",$sting);

Tagged with:

UK to MySQL Date in C#

A C# snippet that will allow a function to be written where a string is passed in, containing a date in the format dd/mm/yyyy, and have that replaced by yyyy-mm-dd:

// Matches 2 digits forward slash 2 digits forward slash 4 digits e.g. 22/02/2013
Match m = Regex.Match(input, "\d{2}/\d{2}/\d{4}");
// could use @"d{2}/d{2}/d{4}" instead of the double backslash escape
if (m.Index > 0)
    string dd = input.Substring(m.Index, 2);
    string mm = input.Substring(m.Index + 3, 2);
    string yyyy = input.Substring(m.Index + 6, 4);

    input = input.Replace(dd + "/" + mm + "/" + yyyy, yyyy + "-" + mm + "-" + dd);

Displaying your latest Tweet in PHP – updated

Back in 2011 I posted a method for displaying your latest tweet in php. However times change, and the twitter API has been changed beyond all recognition. I have updated my script to interpret the JSON response from the latest iteration of the API:

$username = "cytedesign";
$feed = "" . $username . ".json?count=1&include_rts=1&callback=?";

function parse_feed($feed) {
$stepOne = explode('"text":"', $feed);
$stepTwo = explode('","source"', $stepOne[1]);
$tweet = $stepTwo[0];
$tweet = htmlspecialchars_decode($tweet,ENT_QUOTES);
$tweet = str_replace("/", "/", $tweet);
return $tweet;

$twitterFeed = file_get_contents($feed);

I have been very busy with a brand new job that has been keeping me very busy, with more of a focus on developing desktop applications. I intend to start blogging a little again here as I get more comfortable with my new job.

Tagged with: , ,

Notes on the Raspberry Pi

OS used: debian6-19-04-2012

I wanted to be able to play with my Pi, but allow others in my household to watch TV, so I picked up a 3.5″ TFT monitor from ebay for under £15, to make use of the RCA video output.

Videos on youtube I have seen with similar monitors all seem to suffer from not being able to read the text very clearly. I was able to adjust the font size on boot by editing /etc/default/console-setup  to the largest I could find: Lat15-Terminus32x16.psf.gz.

I also found that I needed to create a /boot/config.text file containing the line: overscan_left=20, to allow me to see the left edge of the display.

Hello World in C

Create hello.c in the directory of your choice:

#include <stdio.h>
main ()
  printf("Hello worldn");

I used the joe text editor to create the file.

Then run:

make hello

and you should see the output:

cc hello.c -o hello

finally you can run the program:


and get:

Hello world


Now onto using the GPIO… I have been reading up on WiringPi, so hopefully I will be able to extract some ideas from there.

Tagged with: , ,

Set date and time of a Linux device from a Windows batchfile

I had a client with Linux device that whose clock is not as reliable as it should be, running a time-critical application.

As a workaround fix I wrote a Windows batchfile to update the Linux date and time, and  he has set up a windows scheduled task to run it every morning:

set Day=%date:~0,2%
set Month=%date:~3,2%
set Year=%date:~6,4%

set Hour=%time:~0,2%
set Minute=%time:~3,2%
set Second=%time:~6,2%

@C:path_to_plinkplink.exe -ssh -pw linux_password linux_user@ date %Month%%Day%%Hour%%Minute%%Year%.%Second%

Once again I have utilised one of the free PuTTY tools (plink.exe):

Tagged with: ,