def insert(self):
movie_id = self.check_movie_exists()
if movie_id:
raise ValueError('Movie already exist in database')
director_id = self.check_director_exists()
if not director_id:
raise ValueError('Director do not exist in database')
genres, not_in_db = self.check_genres_exists()
if not_in_db:
raise ValueError(f'genres: {not_in_db} do not exist in database')
# insert query
insert_genres_mapping = """INSERT INTO relation (movie_id, genre_id) VALUES {};"""
insert_movie = """INSERT INTO movies (name, imdb_score, director_id)
VALUES ('{name}', {imdb_score}, {director_id})"""
# insert movie, director id, imdb score
with sqldb() as cursor:
# create new entries
cursor.execute(
insert_movie.format(name=self.movie.movie_name,
imdb_score=self.movie.imdb_score,
director_id=director_id))
# get movie_id
movie_id = self.check_movie_exists()
genres_mapping = ', '.join(
str((movie_id, id_)) for id_ in genres.values())
with sqldb() as cursor:
# create new entries in relation table
cursor.execute(insert_genres_mapping.format(genres_mapping))
def select_by(sub_query):
query = """
select
interested_movies.id,
interested_movies.name,
interested_movies.imdb_score,
interested_movies.director,
GROUP_CONCAT(g.name SEPARATOR ';') as all_genres
from
genres g,
relation r,
(Select m.id, m.name, m.imdb_score, d.name director
from directors d, movies m
where {}
and d.id = m.director_id) as interested_movies
where
r.movie_id = interested_movies.id
and r.genre_id = g.id
group by
r.movie_id;
"""
query = query.format(sub_query)
print(query)
with sqldb() as cursor:
cursor.execute(query)
movies_data = cursor.fetchall()
return movies_data
def insert(self):
insert_director = "INSERT INTO directors (name) VALUES ('{name}')"
director_id = self.check_director_exists()
if director_id:
raise ValueError('Director Already exist in database')
with sqldb() as cursor:
cursor.execute(insert_director.format(name=self.director))
return {'id': self.check_director_exists()}
def check_director_exists(self):
director_exists = "select id from directors where name='{name}';"
with sqldb() as cursor:
cursor.execute(director_exists.format(name=self.director))
id_ = cursor.fetchall()
if id_:
director_id, = id_[0]
return director_id
return None
def check_movie_exists(self):
movie_exists = "select id from movies where name='{name}';"
with sqldb() as cursor:
cursor.execute(movie_exists.format(name=self.movie.movie_name))
id_ = cursor.fetchall()
if id_:
movie_id, = id_[0]
return movie_id
return None
def update(self):
movie_id = self.check_movie_exists()
if not movie_id:
raise ValueError('Movie do not exist in database')
director_id = self.check_director_exists()
if not director_id:
raise ValueError('Director do not exist in database')
genres, not_in_db = self.check_genres_exists()
if not_in_db:
raise ValueError(f'genres: {not_in_db} do not exist in database')
# update query
update_director_id_imdb_score = "UPDATE movies SET director_id = {id_}, " \
"imdb_score={imdb_score} WHERE name = '{movie_name}'"
# delete query
delete_genres_mapping = "DELETE FROM relation WHERE movie_id={id_}"
# insert query
insert_genres_mapping = "INSERT INTO relation (movie_id, genre_id) VALUES {};"
# u cannot update a movie name
# to do that delete the movie and reinsert it
# update director and imdb_score
with sqldb() as cursor:
cursor.execute(
update_director_id_imdb_score.format(
movie_name=self.movie.movie_name,
imdb_score=self.movie.imdb_score,
id_=director_id))
print('Updated director')
# update genres
genres_mapping = ', '.join(
str((movie_id, id_)) for id_ in genres.values())
with sqldb() as cursor:
# delete all entries for movie in relation table
cursor.execute(delete_genres_mapping.format(id_=movie_id))
# create new entries
cursor.execute(insert_genres_mapping.format(genres_mapping))
def check_genres_exists(self):
""" Raise error if genre do not exist in db
This helps in avoiding accidentaly misspelled genres in db
"""
genres_exists = "select id, name from genres where name in ('{names}');"
with sqldb() as cursor:
cursor.execute(
genres_exists.format(names="', '".join(self.movie.genres)))
records = cursor.fetchall()
genres = {}
if records:
# genre: id_
genres = {genre: id_ for id_, genre in records}
not_in_db = set(self.movie.genres) - set(genres.keys())
return genres, not_in_db
def update(self):
update_director = "UPDATE directors SET name = '{new_name}' WHERE name = '{old_name}'"
director_id = self.check_director_exists()
# also check if new_name of director is already occupied b another director
# for now it will fail as name is primary key
# hack for now
if Director(self.new_director).check_director_exists():
raise ValueError(
f'Another Director named {self.new_director} already exist in database'
)
if not director_id:
raise ValueError(f'Director do not exist in database')
with sqldb() as cursor:
cursor.execute(
update_director.format(new_name=self.new_director,
old_name=self.director))
def select_by_genre(self):
genres_query = """select movie_id from relation
where genre_id=(select id from genres where name='{genre}')"""
next_genres_query = """select movie_id from relation
where movie_id in ({movie_ids})
and genre_id=(select id from genres where name='{genre}')"""
with sqldb() as cursor:
cursor.execute(genres_query.format(genre=self.genres[0]))
movie_ids = cursor.fetchall()
for genre_id in self.genres[1:]:
if movie_ids:
movie_ids = ', '.join([str(id_) for (id_,) in movie_ids])
cursor.execute(next_genres_query.format(genre=genre_id, movie_ids=movie_ids))
movie_ids = cursor.fetchall()
movie_ids = ', '.join([str(id_) for (id_,) in movie_ids])
movie_id_query = "m.id in ({ids})".format(ids=movie_ids)
movies_data = self.select_by(movie_id_query)
return movies_data