INTERFACING PYTHON WITH MYSQL - DELETE TUPLE

# Python program to delete tuples from MySQL database

import mysql.connector

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

def display_employees(cursor):
    """Display all employee tuples"""
    cursor.execute("SELECT * FROM employees")
    records = cursor.fetchall()
    
    if records:
        print("\n--- Employee Records ---")
        print("EmpID\tName\t\tDept\t\tSalary")
        print("-" * 50)
        for record in records:
            print(f"{record[0]}\t{record[1]:<12}\t{record[2]:<12}\t{record[3]}")
    else:
        print("No employee records found.")

def delete_tuples():
    """Main function to delete tuples"""
    connection = connect_database()
    
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    try:
        # Display all tuples before deletion
        print("Employee records before deletion:")
        display_employees(cursor)
        
        # Get deletion criteria
        print("\nDelete employees based on:")
        print("1. Employee ID")
        print("2. Department")
        print("3. Salary range")
        print("4. Name")
        
        choice = input("Enter your choice (1-4): ")
        
        if choice == '1':
            emp_id = input("Enter Employee ID to delete: ")
            query = "DELETE FROM employees WHERE emp_id = %s"
            values = (emp_id,)
            
        elif choice == '2':
            dept = input("Enter Department name: ")
            query = "DELETE FROM employees WHERE department = %s"
            values = (dept,)
            
        elif choice == '3':
            min_salary = input("Enter minimum salary: ")
            max_salary = input("Enter maximum salary: ")
            query = "DELETE FROM employees WHERE salary BETWEEN %s AND %s"
            values = (min_salary, max_salary)
            
        elif choice == '4':
            name = input("Enter employee name: ")
            query = "DELETE FROM employees WHERE name = %s"
            values = (name,)
            
        else:
            print("Invalid choice!")
            return
        
        # Confirm deletion
        confirm = input(f"\nAre you sure you want to delete? (y/n): ")
        if confirm.lower() != 'y':
            print("Deletion cancelled.")
            return
        
        # Execute delete query
        cursor.execute(query, values)
        
        if cursor.rowcount > 0:
            connection.commit()
            print(f"\n{cursor.rowcount} tuple(s) deleted successfully!")
            
            # Display remaining tuples
            print("\nEmployee records after deletion:")
            display_employees(cursor)
        else:
            print("No tuples found matching the criteria.")
            
    except mysql.connector.Error as error:
        print(f"Error deleting tuples: {error}")
        connection.rollback()
    
    finally:
        cursor.close()
        connection.close()
        print("\nDatabase connection closed.")

def create_sample_data():
    """Create sample employee data for demonstration"""
    connection = connect_database()
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    try:
        # Create table if not exists
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS employees (
                emp_id INT PRIMARY KEY,
                name VARCHAR(50),
                department VARCHAR(30),
                salary DECIMAL(10,2)
            )
        """)
        
        # Insert sample data
        sample_data = [
            (101, 'John Smith', 'IT', 75000.00),
            (102, 'Jane Doe', 'HR', 65000.00),
            (103, 'Mike Johnson', 'IT', 80000.00),
            (104, 'Sarah Wilson', 'Finance', 70000.00),
            (105, 'Bob Brown', 'IT', 72000.00)
        ]
        
        cursor.executemany(
            "INSERT IGNORE INTO employees VALUES (%s, %s, %s, %s)",
            sample_data
        )
        
        connection.commit()
        print("Sample employee data created!")
        
    except mysql.connector.Error as error:
        print(f"Error creating sample data: {error}")
    
    finally:
        cursor.close()
        connection.close()

# Main program
if __name__ == "__main__":
    print("=== MySQL Database - Delete Tuples ===")
    
    # Create sample data first
    create_sample_data()
    
    # Perform delete operations
    delete_tuples()