Nelson's Weblog: tech / python / nicerMySQL

Nicer MySQLdb interface

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)
c.execute("""
  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.

tech • python
2005-01-31 17:08 Z


Mastodon @nelson@tech.lgbt Linkblog Wed 2024-11-20 Francis Williams painting The Platonic Blow Tue 2024-11-19 EmojiRain Bantam Tools Artframe Using OSM vector tiles Starlink sold out Fine-tuning LLMs at home Bluesky starter packs Mon 2024-11-18 Carbon Mapper Sun 2024-11-17 Love, Acceptance, and Screeching Modems Voyager 1 backup radio Gay Tblisi Usenet feed growth Methane mapping City99 console font Sat 2024-11-16 Lorraine Gervais Sill Justin's Linklog Thune 2004 blog campaign Fri 2024-11-15 Open source garage door Search Archives 2023 12 11 10 09 08 07 06 05 04 03 02 01 2022 12 11 10 09 08 07 06 05 04 03 02 01 2021 12 11 10 09 08 07 06 05 04 03 02 01 2020 12 11 10 09 08 07 06 05 04 03 02 01 2019 12 11 10 09 08 07 06 05 04 03 02 01 2018 12 11 10 09 08 07 06 05 04 03 02 01 2017 12 11 10 09 08 07 06 05 04 03 02 01 2016 12 11 10 09 08 07 06 05 04 03 02 01 2015 12 11 10 09 08 07 06 05 04 03 02 01 2014 12 11 10 09 08 07 06 05 04 03 02 01 2013 12 11 10 09 08 07 06 05 04 03 02 01 2012 12 11 10 09 08 07 06 05 04 03 02 01 2011 12 11 10 09 08 07 06 05 04 03 02 01 2010 12 11 10 09 08 07 06 05 04 03 02 01 2009 12 11 10 09 08 07 06 05 04 03 02 01 2008 12 11 10 09 08 07 06 05 04 03 02 01 2007 12 11 10 09 08 07 06 05 04 03 02 01 2006 12 11 10 09 08 07 06 05 04 03 02 01 2005 12 11 10 09 08 07 06 05 04 03 02 01 2004 12 11 10 09 08 07 06 05 04 03 02 01 2003 12 11 10 09 08 07 06 05 04 03 02 01 2002 12 11 10 09 08 07 06 05 04 03 02 01 2001 12 11 10 09 08 07 One good site MDN Nelson Minar nelson@monkey.org Blog licensed under a Creative Commons License		Nicer MySQLdb interface 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) c.execute(""" 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. tech • python 2005-01-31 17:08 Z Nelson's Weblog • tech • python