app.py

# Import the required libraries
import click
import mysql.connector
from mysql.connector import Error
import os


def get_database_connection():

    # Connect to the MySQL database using environment variables
    connection = mysql.connector.connect(
        host=os.environ['MYSQL_HOST'],
        database=os.environ['MYSQL_DATABASE'],
        user=os.environ['MYSQL_USER'],
        password=os.environ['MYSQL_PASSWORD']
    )
    return connection


@click.group()
def cli():
    pass


# Define a command-line command and the options a user can pass
@click.command()
@click.option('--title', prompt=True, help='Title of the movie')
@click.option('--genre', prompt=True, help='Genre of the movie')
@click.option('--year', prompt=True, type=int, help='Year of release')

# Define the function that will be called when the command is run
def add_movie(title, genre, year):
    """Add a movie to the database"""
    try:
        # Connect to the MySQL database
        connection = get_database_connection()

        # Create a cursor object to execute SQL queries on the db
        cursor = connection.cursor()

        # Construct the SQL query to insert the new movie into the db
        sql_query = f"INSERT INTO movies (title, genre, year) VALUES ('{title}', '{genre}', {year})"

        # Execute the SQL query using the cursor
        cursor.execute(sql_query)

        # Commit the changes to the database
        connection.commit()

        # Print a message indicating that the movie has been stored
        click.echo(f"Added movie {title} ({genre}, {year}) to the database")

    # Catch any errors that occur while connecting to the database
    except Error as e:
        click.echo(f"Error while connecting to MySQL: {e}")

    # Close the database connection and cursor
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()


# Define a command-line command and the options a user can pass
@click.command()
@click.option('--first_name', prompt=True, help='First name of the reviewer')
@click.option('--last_name', prompt=True, help='Last name of the reviewer')

# Define the function that will be called when the command is executed
def add_reviewer(first_name, last_name):
    """Add a reviewer to the database"""
    try:
        # Connect to the MySQL database
        connection = get_database_connection()

        # Create a cursor object to execute SQL queries on the db
        cursor = connection.cursor()

        # Query to insert the reviewer into the reviewers table
        sql_query = f"INSERT INTO reviewers (first_name, last_name) VALUES ('{first_name}', '{last_name}')"

        # Execute the SQL query using the cursor
        cursor.execute(sql_query)

        # Commit the changes to the database
        connection.commit()

        # Print a message indicating that the reviewer has been added
        click.echo(f"Added reviewer {first_name} ({last_name}) to the database")

    # Catch any error that occurs while connecting to the database
    except Error as e:
        click.echo(f"Error while connecting to MySQL: {e}")

    # Close the database connection and cursor
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()


# Define a CLI command
@click.command()
@click.option('--title', prompt=True, help='Title of the movie')
@click.option('--reviewer', prompt=True, help='Name of the reviewer')
@click.option('--rating', prompt=True, type=float, help='Rating for the movie')
def add_rating(title, reviewer, rating):
    """Add a rating for a movie"""
    try:
        # Connect to the database
        connection = get_database_connection()

        # Create a cursor object to execute SQL queries on the db
        cursor = connection.cursor()

        # Query to insert the ratings into the ratings table
        movie_query = f"SELECT id FROM movies WHERE title='{title}'"

        # Execute the SQL query using the cursor
        cursor.execute(movie_query)

        # Fetch the first row of the result
        movie_id = cursor.fetchone()[0]

        # Query to get the reviewer's ID from the reviewers table
        reviewer_query = f"SELECT id FROM reviewers WHERE first_name='{reviewer}'"

        # Execute the SQL query using the cursor
        cursor.execute(reviewer_query)

        # Fetch the first row of the result
        reviewer_id = cursor.fetchone()[0]

        # Query to insert the rating into the ratings table
        rating_query = f"INSERT INTO ratings (movie_id, reviewer_id, rating) VALUES ({movie_id}, {reviewer_id}, {rating})"

        # Execute the SQL query using the cursor
        cursor.execute(rating_query)

        # Commit the transaction to the database
        connection.commit()

        # Print a success message
        click.echo(f"Added rating {rating} for movie {title} by reviewer {reviewer}")

    except Error as e:
        # Print an error message if something goes wrong
        click.echo(f"Error while connecting to MySQL: {e}")

    finally:
        # Close the cursor and connection objects
        if connection.is_connected():
            cursor.close()
            connection.close()


cli.add_command(add_movie)
cli.add_command(add_reviewer)
cli.add_command(add_rating)


if __name__ == '__main__':
    cli()