ALTER TABLE Operations in MySQL

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 create_sample_table(cursor):
    """Create a sample students table"""
    try:
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS students (
                id INT PRIMARY KEY,
                name VARCHAR(50),
                age INT
            )
        """)
        print("Sample table 'students' created successfully")
    except mysql.connector.Error as error:
        print(f"Error creating table: {error}")

def add_column(cursor):
    """Add new column to table"""
    try:
        # Add email column
        cursor.execute("ALTER TABLE students ADD COLUMN email VARCHAR(100)")
        print("Column 'email' added successfully")
        
        # Add phone column with default value
        cursor.execute("ALTER TABLE students ADD COLUMN phone VARCHAR(15) DEFAULT 'Not Provided'")
        print("Column 'phone' added with default value")
        
    except mysql.connector.Error as error:
        print(f"Error adding column: {error}")

def modify_column(cursor):
    """Modify existing column"""
    try:
        # Modify name column size
        cursor.execute("ALTER TABLE students MODIFY COLUMN name VARCHAR(100)")
        print("Column 'name' size modified to VARCHAR(100)")
        
        # Modify age column to add NOT NULL constraint
        cursor.execute("ALTER TABLE students MODIFY COLUMN age INT NOT NULL")
        print("Column 'age' modified to NOT NULL")
        
    except mysql.connector.Error as error:
        print(f"Error modifying column: {error}")

def drop_column(cursor):
    """Drop column from table"""
    try:
        # Drop phone column
        cursor.execute("ALTER TABLE students DROP COLUMN phone")
        print("Column 'phone' dropped successfully")
        
    except mysql.connector.Error as error:
        print(f"Error dropping column: {error}")

def add_constraint(cursor):
    """Add constraints to table"""
    try:
        # Add unique constraint to email
        cursor.execute("ALTER TABLE students ADD CONSTRAINT unique_email UNIQUE (email)")
        print("UNIQUE constraint added to email column")
        
    except mysql.connector.Error as error:
        print(f"Error adding constraint: {error}")

def show_table_structure(cursor):
    """Display table structure"""
    try:
        cursor.execute("DESCRIBE students")
        result = cursor.fetchall()
        
        print("\nTable Structure:")
        print("-" * 60)
        print(f"{'Field':<15} {'Type':<20} {'Null':<5} {'Key':<5} {'Default':<10}")
        print("-" * 60)
        
        for row in result:
            field, type_info, null, key, default, extra = row
            print(f"{field:<15} {type_info:<20} {null:<5} {key:<5} {str(default):<10}")
            
    except mysql.connector.Error as error:
        print(f"Error showing table structure: {error}")

def main():
    """Main function to demonstrate ALTER TABLE operations"""
    connection = connect_database()
    
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    try:
        print("=== ALTER TABLE Operations Demo ===\n")
        
        # Create sample table
        create_sample_table(cursor)
        
        print("\n1. Initial table structure:")
        show_table_structure(cursor)
        
        # Add columns
        print("\n2. Adding columns...")
        add_column(cursor)
        connection.commit()
        show_table_structure(cursor)
        
        # Modify columns
        print("\n3. Modifying columns...")
        modify_column(cursor)
        connection.commit()
        show_table_structure(cursor)
        
        # Add constraints
        print("\n4. Adding constraints...")
        add_constraint(cursor)
        connection.commit()
        show_table_structure(cursor)
        
        # Drop column
        print("\n5. Dropping column...")
        drop_column(cursor)
        connection.commit()
        show_table_structure(cursor)
        
        print("\n=== All ALTER TABLE operations completed successfully ===")
        
    except mysql.connector.Error as error:
        print(f"Database error: {error}")
        connection.rollback()
    
    finally:
        cursor.close()
        connection.close()
        print("\nDatabase connection closed.")

if __name__ == "__main__":
    main()