SQLAlchemy: re-saving a unique model field after trying to save a non-unique value

Question

In my SQLAlchemy application, I have the following model:

from sqlalchemy import Column, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import scoped_session, sessionmaker from zope.sqlalchemy import ZopeTransactionExtension DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) class MyModel(declarative_base()): # ... label = Column(String(20), unique=True) def save(self, force=False): DBSession.add(self) if force: DBSession.flush() 

Later in the code for each new MyModel object I want to randomly generate a label and simply restore it if the generated value already exists in the database.
I am trying to do the following:

 # my_model is an object of MyModel while True: my_model.label = generate_label() try: my_model.save(force=True) except IntegrityError: # label is not unique - will do one more iteration # (*) pass else: # my_model saved successfully - exit the loop break 

but get this error if the first generated label not unique, and save() is called at the second (or later) iteration:

  InvalidRequestError: This Session 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: (IntegrityError) column url_label is not unique... 

When I add DBSession.rollback() to position (*), I get the following:

  ResourceClosedError: The transaction is closed 

What should I do to cope with this situation correctly? Thanks

Answer 1

If your session object is rolling back, you must create a new session and update your models before you can start over. And if you use zope.sqlalchemy , you should use transaction.commit() and transaction.abort() to manage things. So your loop will look something like this:

 # you'll also need this import after your zope.sqlalchemy import statement import transaction while True: my_model.label = generate_label() try: transaction.commit() except IntegrityError: # need to use zope.sqlalchemy to clean things up transaction.abort() # recreate the session and re-add your object session = DBSession() session.add(my_model) else: break 

I pulled the use of the session object from the save object method here. I'm not quite sure how ScopedSession updated when used at the class level, as you did. Personally, I believe that embedding SqlAlchemy things inside your models does not work very well with the SqlAlchemy unit of work approach to any things.

If your label object is indeed a generated and unique value, then I agree with TokenMacGuy and just use the uuid value.

Hope this helps.

Answer 2

Databases do not have a consistent way of telling you why a transaction failed, in a form accessible for automation. Usually you cannot try to complete a transaction, and then try again because it failed for some specific reason.

If you know which condition you want to circumvent (as a unique constraint), then you need to check the constraint yourself. In sqlalchemy it will look something like this:

 # Find a unique label label = generate_label() while DBsession.query( sqlalchemy.exists(sqlalchemy.orm.Query(Model) .filter(Model.lable == label) .statement)).scalar(): label = generate_label() # add that label to the model my_model.label = label DBSession.add(my_model) DBSession.flush() 

---

edit: Another way to answer this question is that you should not automatically repeat the transaction; Instead, you can return the HTTP status code 307 Temporary Redirect (with some salt in the redirected URL) so that the transaction actually starts.

Answer 3

I ran into a similar problem in my webapp written as part of Pyramid. I found a slightly different solution for this problem.

 while True: try: my_model.label = generate_label() DBSession.flush() break except IntegrityError: # Rollback will recreate session: DBSession.rollback() # if my_model was in db it must be merged: my_model = DBSession.merge(my_model) 

The merge part is crucial if my_model was saved before. Without a merge session, it will be empty, so flush will not take any action.