INTERFACING PYTHON WITH MYSQL - UPDATE RECORDS

# Python program to update records in 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 update_records():
    """Main function to update records"""
    connection = connect_database()
    
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    try:
        # Display records before update
        print("Records before update:")
        display_records(cursor)
        
        # Get student ID to update
        student_id = input("\nEnter Student ID to update: ")
        
        # Check if student exists
        cursor.execute("SELECT * FROM students WHERE id = %s", (student_id,))
        student = cursor.fetchone()
        
        if not student:
            print("Student with given ID not found!")
            return
        
        print(f"\nCurrent details: ID={student[0]}, Name={student[1]}, Age={student[2]}, Grade={student[3]}")
        
        # Get what to update
        print("\nWhat do you want to update?")
        print("1. Name")
        print("2. Age")
        print("3. Grade")
        print("4. All fields")
        
        choice = input("Enter your choice (1-4): ")
        
        if choice == '1':
            new_name = input("Enter new name: ")
            query = "UPDATE students SET name = %s WHERE id = %s"
            values = (new_name, student_id)
        elif choice == '2':
            new_age = input("Enter new age: ")
            query = "UPDATE students SET age = %s WHERE id = %s"
            values = (new_age, student_id)
        elif choice == '3':
            new_grade = input("Enter new grade: ")
            query = "UPDATE students SET grade = %s WHERE id = %s"
            values = (new_grade, student_id)
        elif choice == '4':
            new_name = input("Enter new name: ")
            new_age = input("Enter new age: ")
            new_grade = input("Enter new grade: ")
            query = "UPDATE students SET name = %s, age = %s, grade = %s WHERE id = %s"
            values = (new_name, new_age, new_grade, student_id)
        else:
            print("Invalid choice!")
            return
        
        # Execute update query
        cursor.execute(query, values)
        
        if cursor.rowcount > 0:
            connection.commit()
            print(f"\nRecord updated successfully!")
            
            # Display records after update
            print("\nRecords after update:")
            display_records(cursor)
        else:
            print("No records were updated.")
            
    except mysql.connector.Error as error:
        print(f"Error updating records: {error}")
        connection.rollback()
    
    finally:
        cursor.close()
        connection.close()
        print("\nDatabase connection closed.")

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