MySQL-related code by Michael T. Babcock

Note: unless otherwise specified, all code examples are Copyright © 2004, Michael T. Babcock and usable under the LGPL. If you want less (more) restrictive access, just ask. This is just a formality.

Wanted to access MySQL result-sets as dictionary arrays? I wrote myself something like this:

def GetResultsDict(cursor):
    '''Return an array of dictionaries of results, one dict per row
       Each dictionary is identical and has the MySQL column names
       for keys with their respective values'''
    colnames = [ d[0] for d in self.cursor.description ]
    return [ dict(zip(colnames, result)) for result in self.cursor.fetchall() ]


db = MySQLdb.connect( ... )
cursor = db.cursor()
cursor.execute("SELECT ID FROM table")
for result in GetResultsDict(cursor)
    print "This row's ID is %(ID)d" % result

But now I've bothered reading the MySQLdb sources and discovered that it already supports dictionary result sets, like so:

from MySQLdb import cursors
db = MySQLdb.connect( ... , cursorclass=cursors.DictCursor)
cursor = db.cursor()
cursor.execute("SELECT ID FROM table")
for result in cursor.fetchall():
    print "This row's ID is %(ID)d" % result

Want to iterate through those results nicely? See my quick and dirty itercursor_py source and examples.

class itercursor:
        '''Iterable results from a connection'''

        def __init__(self, connection, query, data = None):
                self._cursor = connection.cursor(SSDictCursor)
                self._cursor.execute(query, data)

        def __iter__(self):
                while True:
                        result = self._cursor.fetchone()
                        if not result: return
                        yield result

Now I just need to figure out how to modify the converters to allow me to do this automatically:

cursor.execute("SELECT ID, UNIX_TIMESTAMP(MTime) as MTime FROM faxrcvd")
for row in cursor.fetchall():
        row['MTime'] = datetime.datetime.fromtimestamp(row['MTime'])
        print "%(ID)d %(MTime)s" % row

Served by:	Zope 2.7.6

Page Copyright © 2011, Michael T. Babcock. All Rights Reserved.

To contact me, send an E-mail to sawyoursite at this domain.

If you'd really like your mail server reported for spam, send me some junk mail to junk-yum@mikebabcock.ca or devnull@mikebabcock.ca. This site powered by djbdns