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:

#!/usr/bin/python

import pymysql

conn = pymysql.connect(host='127.0.0.1', 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:
print(row[1])

cur.close()
conn.close()

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.

Advertisements

One Trackback to “BBC iPlayer alerter – Part 2: Data storage”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: