问并非所有参数都在SQL语句中用于Python和MySQL。

EN

Not all parameters were used in the SQL statement

import mysql.connector
from employee import Employee


def add_employee(employee):
    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        password="taker0923",
        database="employee"
    )

    cursor = mydb.cursor(buffered=True)

    try:
        query = "INSERT INTO employee(id, name, email, phoneNumber, salary) " \
                "VALUES(%s, %s, %s, %s, %s)"
        values = (employee.employee_ID, employee.full_name, employee.email, employee.phone_number, employee.salary)

        cursor.execute(query, values)
        mydb.commit()
    except Exception as exception:
        print(exception)
    finally:
        cursor.close()
        mydb.close()


def delete(employee_id):
    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        password="taker0923",
        database="employee"
    )

    cursor = mydb.cursor(buffered=True)

    try:
        query = "DELETE FROM employee " \
                "WHERE id = %s"

        empid = tuple(employee_id)
        cursor.execute(query, empid)
        mydb.commit()
    except Exception as exception:
        print(exception)
    finally:
        cursor.close()
        mydb.close()


def update(empid, column, new_value):
    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        password="taker0923",
        database="employee"
    )

    cursor = mydb.cursor(buffered=True)

    try:
        query = "UPDATE employee " \
                "SET %s = %s " \
                "WHERE id = %s"

        if column == 'name':
            values = (column, new_value, empid)
            cursor.execute(query, values)
        elif column == 'email':
            values = (column, new_value, empid)
            cursor.execute(query, values)
        elif column == 'phoneNumber':
            values = (column, new_value, empid)
            cursor.execute(query, values)
        elif column == 'salary':
            values = (column, new_value, empid)
            cursor.execute(query, values)
        elif column == 'id':
            raise ValueError("Cannot update this column")
        else:
            raise ValueError("ERROR: Could not find matching column")

        mydb.commit()
    except Exception as exception:
        print(exception)
    finally:
        cursor.close()
        mydb.close()


def find(employee_id):
    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        password="taker0923",
        database="employee"
    )

    cursor = mydb.cursor(buffered=True)

    try:
        query = "SELECT %s FROM employee " \
                "WHERE id = %s"

        cursor.execute(query, tuple(employee_id))

        record = cursor.fetchone()
        print(record)

        mydb.commit()
    except Exception as exception:
        print(exception)
    finally:
        cursor.close()
        mydb.close()


def display_all():
    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        password="taker0923",
        database="employee"
    )

    cursor = mydb.cursor(buffered=True)

    try:
        query = 'SELECT * FROM employee'
        cursor.execute(query)

        records = cursor.fetchall()
        print(f'Total number of rows: {cursor.rowcount}')

        for row in records:
            print(f'ID = {row[0]}')
            print(f'Name = {row[1]}')
            print(f'Email: = {row[2]}')
            print(f'Phone Number: = {row[3]}')
            print(f'Salary: = {row[4]}')

    except Exception as exception:
        print(exception)
    finally:
        cursor.close()
        mydb.close()


def main():
    print("1- Add new employee")
    print("2- Delete student by employee ID")
    print("3- Edit employee information")
    print("4- Search employee by ID")
    print("5- Display all employees")

    option = int(input("Enter option: "))

    if option == 1:
        emp_id = input("Enter employee ID: ")
        name = input("Enter employee's name: ")
        email = input("Enter employee's email: ")
        phone_number = input("Enter employee's phone number: ")
        salary = int(input("Enter employee's salary (rounded to nearest whole number): "))

        obj = Employee(emp_id, name, email, phone_number, salary)
        add_employee(obj)

        print(f'You have successfully added {emp_id} - {name} into the system!')

    elif option == 2:
        emp_id = input("Enter employee ID: ")
        delete(emp_id)

        print(f'You have successfully deleted ID  #{emp_id} from the system!')

    elif option == 3:
        print("\n1- Name")
        print("2- Email")
        print("3- Phone Number")
        print("4- Salary")

        choice = int(input("Which column do you want to edit? "))
        empid = input("Enter the employee's ID number: ")
        new_value = input("Enter new value: ")

        if choice == 1:
            update(empid, 'name', new_value)
            print("Successfully updated record")
        elif choice == 2:
            update(empid, 'email', new_value)
            print("Successfully updated record")
        elif choice == 3:
            update(empid, 'phoneNumber', new_value)
            print("Successfully updated record")
        elif choice == 4:
            new_value = int(new_value)
            update(empid, 'salary', new_value)
            print("Successfully updated record")
        else:
            raise ValueError("Invalid selection")

    elif option == 4:
        empid = input("Enter the employee's ID number: ")
        find(empid)

    elif option == 5:
        display_all()

    else:
        raise ValueError("ERROR: Invalid selection")


main()