Gerrat Rickert wrote:
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)?
I didn't look at the code too closely, but here's something to note about the 'reconnect' mode: If the connection fails, the first query on the closed connection will still fail. Adbapi cannot, in general, know whether the query went through and then the connection failed, or not, so to be safe it doesn't retry.
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__ ?
Yes, probably so. Feel submit a ticket, including a patch if you want.
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()
Supporting arraysize in some form is probably a good idea. Another ticket is in order.
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?
Since the query is running in a blocked thread, there's not much you can do, as far as I know.
...is this module widely used in production, or are most people using something like SqlAlchemy (or "rolling their own")?
I use it widely in production with cx_Oracle. I use a subclass so I can set arraysize. I use it to provide connections to other scripts, so when the connections go wonky I close and reopen the pool, or just restart the process.
Thanks, Gerrat
dave