Trying to catch integrity error with SQLAlchemy

Question

I'm having problems with trying to catch an error. I'm using Pyramid/SQLAlchemy and made a sign up form with email as the primary key. The problem is when a duplicate email is entered it raises a IntegrityError, so I'm trying to catch that error and provide a message but no matter what I do I can't catch it, the error keeps appearing.

try:
    new_user = Users(email, firstname, lastname, password)
    DBSession.add(new_user)
    return HTTPFound(location = request.route_url('new'))
except IntegrityError:
    message1 = "Yikes! Your email already exists in our system. Did you forget your password?"

I get the same message when I tried except exc.SQLAlchemyError (although I want to catch specific errors and not a blanket catch all). I also tried exc.IntegrityError but no luck (although it exists in the API).

Is there something wrong with my Python syntax, or is there something I need to do special in SQLAlchemy to catch it?

---

I don't know how to solve this problem but I have a few ideas of what could be causing the problem. Maybe the try statement isn't failing but succeeding because SQLAlchemy is raising the exception itself and Pyramid is generating the view so the except IntegrityError: never gets activated. Or, more likely, I'm catching this error completely wrong.

Solution 1

In Pyramid, if you've configured your session (which the scaffold does for you automatically) to use the ZopeTransactionExtension, then session is not flushed/committed until after the view has executed. If you want to catch any SQL errors yourself in your view, you need to force a flush to send the SQL to the engine. DBSession.flush() should do it after the add(...).

Update

I'm updating this answer with an example of a savepoint just because there are very few examples around of how to do this with the transaction package.

def create_unique_object(db, max_attempts=3):
    while True:
        sp = transaction.savepoint()
        try:
            obj = MyObject()
            obj.identifier = uuid.uuid4().hex
            db.add(obj)
            db.flush()
        except IntegrityError:
            sp.rollback()
            max_attempts -= 1
            if max_attempts < 1:
                raise
        else:
            return obj

obj = create_unique_object(DBSession)

Note that even this is susceptible to duplicates between transactions if no table-level locking is used, but it at least shows how to use a savepoint.

Solution 2

What you need to do is catch a general exception and output its class; then you can make the exception more specific.

except Exception as ex:
    print ex.__class__

Solution 3

There might be no database operations until DBSession.commit() therefore the IntegrityError is raised later in the stack after the controller code that has try/except has already returned.

Solution 4

This is how I do it.

from contextlib import(
        contextmanager,
        )


@contextmanager
def session_scope():
    """Provide a transactional scope around a series of operations."""
    session = Session()
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()



def create_user(email, firstname, lastname, password):

    new_user = Users(email, firstname, lastname, password)

    try:

        with session_scope() as session:

            session.add(new_user)

    except sqlalchemy.exc.IntegrityError as e:
        pass

http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it

Solution 5

Edit: The edited answer above is a better way of doing this, using rollback.

--

If you want to handle transactions in the middle of a pyramid application or something where an automatic transaction commit is performed at the end of a sequence, there's no magic that needs to happen.

Just remember to start a new transaction if the previous transaction has failed.

Like this:

def my_view(request):
   ... # Do things
   if success:
     try:
       instance = self._instance(**data)
       DBSession.add(instance)
       transaction.commit()
       return {'success': True}
     except IntegrityError as e:  # <--- Oh no! Duplicate unique key
       transaction.abort()
       transaction.begin() # <--- Start new transaction
       return {'success': False}

Notice that calling .commit() on a successful transaction is fine, so it is not necessary to start a new transaction after a successful call.

You only need to abort the transaction and start a new one if the transaction is in a failed state.

(If transaction wasn't such a poc, you could use a savepoint and roll back to the savepoint rather than starting a new transaction; sadly, that is not possible, as attempting a commit invalidates a known previous savepoint. Great stuff huh?) (edit: <--- Turns out I'm wrong about that...)

View more solutions

Comments

[JosefAssad]

Why? He's trying to catch a specific exception, I don't understand why you're recommending catching a general exception?

[Lostsoul]

If I remove DBSession.add(new_user) then it doesn't raise the exception. There is no DBSession.commit(), are you refering to code within sqlaclhemey?

[Lostsoul]

Correct, I want to catch specific exceptions so I can provide users with better context on the errors. Are you suggesting I can all errors or catch all errors then get the specific one from it? Sorry I'm new to catching errors(my code rarely has bugs :-) joking

[jfs]

@Lostsoul: call DBSession.commit() inside try/except after DBSession.add(). I'm referring to the code that glues together pyramid/sqlalchemy. It usually calls .commit() if your controller doesn't raise an exception and .rollback() if it does.

[asthasr]

No, I'm saying he can use this to actually determine the class of the exception -- then catch the correct one.

[Lostsoul]

aahhh that did it! I didn't realize it didn't commit until the view was done. Thanks so much Michael!

[Doug]

This is a daft solution. What, just disable transactions entirely? O_o

[Michael Merickel]

What? This is not disabling transactions. Most of what you do in SQLAlchemy is in memory until you flush the changes (execute the SQL commands on the DB). If these changes fail then the transaction is rolled back, and if they succeed then they might be persisted when the transaction is committed. Normally the rollback/commit is handled by pyramid_tm, but optionally you can use savepoints and handle it locally.

[Michael Merickel]

I think you are very confused about how the ZTE interacts with the transaction package, pyramid_tm, and SQLAlchemy. I don't blame you, it's not very well documented. However, transaction.commit should not be necessary in your code, if you want to roll something back, you can use sp = transaction.savepoint() then if the exception happens on a DBSession.flush() you can do sp.rollback() and continue on without creating a new transaction.

[Michael Merickel]

To be clear about transaction being a "poc", it might just feel like an extra step because in simple cases you're only dealing with one transactional source (your database). It actually synchronizes changes to all transactional sources, like how pyramid_mailer will not send an email unless the transaction was successful, preventing accidental emails from being sent when an exception happens during the request.

[Doug]

You're right, that's a better way of doing it. I'm still puzzled why transaction.commit() is somehow different from DBSession.flush() (the former not working and destroying the transaction state, making rollback fail and the latter somehow working)... but yay. Your example makes much more sense now.

[Michael Merickel]

If you're messing around with the database from the command-line, think of a transaction as executing the BEGIN statement. Then think of a flush() as performing a bunch of SQL commands. Finally, everything in-between is just stuff going on in your head/memory (constructing SQL, pushing bits around, organizing what you're about to do). For example, when you invoke DBSession.add(obj) nothing actually happens to the database, until that operation is flushed. In SQLAlchemy when you execute a commit, a flush happens automatically, but you can flush at any point.

[Grant Humphries]

This solution worked for me. My log was telling me that the error was a cx_Oracle.IntegrityError, but using this approach I found that it was actually a sqlalchemy.exc.IntegrityError that I needed to catch