[Twisted-Python] Adbapi issues

Gerrat Rickert grickert at coldstorage.com
Wed Aug 5 16:35:36 MDT 2009


I'd like to use twisted's adbapi module (twisted 8.2.0 for python 2.5)
with cx_Oracle, but I'm having some issues with it.  
Specifically:

1.  It doesn't seem to reconnect (or possibly I just need enlightenment
on how reconnecting works):
If I tweak part of the test_adbapi.py script to work for Oracle (using a
proper "conn_str", and ignoring the irrelevant parts), I get something
like:

class OracleTests(unittest.TestCase):
    """Test adbapi for Oracle"""
  
    timeout = 10
  
    def setUp(self):
        self.dbpool = adbapi.ConnectionPool('cx_Oracle', conn_str, 
               cp_noisy=True, cp_min=1, cp_reconnect=True, cp_max=3,
cp_good_sql='select * from dual', threaded=True)
  
  
    def test_reconnect(self):
        d = defer.succeed(None)
        d.addCallback(self._testPool_1)
        d.addCallback(self._testPool_2)
        d.addCallback(self._testPool_3)
        return d
    
    def _testPool_1(self, res):
        def _success(rslt):
            self.failUnless(rslt[0][0] == 'X', "Select from dual not
working")
    
        d = self.dbpool.runQuery("select * from dual")
        d.addCallback(_success)
        return d
  
    def _testPool_2(self, res):
        self.dbpool.connections.values()[0].close()
  
    def _testPool_3(self, res):
    
        sql = "select * from dual"
        d = self.dbpool.runQuery(sql)
        def _check(row):
            self.failUnless(row[0][0] == 'X', " Select from dual not
working ")
        d.addCallback(_check)
        return d

I get this traceback:

[ERROR]: test_oracle.OracleTests.test_reconnect
Traceback (most recent call last):
  File "C:\Python25\Lib\site-packages\twisted\python\threadpool.py",
line 210, i
n _worker
    result = context.call(ctx, function, *args, **kwargs)
  File "C:\Python25\Lib\site-packages\twisted\python\context.py", line
59, in ca
llWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "C:\Python25\Lib\site-packages\twisted\python\context.py", line
37, in ca
llWithContext
    return func(*args,**kw)
  File "c:\python25\lib\site-packages\twisted\enterprise\adbapi.py",
line 429, i
n _runInteraction
    result = interaction(trans, *args, **kw)
  File "c:\python25\lib\site-packages\twisted\enterprise\adbapi.py",
line 443, i
n _runQuery
    trans.execute(*args, **kw)
cx_Oracle.InterfaceError: not connected

...am I doing something wrong, or is this something specific to Oracle?
I apologize for the seemingly rhetorical question, but does this
actually work for other databases (I don't have any others installed, so
trial just skips most of the tests when I run test_adbapi.py)?


2.  ...since I can't get it to reconnect properly, perhaps I could just
close the connections in the old connection pool, and create a new
one...

dbpool = adbapi.ConnectionPool('cx_Oracle', conn_str, 
               cp_noisy=True, cp_min=1, cp_reconnect=True, cp_max=3,
cp_good_sql='select * from dual', threaded=True)
dbpool.close()

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "c:\python25\lib\site-packages\twisted\enterprise\adbapi.py",
line 359, i
n close
    if self.shutdownID:
AttributeError: ConnectionPool instance has no attribute 'shutdownID'

...shouldn't shutdownID be initialized in the class, or at least in
__init__ ?

3.  ...as stated in PEP 249, under fetchall(), "... Note that the
cursor's arraysize attribute can affect the performance of this
operation."  In the PEP, cursor.arraysize defaults to 1, which results
in absolutely terrible performance when retrieving a large number of
rows from the database (empirically tested).  Since twisted has already
gone through the trouble of wrapping simple calls to the database (like
it's runQuery), it would be ideal if this was an optional parameter that
could be passed in.  It doesn't look possible to do this in the current
adbapi module (or perhaps, I might just need enlightening again):

    def _runQuery(self, trans, *args, **kw):
        trans.execute(*args, **kw)
        return trans.fetchall() 

...perhaps it could be something more like:

    def _runQuery(self, trans, *args, **kw):
        if kw.has_key('arraysize'):
            trans._cursor.arraysize = kw['arraysize']
        trans.execute(*args, **kw)
        return trans.fetchall()


4.  Timeouts.  ...well, since Deferred.setTimeout is deprecated, and we
can't cancel deferreds, most protocols (or asynchronous "mechanisms" if
protocol isn't the right term here) should probably have a timeout
mechanism.  (...or is everyone looking at me like I'm from outer-space?)
Is there a canonical way of timing out a connection?  If I were to do a
dbpool.runQuery("select some_cols from some_table"); and attach a
timeout mechanism to the deferred that it returns, is there a way to
drop/recycle that particular connection from the pool if it didn't
respond in a timely fashion? ...or (assuming the dbpool.close() issue
went away), would "best-practice" be just closing the old pool, and
re-creating it?  


...is this module widely used in production, or are most people using
something like SqlAlchemy (or "rolling their own")?


Thanks,
	Gerrat






More information about the Twisted-Python mailing list