### 构建薪资管理系统 #### 设计思路 为了满足需求，应用程序将分为前端界面设计和后端数据库交互两部分。通过Python编写业务逻辑并与MySQL数据库连接来完成数据操作。 #### 数据库表结构设计 首先定义三个主要实体：`employees`（员工）、`departments`（部门）以及`salaries`（工资）。这些表格之间的关系可以通过外键约束建立关联[^1]。 ```sql CREATE TABLE departments ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL UNIQUE ); CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(255), last_name VARCHAR(255), department_id INT, FOREIGN KEY (department_id) REFERENCES departments(id) ); CREATE TABLE salaries ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT, amount DECIMAL(10, 2), pay_date DATE, FOREIGN KEY (employee_id) REFERENCES employees(id) ); ``` #### Python代码实现 ##### 安装必要的包 确保安装了用于与MySQL通信所需的PyMySQL模块： ```bash pip install pymysql ``` ##### 创建类模型 基于面向对象编程原则，在Python中创建相应的类表示上述各个实体及其行为。 ```python import pymysql.cursors class Department: def __init__(self, db_connection, **kwargs): self.db = db_connection self.id = kwargs.get('id') self.name = kwargs.get('name') @classmethod def all(cls, db_connection): with db_connection.cursor() as cursor: sql = "SELECT * FROM `departments`" cursor.execute(sql) result = cursor.fetchall() return [cls(db_connection=db_connection, **row) for row in result] # Other methods... ``` ```python class Employee: def __init__(self, db_connection, **kwargs): self.db = db_connection self.id = kwargs.get('id') self.first_name = kwargs.get('first_name') self.last_name = kwargs.get('last_name') self.department_id = kwargs.get('department_id') @property def full_name(self): return f"{self.first_name} {self.last_name}" @classmethod def find_by_username_password(cls, username, password_hash, db_connection): with db_connection.cursor() as cursor: query = """ SELECT e.* FROM employees AS e JOIN user_credentials uc ON e.id = uc.employee_id WHERE uc.username=%s AND uc.password_hash=%s; """ cursor.execute(query, (username, password_hash)) record = cursor.fetchone() if not record: raise ValueError("Invalid credentials") return cls(db_connection=db_connection, **record) # Additional methods like save(), delete(), etc. ``` ```python class SalaryRecord: def __init__(self, db_connection, **kwargs): self.db = db_connection self.id = kwargs.get('id') self.employee_id = kwargs.get('employee_id') self.amount = kwargs.get('amount') self.pay_date = kwargs.get('pay_date') @classmethod def create(cls, employee_id, amount, pay_date, db_connection): with db_connection.cursor() as cursor: insert_query = """INSERT INTO salaries (`employee_id`, `amount`, `pay_date`) VALUES (%s,%s,%s);""" cursor.execute(insert_query, (employee_id, float(amount), str(pay_date))) db_connection.commit() select_last_inserted_record_query = "SELECT LAST_INSERT_ID();" cursor.execute(select_last_inserted_record_query) inserted_salary_id = cursor.fetchone()[0] return cls( db_connection=db_connection, id=inserted_salary_id, employee_id=employee_id, amount=float(amount), pay_date=str(pay_date) ) # More operations such as update, retrieve by date range, etc. ``` ##### 登录验证函数 此函数接收用户名和密码作为参数，并尝试从数据库中查找匹配记录以确认身份。 ```python def authenticate_user(username, password_hash, connection): try: authenticated_employee = Employee.find_by_username_password( username=username, password_hash=password_hash, db_connection=connection ) print(f"Welcome back, {authenticated_employee.full_name}") main_menu(authenticated_employee) except Exception as error_message: print(error_message) ``` ##### 主菜单显示 一旦成功登录，则展示给定用户的可用选项列表。 ```python def main_menu(employee_instance): while True: choice = input(""" Choose an option below: 1. View my salary history 2. Update personal information 3. Exit program Your selection: """).strip().lower() if choice == 'view my salary history': view_salary_history(employee_instance) elif choice == 'update personal information': update_personal_info(employee_instance) elif choice == 'exit program': break else: print("Please choose a valid command.") ```