Python Project – Movie Rating and Analytics System

Program 1

# Project: Movie Ratings and Analytics System

import mysql.connector

# Connect to MySQL
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",   # <-- your MySQL password
    database="movie_rating_system"    # <-- your database name
)
cursor = db.cursor()

# ------------------ Functions ------------------

def insert_movie():
    try:
        movie_id = int(input("Enter Movie ID: "))
        title = input("Enter Movie Title: ")
        release_year = int(input("Enter Release Year: "))
        category_id = int(input("Enter Category ID: "))
        
        query = "INSERT INTO movies (movie_id, title, release_year, category_id) VALUES (%s, %s, %s, %s)"
        cursor.execute(query, (movie_id, title, release_year, category_id))
        db.commit()
        print("Movie inserted successfully!\n")
    except Exception as e:
        print(f" Error: {e}\n")

def insert_actor():
    try:
        actor_id = int(input("Enter Actor ID: "))
        actor_name = input("Enter Actor Name: ")
        
        query = "INSERT INTO actors (actor_id, actor_name) VALUES (%s, %s)"
        cursor.execute(query, (actor_id, actor_name))
        db.commit()
        print("Actor inserted successfully!\n")
    except Exception as e:
        print(f"Error: {e}\n")

def insert_user():
    try:
        user_id = int(input("Enter User ID: "))
        user_name = input("Enter User Name: ")
        
        query = "INSERT INTO users (user_id, user_name) VALUES (%s, %s)"
        cursor.execute(query, (user_id, user_name))
        db.commit()
        print(" User inserted successfully!\n")
    except Exception as e:
        print(f" Error: {e}\n")

def insert_rating():
    try:
        rating_id = int(input("Enter Rating ID: "))
        movie_id = int(input("Enter Movie ID: "))
        user_id = int(input("Enter User ID: "))
        rating = float(input("Enter Rating (out of 10): "))
        rating_date = input("Enter Rating Date (YYYY-MM-DD): ")
        
        query = "INSERT INTO ratings (rating_id, movie_id, user_id, rating, rating_date) VALUES (%s, %s, %s, %s, %s)"
        cursor.execute(query, (rating_id, movie_id, user_id, rating, rating_date))
        db.commit()
        print(" Rating inserted successfully!\n")
    except Exception as e:
        print(f" Error: {e}\n")

def show_movies():
    try:
        cursor.execute("SELECT movie_id, title, release_year FROM movies")
        movies = cursor.fetchall()
        print("\n All Movies:")
        for m in movies:
            print("-------------------------------------------------")
            print(f"ID: {m[0]} | Title: {m[1]} | Year: {m[2]}")
        print()
    except Exception as e:
        print(f"Error: {e}\n")

def show_top_movies():
    try:
        cursor.execute("""
            SELECT m.title, AVG(r.rating) as avg_rating
            FROM movies m
            JOIN ratings r ON m.movie_id = r.movie_id
            GROUP BY m.title
            ORDER BY avg_rating DESC
            LIMIT 5
        """)
        top_movies = cursor.fetchall()
        print("\nTop Rated Movies:")
        for tm in top_movies:
            print("-------------------------------------------------")
            print(f"Movie: {tm[0]} | Average Rating: {tm[1]:.2f}")
        print()
    except Exception as e:
        print(f"Error: {e}\n")

def show_movies_by_category():
    try:
        category_id = int(input("Enter Category ID to Filter Movies: "))
        query = """
            SELECT m.title, c.category_name
            FROM movies m
            JOIN categories c ON m.category_id = c.category_id
            WHERE m.category_id = %s
        """
        cursor.execute(query, (category_id,))
        movies = cursor.fetchall()
        print("\n Movies in Selected Category:")
        for m in movies:
            print(f"Title: {m[0]} | Category: {m[1]}")
        print()
    except Exception as e:
        print(f" Error: {e}\n")

def show_top_actors():
    try:
        cursor.execute("""
            SELECT a.actor_name, AVG(r.rating) as avg_rating
            FROM actors a
            JOIN movie_actor ma ON a.actor_id = ma.actor_id
            JOIN ratings r ON ma.movie_id = r.movie_id
            GROUP BY a.actor_name
            ORDER BY avg_rating DESC
            LIMIT 5
        """)
        actors = cursor.fetchall()
        print("\n Top Actors by Ratings:")
        for a in actors:
            print(f"Actor: {a[0]} | Avg Rating: {a[1]:.2f}")
        print()
    except Exception as e:
        print(f" Error: {e}\n")

# ------------------ Menu ------------------

def menu():
    while True:
        print("\n===  Movie Ratings and Analytics System ===")
        print("1. Insert New Movie")
        print("2. Insert New Actor")
        print("3. Insert New User")
        print("4. Insert New Rating")
        print("5. Show All Movies")
        print("6. Show Top Rated Movies")
        print("7. Show Movies by Category")
        print("8. Show Top Actors by Rating")
        print("9. Exit")
        print("-----------------------------------------------------------")
        choice = input("Enter your choice (1-9): ")
        
        if choice == '1':
            insert_movie()
        elif choice == '2':
            insert_actor()
        elif choice == '3':
            insert_user()
        elif choice == '4':
            insert_rating()
        elif choice == '5':
            show_movies()
        elif choice == '6':
            show_top_movies()
        elif choice == '7':
            show_movies_by_category()
        elif choice == '8':
            show_top_actors()
        elif choice == '9':
            print("Exiting Program. Goodbye!")
            break
        else:
            print(" Invalid choice! Please select again.\n")

# Start the program
menu()