title: Pyramid exception logging with SQLAlchemy - commands not committing
body: <p>I am using the Pyramid web framework with SQLAlchemy, connected to a MySQL backend.  The app I've put together works, but I'm trying to add some polish by way of some enhanced logging and exception handling.  </p>
  
  <p>I based everything off of the basic SQLAlchemy tutorial on the Pyramid site, using the session like so:</p>
  
  <pre><code>DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
  </code></pre>
  
  <p>Using DBSession to query works great, and if I need to add and commit something to the database I'll do something like </p>
  
  <pre><code>DBSession.add(myobject)
  DBSession.flush()
  </code></pre>
  
  <p>So I get my new ID.</p>
  
  <p>Then I wanted to add logging to the database, so I followed <a href="http://docs.pylonsproject.org/projects/pyramid_cookbook/en/latest/logging/sqlalchemy_logger.html" rel="nofollow">this tutorial</a>. That seemed to work great.  I did initially run into some weirdness with things getting committed and I wasn't sure how SQLAlchemy was working so I had changed "transaction.commit()" to "DBSession.flush()" to force the logs to commit (this is addressed below!).</p>
  
  <p>Next I wanted to add custom exception handling with the intent that I could put a friendly error page for anything that wasn't explicitly caught and still log things.  So based on <a href="http://docs.pylonsproject.org/projects/pyramid_cookbook/en/latest/pylons/exceptions.html" rel="nofollow">this documentation</a> I created error handlers like so:</p>
  
  <pre><code>from pyramid.view import (
      view_config,
      forbidden_view_config,
      notfound_view_config
      )
  
  from pyramid.httpexceptions import (
      HTTPFound,
      HTTPNotFound,
      HTTPForbidden,
      HTTPBadRequest,
      HTTPInternalServerError
      )
  
  from models import DBSession
  
  import transaction
  import logging
  
  log = logging.getLogger(__name__)
  
  #region Custom HTTP Errors and Exceptions
  @view_config(context=HTTPNotFound, renderer='HTTPNotFound.mako')
  def notfound(request):
      log.exception('404 not found: {0}'.format(str(request.url)))
      request.response.status_int = 404
      return {}
  
  @view_config(context=HTTPInternalServerError, renderer='HTTPInternalServerError.mako')
  def internalerror(request):
      log.exception('HTTPInternalServerError: {0}'.format(str(request.url)))
      request.response.status_int = 500
      return {}
  
  @view_config(context=Exception, renderer="HTTPExceptionCaught.mako")
  def error_view(exc, request):
      log.exception('HTTPException: {0}'.format(str(request.url)))
      log.exception(exc.message)
  
      return {}
  #endregion
  </code></pre>
  
  <p>So now my problem is, exceptions are caught and my custom exception view comes up as expected.  But the exceptions aren't logged to the database.  It appears this is because the DBSession transaction is rolled back on any exception.  So I changed the logging handler back to "transaction.commit".  This had the effect of actually committing my exception logs to the database, BUT now any DBSession action after any log statement throws an "Instance not bound to a session" error...which makes sense because from what I understand after a transaction.commit() the session is cleared out.  The console log always shows exactly what I want logged, including the SQL statements to write the log info to the database.  But it's not committing on exception unless I use transaction.commit(), but if I do that then I kill any DBSession statements after the transaction.commit()!.</p>
  
  <p>Sooooo....how might I set things up so that I can log to the database, but also catch and successfully log exceptions to the database, too?  I feel like I want the logging handler to use some sort of separate database session/connection/instance/something so that it is self-contained but I'm unclear on how that might work.  </p>
  
  <p>Or should I architect what I want to do completely different?</p>
  
  <p>EDIT:
  I did end up going with a separate, log-specific session dedicated only to adding committing log info to the database. This seemed to work well until I started integrating a Pyramid console script into the mix, in which I ran into problems with sessions and database commits within the script not necessarily working like they do in the actual Pyramid web application.</p>
  
  <p>In hindsight (and what I'm doing now) instead of logging to a database I use the standard logging and FileHandlers (TimedRotatingFileHandlers specifically) and log to the file system. </p>