React on UniqueKeyViolation in SQLAlchemy

I have 2 Tables in SQLAlchemy (using declerative_base()) that are joined via a seperate Table relationship

# no comment on the wired naming....
site_word = Table('word_site', Base.metadata,
    Column('site_id', Integer, ForeignKey('sites.site_id'), nullable = False, primary_key = True),
    Column('word_id', Integer, ForeignKey('site_words.word_id')开发者_Python百科, nullable = False, primary_key = True))

Word mapping

class Word(Base):
   # snip
   word =  Column('word', Text, nullable = False, unique = True)
   sites = relationship('Site', secondary = site_word, back_populates = 'words')

Site mapping

class Site(Base):
   # snip
   words = relationship('Word', secondary = site_word, back_populates = 'sites')

Inserting works like expected, inserting of already existing words fails of course due to unique in Word.word.

I tried using a before_insert event to check if the item already exists.

event.listen(Word, 'before_insert', some_event)

I can get the information to identify the word uniquly as expected but I don't know how to add a new value to the join table (site_word).

I could write a trigger or procedure for the database but don't want to move too much logic into the database (don't know if this would be possible since the Site wouldn't be known at that time). I could remove the constraint for the word-column but I still can't figure out how to access the information (the other end of the join) to create an entry in the join table, but not in the Word table.

I am looking for a way to create a entry in the site_word and Site table only.

update 1:

I could attach the event to the Site but I don't see a possibility to get the information of the to-be-inserted Site. Is there a possibility to save the Site and create the join-relation afterwards?

It is difficult to see the problem without an example of how you are working with your objects. I take it you are trying to do something like this:

site = Site()
site.words.append(Word('Someword'))
session.add(site)
session.commit()

This will of course raise an IntegrityError if 'someword' already exists. To get around this, you need to first query the database for the word before adding it to the site:

def get_unique_word(session, word):
    try:
        return session.query(Word).filter(Word.word==word).one()
    except sqlalchemy.orm.exc.NoResultFound:
        return Word(word)

site = Site()
site.words.append(get_unique_word(session, 'someword'))
session.add(site)
session.commit()

If this is completely off base, then you'll have to describe your problem in greater detail.

I managed to do it. Mark Gemmill's answer got me thinking in the right direction.

I need to listen to an AttributeEvent instead of an MapperEvent.

event.listen(Site.words, 'append', test_event, retval = True)

Then I can do something like:

def test_event(target, value, initiator):
    try:
        return session.query(Word).filter(Word.word == value.word.lower()).one()
    except sqlalchemy.orm.exc.NoResultFound:
        return value 

This either returns the existing word from the table or simply returns the mapped object that needs to be persisted.