创建一个基于 Python 的学生管理系统，使用 Qt 组件（通过 PyQt5 或 PySide2）和 MySQL 数据库，是一个综合性的项目。以下是一个详细的指南，包括环境设置、数据库设计、Python 代码示例以及如何使用 Qt 设计用户界面。
## 目录
1. [环境设置](#环境设置)
2. [数据库设计](#数据库设计)
3. [安装必要的 Python 库](#安装必要的-python-库)
4. [连接 MySQL 数据库](#连接-mysql-数据库)
5. [创建 Qt 用户界面](#创建-qt-用户界面)
6. [实现功能模块](#实现功能模块)
- [添加学生](#添加学生)
- [查看学生列表](#查看学生列表)
- [编辑学生信息](#编辑学生信息)
- [删除学生](#删除学生)
7. [完整代码示例](#完整代码示例)
8. [运行项目](#运行项目)
---
## 环境设置
确保你的开发环境中安装了以下软件：
1. **Python 3.x**: [下载链接](https://www.python.org/downloads/)
2. **MySQL**: [下载链接](https://dev.mysql.com/downloads/mysql/)
3. **Qt Designer**（可选，用于设计 UI）：[下载链接](https://build-system.fman.io/qt-designer-download)
## 数据库设计
首先，设计一个简单的数据库来存储学生信息。假设我们有一个名为 `students` 的表，包含以下字段：
- `id` (INT, 主键, 自增)
- `name` (VARCHAR)
- `age` (INT)
- `gender` (VARCHAR)
- `email` (VARCHAR)
- `phone` (VARCHAR)
### 创建数据库和表
```sql
CREATE DATABASE student_management;
USE student_management;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT,
gender VARCHAR(10),
email VARCHAR(100),
phone VARCHAR(20)
);
```
你可以使用 MySQL 命令行或 MySQL Workbench 来执行上述 SQL 语句。
## 安装必要的 Python 库
你需要安装以下 Python 库：
- `PyQt5` 或 `PySide2`: 用于创建用户界面
- `PyMySQL`: 用于连接 MySQL 数据库
使用 pip 安装：
```bash
pip install PyQt5 PyMySQL
```
或者，如果你更喜欢 `PySide2`：
```bash
pip install PySide2 PyMySQL
```
## 连接 MySQL 数据库
以下是如何使用 `PyMySQL` 连接 MySQL 数据库的示例代码：
```python
import sys
from PyQt5 import QtWidgets
import pymysql
# 数据库配置
db_config = {
'host': 'localhost',
'user': 'your_username',
'password': 'your_password',
'database': 'student_management',
'charset': 'utf8mb4'
}
def get_db_connection():
try:
connection = pymysql.connect(**db_config)
return connection
except pymysql.MySQLError as e:
print(f"数据库连接错误: {e}")
return None
```
确保将 `'your_username'` 和 `'your_password'` 替换为你的 MySQL 用户名和密码。
## 创建 Qt 用户界面
你可以使用 Qt Designer 设计和保存 `.ui` 文件，或者使用代码编写界面。以下示例将使用代码编写一个简单的界面。
### 示例界面
```python
from PyQt5 import QtWidgets, QtCore
class StudentManagementApp(QtWidgets.QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle("学生管理系统")
self.setGeometry(100, 100, 800, 600)
self.central_widget = QtWidgets.QWidget()
self.setCentralWidget(self.central_widget)
self.layout = QtWidgets.QVBoxLayout()
self.central_widget.setLayout(self.layout)
# 创建表格视图
self.table = QtWidgets.QTableWidget()
self.table.setColumnCount(6)
self.table.setHorizontalHeaderLabels(['ID', '姓名', '年龄', '性别', '邮箱', '电话'])
self.layout.addWidget(self.table)
# 创建按钮
self.button_layout = QtWidgets.QHBoxLayout()
self.add_button = QtWidgets.QPushButton("添加学生")
self.edit_button = QtWidgets.QPushButton("编辑学生")
self.delete_button = QtWidgets.QPushButton("删除学生")
self.refresh_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.refresh_button)
self.layout.addLayout(self.button_layout)
# 连接按钮信号
self.add_button.clicked.connect(self.add_student)
self.edit_button.clicked.connect(self.edit_student)
self.delete_button.clicked.connect(self.delete_student)
self.refresh_button.clicked.connect(self.refresh_student_list)
# 初始化学生列表
self.refresh_student_list()
def refresh_student_list(self):
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM students"
cursor.execute(sql)
result = cursor.fetchall()
self.table.setRowCount(0)
for row_number, row_data in enumerate(result):
self.table.insertRow(row_number)
for column_number, data in enumerate(row_data):
self.table.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(str(data)))
except pymysql.MySQLError as e:
print(f"查询错误: {e}")
finally:
connection.close()
```
## 实现功能模块
### 添加学生
```python
def add_student(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 students (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.refresh_student_list()
dialog.close()
def on_cancel():
dialog.close()
ok_button.clicked.connect(on_ok)
cancel_button.clicked.connect(on_cancel)
dialog.exec_()
```
### 查看学生列表
在上面的 `refresh_student_list` 方法中已经实现了查看学生列表的功能。
### 编辑学生信息
```python
def edit_student(self):
selected_items = self.table.selectedItems()
if not selected_items:
QtWidgets.QMessageBox.warning(self, "选择错误", "请选择要编辑的学生")
return
row = selected_items[0].row()
student_id = self.table.item(row, 0).text()
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM students WHERE id = %s"
cursor.execute(sql, (student_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 students SET name = %s, age = %s, gender = %s, email = %s, phone = %s WHERE id = %s"
cursor.execute(sql, (name, age, gender, email, phone, student_id))
connection.commit()
except pymysql.MySQLError as e:
print(f"更新错误: {e}")
finally:
connection.close()
self.refresh_student_list()
dialog.close()
def on_cancel():
dialog.close()
ok_button.clicked.connect(on_ok)
cancel_button.clicked.connect(on_cancel)
dialog.exec_()
```
### 删除学生
```python
def delete_student(self):
selected_items = self.table.selectedItems()
if not selected_items:
QtWidgets.QMessageBox.warning(self, "选择错误", "请选择要删除的学生")
return
row = selected_items[0].row()
student_id = self.table.item(row, 0).text()
reply = QtWidgets.QMessageBox.question(self, "确认删除", f"确定要删除学生 ID {student_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 students WHERE id = %s"
cursor.execute(sql, (student_id,))
connection.commit()
except pymysql.MySQLError as e:
print(f"删除错误: {e}")
finally:
connection.close()
self.refresh_student_list()
```
## 完整代码示例
以下是完整的 Python 代码示例，整合了上述所有部分：
```python
import sys
from PyQt5 import QtWidgets, QtCore
import pymysql
# 数据库配置
db_config = {
'host': 'localhost',
'user': 'your_username',
'password': 'your_password',
'database': 'student_management',
'charset': 'utf8mb4'
}
def get_db_connection():
try:
connection = pymysql.connect(**db_config)
return connection
except pymysql.MySQLError as e:
print(f"数据库连接错误: {e}")
return None
class StudentManagementApp(QtWidgets.QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle("学生管理系统")
self.setGeometry(100, 100, 800, 600)
self.central_widget = QtWidgets.QWidget()
self.setCentralWidget(self.central_widget)
self.layout = QtWidgets.QVBoxLayout()
self.central_widget.setLayout(self.layout)
# 创建表格视图
self.table = QtWidgets.QTableWidget()
self.table.setColumnCount(6)
self.table.setHorizontalHeaderLabels(['ID', '姓名', '年龄', '性别', '邮箱', '电话'])
self.layout.addWidget(self.table)
# 创建按钮
self.button_layout = QtWidgets.QHBoxLayout()
self.add_button = QtWidgets.QPushButton("添加学生")
self.edit_button = QtWidgets.QPushButton("编辑学生")
self.delete_button = QtWidgets.QPushButton("删除学生")
self.refresh_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.refresh_button)
self.layout.addLayout(self.button_layout)
# 连接按钮信号
self.add_button.clicked.connect(self.add_student)
self.edit_button.clicked.connect(self.edit_student)
self.delete_button.clicked.connect(self.delete_student)
self.refresh_button.clicked.connect(self.refresh_student_list)
# 初始化学生列表
self.refresh_student_list()
def refresh_student_list(self):
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM students"
cursor.execute(sql)
result = cursor.fetchall()
self.table.setRowCount(0)
for row_number, row_data in enumerate(result):
self.table.insertRow(row_number)
for column_number, data in enumerate(row_data):
self.table.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(str(data)))
except pymysql.MySQLError as e:
print(f"查询错误: {e}")
finally:
connection.close()
def add_student(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 students (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.refresh_student_list()
dialog.close()
def on_cancel():
dialog.close()
ok_button.clicked.connect(on_ok)
cancel_button.clicked.connect(on_cancel)
dialog.exec_()
def edit_student(self):
selected_items = self.table.selectedItems()
if not selected_items:
QtWidgets.QMessageBox.warning(self, "选择错误", "请选择要编辑的学生")
return
row = selected_items[0].row()
student_id = self.table.item(row, 0).text()
connection = get_db_connection()
if connection:
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM students WHERE id = %s"
cursor.execute(sql, (student_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 students SET name = %s, age = %s, gender = %s, email = %s, phone = %s WHERE id = %s"
cursor.execute(sql, (name, age, gender, email, phone, student_id))
connection.commit()
except pymysql.MySQLError as e:
print(f"更新错误: {e}")
finally:
connection.close()
self.refresh_student_list()
dialog.close()
def on_cancel():
dialog.close()
ok_button.clicked.connect(on_ok)
cancel_button.clicked.connect(on_cancel)
dialog.exec_()
def delete_student(self):
selected_items = self.table.selectedItems()
if not selected_items:
QtWidgets.QMessageBox.warning(self, "选择错误", "请选择要删除的学生")
return
row = selected_items[0].row()
student_id = self.table.item(row, 0).text()
reply = QtWidgets.QMessageBox.question(self, "确认删除", f"确定要删除学生 ID {student_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 students WHERE id = %s"
cursor.execute(sql, (student_id,))
connection.commit()
except pymysql.MySQLError as e:
print(f"删除错误: {e}")
finally:
connection.close()
self.refresh_student_list()
def main():
app = QtWidgets.QApplication(sys.argv)
window = StudentManagementApp()
window.show()
sys.exit(app.exec_())
if __name__ == "__main__":
main()
```
## 运行项目
1. **配置数据库**: 确保 MySQL 数据库已启动，并且 `student_management` 数据库和 `students` 表已创建。
2. **配置数据库连接**: 在代码中，将 `db_config` 的 `user` 和 `password` 替换为你的 MySQL 用户名和密码。
3. **运行脚本**: 保存上述代码为 `student_management.py`，然后在命令行中运行：
```bash
python student_management.py
```
4. **使用应用**: 使用界面上的按钮来添加、编辑、删除和刷新学生列表。
## 注意事项
- **安全性**: 在实际应用中，请确保对用户输入进行适当的验证和消毒，以防止 SQL 注入等安全问题。
- **错误处理**: 增强错误处理机制，例如在数据库操作失败时提供更详细的错误信息。
- **用户体验**: 可以进一步优化用户界面，例如添加搜索功能、分页显示等。
- **数据持久性**: 确保数据库连接在应用运行期间保持打开，并在适当的时候关闭连接以释放资源。
---
通过以上步骤，你应该能够创建一个基本的学生管理系统。根据需要，你可以扩展功能，例如添加搜索功能、导入导出数据、用户权限管理等。
