[sqlalchemy] Re: Testing for validity of a Connection - Mike Orr - com.googlegroups.sqlalchemy

7 messages in com.googlegroups.sqlalchemy[sqlalchemy] Re: Testing for validity...

From	Sent On	Attachments
Moshe C.	30 Aug 2007 06:06
Paul Johnston	30 Aug 2007 07:25
Moshe C.	30 Aug 2007 14:37
Mike Orr	30 Aug 2007 15:40
Michael Bayer	31 Aug 2007 07:28
Mike Orr	31 Aug 2007 08:26
Paul Johnston	31 Aug 2007 13:37

Subject:	[sqlalchemy] Re: Testing for validity of a Connection
From:	Mike Orr (slug...@gmail.com)
Date:	08/30/2007 03:40:49 PM
List:	com.googlegroups.sqlalchemy

On 8/30/07, Moshe C. <mos...@gmail.com> wrote:

I was hoping there was something more elegant than just trying and catching a possible exception. The motivation is just simpler and more readable code like if not connection.is_valid(): get another one

The issue is that in a packet-based network, there's no way to tell if the connection is alive without generating some traffic and seeing if it succeeds. SQLAlchemy could, and perhaps should, encapsulate this in a method but it doesn't. I've also argued that SQLAlchemy should seamlessly retry a query if it finds a connection dead, but MikeB says this is not safe in a transaction.

The SQLAlchemy Way is to set the 'pool_recycle' engine option to a value lower than the database's timeout. MySQL seems to be the main culprit, and it has a default timeout of 8 hours or so, so setting pool_recycle=3600 (one hour) is well within the limit.