Results as a dictionary

[Daniel]

Hello,

I have an existing application that uses various databases, including
SQLite, MySQL and MSSQL. Many of these offer a way to modify the
result to be indexed by column name instead of/in addition to the
numeric index.

I've found on your wiki (http://code.google.com/p/pyodbc/wiki/
Features) that you allow named access. Here's what I need:

Rather than access the column name 'col01value' as row.col01value, I
would like to access it as row['col01value'].

Please let me know if this is possible and what it would take. In the
case of SQLite and MySQL, there is a connection level setting that
enables this. In the case of MSSQL (pymssql), I modified the library
myself and it will be included in the next release (but that was
native python and I'm not as familiar with CPP).

Thanks,
Daniel

[mkleehammer]

I'd recommend writing a quick wrapper class for Connections and
Cursors. It's only a few lines of code and allows you to hook
anything you need. I use this technique to track SQL statement usage
when looking to optimize something.

(I'm doing this from memory, so you'll need to correct my errors
here...)

First, you need a Connection class that holds the real connection and
forwards all getattr requests to that connection. Override
just .cursor() so you can return a cursor wrapper:

class ConnectionWrapper(object):
def __init__(self, cnxn):
self.cnxn = cnxn

def __getattr__(self, attr):
return getattr(self.cnxn, attr)

def cursor(self):
return CursorWrapper(self.cnxn.cursor())

Now create a Cursor class that overrides the fetch methods. I'll show
one:

class CursorWrapper(object):
def __init__(self, cursor):
self.cursor = cursor

def __getattr__(self, attr):
return getattr(self.cursor, attr)

def fetchone(self):
row = self.cursor.fetchone()
if not row:
return None
return dict((t[0], value) for t, value in zip
(self.cursor.description, row))

This is very easy and provides an easy way to trace SQL, print better
error messages, etc. I think it will work well for you.

PS,

I understand you want to use the dictionary approach to make it match
the others, but I think the ['xxx'] approach is a very bad match for
Python. Literally all non-C Python code is a dynamic lookup in a
dictionary based on what is after the period. For example, "print
sys.argv" creates a dictionary lookup of sys.__dict__['argv']. I
think the extra brackets and quotes are just clutter. The only thing
I can think of is they don't want column names conflicting with their
row methods, to which I say, don't make so many row methods!

I'd seriously consider making the modifications to the *other*
products to make them sane. ;)