Creativity and Computation The superlative LAB WEEK 6! Connecting to MySQL using Python and HTML

1
Creativity and ComputationThe superlativeLAB
WEEK 6!Connecting to MySQL using Python and
HTML
2
This week we will show you how to do what you are
all worried aboutConnect to a database from
the web, retrieve info and use it.(Thanks to
Mike Edwards for his work on this.)
3
We will use Python for this, continuing to
challenge the Gods!
4
This PPT file is up on the CC Lab site in
MySQL.You might want to download it and cut
and paste from it.
5
A brief description of what we will doWe will
embed Python code in an HTML script. The Python
code will retrieve a URL (possibly the address of
your mp3?) from a MySQL database.
6
We will do this in three steps

1. Test the MySQL from the command line
2. Test the Python from the interactive shell
3. Create a script using PyHP

7
Everyone should have downloaded and untarred the
PyHP tarball into their cgi-bin dir (we did this
a couple of sessions ago)
8
Also, this demo assumes you have a table in your
MySQL database called "items" with a column
called "mp3_url" and at least one row.You can
change names as needed.
9
So, lets check the MySQL from the command line
(on lta.parsons.edugt) mysql -u username -p
password
10
mysqlgt SHOW tables--------------------
Tables_in_username --------------------
items --------------------10
rows in set (0.00 sec)
11
mysqlgt SELECT mp3_url FROM items WHERE
id1(As long as you have a URL stored in the
first row of items, you should get the desired
response here.)
12
Now lets walk through the Python code in the
interactive mode (on lta.parsons.edugt) python
13
gtgtgt import MySQLdbgtgtgt connection
MySQLdb.connect(user"medwards",password"XXXXXX
XXX",db"medwards")gtgtgt cursor
connection.cursor()(These are each single line
entries-- open up this powerpoint on the CCLab
site in the MySQL section and cut and paste)
14
gtgtgt cursor.execute("SELECT mp3_url FROM items
WHERE id1")1Lgtgtgt rows cursor.fetchall()gtgt
gt print rowsgtgtgt mp3_url rows00gtgtgt print
mp3_url
15
Ok, now lets use PyHP to embed the Python (and
the MySQL access) in HTML code
16
If you study the following file, youll notice
the Python is exactly the same as we used in
interactive mode, with the exception that we pass
the variable mp3_url to the HTML output.
17
!/usr/bin/env /u/m2008/username/public_html/cgi-b
in/PyHP/pyhp.pyltimport MySQLdbconnection
MySQLdb.connect(userusername",passwd"XXXXXXXX
X",dbusername")cursor connection.cursor()cur
sor.execute("SELECT mp3_url FROM items WHERE
id1")rows cursor.fetchall()mp3_url
rows00gtlthtmlgt ltheadgt
lttitlegtMy MP3 Filelt/titlegt lt/headgt
ltbodygt lth1gtMy MP3 Filelt/h1gt
ltembed src"lt mp3_url gt"/gt
lt/bodygtlt/htmlgt
18
This file will need to be saved in your cgi-bin
folder in order to execute properly. You might
want to have it open a new window to
automatically play your mp3 file. You decide.
19
You can play with this all you want. Do more
complicated things with the SQL or the
HTML.Remember, if you need more review, Mike
Edwards will do a workshop on all of this 11am
Saturday morning, 10FL
20
Pretty simple, eh? The main things are to make
certain

1. The database (and URL data) is in place (test it
   from the command line)
2. The Python code works (from the command line

21
You can use this demo to take care of two aspects
of your poscasting assignment1. The URL for
the mp3 of your podcast2. Use of Python and
MySQL
22
Enough for now. Have fun making and posting
your podcasts!