On Apr 18, 2008, at 2:05 PM, Phil Christensen wrote:

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: [snip wrong code] 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.

Ah yes, this is the right one: 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))): colanmes = [c[0] for c in trans._cursor.description] result = [dict(zip(colnames, item)) for item in result] trans.close() conn.commit() return result except: conn.rollback() raise Small change, but I was feeling some....what's the opposite of shame? Pride? No, not that far from shame...less shame? -phil

On Fri, Apr 18, 2008 at 2:12 PM, Vlado Handziski <handzisk@tkn.tu-berlin.de> wrote:

I am using the hack below from Federico Di Gregorio to force a RealDictCursor in my adbapi stores:

#module twistedpg.py #Author: Federico Di Gregorio

from psycopg2 import * from psycopg2 import _psycopg as _2psycopg from psycopg2.extensions import register_type, UNICODE, connection as _2connection from psycopg2.extras import RealDictCursor

del connect def connect(*args, **kwargs): kwargs['connection_factory'] = connection return _2psycopg.connect(*args, **kwargs)

class connection(_2connection): def cursor(self): return _2connection.cursor(self, cursor_factory=RealDictCursor)

register_type(UNICODE)

and then:

self.dbpool = adbapi.ConnectionPool('twistedpg', database=dname, user=duser, password=dpasswd, host=dhost, cp_reconnect=True)

Oh, ok! That makes sense. So since twisted just calls the underlying db module's connect() function, you just (in essence) sub-classed the whole psycopg2 module and overrode connect() to use the DictCursor. I don't understand what the "UNICODE" stuff is about, though. Do you know what that is? ~ Nathan

On Apr 18, 2008, at 2:05 PM, Phil Christensen wrote:

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: [snip wrong code] 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.

Ah yes, this is the right one: 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))): colanmes = [c[0] for c in trans._cursor.description] result = [dict(zip(colnames, item)) for item in result] trans.close() conn.commit() return result except: conn.rollback() raise Small change, but I was feeling some....what's the opposite of shame? Pride? No, not that far from shame...less shame? -phil

On Fri, Apr 18, 2008 at 2:12 PM, Vlado Handziski <handzisk@tkn.tu-berlin.de> wrote:

I am using the hack below from Federico Di Gregorio to force a RealDictCursor in my adbapi stores:

#module twistedpg.py #Author: Federico Di Gregorio

from psycopg2 import * from psycopg2 import _psycopg as _2psycopg from psycopg2.extensions import register_type, UNICODE, connection as _2connection from psycopg2.extras import RealDictCursor

del connect def connect(*args, **kwargs): kwargs['connection_factory'] = connection return _2psycopg.connect(*args, **kwargs)

class connection(_2connection): def cursor(self): return _2connection.cursor(self, cursor_factory=RealDictCursor)

register_type(UNICODE)

and then:

self.dbpool = adbapi.ConnectionPool('twistedpg', database=dname, user=duser, password=dpasswd, host=dhost, cp_reconnect=True)

Oh, ok! That makes sense. So since twisted just calls the underlying db module's connect() function, you just (in essence) sub-classed the whole psycopg2 module and overrode connect() to use the DictCursor. I don't understand what the "UNICODE" stuff is about, though. Do you know what that is? ~ Nathan