I've been doing a lot of MySQL hacking in Python. And like all Python projects I do, I start by stuffing data into anonymous lists and remembering "oh yeah, foo[3] is the name of the wine, and foo[1] is the year". This doesn't scale well, and fortunately MySQLdb has a better way.
import MySQLdb, MySQLdb.cursors
db = MySQLdb.connect(db="wine")
c = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  select name, type, year from wine
  where color = %(color)s and year < %(year)s
  """, { "color": "Red", "year": 1972 })

for row in c.fetchall():
  print row['year'], row['name']
The code snippet above is using dictionaries everywhere; both forming the query and handling the response. This lets me name parameters so that if I add a new condition to the where clause or a new field to the select, the rest of my code doesn't break.

I'm taking advantage of two MySQLdb features that go beyond the standard Python DB API. The first is simple; the magic query construction of execute() handles dictionary style substitution just like you'd expect.

The second is more subtle. MySQLdb supports different cursor classes that extend the basic "tuple of tuples" datatype you usually get from fetchall(). I'm using DictCursor, which builds a dictionary from the names in the description field of the cursor. There are also server-side cursors for efficiency with large result sets. It's all implemented via mixins for flexibility.

I'm particularly looking forward to Andy's 2.0 plan to have a "row object that can behave like a sequence or a mapping (dictionary)", giving you the best of both worlds. Combine that with iterators and you could really have something.

PS: if you search for MySQLdb docs, you quickly land at the obsolete module docs. I used these docs for two years! The MySQLdb project has moved to SourceForge and the MySQLdb docs are nicely hosted there.

  2005-01-31 17:08 Z