Prevent Duplicate Table Entries In A Many-to-many Relationship In Sqlalchemy

I am trying to set up a movie database using SQLAlchemy with a many-to-many relationship. I have two tables, 'movie' and 'actor', and an association table 'movie_actor'. I would li

Solution 1:

You'll likely need to insert a self.session.begin_nested() in your try: block. Then if you need to rollback because of the duplicate key, you can still add the actors to the movie:

from sqlalchemy.exc import IntegrityError  # only catch the right exception!# in for loop:try:
                        session.begin_nested()
                        actor = Actor(actors[i])
                except IntegrityError:
                        print"Duplicate Actor"
                        self.session.rollback() # subtransaction
                        actor = self.session.query(Actor).\
                           filter(Actor.name==actors[i]).first()
                else:
                        self.session.commit()  # subtransaction# add the appropriate association between the movie and the actors to the MovieActor table
                movie.movie_actor.append(MovieActor(actor))
                # add the new actor and movieactor association to the session
                self.session.add(movie)
                self.session.commit()

---

Edit: always except IntegrityError when expecting duplicate key errors.