Archive for ‘SQL’

06/11/2013

BBC iPlayer alerter – Part 3: Email

Before reading this article you should read the previous two posts of this series: BBC iPlayer alerter – Part 1: JSON processing, and BBC iPlayer alerter – Part 2: Data storage.

The final thing I had to prove was that I could send an email from a Python script. A very trivial task as it turned out:

#!/usr/bin/python

import smtplib

sender = 'sender@address.co.uk'
receivers = ['recipient@address.co.uk']

message = """From: From sender <sender@address.co.uk>
To: To recipient <recipient@address.co.uk>
Subject: SMTP e-mail test

Email text ...
"""

try:
smtpObj = smtplib.SMTP('mail.address.co.uk', 25)
smtpObj.login("smtpuser", "smtppwd" )
smtpObj.sendmail(sender, receivers, message)
print ("Successfully sent email")
except SMTPException:
print ("Error: unable to send email")

Bringing all 3 elements together was less trivial – in essence I create arrays of RadioShow objects and compare those. Hopefully the comments in the code will explain most of the rest (please note – the WordPress <code> tags have messed up the indentation of my Python, which, if you know Python, is a big deal – I’ll try and fix this at a later date):

#!/usr/bin/python

from urllib import request
import json
import pymysql
import datetime
import smtplib
import re

# get the current shows as json feed
url = "http://www.bbc.co.uk/radio4extra/programmes/genres/drama/current.json"
response = request.urlopen(url)

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

class RadioShow:
def __init__(self, id, title, short_synopsis, current):
self.id = id
self.title = title
self.short_synopsis = short_synopsis
self.current = current

#create an array to hold the current shows from the json
radio_shows = []
#create an array to hold the existing shows from the database
existing_radio_shows = []
#new shows - add these to the email alert, and also to the database
new_radio_shows = []
#old shows - update these in the database so that current = n
old_radio_shows = []

current_date = datetime.datetime.now().strftime("%Y-%m-%d")

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

i = 0

# read the current shows into the programmes array
for program in programmes:
#print (program['title'])
#print (program['short_synopsis'])
radio_shows.append(RadioShow(i,program['title'],program['short_synopsis'], 'Y'))
i += i

if len(radio_shows) == 0:
print("ERROR: no shows found from the BBC website")
exit()

#for item in radio_shows:
#print(item.title)
#print(item.short_synopsis)

# connect to the mysql database

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

cur = conn.cursor()

cur.execute("SELECT id,title,short_synopsis,current,detecteddt FROM radio_4_extra WHERE current='Y'")

for row in cur:
existing_radio_shows.append(RadioShow(row[0],row[1],row[2],row[3]))

#loop through the radio shows from the json to check for any new ones
for item in radio_shows:
#print("first for loop")
exists = False
for existingitem in existing_radio_shows:
#print(item.title + " " + existingitem.title)
if item.title == existingitem.title:
exists = True
break
if exists == False:
# insert into database, and into the list for emailing
#print("Execute insert")
cur.execute("INSERT INTO radio_4_extra (title,short_synopsis,current,detecteddt) VALUES ('%s','%s','Y','%s')" % (re.escape(item.title),re.escape(item.short_synopsis),current_date))
new_radio_shows.append(RadioShow(1,item.title,item.short_synopsis, 'Y'))
conn.commit()

#loop through the radio shows from the database to check if any have gone from the json
for existingitem in existing_radio_shows:
#print("second for loop")
exists = False
for item in radio_shows:
if existingitem.title == item.title:
exists = True
break
if exists == False:
# its not there anymore - update the existing item so current=N
#print("Execute update")
cur.execute("UPDATE radio_4_extra SET current='N' WHERE id=%d" % (existingitem.id))
old_radio_shows.append(RadioShow(1,item.title,item.short_synopsis, 'Y'))
conn.commit()

cur.close()
conn.close()

# send an email
sender = 'sender@address.co.uk'
receivers = ['recipient@address.co.uk']

message = """From: From sender <sender@address.co.uk>
To: To recipient<recipient@address.co.uk>
Subject: Radio Show Alert

New shows detected ...

"""

for item in new_radio_shows:
message += item.title
message += "\n"
message += item.short_synopsis
message += "\n\n"

try:
smtpObj = smtplib.SMTP('mail.address.co.uk', 25)
smtpObj.login("smtpuser", "smtppwd" )
smtpObj.sendmail(sender, receivers, message)
#print ("Successfully sent email")
except SMTPException:
exit()
#print ("Error: unable to send email")

06/11/2013

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.

07/08/2013

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;

17/03/2011

PHP/MySQL dates

I have posted before as to a MySQL date, i.e. Y-m-d can be converted in a previous post here.

I found myself this evening in need of converting UK dates back into the MySQL format.

Now, the strtotime() function requires a MySQL-style date going in, so doesn’t work in the way I needed it to tonight.

Luckily I found the ukstrtotime() function online, which works great:

function ukstrtotime($str) {
return strtotime(preg_replace("/^([0-9]{1,2})[\/\. -]+([0-9]{1,2})[\/\. -]+([0-9]{1,4})/", "\\2/\\1/\\3", $str));
}

This is then used in the following way:

$mysqldate = date('Y-m-d',ukstrtotime($ukdate));

Tags: , , ,
24/11/2010

Converting stored MySQL dates into UK format

You will no doubt be aware that when you save a date in a MySQL database, it must be in the form of Y-m-d.

I had some difficulty wrestling with the date() section of the PHP manual , and experimented for a few minutes on trying the use the SQL SELECT statement itself to get the date data from the database in my preferred UK (d/m/Y) format without sucess.

I was quite pleased therefore when my perseverance paid off, and I was able to work out how to solve my problem with a single line of PHP code:

$ukdate = date('d/m/Y',strtotime($mysqldate));

Tags: , ,
16/11/2010

Automatic MSSQL 2008 Backup Batchfile

I was asked to create a simple process for backups to a MSSQL database. After a little searching around online I came up with a Windows batchfile, and then used scheduled tasks to run it weekly:

set ToDaysDate=%date:~3,2%%date:~0,2%%date:~6,4%

"C:\Program Files\Microsoft SQL Server\90\Tools\binn\sqlcmd.exe" -U sa -P
Password -S .\INSTANCENAME -Q "BACKUP DATABASE DatabaseName TO
DISK='C:\Backups\DatabaseName%ToDaysDate%.bak' WITH FORMAT"

This saves a backup file in the folder you specify with the data appended – i.e. DatabaseName161110.bak