Joining a MySQL table from within a Python program
Archive - Originally posted on "The Horse's Mouth" - 2010-06-02 19:31:43 - Graham Ellis
Python is a big subject.
MySQL is a big subject.
Yet the two fin so well together that they can be firmly linked, and work well together, using the tiniest drops of glue.
I've provided examples here before, but in answer to a request from a recent delegate, I've just added a fresh example onto our web site. The request was for an example that shows how to join two tables are report the results through a Python program.
1. Load in an appropriate module, and connect to the database:
import MySQLdb
db = MySQLdb.connection(host="127.0.0.1",
user="graham", passwd="abc123", db="whm")
2. Run the query, and store the result
db.query("""select guests.rid, name, about, locate
from guests join rooms on guests.rid = rooms.rid""")
r = db.store_result()
3. Step through the result set, row by row
for row in r.fetch_row(0):
print row
Full source code of my example is [here], and that includes a data dump (mysqldump) of both of the tables - a table of rooms and a table of guests.
By joining the room table and the guest table (the guest table including just a note of the room NUMBER that a guest stayed in), we can find out who had a shower and who had a bath ... and whether they looked out over the back, side or front:
[trainee@holt px]$ python pysql_another
('2', 'John Smith', 'Shower', 'side')
('4', 'Jane and John Brown', 'Shower', 'side')
('5', 'Boris Morrison', 'Bath and Shower', 'front')
[trainee@holt px]$