def create_directors_table():
query = f"""CREATE TABLE IF NOT EXISTS {table_name} (
director_id integer PRIMARY KEY,
first_name text NOT NULL,
last_name text NOT NULL
)"""
execute_query(query)
def create_movies_table():
query = f"""CREATE TABLE IF NOT EXISTS {table_name} (
movie_id integer PRIMARY KEY,
title text NOT NULL,
rating decimal NOT NULL,
runtime time NOT NULL,
release_year text NOT NULL
)"""
execute_query(query)
def create_studios_table():
query = f"""CREATE TABLE IF NOT EXISTS {table_name} (
studio_id integer PRIMARY KEY,
movie_id integer,
title text NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(movie_id)
ON UPDATE CASCADE
ON DELETE CASCADE
)"""
execute_query(query)
def create_box_offices_table():
query = f"""CREATE TABLE IF NOT EXISTS {table_name} (
box_office_id integer PRIMARY KEY,
movie_id integer,
opening_weekend decimal NOT NULL,
overall_sales decimal NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(movie_id)
ON UPDATE CASCADE
ON DELETE CASCADE
)"""
execute_query(query)
def create_movies_writers_table():
query = f"""CREATE TABLE IF NOT EXISTS {table_name} (
movies_writers_id integer PRIMARY KEY,
movie_id integer,
writer_id integer,
FOREIGN KEY(movie_id) REFERENCES movies(movie_id),
FOREIGN KEY(writer_id) REFERENCES writers(writer_id)
ON UPDATE CASCADE
ON DELETE CASCADE
)"""
execute_query(query)
def get_all_data():
query = f"""SELECT movies.title, release_year, runtime, actors.last_name,
directors.last_name, writers.last_name,
genres.genre_name, studios.title, box_offices.overall_sales
FROM {movies}
JOIN {movies_actors}
ON movies.movie_id = movies_actors.movie_id
JOIN {actors}
ON movies_actors.actor_id = actors.actor_id
JOIN {movies_writers}
ON movies.movie_id = movies_writers.movie_id
JOIN {writers}
ON movies_writers.writer_id = writers.writer_id
JOIN {movies_directors}
ON movies.movie_id = movies_directors.movie_id
JOIN {directors}
ON movies_directors.director_id = directors.director_id
JOIN {movies_genres}
ON movies.movie_id = movies_genres.movie_id
JOIN {genres}
ON movies_genres.genre_id = genres.genre_id
JOIN {studios}
ON movies.movie_id = studios.studio_id
JOIN {box_offices}
ON movies.movie_id = box_offices.box_office_id
GROUP BY movies.title
"""
return execute_query(query, None, True)
def filter_by_opening_weekend_sales(box_office_id):
query = f"""SELECT title, release_year, runtime, opening_weekend
FROM {movies}
JOIN {box_offices} USING (movie_id)
WHERE box_office_id = ? ODER BY opening_weekend DESC"""
params = (box_office_id, )
return execute_query(query, params, True)
def filter_by_studio(studio_id):
query = f"""SELECT title, release_year, runtime
FROM {movies}
JOIN {studios}
USING (movie_id)
WHERE studio_id = ?"""
params = (studio_id, )
return execute_query(query, params, True)
def filter_by_overall_sales(box_office_id):
query = f"""SELECT title, release_year, runtime, overall_sales
FROM {movies}
JOIN {box_offices}
USING (movie_id)
WHERE box_office_id = ? ORDER BY overall_sales DESC"""
params = (box_office_id, )
return execute_query(query, params, True)
def filter_by_actor(actor_id):
query = f"""SELECT title, release_year, runtime
FROM {movies} JOIN {movies_actors}
USING (movie_id)
WHERE actor_id IN(
SELECT actor_id
FROM {actors}
JOIN {movies_actors}
USING (actor_id)
WHERE actor_id = ?
)"""
params = (actor_id, )
return execute_query(query, params, True)
def filter_by_writer(writer_id):
query = f"""SELECT title, release_year, runtime
FROM {movies}
JOIN {movies_writers}
USING (movie_id)
WHERE writer_id IN(
SELECT writer_id
FROM {writers}
JOIN {movies_writers}
USING (writer_id)
WHERE writer_id = ?
)"""
params = (writer_id, )
return execute_query(query, params, True)
def filter_by_genre(genre_id):
query = f"""SELECT title, release_year, runtime
FROM {movies}
JOIN {movies_genres}
USING (movie_id)
WHERE genre_id IN(
SELECT genre_id
FROM {genres}
JOIN {movies_genres}
USING (genre_id)
WHERE genre_id = ?
)"""
params = (genre_id, )
return execute_query(query, params, True)
def create_director(director):
query = f"INSERT INTO {table_name} VALUES(?, ?, ?)"
params = (director.director_id, director.first_name, director.last_name)
director.director_id = execute_query(query, params, None, True)
def update_last_name(writer_last_name, writer_id):
query = f"UPDATE {table_name} SET last_name = (?) WHERE writer_id = (?)"
params = (writer_last_name, writer_id)
execute_query(query, params)
def delete_writer(writer_id):
query = f"DELETE FROM {table_name} WHERE writer_id = (?)"
params = (writer_id, )
execute_query(query, params)
def create_writer(writer, writer_id=None):
query = f"INSERT INTO {table_name} VALUES(?, ?, ?)"
params = (writer.writer_id, writer.first_name, writer.last_name)
writer.writer_id = execute_query(query, params, None, True)
def read_writer(writer_id):
query = f"SELECT * FROM {table_name} WHERE writer_id = (?)"
params = (writer_id, )
execute_query(query, params, True)
def create_actor(actor):
query = f"INSERT INTO {table_name} VALUES(?, ?, ?)"
params = (actor.actor_id, actor.first_name, actor.last_name)
actor.actor_id = execute_query(query, params, None,
True) # insert and get last inserted id
def update_name(actor_first_name, actor_id):
query = f"UPDATE {table_name} SET first_name = (?) WHERE actor_id = (?)"
params = (actor_first_name, actor_id)
execute_query(query, params)
def read_director(director_id):
query = f"SELECT * FROM {table_name} WHERE director_id = (?)"
params = (director_id, )
execute_query(query, params, True)
def delete_director(director_id):
query = f"DELETE FROM {table_name} WHERE director_id = (?)"
params = (director_id, )
execute_query(query, params)
def update_release_year(movie_id, movie_release_year):
query = "UPDATE {} SET release_year = (?) WHERE movie_id = (?)".format(table_name)
params = (movie_release_year, movie_id)
execute_query(query, params)
def create_movie(movie):
query = f"INSERT INTO {table_name} VALUES(?, ?, ?, ?, ?)"
params = (movie.movie_id, movie.title, movie.rating, movie.runtime, movie.release_year)
movie.movie_id = execute_query(query, params, None, True)
def update_rating(movie_id, movie_rating):
query = f"UPDATE {table_name} SET rating = (?) WHERE movie_id = (?)"
params = (movie_rating, movie_id)
execute_query(query, params)
def update_title(movie_id, movie_title):
query = f"UPDATE {table_name} SET title = (?) WHERE movie_id = (?)"
params = (movie_title, movie_id)
execute_query(query, params)
def read_movie(movie_id):
query = f"SELECT * FROM {table_name} WHERE movie_id = (?)"
params = (movie_id,)
execute_query(query, params, True)
def update_last_name(director_last_name, director_id):
query = f"UPDATE {table_name} SET last_name = (?) WHERE director_id = (?)"
params = (director_last_name, director_id)
execute_query(query, params)
def delete_movie(movie_id):
query = "DELETE FROM {} WHERE movie_id = (?)".format(table_name)
params = (movie_id,)
execute_query(query, params)
def update_runtime(movie_id, movie_runtime):
query = f"UPDATE {table_name} SET runtime = (?) WHERE movie_id = (?)"
params = (movie_runtime, movie_id)
execute_query(query, params)
def create_relationship(movie_id, writer_id):
query = f"""INSERT INTO {table_name} (movie_id, writer_id) VALUES(?, ?)"""
params = (movie_id, writer_id)
execute_query(query, params, None, True)
