cannot catch SQLAlchemy IntegrityError

Solution 1:

I have the same need in my Flask application, I handle it like below and it works:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import exc

db = SQLAlchemy(Flask(__name__))

try:
     db.session.add(resource)
     return db.session.commit()
except exc.IntegrityError:
     db.session.rollback()

Solution 2:

As soon as the IntegrityError is raised, regardless of whether or not you've caught the error, the session you were working in is invalidated. As the second error message is instructing you, To begin a new transaction with this Session, first issue Session.rollback()., to continue using the session you'll need to issue a session.rollback()

I cannot say for sure, but I am guessing you or your web framework is attempting to continue using the session which raised the IntegrityError in some way. I recommend you issue a session.rollback() either after you catch the exception or in your handle_elegantly function.

If you run the below you'll see what I mean:

from sqlalchemy import types
from sqlalchemy import exc
from sqlalchemy import create_engine
from sqlalchemy.schema import Column
from zope.sqlalchemy import ZopeTransactionExtension
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    name = Column(types.String, primary_key=True)


def handle_elegantly(name):
    session = DBSession()
    session.add(User(name=name))
    session.flush()
    print 'Exception elegantly handled!!\n'


def pretend_view(request):
    """Pretend view in a Pyramid application using pyramid_tm"""
    session = DBSession()
    user = User()
    print '\n-------Here we rollback before continuing -------'
    try:
        session.add(user)
        session.flush()
    except exc.IntegrityError:
        session.rollback()
        handle_elegantly('This will run fine')

    print '\n------- Here we do not, and this will error -------'
    try:
        session.add(user)
        session.flush()
    except exc.IntegrityError:
        handle_elegantly('Exception will be raised')


if __name__ == '__main__':
    engine = create_engine('sqlite://')
    global DBSession
    DBSession = scoped_session(
        sessionmaker(extension=ZopeTransactionExtension()))
    DBSession.configure(bind=engine)
    Base.metadata.bind = engine
    Base.metadata.create_all()
    pretend_view("dummy request")

Solution 3:

You have to add session.rollback() after you catch an error:

try:
    session.flush()
except IntegrityError:
    session.rollback()