[Twisted-Python] Exception handling in t.e.adbapi
exarkun at divmod.com
Wed Nov 19 19:45:42 EST 2008
On Wed, 19 Nov 2008 16:38:54 -0800, Don Dwiggins <ddwiggins at advpubtech.com> wrote:
>I've been using adbapi (with pyodbc talking to MS SQL Server) successfully
>in a Windows service. I've discovered that occasionally, the network
>between the service and the database machine goes down and comes back up
>(either that or the database server itself cycles down and back up). When
>this happens, any pyodbc connections hanging around are corrupted, so that
>the next query gets an exception (class pyodbc.Error). Currently, the best
>thing I can do is to restart the service.
>I'm trying to rewrite the service to be able to catch, analyze, and respond
>to exceptions from pyodbc. In particular, in the case above, it's possible
>to reconnect and retry the query. If the network connection is still down,
>I'd like to log it, send a message to alert someone, or whatever.
How do you tell the difference between a network error which prevented a
statement from being executed by the SQL server and a network error which
only prevented the response indicating that the statement was successfully
executed from being returned to you? If you can't tell the difference, how
do you ensure that you don't re-execute statements which modify the database
causing corruption of your data?
>I've rewritten a ConnectionPool.runQuery call to use runInteraction to call
>a function (in a thread) that works with the cursor created in
>runInteraction to execute the query. The function wraps the
>cursor.execute(...) in a try-except. I can successfully catch the
>exception, and I try to recover by doing cursor.reconnect(), then
>cursor.reopen(), and finally cursor.execute(...) again. I'm finding that
>this fails with a "wrong connection for thread" exception. I haven't been
>able to figure out why that should happen; more importantly, I'm not sure
>that I'm going about this in the right way. Any insights or pointers to code
>that does this kind of thing would be appreciated.
I've never used pyodbc, but presumably the exception indicates you're
using the objects in a thread where they're not allowed to be used. You
should find out what the threading restrictions of the module are and
then see where you're violating them. One thing to keep in mind is that
ConnectionPool uses a ThreadPool. That means you're never guaranteed that
two different functions will run in the same thread. Whichever thread in
the pool is free will run the next task.
More information about the Twisted-Python