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:
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:
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.