I don't think you have to use 'with' at all because the 'pyramid_tm' tween does it for you. You should have 'pyramid_includes = pyramid_tm' in the INI file, and the DB session should have been created like this: DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Then if your view raises an exception, the tween will automatically roll back the transaction. If there's no exception, it will automatically commit. In special cases when you want to commit or rollback a small chunk of work regardless of whether a later exception occurs, use transaction.commit() or transaction.abort(). Or to sabotage the transaction so that later queries will be rolled back too, use transaction.abort(). So there may be a problem with using 'with transaction.manager' recursively. Or pyramid_tm or the DBSession may not be properly set up. Or something may be catching and ignoring the exceptions (while dumping them to the log), allowing a query to occur on the same transaction/connection where a database error has occurred but not been rolled back. Or somehow your connections are being shared between threads/requests (which would cause these kinds of errors). Which database engine are you using? On Sun, Jan 20, 2013 at 11:56 AM, Maarten De Schrijver <[email protected]> wrote: > Hello, > > I developed a little "prize question game" in Pyramid. I used the url > dispatch/sqlalchemy scaffold to get started. Sqlite is being used for > persistence. > > In my models I defined a "Person" class (for participants) with a unique > constraint on the email attribute/field. After some time when the > application is running (and people are participating), I get these errors > (usually in the same order): > > 1) <class 'sqlalchemy.exc.IntegrityError'>: (IntegrityError) column email is > not unique ... > 2) <class 'transaction.interfaces.TransactionFailedError'>: An operation > previously failed, with traceback: ... > 3) <class 'sqlalchemy.exc.OperationalError'>: (OperationalError) database is > locked u'INSERT INTO persons...' > Finally: > 4) <class 'sqlalchemy.exc.InvalidRequestError'>: This Session's transaction > has been rolled back due to a previous exception during flush. To begin a > new transaction with this Session, first issue Session.rollback(). Original > exception was: (OperationalError) database is locked u'INSERT INTO persons > ...' > > After that I only get the same 'sqlalchemy.exc.InvalidRequestError' over and > over again. At that moment, the application is still running but only shows > an "Internal Server Error" to the user. > > The code responsible for persisting the participant is shown below: > > -------------------------- > # Search person > person = DBSession.query(Person).filter(Person.email == > sess['person']['email']).first() > if person is None: > person = Person() > sess['person']['creation_time'] = datetime.now() > else: > person.modification_time = datetime.now() > for k, v in sess['person'].iteritems(): > setattr(person, k, v) > # Merge and add to db > with transaction.manager: > p = DBSession.merge(person) > DBSession.add(p) > -------------------------- > > As you can see, I always only wrap the write/update database interactions in > "with transaction.manager:". Could that be the problem? Should all database > interaction be wrapped in "with transaction.manager:"? Or am I missing the > point here? > > Also, I have defined my views as methods of a class GameViews(object). Much > of the errors originate from this line of code "game_data = > DBSession.query(Game).filter(Game.name == game).first()" within the > "game_exists" method. > > -------------------------- > class GameViews(object): > """ > GameViews class > """ > def __init__(self, request): > self.request = request > self.game_data = self.get_game_data() > > def get_game_data(self): > # fetch request information > game = self.request.matchdict['game'] > # fetch game information from db > return DBSession.query(Game).filter(Game.name == game).first() > > def game_exists(info, request): > game = request.matchdict['game'] > game_data = DBSession.query(Game).filter(Game.name == game).first() > if game_data: > return True > else: > return False > -------------------------- > > "game_exists" is being used as a custom predicate as such: > > @view_config(route_name='start', renderer='templates/01_start.pt', > custom_predicates=(game_exists)) > > Would this suffice as information for solving this issue? > > Thanks in advance! > Maarten > > -- > You received this message because you are subscribed to the Google Groups > "pylons-discuss" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/pylons-discuss/-/nLZIIOXhFi4J. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/pylons-discuss?hl=en. -- Mike Orr <[email protected]> -- You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en.
