A quick guide to using MySQL in Python

Need to access some MySQL databases in Python right now? As in now, really, I don’t have time to read stuff, and please stop rambling because you’re wasting my time now? Read on!

Access to MySQL databases is through the MySQLdb module. It’s available in the python-mysqldb package for Debian/Ubuntu users.

Your first step in any Python code is:

import MySQLdb

Python database access modules all have similar interfaces, described by the Python DB-API. Most database modules use the same interface, thus maintaining the illusion that you can substitute your database at any time without changing your code. I suspect that anyone doing this in reality has failed with hilarious consequences, but nonetheless…

Create the connection with:

db = MySQLdb.connect(host="localhost", port=3306, user="foo", passwd="bar", db="qoz")

substituting appropriate local values for each argument.

db is now a handle to the database. Normally, you’ll create a cursor on this handle like so:

cursor = db.cursor()

MySQL doesn’t really support cursors in any sense that’s useful to us here, but the DB-API requires that you interface to them that way. So just copy and paste the line into your code.

To execute queries:

cursor.execute("SELECT name, phone_number FROM coworkers WHERE name=%s AND clue > %s LIMIT 5", (name, clue_threshold))

String interpolation is a bit different here. You can still use Python’s built-in interpolation and write something like:

cursor.execute("SELECT name, phone_number FROM coworkers WHERE name='%s' AND clue > %d LIMIT 5" % (name, clue_threshold))

but the DB-API interpolation will automatically quote things and guard you from SQL injection attacks, to some extent. If you had a name value of "'; DELETE FROM coworkers;" in the first case, you’d be fine (as the single-quote character would be auto-quoted), but you might run into some slight data loss in the second case.

SQL queries are a good place to use Python’s multi-line strings, so you can write something like:

cursor.execute("""SELECT name, phone_number 
                  FROM coworkers 
                  WHERE name=%s 
                  AND clue > %s 
                  LIMIT 5""",
               (name, clue_threshold))

if you want to get fancy about it.

The DB-API quoting seems to work best when using %s quoting exclusively (even for numbers). I’m not exactly sure why.

cursor.execute() will return the number of rows modified or retrieved, just like in PHP.

When performing a SELECT query, each row is represented in Python by an array. For the above SELECT query with columns ‘name’ and ‘phone_number’, you’ll end up with something like:

['Bob', '9123 4567']

cursor.fetchall() will return you an array containing each row in your query results. That is, you get an array of arrays. So the above SELECT query might give you:

[['Bob', '9123 4567'], ['Janet', '8888 8888']]

The easiest thing to do with this is to iterate with something like:

data = cursor.fetchall()
for row in data:
    do stuff

You can also use cursor.fetchone() if you want to retrieve one row at a time. This is handy when you’re doing queries like "SELECT COUNT(*) ..." which only return a single row.

Finally, db.close() will close a database handle. I only mention this because some versions of MySQLdb don’t garbage collect correctly, so you can run out of database connections if you’re not careful.

My own experience has been that exceptions make it extremely difficult to clean up fully by hand; you always end up leaking a connection here or there. I get around this by manually invoking the Python garbage collector:

import gc 
gc.collect()

which will close off any old MySQL connections. You could do it just before creating a new connection.

The Python DB-API doesn’t have a mysql_fetch_assoc() function like PHP. mysql_fetch_assoc() would return an associative array/dictionary containing the results of a SELECT query, like so:

[name: 'Bob', phone_number: '9123 4567']

The nice thing about this is that you can write code like if row['name'] == 'blah':, instead of being dependent on the row ordering in the query.

I wrote this little function to do the same in Python. It’s MySQL-specific, which is why there’s no mysql_fetch_assoc() equivalent in the DB-API already:

def FetchOneAssoc(cursor):
    data = cursor.fetchone()
    if data == None:
        return None
    desc = cursor.description

    dict = {}

    for (name, value) in zip(desc, data):
        dict[name[0]] = value

    return dict

Ian Howson