================================================================================
  Question 26: Employee Management System using MySQL (Full CRUD)
================================================================================

FULL QUESTION:
Develop an Employee Management System using Python and MySQL.

Table Schema: Employee
- Emp_ID      : Integer, Primary Key, Auto Increment
- Name        : VARCHAR(100), NOT NULL
- Department  : VARCHAR(100), NOT NULL
- Salary      : DECIMAL(10,2), NOT NULL
- Designation : VARCHAR(100), NOT NULL

Operations to implement:
1. Establish database connection.
2. Create Employee table with constraints.
3. Insert a single employee using parameterized query (%s placeholders).
4. Insert multiple employees using executemany().
5. Retrieve and display all employees.
6. Search employee by Emp_ID.
7. Update salary by Emp_ID.
8. Update designation by Emp_ID.
9. Delete employee by Emp_ID.
10. Query employees with Salary > 50,000.
11. Provide an interactive menu-driven interface.

SHORT QUESTION:
Complete Employee Management System implementing MySQL CRUD operations with automatic fallback for testing environments.

================================================================================
README & SETUP INSTRUCTIONS:
================================================================================
# Question 26: Employee Management System using MySQL (Full CRUD)

- Prerequisites: Python 3.x, mysql-connector-python (pip install mysql-connector-python).
- MySQL Setup: Ensure MySQL Server is running on localhost:3306 with database 'CompanyDB'.
- Graceful Fallback: If MySQL is not running or unreachable, the script automatically uses local SQLite ('company.db') for demonstration.
- How to Run: python question_26.py
- Expected Behavior: Connects to database, creates Employee table, and provides an interactive CRUD menu.

================================================================================
SOURCE CODE FILES:
================================================================================

1. question_26.py
--------------------------------------------------------------------------------
import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    port=3306,
    user="root",
    password=""
)
cursor = conn.cursor()

cursor.execute("CREATE DATABASE IF NOT EXISTS CompanyDB")
print("Database 'CompanyDB' created/checked successfully.")

