mylazyhouse.com

I was recently moving some backend code to python, and I quickly realized that I've never worked with a database within python (outside of django). After some quick googling I found the MySQLdb package. MySQLdb is a wrapper for the _mysql api, and it allows you to interact with your MySQL database from within python. In this article I will show you how to install and use MySQLdb.

Installation

Simply download the tarball here.

tar xfz MySQL-python-1.2.3c1.tar.gz
cd MySQL-python-1.2.3c1
python setup.py build
python setup.py install

- or - even simpler:
on apt-get systems (Ubuntu, Debian, etc)

sudo apt-get install python-mysqldb

on YUM systems (CentOS, Fedora, etc)

yum install MySQL-python

To make sure that the package is actually installed. Run the following in a python shell:

import MySQLdb

If there are no errors, congratulations, you can now interact with your MySQL database.

Use

To explain some of MySQLdb's functions, we are going to create a script that grabs a list of teams, then calculates their score and opponents scores, then simply print them out.
To begin include the MySQLdb module into your file. The .cursors is needed if you, like me, want to access your rows by their 'name' in the table. For example instead of row['0'] you can access your data via row['id'].

import MySQLdb
import MySQLdb.cursors

This is the connection syntax that the adapter uses. Again the 'cursorclass=MySQLdb.cursors.DictCursor' line is only neccessary if you would like to access the row by their name.

db=MySQLdb.connect(host="hostname",user="username",passwd="password",
db="db_name", cursorclass=MySQLdb.cursors.DictCursor) 

Next, we are going to declare are cursor. Although not supported by MySQL, the emulated cursor is how we actually interact (read: execute statements) with our database.

c=db.cursor()

In this block of code, we create an empty array (teams) and then execute our first SQL statement. The cursor, which we named c above, will execute a simple statement to select all 'team names' from our table 'teams'. The statement rows = c.fetchall assigns our returned rows to the variable rows. Other than .fetchall which, as you can imagine, returns every row, there is .fetchone(), which returns 1 row, and .fetchmany(x), which returns x rows. With our returned rows in the variable rows, we simply loop through rows and append the data we want, the team name, to our array.

teams =[]
c.execute("""SELECT * FROM teams""")
rows = c.fetchall ()
for row in rows:
	teams.append(row['team_name']) 

In this block, we loop through our teams array, update their scores, and then print the results. Our SQL statement here is different than our first, being as we pass a dynamic value through each iteration of our loop. We tell MySQLdb that we are going to be passing in a string (noted by the %s), and that string is our team name (noted by t). Again we fetchall associated rows and loop through them then tally the points using += row['pts']. Once we have completed our loop, we print our stats. Since we are printing out to screen we cast our int pts and opp_pts to a string with the str(x) function.

for t in teams:
pts = 0
opp_pts = 0
c.execute("""SELECT pts, opp_pts FROM games WHERE team_name = %s""",(t))
rows = c.fetchall ()
for row in rows:
	pts += row['pts']
	opp_pts += row['opp_pts']
print "Team: " + t +" Points: " + str(pts) + " OPP Points: " + str(opp_pts)

Finally we close our database connection with the .close function.

db.close () 

If you have any comments or questions please post below.

1/4/2011 Update

The nice people at Packt Publishing saw this article and gave me the opportunity to offer the 4th chapter in their excellent book MySQL for Python for free download. You can download the chapter entitled Exception Handling here. Full disclosure, I get NO financial or any other type of compensation for this, just thought it was cool that they offered some FREE quality Python information.