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 Sat 2024-12-21 License Plate Game Fri 2024-12-20 The Loves of my Life Musk + AfD Thu 2024-12-19 Another $2B Wed 2024-12-18 mail-tester.com Kenji and alcohol Amazon Time Sync hydroxychloroquine fraud withdrawn Tue 2024-12-17 A New Social Tacticalgramma Sun 2024-12-15 Drone nonsense Sat 2024-12-14 OK Nintendo 64 Fri 2024-12-13 Unix Windows LocalThunk interview Sora AI gymnastics Thu 2024-12-12 Queer interview Tue 2024-12-10 OldInsuranceMaps.net Sanborn Maps Harmeet Dhillon (CW: anti-trans) Uranian (sexuality) 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