Hi Chris:
I'm a bit hesitant to share what I've done, b/c it's still a work in
progress etc, but here goes:
MySQL MyISAM, wait_timeout=28800
SQLAlchemy 0.5.6, pool_recycle=3600
I've written a few decorators (mostly stolen from SQLAlchemy docs &
examples):
def with_query_write(fn):
def go(self, *args, **kw):
try:
result = fn(self, *args, **kw)
self.session.commit()
return result
except:
self.session.rollback()
raise
return go
def with_query_read(fn):
def go(self, *args, **kw):
try:
return fn(self, *args, **kw)
except:
self.session.rollback()
raise
return go
def with_session_write(fn):
def go(*args, **kw):
try:
result = fn(*args, **kw)
session.commit()
return result
except:
session.rollback()
raise
return go
All session writes go through base_dao.py, and are decorated with
@with_session_write. This way the code isn't littered with session.add(),
session.commit(), session.rollback() etc.
@with_session_write
def save(self, instance):
session.add(instance)
...
We've also extended Query (for reasons other than framing -- code omitted)
and added @with_query_write and @with_query_read decorators. I don't love
how I extended Query, and as of PyCon I know a better way to do this, but I
haven't had a chance to re-implement it.
Anyhoo, we pass the custom query class to the sessionmaker:
session = orm.scoped_session(orm.sessionmaker(query_cls=FooQuery))
The custom query class:
class FooQuery(Query):
def __init__(self, *arg, **kw):
Query.__init__(self, *arg, **kw)
...
@with_query_read
def all(self):
return Query.all(self)
@with_query_write
def delete(self):
return Query.delete(self)
...
Finally, we're using pylons and are removing the contextual session in the
finally clause of the base controller's __call__ method.
class BaseController(WSGIController):
def __call__(self, environ, start_response):
try:
...
finally:
session.remove()
We only ever see 'MySQL server has gone away' on our idle failover app
instances, as the only traffic they get are occasional pings from nagios. I
would have thought a combination of wait_timeout & pool_recycle would
prevent this... but I'm probably missing some piece of the big picture.
OperationalError: (OperationalError) (2006, 'MySQL server has gone
away')
I do wonder how possible it would be wrt the open session in view etc to
implement a reconnect on is_dissconect() in _handle_dbapi_exception()...
I also wonder if there's some app out there using SQLAlchemy with an
exemplary data access layer that we could all learn from. I should try a few
code search engines... I went with DAOs (one per mapped table) which extend
a base DAO that know how to do pagination, saves, deletes, etc. I dunno...
Thanks fro asking Chris. I'm watching the answers to these threads too.
--diana
On Wed, Apr 28, 2010 at 9:37 AM, Chris Withers <ch...@simplistix.co.uk>wrote:
> Hi All,
>
> I'm still trying to get an answer on this...
>
> Am I right in understanding that the basic session lifecycle should be:
>
> try:
> <use session>
> session.commit()
> except:
> log()
> session.rollback()
> finally:
> session.remove()
>
> The structure I've traditionally used with transactions has been:
>
> try:
> <use session>
> except:
> log()
> session.rollback()
> else:
> session.commit()
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
