queries_db_script.py

import mysql.connector
from tabulate import tabulate

# -DATABASE CONFIGURATION
DB_CONFIG = {
    'user': '****',
    'password': '****',
    'host': '****',
    'database': '****',
    'port': 3305,
    'raise_on_warnings': True,
    'use_pure': True,
    'connection_timeout': 10
}

# establish the database connection
def get_connection():
    return mysql.connector.connect(**DB_CONFIG)


# --- Query 1: Full-Text Search on Title
def query_1(keyword):
    """
    Search movies containing a specific keyword in their title
    return their name, release date and meta_score.
    """
    q = f"""
        SELECT
            title, release_date, meta_score 
        FROM
            Movie
        WHERE
            MATCH(title) AGAINST('{keyword}');
        """
    return q


# --- Query 2: Full-Text Search on Staff Name
def query_2(keyword):
    """
    filter movies by searched staff member.
    include the movie title, staff member name and his role in the film.
    results sorted first by actor name and then by movie title.
    """
    q = f"""
        SELECT
            SM.person_name AS actor_name,
            M.title AS movie_title,
            SM.role AS actor_role
        FROM 
            Movie M
        JOIN 
            Staff_Movie SM ON M.movie_id = SM.movie_id
        WHERE
            MATCH(SM.person_name) AGAINST('%{keyword}%')
        ORDER BY 
            SM.person_name, movie_title;
        """
    return q


# --- Query 3: complex (Group By + Aggregation)
def query_3():
    """
    Find the total revenue generated by movies grouped by their release year.
    Sorted by year ascending.
    """
    q = """
        SELECT
            YEAR(release_date) AS release_year,
            SUM(revenue) AS total_revenue
        FROM
            Movie
        WHERE release_date IS NOT NULL
        GROUP BY
            release_year
        ORDER BY
            release_year ASC;
        """
    return q


# --- Query 4: Complex (Nested Query + Group By + Having)
def query_4():
    """
    Find actors whose movies have an average rating higher than the global average,
    displaying their name, average rating (rounded to 2 decimals) and the global average rating.
    """
    q = """
        SELECT 
            SM.person_name AS actor_name,
            ROUND(AVG(M.average_rating), 2) AS actor_avg_rating,
            ROUND((SELECT AVG(average_rating) FROM Movie), 2) AS global_avg_rating
        FROM 
            Staff_Movie SM
        JOIN 
            Movie M ON SM.movie_id = M.movie_id
        WHERE 
            SM.role = 'actor'
        GROUP BY 
            SM.person_name
        HAVING 
            actor_avg_rating > global_avg_rating
        ORDER BY
            actor_avg_rating DESC
        LIMIT
            20;
        """
    return q


# --- Query 5: Complex (Exists + Case + Join)
def query_5():
    """
    Find movies with a budget greater than $100M and produced in multiple languages.
    Including a Boolean column (available_in_Arabic) indicating whether each movie is available in Arabic.
    The results are sorted by budget in descending order.
    """
    q = """
        SELECT
            M.title AS movie_title,
            M.budget,
            COUNT(ML.language_name) AS number_of_languages,
            CASE 
                WHEN EXISTS (SELECT 1 FROM Movie_Language ML2 WHERE ML2.movie_id = M.movie_id AND ML2.language_name = 'Arabic') 
                THEN 'yes' 
                ELSE 'no' 
            END AS available_in_Arabic
        FROM 
            Movie M
        JOIN 
            Movie_Language ML ON M.movie_id = ML.movie_id
        WHERE 
            M.budget > 100000000
        GROUP BY 
            M.movie_id
        HAVING 
            number_of_languages > 1
        ORDER BY 
            M.budget DESC;
        """
    return q


def execute_query(query, prnt=False):
    """
    Executes the input query string.
    If prnt == True: prints the results using tabulate.
    """
    con = get_connection()
    cursor = con.cursor()
    cursor.execute(query)
    results = cursor.fetchall()

    if prnt:
        # Get column names from cursor description
        if cursor.description:
            headers = [x[0] for x in cursor.description]
            print(tabulate(results, headers=headers, tablefmt="grid"))

    cursor.close()
    con.close()
    return results