IntegrityError not caught in SQLAlchemy event listener

Question

I'm building a simple database driven blog with Flask and SQLAlchemy. In the model for the blog postings I define title and slug attributes:

class BlogPost(Model):
    ...
    title = Column(String(80))
    slug = Column(String(80), unique=True)

Later I use an event listener to automatically create and insert a slug from the title:

@event.listens_for(BlogPost.title, 'set')
def autoslug(target, value, oldvalue, initiator):
    target.slug = slugify(value)

As expected, if I try to add a post to the database, and the title of the post evaluates to the same slug as a previous post, then the transaction fails with an IntegrityError. I don't think in practice this will be a problem anyway. But just for giggles I tried something like this:

from sqlalchemy.exc import IntegrityError

@event.listens_for(BlogPost.title, 'set')
def autoslug(target, value, oldvalue, initiator):
    try:
        target.slug = slugify(value)
    except IntegrityError:
        target.slug = slugify(value) + random_string()

random_string could be anything, really, the point is that nothing that I've tried gets executed because the IntegrityError isn't getting caught, and I'm not sure why - attempting to add & commit a post to the database with the same title still raises an IntegrityError and aborts the transaction when I try to commit. I've seen a handful of other posts about it, but the answers are mostly pretty specific to Pyramid, which I'm not using.

Anybody know what I'm missing here?

Tech involved: Python3, Flask, Flask-Sqlalchemy, Sqlalchemy

Ilja Everilä · Accepted Answer · 2016-03-15T08:14:34.723

1

SQLAlchemy will not flush changes to model objects to DB when setting. In order to get the error you have to do something like

from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm.session import object_session

@event.listens_for(BlogPost.title, 'set')
def autoslug(target, value, oldvalue, initiator):
    session = object_session(target)
    try:
        with session.begin_nested():
            target.slug = slugify(value)
            session.flush()
    except IntegrityError:
        target.slug = slugify(value) + random_string()

Note that you have to wrap your possible integrity violation in a nested transaction (a savepoint), or your whole transaction will fail even though you catch the IntegrityError. If your DB doesn't support savepoints or an SQLAlchemy implementation of the idea, you're out of luck.

edited Mar 15 '16 at 08:14

answered Mar 15 '16 at 07:44

Ilja Everilä

50,538
7
126
127

I'm getting "'NoneType' object has no attribute 'flush/begin_nested' errors for the inspect and object_session approaches respectively. I think it may have to do with Flask's app contexts and how Flask-Sqlachemy wraps things like the db session. Nevertheless I hadn't heard of either inspect or object_session before, so I think there's some fruitful research for me to do here, thank you for the answer. – John Still Mar 15 '16 at 08:14
@JohnStill I'm guessing that the `target` (your model object) is not bound to a session and you get `None`. Have you `session.add()`ed it? Though if that is the case, why would the event fire... I'm not quite familiar with the event system in itself, so not 100% sure if ORM events fire even if not bound to a session etc. – Ilja Everilä Mar 15 '16 at 08:16
When testing in the shell the 'NoneType' errors are popping up at object creation time, so I never get an object to add to the session. Would I want to do that in the event listener somewhere, like before the try block? – John Still Mar 15 '16 at 08:26
@JohnStill ah so you're creating the object like `bp = BlogPost(title="my title")` and the event handler will fire during object creation time and there will be no session. You either have to first create the object like `bp = BlogPost()`, `session.add(bp)` and then `bp.title = "mytitle"` or rethink your logic, since automatic slug generation in an event will be infeasible. – Ilja Everilä Mar 15 '16 at 08:28
@JohnStill all in all the problem still boils down to the changes not being flushed to the DB and hence no integrity violation happening before a `COMMIT` is attempted. – Ilja Everilä Mar 15 '16 at 08:38
I'm pretty sure you're right. As for creating the object first, then adding to the session, then trying to set the attributes, I'm now getting an OperationalError: no such savepoint (from the begin_nested call). At any rate I'm going to tackle this again in the morning, but you've been very helpful, thank you again. – John Still Mar 15 '16 at 08:44
Might be this http://stackoverflow.com/q/1654857/2681632 , if you're testing with sqlite. – Ilja Everilä Mar 15 '16 at 08:47

IntegrityError not caught in SQLAlchemy event listener

1 Answers1