[Michael T. Babcock] Iterable cursor for MySQL/Python


When writing quick and dirty SQL programs, I wanted to be able to iterate through the results from a MySQL connection with Python. I also wanted easily usable results as a dictionary, so I wrote this simple class that in hope that it would be useful for others as well.


import MySQLdb
assert MySQLdb.apilevel == "2.0"

from MySQLdb.cursors import DictCursor
from MySQLdb.cursors import SSDictCursor

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


Simply pass your connection object and query text (optionally with passed arguments, just as you would pass to 'execute' yourself) and then iterate over the object itself.


data = {'city': 'Toronto'}
# Since itercursor is an iterator, this will fetch one result at a time:
for row in itercursor(connection, "SELECT Name, Agent, Area FROM Customer WHERE City = %(city)s", data):
        # Since 'row' is a dictionary, I can do this:
        print "%(Customer)s in %(Area)s is handled by %(Agent)s." % row

Obsolete

I have since been shown how to do this directly using the built-in functionality of the MySQL connector but I'm leaving this here as a quick and dirty Python code reference for those who are interested.

A the very least take away that it is often productive to write a quick class or function to do something that makes your future programming efforts easier. Writing this didn't take very long once I'd thought about it for a minute and it saved me many more minutes of typing and program debugging by reducing the complexity of future programs that used this functionality.

PS, I love learning -- if you have any suggestions or critiques of my style or formatting in this or any other example, please E-mail me.


Stumble it! XFN Friendly Powered by DJBDNS Powered by Zope Valid CSS! Website Security Test

Served by:  Zope 2.7.6

Page Copyright © 2014, 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.me or devnull@mikebabcock.me. This site powered by djbdns