INTERFACING PYTHON WITH MYSQL - DELETE RECORDS

# Python program to delete records from MySQL database

import mysql.connector

def connect_database():
    """Establish connection to MySQL database"""
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='school',
            user='root',
            password='password'
        )
        return connection
    except mysql.connector.Error as error:
        print(f"Error connecting to MySQL: {error}")
        return None

def display_records(cursor):
    """Display all records from students table"""
    cursor.execute("SELECT * FROM students")
    records = cursor.fetchall()
    
    if records:
        print("\n--- Student Records ---")
        print("ID\tName\t\tAge\tGrade")
        print("-" * 40)
        for record in records:
            print(f"{record[0]}\t{record[1]:<12}\t{record[2]}\t{record[3]}")
    else:
        print("No records found in the table.")

def delete_records():
    """Main function to delete records"""
    connection = connect_database()
    
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    try:
        # Display records before deletion
        print("Records before deletion:")
        display_records(cursor)
        
        # Get deletion criteria from user
        print("\nDelete records based on:")
        print("1. Student ID")
        print("2. Age")
        print("3. Grade")
        
        choice = input("Enter your choice (1-3): ")
        
        if choice == '1':
            student_id = input("Enter Student ID to delete: ")
            query = "DELETE FROM students WHERE id = %s"
            values = (student_id,)
        elif choice == '2':
            age = input("Enter age (delete all students with this age): ")
            query = "DELETE FROM students WHERE age = %s"
            values = (age,)
        elif choice == '3':
            grade = input("Enter grade (delete all students with this grade): ")
            query = "DELETE FROM students WHERE grade = %s"
            values = (grade,)
        else:
            print("Invalid choice!")
            return
        
        # Execute delete query
        cursor.execute(query, values)
        
        # Check if any records were deleted
        if cursor.rowcount > 0:
            connection.commit()
            print(f"\n{cursor.rowcount} record(s) deleted successfully!")
            
            # Display records after deletion
            print("\nRecords after deletion:")
            display_records(cursor)
        else:
            print("No records found matching the criteria.")
            
    except mysql.connector.Error as error:
        print(f"Error deleting records: {error}")
        connection.rollback()
    
    finally:
        cursor.close()
        connection.close()
        print("\nDatabase connection closed.")

# Main program
if __name__ == "__main__":
    print("=== MySQL Database - Delete Records ===")
    delete_records()