Skip to main content
 首页 » 编程设计

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

2024年11月01日8zhoujg

我正在尝试使用具有多对多关系的 SQLAlchemy 建立一个电影数据库。我有两个表,“电影”和“ Actor ”,以及一个关联表“movie_actor”。我希望能够将一部新电影添加到电影表中,但是如果这部新电影中的一些 Actor 已经在类型转换中,我想防止在类型转换中重复他们,同时仍然添加 movie_id 和actor_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 block 防止将重复的 Actor 添加到数据库中,因为类型转换中的“全名”列设置为 unique=True,但这也防止一个条目被添加到 movie_actor 关联表中。

基本上我想知道的是如何添加电影,查看电影中的 Actor 是否已经存在于actor表中,如果存在,则不将 Actor 插入到actor表中,而是取他们现有的 actor_id 来自 actor 表,并在 movie_actor 关联表中创建适当的关联。

请您参考如下方法:

您可能需要在 try: block 中插入一个 self.session.begin_nested()。 然后如果因为重复键需要回滚,你仍然可以将 Actor 添加到电影中:

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。