Q.A table, named THEATRE, in CINEMA database, has the following structure : Fields Type Th_ID char(5) Name varchar(15) City varchar(15) Location varchar(15) Seats int Write a function Delete_Theatre(), to input the value of Th_ID from the user and permanently delete the corresponding record from the table. Assume the following for Python-Database connectivity : Host : localhost, User : root, Password : Ex2025

The key idea is to use Python's mysql.connector library to establish a connection with the database and execute an SQL DELETE statement.

Here are the essential steps:

1. Establish a connection to the CINEMA database using the provided credentials.
2. Prompt the user to input the Th_ID of the record to be deleted.
3. Construct an SQL DELETE query targeting the THEATRE table, using a placeholder for the Th_ID.
4. Execute the query with the user-provided Th_ID, then commit the changes to the database and close the connection.

import mysql.connector

def Delete_Theatre():
    try:
        # Establish database connection
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            password="Ex2025",
            database="CINEMA"
        )
        mycursor = mydb.cursor()

        # Input Th_ID from user
        th_id_to_delete = input("Enter Theatre ID (Th_ID) to delete: ")

        # Formulate SQL DELETE query
        sql = "DELETE FROM THEATRE WHERE Th_ID = %s"
        val = (th_id_to_delete,)

        # Execute query and commit changes
        mycursor.execute(sql, val)
        mydb.commit() …

When you need to interact with a database from a Python program, you essentially need a translator. Python speaks Python, and MySQL speaks SQL. A database connector library acts as this translator, allowing your Python code to send SQL commands to the database and receive results. For MySQL, the mysql.connector library is the standard choice.

The core idea here is to:

1. Connect to the MySQL database using the provided credentials.
2. Obtain input from the user for the Th_ID of the record to be deleted.
3. Construct an SQL DELETE statement that specifically targets the record with the given Th_ID. The WHERE clause is critical here, as it prevents accidental deletion of all records.
4. Execute this SQL statement.
5. Commit the transaction to make the deletion permanent. Without committing, the changes would only be temporary and would be rolled back.
6. Close the connection to release database resources.

Let's break down the implementation step-by-step.

1. Import the mysql.connector library

   This is the first step to enable Python to communicate with MySQL. If you haven't installed it, you would typically do so using pip install mysql-connector-python.

   import mysql.connector
   

2. Define the Delete_Theatre() function

   We encapsulate the logic within a function as requested.

   def Delete_Theatre():
       # ... function body ...
   

3. Get Th_ID from the user

   The function needs to know which theatre record to delete. We use input() to prompt the user for the Th_ID. Since Th_ID is char(5), it's a string.

   th_id_to_delete = input("Enter the Theatre ID (Th_ID) of the record to delete: ").strip()
   if not th_id_to_delete:
       print("Theatre ID cannot be empty. Deletion cancelled.")
       return
   

   Tip

   Using .strip() removes any leading or trailing whitespace from the user's input, which can prevent issues if the user accidentally types a space.

4. Establish a database connection

   We use mysql.connector.connect() to establish a connection. The problem specifies the host, user, and password. The database name is implied as CINEMA from the problem description "in CINEMA database". It's good practice to wrap this in a try-except block to handle potential connection errors (e.g., wrong credentials, database server not running).

   mydb = None # Initialize to None
   try:
       mydb = mysql.connector.connect(
           host="localhost",
           user="root",
           password="Ex2025",
           database="CINEMA" # As specified in the problem context
       )
       print("Successfully connected to the CINEMA database.")
   except mysql.connector.Error as err:
       print(f"Error connecting to MySQL database: {err}")
       return # Exit the function if connection fails
   

5. Create a cursor object

   Once connected, you need a cursor object to execute SQL queries. Think of the cursor as the interface through which you send commands to the database.

   mycursor = mydb.cursor()
   

6. Construct the DELETE SQL query

   The SQL DELETE statement is used to remove rows from a table. The WHERE clause is crucial to specify which rows to delete. Without a WHERE clause, all rows would be deleted, which is almost never the intention. We use a parameterized query (%s) for safety and to prevent SQL injection vulnerabilities.

   sql_query = "DELETE FROM THEATRE WHERE Th_ID = %s"
   val = (th_id_to_delete,) # The value must be a tuple, even for a single parameter
   

   Watch out

   Never concatenate user input directly into an SQL query string (e.g., f"DELETE FROM THEATRE WHERE Th_ID = '{th_id_to_delete}'"). This is a major security risk known as SQL injection. Always use parameterized queries where the database connector handles the proper escaping of values.

7. Execute the query and commit changes

   The mycursor.execute() method runs the SQL query. After executing a data modification query (like DELETE, INSERT, UPDATE), you must call mydb.commit() to save the changes permanently to the database. If you don't commit, the changes will be rolled back when the connection is closed.

   try:
       mycursor.execute(sql_query, val)
       mydb.commit() # Make the changes permanent
       print(f"{mycursor.rowcount} record(s) deleted successfully.")
   
       if mycursor.rowcount == 0:
           print(f"No record found with Th_ID: '{th_id_to_delete}'.")
   
   except mysql.connector.Error as err:
       print(f"Error deleting record: {err}")
       mydb.rollback() # Rollback changes if an error occurs
   

   Important

   mydb.commit() is essential for DELETE, INSERT, UPDATE operations. Without it, your changes will not be saved. mycursor.rowcount is a useful attribute that tells you how many rows were affected by the last executed query.

8. Close cursor and connection

   It's good practice to close the cursor and the database connection when you are done with them to free up resources. This should ideally happen in a finally block to ensure they are closed even if errors occur.

   finally: …