SQLALchemy "after_insert" doesn't update target object fields

Question

I have a model (see code below) on which I want to execute a function after an object is inserted that will update one of the object's fields. I'm using the after_insert Mapper Event to do this.

I've confirmed that the after_insert properly calls the event_extract_audio_text() handler, and the target is getting updated with the correct audio_text value. However, once the event handler finishes executing, the text value is not set for the object in the database.

Code

# Event handler
def event_extract_audio_text(mapper, connect, target):

    # Extract text from audio file
    audio_text = compute_text_from_audio_file(target.filename)

    # Update the 'text' field with extracted text
    target.audio_text = audio_text

# Model
class SoundsRaw(db.Model):
    __tablename__ = 'soundsraw'

    id = db.Column(db.BigInteger(), primary_key=True, autoincrement=True)
    filename = db.Column(db.String(255))
    audio_text = db.Column(db.Text())

# Event listener
event.listen(SoundsRaw, 'after_insert', event_extract_audio_text)

I've also tried calling db.session.commit() to try to update the object with the text value, but then I get the following stack trace:

File "/Users/alexmarse/.virtualenvs/techmuseum/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 219, in _assert_active
raise sa_exc.ResourceClosedError(closed_msg)

ResourceClosedError: This transaction is closed

Any ideas?

Software versions

SQLAlchemy 0.9.4 Flask 0.10.1 Flask-SQLAlchemy 1.0

Answer 1

The thing with 'after_insert' kind of handlers is to use the connection directly. Here's how I did it:

class Link(db.Model):
    "News link data."

    __tablename__ = 'news_links'

    id = db.Column(db.BigInteger, primary_key=True)
    slug = db.Column(db.String, unique=True) #, nullable=False 

    url = db.Column(db.String, nullable=False, unique=True)
    title = db.Column(db.String)
    image_url = db.Column(db.String)
    description = db.Column(db.String)

@db.event.listens_for(Link, "after_insert")
def after_insert(mapper, connection, target):
    link_table = Link.__table__
    if target.slug is None:
        connection.execute(
            link_table.update().
            where(link_table.c.id==target.id).
            values(slug=slugify(target.id))
        )