防止 SQLAlchemy 中多对多关系中的重复表条目 [英] Prevent duplicate table entries in a many-to-many relationship in SQLAlchemy
问题描述
我正在尝试使用具有多对多关系的 SQLAlchemy 设置电影数据库.我有两个表，电影"和演员"，以及一个关联表电影演员".我希望能够将新电影添加到电影表中，但是如果这部新电影中的某些演员已经在演员表中，我想防止在演员表中复制它们，同时仍然添加 movie_id 和actor_id 到关联表.这是我的表设置:
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 like to be able to add a new movie to the movie table, but if some of the actors in this new movie are already in the actor table, I would like to prevent duplicating them in the actor table while still adding the movie_id and actor_id's to the association table. Here is my table setup:
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)
这是一个处理插入新条目和查询数据库的类:
And here is a class which will handle inserting new entries and querying the database:
###########################################################################################
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 块可防止将重复的 actor 添加到数据库中，因为 actor 表中的full_name"列设置为 unique=True，但这也防止了添加到电影演员关联表中的条目.
As my code stands now, the try/except block in the add_new() method prevents duplicate actors from being added to the database since the 'full_name' column in the actor table is set to unique=True, but this also prevents an entry from being added to the movie_actor association table.
基本上我想知道的是如何添加电影，检查电影中的演员是否已经存在于演员表中，如果存在，不要将演员插入演员表中，而是取他们现有的actor_id 从actor 表中创建并在movie_actor 关联表中创建适当的关联.
Basically what I would like to know is how to add a movie, check to see if the actors in the movie already exist in the actor table, and if they do, do not insert the actors into the actor table, but take their existing actor_id from the actor table and create the appropriate association in the movie_actor association table.
推荐答案
您可能需要在 try: 中插入 self.session.begin_nested()块.那么如果因为key重复需要回滚，还是可以把演员加到电影里:
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()
<小时>
总是除了 IntegrityError 预期重复的键错误.
这篇关于防止 SQLAlchemy 中多对多关系中的重复表条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