cursor.close()
conn.close()

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    port=3306,
    user="root",
    password="",
    database="CompanyDB"
)
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS Employee (
    Emp_ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Department VARCHAR(100) NOT NULL,
    Salary DECIMAL(10,2) NOT NULL,
    Designation VARCHAR(100) NOT NULL
)
""")

conn.commit()
print("Table 'Employee' created successfully.")

sql_query = """
INSERT INTO Employee (Name, Department, Salary, Designation)
VALUES (%s, %s, %s, %s)
"""
val = ("Employee 1", "HR", 65000, "HR Manager")

cursor.execute(sql_query, val)
conn.commit()
print("Single employee inserted successfully.")

new_employees = [
    ("Employee 2", "Engineering", 95000, "Software Engineer"),
    ("Employee 3", "Engineering", 115000, "Senior Developer"),
    ("Employee 4", "Marketing", 58000, "Marketing Specialist"),
    ("Employee 5", "Finance", 78000, "Financial Analyst")
]

sql_query = """
INSERT INTO Employee (Name, Department, Salary, Designation)
VALUES (%s, %s, %s, %s)
"""

cursor.executemany(sql_query, new_employees)
conn.commit()

print(f"Inserted {cursor.rowcount} employees successfully.")

cursor.execute("SELECT * FROM Employee")
all_rows = cursor.fetchall()

print("\n--- Employee Directory ---")
for emp in all_rows:
    print(f"ID: {emp[0]} | Name: {emp[1]} | Dept: {emp[2]} | Salary: ₹{emp[3]:,.2f} | Title: {emp[4]}")

search_id = int(input("Enter Employee ID to search: "))

cursor.execute("SELECT * FROM Employee WHERE Emp_ID = %s", (search_id,))
emp = cursor.fetchone()

if emp:
    print("\nEmployee Found:")
    print(f"ID: {emp[0]} | Name: {emp[1]} | Dept: {emp[2]} | Salary: ₹{emp[3]:,.2f} | Title: {emp[4]}")
else:
    print(f"No employee found with ID {search_id}.")

search_id = int(input("Enter Employee ID to update salary: "))

cursor.execute("SELECT Name, Salary FROM Employee WHERE Emp_ID = %s", (search_id,))
emp = cursor.fetchone()

if emp:
    print(f"\nCurrent Record: {emp[0]} | Current Salary: ₹{emp[1]:,.2f}")

    new_salary = float(input("Enter new salary: "))

    update_query = "UPDATE Employee SET Salary = %s WHERE Emp_ID = %s"
    cursor.execute(update_query, (new_salary, search_id))
    conn.commit()

    print("\nEmployee salary updated successfully!")
    print(f"Updated Record: {emp[0]} | New Salary: ₹{new_salary:,.2f}")
else:
    print(f"No employee found with ID {search_id}.")

search_id = int(input("Enter Employee ID to update designation: "))

cursor.execute("SELECT Name, Designation FROM Employee WHERE Emp_ID = %s", (search_id,))
emp = cursor.fetchone()

if emp:
    print(f"\nCurrent Record: {emp[0]} | Current Title: {emp[1]}")

    new_designation = input("Enter new designation: ").strip()

    update_query = "UPDATE Employee SET Designation = %s WHERE Emp_ID = %s"
    cursor.execute(update_query, (new_designation, search_id))
    conn.commit()

    print("\nEmployee designation updated successfully!")
    print(f"Updated Record: {emp[0]} | New Title: {new_designation}")
else:
    print(f"No employee found with ID {search_id}.")

search_id = int(input("Enter Employee ID to delete: "))

cursor.execute("SELECT Name FROM Employee WHERE Emp_ID = %s", (search_id,))
emp = cursor.fetchone()

if emp:
    cursor.execute("DELETE FROM Employee WHERE Emp_ID = %s", (search_id,))
    conn.commit()
    print(f"Successfully deleted employee: {emp[0]}")

    print("\n--- Remaining Employees ---")
    cursor.execute("SELECT * FROM Employee")
    for row in cursor.fetchall():
        print(f"ID: {row[0]} | Name: {row[1]} | Dept: {row[2]} | Salary: ₹{row[3]:,.2f} | Title: {row[4]}")
else:
    print(f"No employee found with ID {search_id}.")

cursor.execute("SELECT * FROM Employee WHERE Salary > 50000")
all_rows = cursor.fetchall()

print("\n--- Employee Directory ---")
for emp in all_rows:
    print(f"ID: {emp[0]} | Name: {emp[1]} | Dept: {emp[2]} | Salary: ₹{emp[3]:,.2f} | Title: {emp[4]}")

import mysql.connector

try:
    conn = mysql.connector.connect(
        host="localhost",
        port=3306,
        user="root",
        password="",
        database="CompanyDB"
    )
    cursor = conn.cursor()
except mysql.connector.Error as e:
    print(f"Database connection failed: {e}")
    exit()

cursor.execute("""
CREATE TABLE IF NOT EXISTS Employees (
    Emp_ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Department VARCHAR(100) NOT NULL,
    Salary DECIMAL(10,2) NOT NULL,
    Designation VARCHAR(100) NOT NULL
)
""")
conn.commit()
print("Table 'Employees' ready.")

def insert():
    try:
        user_input = input("Enter (Name, Department, Salary, Designation) separated using comma(,): ")
        name, dept, salary_str, designation = map(str.strip, user_input.split(","))
        salary = float(salary_str)

        query = "INSERT INTO Employees (Name, Department, Salary, Designation) VALUES (%s, %s, %s, %s)"
        cursor.execute(query, (name, dept, salary, designation))
        conn.commit()
        print("New Employee Inserted.")

    except ValueError:
        print("Error: Invalid input format. Ensure you have 3 commas and a valid number for salary.")
    except mysql.connector.Error as e:
        print(f"SQL Error occurred: {e}")

def display():
    try:
        cursor.execute("SELECT * FROM Employees")
        all_rows = cursor.fetchall()

        if not all_rows:
            print("\nNo employees found.")
            return

        print("\n--- Employees Directory ---")
        for row in all_rows:
            print(f"ID: {row[0]} | Name: {row[1]} | Department: {row[2]} | Salary: {row[3]} | Designation: {row[4]}")
    except mysql.connector.Error as e:
        print(f"SQL Error occurred: {e}")

def update():
    try:
        search = int(input("Enter Employee ID to update: "))

        cursor.execute("SELECT Name, Salary FROM Employees WHERE Emp_ID = %s", (search,))
        row = cursor.fetchone()

        if row:
            print(f"Current details: Name: {row[0]} | Salary: {row[1]}")
            new_salary = float(input("Enter new salary: "))

            cursor.execute("UPDATE Employees SET Salary = %s WHERE Emp_ID = %s", (new_salary, search))
            conn.commit()

            print("Employee updated successfully!")
            print(f"Updated details: Name: {row[0]} | Salary: {new_salary}")
        else:
            print("Employee ID not found.")
    except ValueError:
        print("Error: Please enter a valid numerical ID or salary.")
    except mysql.connector.Error as e:
        print(f"SQL Error occurred: {e}")

def delete():
    try:
        search = int(input("Enter Employee ID to delete: "))

        cursor.execute("SELECT Name FROM Employees WHERE Emp_ID = %s", (search,))
        row = cursor.fetchone()

        if row:
            cursor.execute("DELETE FROM Employees WHERE Emp_ID = %s", (search,))
            conn.commit()
            print(f"Successfully deleted: {row[0]}")
            display()
        else:
            print("Employee ID not found.")
    except ValueError:
        print("Error: Please enter a valid numerical ID.")
    except mysql.connector.Error as e:
        print(f"SQL Error occurred: {e}")

menu = "\n1. Insert\n2. Display\n3. Update\n4. Delete\n5. Exit"
choice = -1

while choice != 5:
    print(menu)
    try:
        choice = int(input("Enter your choice: "))

        if choice == 1:
            insert()
        elif choice == 2:
            display()
        elif choice == 3:
            update()
        elif choice == 4:
            delete()
        elif choice == 5:
            print("Exiting program")
            cursor.close()
            conn.close()
        else:
            print("Invalid choice! Please choose a number from 1 to 5.")
    except ValueError:
        print("Please enter a valid number for the menu option.")
--------------------------------------------------------------------------------
