SQLAlchemy: Re-saving model's unique field after trying to save non-unique value

Question

In my SQLAlchemy app 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 code for every new MyModel objects I want to generate label randomly, and just regenerate it if the generated value is already exist in DB.
I'm 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 in case when first generated label is not unique and save() called on the second (or later) iteration:

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

When I add DBSession.rollback() in the position (*) I get this:

 ResourceClosedError: The transaction is closed

What should I do to handle this situation correctly?
Thanks

Answer 1

If your session object rolls back essentially you have to create a new session and refresh your models before you can start again. And if you are use zope.sqlalchemy you should be using transaction.commit() and transaction.abort() to control things. So your loop would 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've pulled the use of the session object out of the object's save method here. I am not entirely sure how the ScopedSession refreshes itself when being used at the class level as you have done. Personally, I think embedding SqlAlchemy stuff inside your models doesn't really work well with SqlAlchemy's unit of work approach to things any how.

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

Hope that helps.

Answer 2

Databases don't have a consistent way of telling you why a transaction failed, in a form that is accessible to automation. You can't generally try the transaction, and then retry because it failed for some particular reason.

If you know of a condition that you want to work around (like a unique constraint), what you have to do is check the constraint yourself. In sqlalchemy, that's going to 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 is that you shouldn't automatically retry the transaction; You could instead return an HTTP status code of 307 Temporary Redirect (with some salt in the Redirected URL) so that the transaction really is started fresh.