Avoiding Duplicate Table Records in Many-to-Many Relationships 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 like to be able to add a new movie to the movies table, but if some of the contributors to this new movie are already in the actors table, I would like them not to duplicate them in the actors table while adding movie_id and actor_id 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 the class that will handle inserting new records 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()
Since my code is now, the try / except block in the add_new () method prevents duplicate principals from being added to the database, since the "full_name" column in the actor table is unique = True, but this also prevents the entry from being added to the movie_actor association table.
Basically, I would like to know how to add a movie, check if there are actors in the movie in the movie, and if they do, do not insert the actors into the actors table, but take their existing actor_id from the actor table and create the corresponding association in the association table movie_actor.
source to share
You probably need to insert self.session.begin_nested()
into your block try:
. Then, if you need to rollback due to a duplicate key, you can add 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 waiting for repeated key errors.
source to share