Logging Exceptions in Pyramid with SQLAlchemy - Commands Not Committing

I am using Pyramid web framework with SQLAlchemy connected to MySQL server. The app I put together works, but I am trying to add some policy with some advanced logging and exception handling.

I based everything but the main SQLAlchemy tutorial on the Pyramid site using a session like this:

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

      

        
        
        
      

    

Using DBSession for the query works great and if I need to add and copy something to the database I will do something like

DBSession.add(myobject)
DBSession.flush()

      

        
        
        
      

    

So I get my new ID.

Then I wanted to add the log to the database, so I followed this tutorial . It seemed to be great. At the beginning I ran into some weirdness when trying and I wasn't sure how SQLAlchemy was working, so I changed "transaction.commit ()" to "DBSession.flush ()" to force the logs to commit (this is addressed below !).

Next, I wanted to add custom exception handling with intent so that I could put a friendly error page for anything that wasn't explicitly caught and still logs things. Therefore, based on this documentation, I created error handlers as follows:

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

      

        
        
        
      

    

So now my problem is that the exceptions are caught and my custom view of the exception looks as expected. But exceptions are not logged in the database. It looks like it has something to do with the DBSession transaction being rolled back on any exception. So I changed the registration handler to "transaction.commit". This led to actually committing my exception logs to the database, but now any DBSession action after any log statement throws a "Non-session related case" error ... which makes sense because from what I understand after transaction () the session is cleared. The console log always shows what I want to log, including SQL statements to write log information to the database. But it doesn't throw an exception unless I use transaction.commit (), but if I do,then I will kill any DBSession statements after transaction.commit () !.

Sooooooooooooooooooooooooooooooooooooooooooo I feel like I want the logging handler to use some kind of separate session / connection / instance / something in isolation to be self-contained, but I don't understand how that might work.

Or do I need to archive what I want to do in a completely different way?

EDIT: In the end, I did a separate log session dedicated to just adding log information to the database. This seemed to work well until I started integrating the Pyramid script console into the mix, in which I ran into problems with database sessions and commits in the script that don't necessarily work the way they do in a real Pyramid web app.

In retrospect (and what I am doing now), instead of logging into the database, I use standard logging and FileHandlers (specifically TimedRotatingFileHandlers) and log into the file system.