为了在现有的学生管理系统中增加**教师管理**、**班级管理**以及**角色和权限管理**，我们需要对数据库进行扩展，并相应地更新 Python 代码和用户界面。以下是详细的步骤和代码示例。
## 1. 数据库扩展
### 1.1 创建新表
#### 教师表 (`teachers`)
```sql
CREATE TABLE teachers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT,
gender VARCHAR(10),
email VARCHAR(100),
phone VARCHAR(20)
);
```
#### 班级表 (`classes`)
```sql
CREATE TABLE classes (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
teacher_id INT,
FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL
);
```
#### 学生表更新 (`students`)
为了将学生与班级关联，需要在 `students` 表中添加 `class_id` 字段。
```sql
ALTER TABLE students
ADD COLUMN class_id INT,
ADD FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL;
```
#### 角色表 (`roles`)
```sql
CREATE TABLE roles (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
);
```
#### 权限表 (`permissions`)
```sql
CREATE TABLE permissions (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
description VARCHAR(255)
);
```
#### 角色权限关联表 (`role_permissions`)
```sql
CREATE TABLE role_permissions (
role_id INT,
permission_id INT,
PRIMARY KEY (role_id, permission_id),
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);
```
#### 用户表 (`users`)
假设系统有用户登录功能，可以创建 `users` 表。
```sql
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
role_id INT,
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL
);
```
### 1.2 插入初始数据
```sql
-- 插入角色
INSERT INTO roles (name) VALUES ('管理员'), ('教师'), ('学生');
-- 插入权限
INSERT INTO permissions (name, description) VALUES
('add_student', '添加学生'),
('edit_student', '编辑学生'),
('delete_student', '删除学生'),
('view_student', '查看学生'),
('add_teacher', '添加教师'),
('edit_teacher', '编辑教师'),
('delete_teacher', '删除教师'),
('view_teacher', '查看教师'),
('add_class', '添加班级'),
('edit_class', '编辑班级'),
('delete_class', '删除班级'),
('view_class', '查看班级');
-- 关联角色与权限
INSERT INTO role_permissions (role_id, permission_id) VALUES
(1, 1), (1, 2), (1, 3), (1, 4),
(1, 5), (1, 6), (1, 7), (1, 8),
(1, 9), (1, 10), (1, 11), (1, 12),
(2, 1), (2, 4), (2, 5), (2, 8),
(3, 4);
```
## 2. 更新 Python 代码
### 2.1 数据库连接与基础模型
为了更好地管理数据库操作，建议使用 ORM（如 SQLAlchemy）或继续使用 PyMySQL。以下示例继续使用 PyMySQL，但建议考虑使用 ORM 以提高代码的可维护性。
### 2.2 添加教师管理功能
#### 教师管理界面
```python
def __init__(self):
# ... [初始化代码保持不变]
self.add_teacher_button = QtWidgets.QPushButton("添加教师")
self.edit_teacher_button = QtWidgets.QPushButton("编辑教师")
self.delete_teacher_button = QtWidgets.QPushButton("删除教师")
self.view_teacher_button = QtWidgets.QPushButton("查看教师")
self.button_layout.addWidget(self.add_teacher_button)
self.button_layout.addWidget(self.edit_teacher_button)
self.button_layout.addWidget(self.delete_teacher_button)
self.button_layout.addWidget(self.view_teacher_button)
# 连接教师按钮信号
self.add_teacher_button.clicked.connect(self.add_teacher)
self.edit_teacher_button.clicked.connect(self.edit_teacher)
self.delete_teacher_button.clicked.connect(self.delete_teacher)
self.view_teacher_button.clicked.connect(self.view_teachers)
# 教师表格
self.teacher_table = QtWidgets.QTableWidget()
self.teacher_table.setColumnCount(6)
self.teacher_table.setHorizontalHeaderLabels(['ID', '姓名', '年龄', '性别', '邮箱', '电话'])
self.layout.addWidget(self.teacher_table)
```
#### 添加教师
```python
def add_teacher(self):
dialog = QtWidgets.QDialog(self)
dialog.setWindowTitle("添加教师")
dialog_layout = QtWidgets.QVBoxLayout()
form_layout = QtWidgets.QFormLayout()
name_input = QtWidgets.QLineEdit()
age_input = QtWidgets.QLineEdit()
gender_input = QtWidgets.QLineEdit()
email_input = QtWidgets.QLineEdit()
phone_input = QtWidgets.QLineEdit()
form_layout.addRow("姓名", name_input)
form_layout.addRow("年龄", age_input)
form_layout.addRow("性别", gender_input)
form_layout.addRow("邮箱", email_input)
form_layout.addRow("电话", phone_input)
dialog_layout.addLayout(form_layout)
button_layout = QtWidgets.QHBoxLayout()
ok_button = QtWidgets.QPushButton("确定")
cancel_button = QtWidgets.QPushButton("取消")
button_layout.addWidget(ok_button)
button_layout.addWidget(cancel_button)
dialog_layout.addLayout(button_layout)
dialog.setLayout(dialog_layout)
def on_ok():
name = name_input.text()
age = age_input.text()
gender = gender_input.text()
email = email_input.text()
phone = phone_input.text()
if not name:
QtWidgets.QMessageBox.warning(self, "输入错误", "姓名不能为空")
return
try:
age = int(age)
except ValueError:
QtWidgets.QMessageBox.warning(self, "输入错误", "年龄必须是数字")
return
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "INSERT INTO teachers (name, age, gender, email, phone) VALUES (%s, %s, %s, %s, %s)"
cursor.execute(sql, (name, age, gender, email, phone))
connection.commit()
except pymysql.MySQLError as e:
print(f"插入错误: {e}")
finally:
connection.close()
self.view_teachers()
dialog.close()
def on_cancel():
dialog.close()
ok_button.clicked.connect(on_ok)
cancel_button.clicked.connect(on_cancel)
dialog.exec_()
```
#### 编辑教师
```python
def edit_teacher(self):
selected_items = self.teacher_table.selectedItems()
if not selected_items:
QtWidgets.QMessageBox.warning(self, "选择错误", "请选择要编辑的教师")
return
row = selected_items[0].row()
teacher_id = self.teacher_table.item(row, 0).text()
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM teachers WHERE id = %s"
cursor.execute(sql, (teacher_id,))
result = cursor.fetchone()
connection.close()
except pymysql.MySQLError as e:
print(f"查询错误: {e}")
connection.close()
return
dialog = QtWidgets.QDialog(self)
dialog.setWindowTitle("编辑教师")
dialog_layout = QtWidgets.QVBoxLayout()
form_layout = QtWidgets.QFormLayout()
name_input = QtWidgets.QLineEdit(result[1])
age_input = QtWidgets.QLineEdit(str(result[2]))
gender_input = QtWidgets.QLineEdit(result[3])
email_input = QtWidgets.QLineEdit(result[4])
phone_input = QtWidgets.QLineEdit(result[5])
form_layout.addRow("姓名", name_input)
form_layout.addRow("年龄", age_input)
form_layout.addRow("性别", gender_input)
form_layout.addRow("邮箱", email_input)
form_layout.addRow("电话", phone_input)
dialog_layout.addLayout(form_layout)
button_layout = QtWidgets.QHBoxLayout()
ok_button = QtWidgets.QPushButton("确定")
cancel_button = QtWidgets.QPushButton("取消")
button_layout.addWidget(ok_button)
button_layout.addWidget(cancel_button)
dialog_layout.addLayout(button_layout)
dialog.setLayout(dialog_layout)
def on_ok():
name = name_input.text()
age = age_input.text()
gender = gender_input.text()
email = email_input.text()
phone = phone_input.text()
if not name:
QtWidgets.QMessageBox.warning(self, "输入错误", "姓名不能为空")
return
try:
age = int(age)
except ValueError:
QtWidgets.QMessageBox.warning(self, "输入错误", "年龄必须是数字")
return
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "UPDATE teachers SET name = %s, age = %s, gender = %s, email = %s, phone = %s WHERE id = %s"
cursor.execute(sql, (name, age, gender, email, phone, teacher_id))
connection.commit()
except pymysql.MySQLError as e:
print(f"更新错误: {e}")
finally:
connection.close()
self.view_teachers()
dialog.close()
def on_cancel():
dialog.close()
ok_button.clicked.connect(on_ok)
cancel_button.clicked.connect(on_cancel)
dialog.exec_()
```
#### 删除教师
```python
def delete_teacher(self):
selected_items = self.teacher_table.selectedItems()
if not selected_items:
QtWidgets.QMessageBox.warning(self, "选择错误", "请选择要删除的教师")
return
row = selected_items[0].row()
teacher_id = self.teacher_table.item(row, 0).text()
reply = QtWidgets.QMessageBox.question(self, "确认删除", f"确定要删除教师 ID {teacher_id} 吗？", QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No)
if reply == QtWidgets.QMessageBox.Yes:
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "DELETE FROM teachers WHERE id = %s"
cursor.execute(sql, (teacher_id,))
connection.commit()
except pymysql.MySQLError as e:
print(f"删除错误: {e}")
finally:
connection.close()
self.view_teachers()
```
#### 查看教师
```python
def view_teachers(self):
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM teachers"
cursor.execute(sql)
result = cursor.fetchall()
self.teacher_table.setRowCount(0)
for row_number, row_data in enumerate(result):
self.teacher_table.insertRow(row_number)
for column_number, data in enumerate(row_data):
self.teacher_table.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(str(data)))
except pymysql.MySQLError as e:
print(f"查询错误: {e}")
finally:
connection.close()
```
### 2.3 添加班级管理功能
#### 班级管理界面
```python
def __init__(self):
# ... [初始化代码保持不变]
self.add_class_button = QtWidgets.QPushButton("添加班级")
self.edit_class_button = QtWidgets.QPushButton("编辑班级")
self.delete_class_button = QtWidgets.QPushButton("删除班级")
self.view_class_button = QtWidgets.QPushButton("查看班级")
self.button_layout.addWidget(self.add_class_button)
self.button_layout.addWidget(self.edit_class_button)
self.button_layout.addWidget(self.delete_class_button)
self.button_layout.addWidget(self.view_class_button)
# 连接班级按钮信号
self.add_class_button.clicked.connect(self.add_class)
self.edit_class_button.clged.connect(self.edit_class)
self.delete_class_button.clicked.connect(self.delete_class)
self.view_class_button.clicked.connect(self.view_classes)
# 班级表格
self.class_table = QtWidgets.QTableWidget()
self.class_table.setColumnCount(3)
self.class_table.setHorizontalHeaderLabels(['ID', '班级名称', '教师'])
self.layout.addWidget(self.class_table)
```
#### 添加班级
```python
def add_class(self):
dialog = QtWidgets.QDialog(self)
dialog.setWindowTitle("添加班级")
dialog_layout = QtWidgets.QVBoxLayout()
form_layout = QtWidgets.QFormLayout()
name_input = QtWidgets.QLineEdit()
teacher_input = QtWidgets.QComboBox()
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT id, name FROM teachers"
cursor.execute(sql)
teachers = cursor.fetchall()
for teacher in teachers:
teacher_input.addItem(f"{teacher[1]} (ID: {teacher[0]})", teacher[0])
except pymysql.MySQLError as e:
print(f"查询错误: {e}")
finally:
connection.close()
form_layout.addRow("班级名称", name_input)
form_layout.addRow("教师", teacher_input)
dialog_layout.addLayout(form_layout)
button_layout = QtWidgets.QHBoxLayout()
ok_button = QtWidgets.QPushButton("确定")
cancel_button = QtWidgets.QPushButton("取消")
button_layout.addWidget(ok_button)
button_layout.addWidget(cancel_button)
dialog_layout.addLayout(button_layout)
dialog.setLayout(dialog_layout)
def on_ok():
name = name_input.text()
teacher_id = teacher_input.currentData()
if not name:
QtWidgets.QMessageBox.warning(self, "输入错误", "班级名称不能为空")
return
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "INSERT INTO classes (name, teacher_id) VALUES (%s, %s)"
cursor.execute(sql, (name, teacher_id))
connection.commit()
except pymysql.MySQLError as e:
print(f"插入错误: {e}")
finally:
connection.close()
self.view_classes()
dialog.close()
def on_cancel():
dialog.close()
ok_button.clicked.connect(on_ok)
cancel_button.clicked.connect(on_cancel)
dialog.exec_()
```
#### 编辑班级
```python
def edit_class(self):
selected_items = self.class_table.selectedItems()
if not selected_items:
QtWidgets.QMessageBox.warning(self, "选择错误", "请选择要编辑的班级")
return
row = selected_items[0].row()
class_id = self.class_table.item(row, 0).text()
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM classes WHERE id = %s"
cursor.execute(sql, (class_id,))
result = cursor.fetchone()
connection.close()
except pymysql.MySQLError as e:
print(f"查询错误: {e}")
connection.close()
return
dialog = QtWidgets.QDialog(self)
dialog.setWindowTitle("编辑班级")
dialog_layout = QtWidgets.QVBoxLayout()
form_layout = QtWidgets.QFormLayout()
name_input = QtWidgets.QLineEdit(result[1])
teacher_input = QtWidgets.QComboBox()
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT id, name FROM teachers"
cursor.execute(sql)
teachers = cursor.fetchall()
for teacher in teachers:
teacher_input.addItem(f"{teacher[1]} (ID: {teacher[0]})", teacher[0])
teacher_input.setCurrentIndex(teacher_input.findData(result[2]))
except pymysql.MySQLError as e:
print(f"查询错误: {e}")
finally:
connection.close()
form_layout.addRow("班级名称", name_input)
form_layout.addRow("教师", teacher_input)
dialog_layout.addLayout(form_layout)
button_layout = QtWidgets.QHBoxLayout()
ok_button = QtWidgets.QPushButton("确定")
cancel_button = QtWidgets.QPushButton("取消")
button_layout.addWidget(ok_button)
button_layout.addWidget(cancel_button)
dialog_layout.addLayout(button_layout)
dialog.setLayout(dialog_layout)
def on_ok():
name = name_input.text()
teacher_id = teacher_input.currentData()
if not name:
QtWidgets.QMessageBox.warning(self, "输入错误", "班级名称不能为空")
return
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "UPDATE classes SET name = %s, teacher_id = %s WHERE id = %s"
cursor.execute(sql, (name, teacher_id, class_id))
connection.commit()
except pymysql.MySQLError as e:
print(f"更新错误: {e}")
finally:
connection.close()
self.view_classes()
dialog.close()
def on_cancel():
dialog.close()
ok_button.clicked.connect(on_ok)
cancel_button.clicked.connect(on_cancel)
dialog.exec_()
```
#### 删除班级
```python
def delete_class(self):
selected_items = self.class_table.selectedItems()
if not selected_items:
QtWidgets.QMessageBox.warning(self, "选择错误", "请选择要删除的班级")
return
row = selected_items[0].row()
class_id = self.class_table.item(row, 0).text()
reply = QtWidgets.QMessageBox.question(self, "确认删除", f"确定要删除班级 ID {class_id} 吗？", QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No)
if reply == QtWidgets.QMessageBox.Yes:
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "DELETE FROM classes WHERE id = %s"
cursor.execute(sql, (class_id,))
connection.commit()
except pymysql.MySQLError as e:
print(f"删除错误: {e}")
finally:
connection.close()
self.view_classes()
```
#### 查看班级
```python
def view_classes(self):
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT classes.id, classes.name, teachers.name FROM classes LEFT JOIN teachers ON classes.teacher_id = teachers.id"
cursor.execute(sql)
result = cursor.fetchall()
self.class_table.setRowCount(0)
for row_number, row_data in enumerate(result):
self.class_table.insertRow(row_number)
for column_number, data in enumerate(row_data):
self.class_table.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(str(data)))
except pymysql.MySQLError as e:
print(f"查询错误: {e}")
finally:
connection.close()
```
### 2.4 添加角色与权限管理
#### 角色管理界面
```python
def __init__(self):
# ... [初始化代码保持不变]
self.add_role_button = QtWidgets.QPushButton("添加角色")
self.edit_role_button = QtWidgets.QPushButton("编辑角色")
self.delete_role_button = QtWidgets.QPushButton("删除角色")
self.view_role_button = QtWidgets.QPushButton("查看角色")
self.button_layout.addWidget(self.add_role_button)
self.button_layout.addWidget(self.edit_role_button)
self.button_layout.addWidget(self.delete_role_button)
self.button_layout.addWidget(self.view_role_button)
# 连接角色按钮信号
self.add_role_button.clicked.connect(self.add_role)
self.edit_role_button.clicked.connect(self.edit_role)
self.delete_role_button.clicked.connect(self.delete_role)
self.view_role_button.clicked.connect(self.view_roles)
# 角色表格
self.role_table = QtWidgets.QTableWidget()
self.role_table.setColumnCount(2)
self.role_table.setHorizontalHeaderLabels(['ID', '角色名称'])
self.layout.addWidget(self.role_table)
```
#### 添加角色
```python
def add_role(self):
dialog = QtWidgets.QDialog(self)
dialog.setWindowTitle("添加角色")
dialog_layout = QtWidgets.QVBoxLayout()
form_layout = QtWidgets.QFormLayout()
name_input = QtWidgets.QLineEdit()
form_layout.addRow("角色名称", name_input)
dialog_layout.addLayout(form_layout)
button_layout = QtWidgets.QHBoxLayout()
ok_button = QtWidgets.QPushButton("确定")
cancel_button = QtWidgets.QPushButton("取消")
button_layout.addWidget(ok_button)
button_layout.addWidget(cancel_button)
dialog_layout.addLayout(button_layout)
dialog.setLayout(dialog_layout)
def on_ok():
name = name_input.text()
if not name:
QtWidgets.QMessageBox.warning(self, "输入错误", "角色名称不能为空")
return
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "INSERT INTO roles (name) VALUES (%s)"
cursor.execute(sql, (name,))
connection.commit()
except pymysql.MySQLError as e:
print(f"插入错误: {e}")
finally:
connection.close()
self.view_roles()
dialog.close()
def on_cancel():
dialog.close()
ok_button.clicked.connect(on_ok)
cancel_button.clicked.connect(on_cancel)
dialog.exec_()
```
#### 编辑角色
```python
def edit_role(self):
selected_items = self.role_table.selectedItems()
if not selected_items:
QtWidgets.QMessageBox.warning(self, "选择错误", "请选择要编辑的角色")
return
row = selected_items[0].row()
role_id = self.role_table.item(row, 0).text()
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT name FROM roles WHERE id = %s"
cursor.execute(sql, (role_id,))
result = cursor.fetchone()
connection.close()
except pymysql.MySQLError as e:
print(f"查询错误: {e}")
connection.close()
return
dialog = QtWidgets.QDialog(self)
dialog.setWindowTitle("编辑角色")
dialog_layout = QtWidgets.QVBoxLayout()
form_layout = QtWidgets.QFormLayout()
name_input = QtWidgets.QLineEdit(result[0])
form_layout.addRow("角色名称", name_input)
dialog_layout.addLayout(form_layout)
button_layout = QtWidgets.QHBoxLayout()
ok_button = QtWidgets.QPushButton("确定")
cancel_button = QtWidgets.QPushButton("取消")
button_layout.addWidget(ok_button)
button_layout.addWidget(cancel_button)
dialog_layout.addLayout(button_layout)
dialog.setLayout(dialog_layout)
def on_ok():
name = name_input.text()
if not name:
QtWidgets.QMessageBox.warning(self, "输入错误", "角色名称不能为空")
return
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "UPDATE roles SET name = %s WHERE id = %s"
cursor.execute(sql, (name, role_id))
connection.commit()
except pymysql.MySQLError as e:
print(f"更新错误: {e}")
finally:
connection.close()
self.view_roles()
dialog.close()
def on_cancel():
dialog.close()
ok_button.clicked.connect(on_ok)
cancel_button.clicked.connect(on_cancel)
dialog.exec_()
```
#### 删除角色
```python
def delete_role(self):
selected_items = self.role_table.selectedItems()
if not selected_items:
QtWidgets.QMessageBox.warning(self, "选择错误", "请选择要删除的角色")
return
row = selected_items[0].row()
role_id = self.role_table.item(row, 0).text()
reply = QtWidgets.QMessageBox.question(self, "确认删除", f"确定要删除角色 ID {role_id} 吗？", QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No)
if reply == QtWidgets.QMessageBox.Yes:
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "DELETE FROM roles WHERE id = %s"
cursor.execute(sql, (role_id,))
connection.commit()
except pymysql.MySQLError as e:
print(f"删除错误: {e}")
finally:
connection.close()
self.view_roles()
```
#### 查看角色
```python
def view_roles(self):
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM roles"
cursor.execute(sql)
result = cursor.fetchall()
self.role_table.setRowCount(0)
for row_number, row_data in enumerate(result):
self.role_table.insertRow(row_number)
for column_number, data in enumerate(row_data):
self.role_table.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(str(data)))
except pymysql.MySQLError as e:
print(f"查询错误: {e}")
finally:
connection.close()
```
### 2.5 权限管理
权限管理通常与角色管理结合在一起。以下是一个简单的权限分配界面。
#### 权限分配界面
```python
def __init__(self):
# ... [初始化代码保持不变]
self.assign_permission_button = QtWidgets.QPushButton("分配权限")
self.button_layout.addWidget(self.assign_permission_button)
# 连接权限分配按钮信号
self.assign_permission_button.clicked.connect(self.assign_permission)
# 权限表格
self.permission_table = QtWidgets.QTableWidget()
self.permission_table.setColumnCount(3)
self.permission_table.setHorizontalHeaderLabels(['ID', '权限名称', '描述'])
self.layout.addWidget(self.permission_table)
```
#### 分配权限
```python
def assign_permission(self):
selected_items = self.role_table.selectedItems()
if not selected_items:
QtWidgets.QMessageBox.warning(self, "选择错误", "请选择要分配权限的角色")
return
row = selected_items[0].row()
role_id = self.role_table.item(row, 0).text()
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT id, name FROM permissions"
cursor.execute(sql)
permissions = cursor.fetchall()
except pymysql.MySQLError as e:
print(f"查询错误: {e}")
connection.close()
return
dialog = QtWidgets.QDialog(self)
dialog.setWindowTitle("分配权限")
dialog_layout = QtWidgets.QVBoxLayout()
form_layout = QtWidgets.QFormLayout()
permission_input = QtWidgets.QListWidget()
for permission in permissions:
item = QtWidgets.QListWidgetItem(f"{permission[1]}")
item.setData(QtCore.Qt.UserRole, permission[0])
permission_input.addItem(item)
form_layout.addRow("权限", permission_input)
dialog_layout.addLayout(form_layout)
button_layout = QtWidgets.QHBoxLayout()
ok_button = QtWidgets.QPushButton("确定")
cancel_button = QtWidgets.QPushButton("取消")
button_layout.addWidget(ok_button)
button_layout.addWidget(cancel_button)
dialog_layout.addLayout(button_layout)
dialog.setLayout(dialog_layout)
def on_ok():
permission_ids = [item.data(QtCore.Qt.UserRole) for item in permission_input.selectedItems()]
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "DELETE FROM role_permissions WHERE role_id = %s"
cursor.execute(sql, (role_id,))
if permission_ids:
sql = "INSERT INTO role_permissions (role_id, permission_id) VALUES (%s, %s)"
for permission_id in permission_ids:
cursor.execute(sql, (role_id, permission_id))
connection.commit()
except pymysql.MySQLError as e:
print(f"更新错误: {e}")
finally:
connection.close()
self.view_roles()
self.view_permissions()
dialog.close()
def on_cancel():
dialog.close()
ok_button.clicked.connect(on_ok)
cancel_button.clicked.connect(on_cancel)
dialog.exec_()
```
#### 查看权限
```python
def view_permissions(self):
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM permissions"
cursor.execute(sql)
result = cursor.fetchall()
self.permission_table.setRowCount(0)
for row_number, row_data in enumerate(result):
self.permission_table.insertRow(row_number)
for column_number, data in enumerate(row_data):
self.permission_table.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(str(data)))
except pymysql.MySQLError as e:
print(f"查询错误: {e}")
finally:
connection.close()
```
## 3. 整合界面
为了更好地管理不同的功能模块，建议在主界面中添加标签页（Tabs），将学生、教师、班级、角色与权限分别放在不同的标签页中。
### 示例主界面
```python
def __init__(self):
super().__init__()
self.setWindowTitle("综合管理系统")
self.setGeometry(100, 100, 1000, 800)
self.central_widget = QtWidgets.QTabWidget()
self.setCentralWidget(self.central_widget)
# 学生管理标签页
student_tab = QtWidgets.QWidget()
student_layout = QtWidgets.QVBoxLayout()
student_tab.setLayout(student_layout)
student_tab.setObjectName("学生管理")
self.central_widget.addTab(student_tab, "学生管理")
self.table = QtWidgets.QTableWidget()
self.table.setColumnCount(6)
self.table.setHorizontalHeaderLabels(['ID', '姓名', '年龄', '性别', '邮箱', '电话'])
student_layout.addWidget(self.table)
# 教师管理标签页
teacher_tab = QtWidgets.QWidget()
teacher_layout = QtWidgets.QVBoxLayout()
teacher_tab.setLayout(teacher_layout)
teacher_tab.setObjectName("教师管理")
self.central_widget.addTab(teacher_tab, "教师管理")
self.teacher_table = QtWidgets.QTableWidget()
self.teacher_table.setColumnCount(6)
self.teacher_table.setHorizontalHeaderLabels(['ID', '姓名', '年龄', '性别', '邮箱', '电话'])
teacher_layout.addWidget(self.teacher_table)
# 班级管理标签页
class_tab = QtWidgets.QWidget()
class_layout = QtWidgets.QVBoxLayout()
class_tab.setLayout(class_layout)
class_tab.setObjectName("班级管理")
self.central_widget.addTab(class_tab, "班级管理")
self.class_table = QtWidgets.QTableWidget()
self.class_table.setColumnCount(3)
self.class_table.setHorizontalHeaderLabels(['ID', '班级名称', '教师'])
class_layout.addWidget(self.class_table)
# 角色管理标签页
role_tab = QtWidgets.QWidget()
role_layout = QtWidgets.QVBoxLayout()
role_tab.setLayout(role_layout)
role_tab.setObjectName("角色管理")
self.central_widget.addTab(role_tab, "角色管理")
self.role_table = QtWidgets.QTableWidget()
self.role_table.setColumnCount(2)
self.role_table.setHorizontalHeaderLabels(['ID', '角色名称'])
role_layout.addWidget(self.role_table)
# 权限管理标签页
permission_tab = QtWidgets.QWidget()
permission_layout = QtWidgets.QVBoxLayout()
permission_tab.setLayout(permission_layout)
permission_tab.setObjectName("权限管理")
self.central_widget.addTab(permission_tab, "权限管理")
self.permission_table = QtWidgets.QTableWidget()
self.permission_table.setColumnCount(3)
self.permission_table.setHorizontalHeaderLabels(['ID', '权限名称', '描述'])
permission_layout.addWidget(self.permission_table)
# 按钮布局
self.button_layout = QtWidgets.QHBoxLayout()
self.add_button = QtWidgets.QPushButton("添加")
self.edit_button = QtWidgets.QPushButton("编辑")
self.delete_button = QtWidgets.QPushButton("删除")
self.view_button = QtWidgets.QPushButton("查看")
self.button_layout.addWidget(self.add_button)
self.button_layout.addWidget(self.edit_button)
self.button_layout.addWidget(self.delete_button)
self.button_layout.addWidget(self.view_button)
self.layout = QtWidgets.QVBoxLayout()
self.central_widget.currentChanged.connect(self.on_tab_changed)
self.layout.addLayout(self.button_layout)
self.layout.addWidget(self.table)
self.layout.addWidget(self.teacher_table)
self.layout.addWidget(self.class_table)
self.layout.addWidget(self.role_table)
self.layout.addWidget(self.permission_table)
self.central_widget.setLayout(self.layout)
# 连接按钮信号
self.add_button.clicked.connect(self.on_add)
self.edit_button.clicked.connect(self.on_edit)
self.delete_button.clicked.connect(self.on_delete)
self.view_button.clicked.connect(self.on_view)
def on_tab_changed(self, index):
if index == 0:
self.view_students()
elif index == 1:
self.view_teachers()
elif index == 2:
self.view_classes()
elif index == 3:
self.view_roles()
elif index == 4:
self.view_permissions()
def on_add(self):
current_tab = self.central_widget.currentIndex()
if current_tab == 0:
self.add_student()
elif current_tab == 1:
self.add_teacher()
elif current_tab == 2:
self.add_class()
elif current_tab == 3:
self.add_role()
elif current_tab == 4:
self.assign_permission()
def on_edit(self):
current_tab = self.central_widget.currentIndex()
if current_tab == 0:
self.edit_student()
elif current_tab == 1:
self.edit_teacher()
elif current_tab == 2:
self.edit_class()
elif current_tab == 3:
self.edit_role()
elif current_tab == 4:
self.edit_permission()
def on_delete(self):
current_tab = self.central_widget.currentIndex()
if current_tab == 0:
self.delete_student()
elif current_tab == 1:
self.delete_teacher()
elif current_tab == 2:
self.delete_class()
elif current_tab == 3:
self.delete_role()
elif current_tab == 4:
self.delete_permission()
def on_view(self):
current_tab = self.central_widget.currentIndex()
if current_tab == 0:
self.view_students()
elif current_tab == 1:
self.view_teachers()
elif current_tab == 2:
self.view_classes()
elif current_tab == 3:
self.view_roles()
elif current_tab == 4:
self.view_permissions()
```
## 4. 权限控制
为了实现基于角色的权限控制，需要在应用启动时加载当前用户的角色和权限，并根据权限控制按钮的可用性。
### 示例代码
```python
def __init__(self):
# ... [初始化代码保持不变]
self.current_user_role = None
self.current_user_permissions = []
# 登录逻辑
self.login()
# 根据权限控制按钮
self.update_button_visibility()
def login(self):
# 简单的登录逻辑
dialog = QtWidgets.QDialog(self)
dialog.setWindowTitle("登录")
dialog_layout = QtWidgets.QVBoxLayout()
form_layout = QtWidgets.QFormLayout()
username_input = QtWidgets.QLineEdit()
password_input = QtWidgets.QLineEdit()
password_input.setEchoMode(QtWidgets.QLineEdit.Password)
form_layout.addRow("用户名", username_input)
form_layout.addRow("密码", password_input)
dialog_layout.addLayout(form_layout)
button_layout = QtWidgets.QHBoxLayout()
ok_button = QtWidgets.QPushButton("登录")
cancel_button = QtWidgets.QPushButton("取消")
button_layout.addWidget(ok_button)
button_layout.addWidget(cancel_button)
dialog_layout.addLayout(button_layout)
dialog.setLayout(dialog_layout)
def on_ok():
username = username_input.text()
password = password_input.text()
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT id, role_id FROM users WHERE username =
