在SQLAlchemy中防止多对多关系中的重复表条目

Question

我正在尝试使用SQLAlchemy建立一个电影数据库，这里有一个多对多的关系。我有两个表，一个是'movie'（电影），另一个是'actor'（演员），还有一个关联表'movie_actor'。我希望能够向电影表中添加一部新电影，但如果这部新电影中的某些演员已经在演员表中存在，我希望能避免在演员表中重复添加他们，同时仍然将电影ID和演员ID添加到关联表中。以下是我的表结构：

   from sqlalchemy import Table, Column, Integer, String, ForeignKey, create_engine, and_, or_
   from sqlalchemy.ext.declarative import declarative_base
   from sqlalchemy.orm import backref, mapper, relationship, Session

   Base = declarative_base()

   formats = {'1': 'DVD', '2': 'Blu-ray', '3': 'Digital', '4': 'VHS'}

   ###########################################################################################
   class Movie(Base):
   """Movie Class"""

        __tablename__ = "movie"

        movie_id = Column(Integer, primary_key=True)
        title = Column(String(20), nullable=False, unique=True)
        year = Column(Integer, nullable=False)
        format = Column(String, nullable=False)
        movie_actor = relationship("MovieActor", cascade="all, delete-orphan", backref="movie")

        def __init__(self, title, year, format):
            self.title = title
            self.year = year
            self.format = format

        def __repr__(self):
            return "%s %s" % (self.movie_id, self.title)

    ###########################################################################################
    class Actor(Base):
    """Actor Class"""

        __tablename__ = "actor"

        actor_id = Column(Integer, primary_key=True)
        full_name = Column(String(30), nullable=False, unique=True)

        def __init__(self, full_name):
            self.full_name = full_name

        def __repr__(self):
            return "%s %s" % (self.actor_id, self.full_name)

    ###########################################################################################
    class MovieActor(Base):
    """MovieActor Association Class"""

        __tablename__ = "movieactor"
        movie_id = Column(Integer, ForeignKey('movie.movie_id'), primary_key=True)
        actor_id = Column(Integer, ForeignKey('actor.actor_id'), primary_key=True)

        def __init__(self, actor):
            self.actor = actor
        actor = relationship(Actor, lazy='joined')

        def __repr__(self):
            return "%s, %s" % (self.movie_id, self.actor_id)

接下来是一个处理插入新记录和查询数据库的类：

    ###########################################################################################
    class Database(object):

    # A connection to the movie database is established upon instantiation.
        def __init__(self):
            engine = create_engine('sqlite:///bmdb.db')
            Base.metadata.create_all(engine)
            session = Session(engine)
            self.session = session

    # add_new method takes a dictionary of strings containing all the info for a new movie: "title, year, format, actors"
    # and formats the strings, then adds them to the proper tables in the database

        def add_new(self, new_movie):

            #find out what formats exist
            format = ""
            for i in range(1,5):
                    try:
                            format += new_movie[formats[str(i)]]
                            format += ", "
                    except:
                            pass

            format = format[:-1]
            format = format[:-1]

            # capitalize the first letter of each word in the movie title
            title = " ".join(word[0].upper() + word[1:].lower() for word in new_movie['title'].split())
            try:
                    movie = Movie(title, new_movie['year'], format)
                    # add the new movie to the session
                    self.session.add(movie)
                    # commit the new movie to the database
                    self.session.commit()
            except:
                    print "Duplicate Movie"
                    self.session.rollback()
                    return

            # parse the text in the actors entry
            # take the incoming string of all actors in the movie and split it into a list of individual actors

            actors = new_movie['actors'].split(", ")        
            for i in range(len(actors)):
                    # for each actor in the list, capitalize the first letter in their first and last names
                    actors[i] = " ".join(word[0].upper() + word[1:].lower() for word in actors[i].split())
                    # add each formatted actor name to the Actor table
                    actor = Actor(actors[i])
                    try:
                            # 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()
                    except:
                            print "Duplicate Actor"
                            self.session.rollback()

目前我的代码中，在add_new()方法里的try/except块可以防止重复的演员被添加到数据库，因为演员表中的'full_name'列被设置为唯一（unique=True），但这也导致无法向movie_actor关联表中添加记录。

简单来说，我想知道的是如何添加一部电影，检查这部电影中的演员是否已经存在于演员表中，如果存在，就不把这些演员插入演员表，而是从演员表中获取他们已有的演员ID，并在movie_actor关联表中创建相应的关联。

Answer 1

你可能需要在你的 try: 代码块里插入一个 self.session.begin_nested()。这样，如果因为重复的键值需要回滚操作，你仍然可以把演员添加到电影里。

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()

---

补充：在处理重复键值错误时，记得要捕获 IntegrityError 这个错误。