Skip to content

Instantly share code, notes, and snippets.

@tonykurya
Created March 20, 2023 14:50
Show Gist options
  • Save tonykurya/92ba205c0059960b21416cc46b9469c7 to your computer and use it in GitHub Desktop.
Save tonykurya/92ba205c0059960b21416cc46b9469c7 to your computer and use it in GitHub Desktop.
# 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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment