bobsedge's Python - MySQL Tutorial

One of the primary reasons we advocated python in the previous tutorial is it's easy handling of data with complex relationships beyond flat text. But this is useless if we can't get the information out of our database! This document describes the interface between python and MySQL using a common library called MySQLdb.

API Standards

Just as SQL is the standard language for querying many database servers, there are standards for designing programatic interfaces to databases to minimize the effort porting code if you change database server. MySQLdb implements python's DB-API v2.0 standard, which in turn is based on other standards for the C programming language. You can reference DB-API here, or investigate MySQLdb by doing the following in the interpreter:

>>> import MySQLdb >>> help("MySQLdb")
(all other examples in this text require you to import MySQLdb or DurandLab first).

Connections and Cursors

Connecting to the DB is a 2-step process.

Connect to the Server.
This first call creates the TCP/IP connection, authenticates you to get access rights, and establishes the initial context for your work (IE which database you'll be using). You get back a connection object which manages the connection.
>>> db = MySQLdb.connect("motif.compbio.cs.cmu.edu","user","password","CDART") >>> type(db) < class 'MySQLdb.connections.Connection'> #do work >>> db.close()
In our lab, the module DurandLab stores many variables common to our environment. It also wraps the MySQLdb "connect()" calls so that you only have to change the code in one place if you move the database.
>>> import DurandLab >>> db = db.getConn("user","password","CDART") >>> type(db) < class 'MySQLdb.connections.Connection'> #do work >>> db.close()
Managing connections can be a real hassle when writing functions rather than stand-alone utilities. If you call a function that creates a connection in a loop, you may end up flooding the database with too many connections. One way around that is to create connections once per module, so that when the module is first imported the connection is made. Then each function refers to the global connection. This isn't thread-safe, but it's convenient for non-threaded programs.
Sometimes, you want to make a new query before retrieving results from the last query. This is the reason for the cursor object.
>>> c = db.cursor()
The cursor object contains the functions to perform actual queries, like fetchone(). In Oracle and other DBs, cursors allow you to "check out" a temporary state in the database, and manipulate data without committing to the db until ready. In MySQL, cursors are handled only on the client side. You can perform seperate queries and fetchone() from the results using two cursor objects, but any updates to the database will effect both cursors.

Retrieving Data

Now let's work with an entire block of code. The purpose of this code is to retrieve all accessions from SwissProt that refer to mouse proteins. Afterwards, we'll create a dictionary connecting these accessions to entry names.

1 import DurandLab 2 import sys 3 4 #establish connection 5 db = DurandLab.pushConn(dbName='SwissProt') 6 c = db.cursor() 7 8 #retrieve accessions 9 n = c.execute("SELECT accession FROM entries WHERE INSTR('gb_taxID_list', '10090')") 10 if n == 0 : 11 print "No mouse proteins found!" 12 sys.exit(1) 13 accessions = c.fetchall() 14 15 #unpack accessions 16 accessions = map(lambda x : x[0], accessions) 17 18 #create dictionary 19 dict = {} 20 for accession in accessions: 21 c.execute("SELECT name WHERE accession = %r"%accession) 22 name = c.fetchone()[0] 23 dict[accession] = name

The "execute" function is used to pass SQL queries directly to the database. (Read my SQL Tutorial to find out about the syntax). Note that the query contains quotes, but we can use SQL's standard single quotes within double or triple-quoted python string. Also, in line 9, I check the return value of the query. This will be a long int containing the number of rows in the return set.
The "fetchall()" command in line 13 downloads the entire result set into a list, where each element is a row. On exceptionally large datasets, this may be a bad choice: your program will lock while it downloads all the data to ram. Line 22 demonstrates the "fetchone()" function which deals with only one result at a time.
Each entry in the "fetchall()" list is a tuple, with each element being the value of one field in that row. Since I retrieved only one column, something odd happened: we have a single-element tuple, which looks like this in python: (data, ). I used a bit of functional programming to get rid of the tuple wrappers. The "map" function applies a function to each element of a list and returns a list of the results. The "lambda" keyword allows me to specify an unnamed function to pass to map. In this case, the function simply returns the first element in the tuple, as a stand alone object.
In line 21, we use python's formatting strings to dynamically generate an SQL query. Note the use of "%r". This codes for "represent": it leaves the single quotes around a string, while printing out in integer normally. Perfect for passing to SQL!

State Info

Examine dir(MySQLdb.cursor) for more functions about the state of the database. Here are some useful ones:

insert_id() -- Returns last automatically generated ID.
rowcount() -- Returns number of results for last query
info() -- Returns results column headings, types and sizes.

back home
bobsedge@andrew.cmu.edu