[Twisted-Python] Using different cursor types with psycopg+twisted.enterprise.adbapi?

[Phil Christensen]

On Apr 18, 2008, at 1:09 PM, Nathan wrote:
> In my non-twisted scripts, I usually use psycopg2 DictCursors so that
> my query results come back in dicts instead of lists.
>
> curs = db.cursor(cursor_factory = psycopg2.extras.DictCursor)
>
> Now I'd like to do the same thing in Twisted.  Is there a way to ask
> t.e.adbapi to use that DictCursor cursor factory?
>
> Or maybe there's a way to globally configure psycopg2 so that it used
> psycopg2.extras.DictCursor by default...

I think I ran into this while trying to support multiple DB drivers.

The adbapi.ConnectionPool constructor automatically passes any  
additional arguments or keyword params to the DB-API Connection object  
constructor.

I remember having problems setting a default cursor via Connection()  
params, but I thought it was with the other PG driver, not psycopg2.  
In fact, I almost seem to remember pgdb not even directly supporting  
DictCursors at all, but I could easily be mistaken.

In any event, I ended up having the real drivers return a the row as  
sequence (the normal behavior), and made a trivial ConnectionPool  
subclass that contained the following:

     def _runInteraction(self, interaction, *args, **kw):
         conn = adbapi.Connection(self)
         trans = adbapi.Transaction(self, conn)
         try:
             result = interaction(trans, *args, **kw)
             if(result and isinstance(result[0], (list, tuple))):
                 result = [dict(zip([c[0] for c in  
trans._cursor.description], item)) for item in result]
             trans.close()
             conn.commit()
             return result
         except:
             conn.rollback()
             raise

Hacky, I know, but it did the trick. This way if you have a driver  
that does support DictCursors by passing params to the Connection  
constructor, it will use that functionality instead.

-phil