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)