>-----Original Message-----
>From: twisted-python-boun...@twistedmatrix.com [mailto:twisted-python-
>boun...@twistedmatrix.com] On Behalf Of Dave Peticolas
>Sent: Wednesday, August 05, 2009 9:56 PM
>To: Twisted general discussion
>Subject: Re: [Twisted-Python] Adbapi issues
>
>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.
Thanks Dave! That explains it...and retrying, and issuing queries
after that work. Great!
<snip>
>
>> 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.
Ok, I filed ticket #3956
...along with a patch for adbapi.py, and test_adbapi.py.
My apologies in advance for the test patch :o)
I haven't written many tests, so I'm not exactly sure the best
(or perhaps even a good way) of testing some things.
>> 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.
...my understanding is that it's relatively easy to kill a thread on a
Unix platform, but not as easy with Windows (which, unfortunately I
use almost exclusively).
...maybe once *most* users are up to at least python version 2.6 (I'm
not),
I wonder if the threadpool in adbapi could be replaced with
functionality
>From the multiprocessing package. I would think it would be easier to
just kill and restart dead connections then.
>> ...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 for your insight Dave.
_______________________________________________
Twisted-Python mailing list
Twisted-Python@twistedmatrix.com
http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
