目录
前言
期末作业要求用Qt做一个学生管理系统,笔者这段时间在自学Python,故在征求老师同意后不用C++而选择了Python的PyQt5,本项目主要实现了PyQt5的增删改查。因为学习PyQt5的时间短,写代码时遇到了很多困难,笔者在CSDN与Github上我查阅了大量的资料,最终做了一个半成品出来。但笔者认为,以这个小项目作为一个入门练手项目十分合适,现将其分享,希望能够帮助到新手入门的人。
一、准备工作
本项目主要用到了PyQt5、pymysql、xlwt模块、Mysql数据库、QtDesigner和pyuic。PyQt5用于构建GUI界面,pymysql用于与MySQL数据库连接,xlwt用于操作excel。网上大牛们以及将操作说得很详细,故此处只向大家贴一些链接,如仍有疑问,可留言一起讨论。
二、运行效果
登录界面
主界面
添加信息界面
更改信息界面
删除信息界面
三、数据库准备
MySQL数据库笔者采用了navicat数据库管理工具。MySQL具体使用方法此文不作详细解释,此处只贴出该项目需要准备的东西。
新建一个数据库,创建一张表用来存放管理员的账号与密码,一张表用来存放学生信息。项目中,我使用的数据库名称为linux,创建的admin表用来存放管理员账号密码(该系统只能由管理员登录)、student表用来存放学生信息。
四、代码实现
篇幅原因,只贴出主要代码,源码及ui文件放在了网盘,需要自取,有疑问可留言。
实现用户的登录,需要用户名以及密码在数据库中才能登录
def log(self):
#获取用户名和密码
user_name = self.user_name.text()
user_pass = self.user_pass.text()
#没有连接数据库跳出提示框
try:
self.db = pymysql.connect(host="localhost", user="root", passwd="Pyf757620", database="linux")
except:
QMessageBox.warning(MainWindow, '警告', "请连接数据库", QMessageBox.Yes)
#获取数据库中的数据存放在name与wd的列表中
sql = 'select * from admin'
cur = self.db.cursor()
cur.execute(sql)
results = cur.fetchall()
name = []
wd = []
for row in results:
stu_name = row[0]
name.append(stu_name)
stu_passwd = row[1]
wd.append(stu_passwd)
x = 0
#判断填入的数据是否存在于数据库中
for i in range(len(name)):
if user_name == name[i] and user_pass == wd[i]:
x = 1
else:
pass
self.db.close()
if x == 1:
#如果存在则打开跳转至主界面
self.open()
else:
QMessageBox.question(MainWindow, '错误', "你输入的账户名或密码不正确", QMessageBox.Yes)
def open(self):
self.Main.show()
MainWindow.close()
def test(self):
from PyQt5.QtWidgets import QMessageBox
reply = QMessageBox.question(MainWindow, '退出', '确定退出?', QMessageBox.Yes | QMessageBox.No, QMessageBox.No)
if reply == QMessageBox.Yes:
sys.exit(app.exec_())
显示所有学生的信息
def showall(self):
db = pymysql.connect(host="localhost", user="root", passwd="Pyf757620", database="linux")
cur = db.cursor()
sql = 'select * from student'
cur.execute(sql)
items = cur.fetchall()
#这段代码简直万能,将数据以表格的形式显示在tableWidget的控件中
for i in range(len(items)):
item = items[i]
row = self.tableWidget.rowCount()
self.tableWidget.insertRow(row)
for j in range(len(item)):
item = QTableWidgetItem(str(items[i][j]))
self.tableWidget.setItem(row, j, item)
localtime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
print(str(localtime))
self.dbviewer.append(str(localtime))
db.close()
cur.close()
查询学生的信息,当有学号输入时直接查询学号,没有学号时根据输入的信息进行搜索。
def select(self):
name = self.name_.text()
numb = self.num_.text()
age = self.age_.text()
sex = self.sex_box.currentText()
sql = 'select * from student where '
db = pymysql.connect(host="localhost", user="root", passwd="Pyf757620", database="linux")
cur = db.cursor()
msg = []
if len(numb) != 0: #学号是唯一的,有学号直接搜索学号即可
p = sql + 'stu_num = ' + numb
print(p)
cur.execute(p)
data = cur.fetchall()
for i in range(len(data)):
item = data[i]
row = self.tableWidget.rowCount()
self.tableWidget.insertRow(row)
for j in range(len(item)):
item = QTableWidgetItem(str(data[i][j]))
self.tableWidget.setItem(row, j, item)
# self.dbviewer.append(str(data))
print(data)
db.close()
cur.close()
elif len(name) != 0 and sex == "男" and len(age) == 0: #sex肯定不为None 故这个pass掉
p = sql + 'stu_name = ' + '\'' + name + '\'' + ' and ' + 'stu_sex = ' + '\'' + sex + '\''
print(p)
cur.execute(p)
data = cur.fetchall()
for i in range(len(data)):
item = data[i]
row = self.tableWidget.rowCount()
self.tableWidget.insertRow(row)
for j in range(len(item)):
item = QTableWidgetItem(str(data[i][j]))
self.tableWidget.setItem(row, j, item)
db.close()
cur.close()
elif len(name) != 0 and sex == "女" and len(age) == 0: #sex肯定不为None 故这个pass掉
p = sql + 'stu_name = ' + '\'' + name + '\'' + ' and ' + 'stu_sex = ' + '\'' + sex + '\''
print(p)
cur.execute(p)
data = cur.fetchall()
for i in range(len(data)):
item = data[i]
row = self.tableWidget.rowCount()
self.tableWidget.insertRow(row)
for j in range(len(item)):
item = QTableWidgetItem(str(data[i][j]))
self.tableWidget.setItem(row, j, item)
db.close()
cur.close()
elif len(name) != 0 and sex == "男" and len(age) != 0:
p = sql + 'stu_name = ' + '\'' + name + '\'' + ' and ' + 'stu_sex = ' + '\'' + sex + '\'' + ' and stu_age = ' + age
print(p)
cur.execute(p)
data = cur.fetchall()
for i in range(len(data)):
item = data[i]
row = self.tableWidget.rowCount()
self.tableWidget.insertRow(row)
for j in range(len(item)):
item = QTableWidgetItem(str(data[i][j]))
self.tableWidget.setItem(row, j, item)
db.close()
cur.close()
elif len(name) != 0 and sex == "女" and len(age) != 0:
p = sql + 'stu_name = ' + '\'' + name + '\'' + ' and ' + 'stu_sex = ' + '\'' + sex + '\'' + ' and stu_age = ' + age
print(p)
cur.execute(p)
data = cur.fetchall()
for i in range(len(data)):
item = data[i]
row = self.tableWidget.rowCount()
self.tableWidget.insertRow(row)
for j in range(len(item)):
item = QTableWidgetItem(str(data[i][j]))
self.tableWidget.setItem(row, j, item)
db.close()
cur.close()
elif len(name) == 0 and sex == "男" and len(age) != 0:
p = sql + 'stu_sex = ' + '\'' + sex + '\'' + ' and stu_age = ' + age
print(p)
cur.execute(p)
data = cur.fetchall()
for i in range(len(data)):
item = data[i]
row = self.tableWidget.rowCount()
self.tableWidget.insertRow(row)
for j in range(len(item)):
item = QTableWidgetItem(str(data[i][j]))
self.tableWidget.setItem(row, j, item)
db.close()
cur.close()
elif len(name) == 0 and sex == "女" and len(age) != 0:
p = sql + 'stu_sex = ' + '\'' + sex + '\'' + ' and stu_age = ' + age
print(p)
cur.execute(p)
data = cur.fetchall()
for i in range(len(data)):
item = data[i]
row = self.tableWidget.rowCount()
self.tableWidget.insertRow(row)
for j in range(len(item)):
item = QTableWidgetItem(str(data[i][j]))
self.tableWidget.setItem(row, j, item)
db.close()
cur.close()
增加学生信息
def insert(self):
sql_insert = 'INSERT INTO Student (stu_name, stu_num, stu_age, stu_sex) VALUE (%s,%s,%s,%s) '
db = pymysql.connect(host="localhost", user="root", passwd="Pyf757620", database="linux")
cur = db.cursor()
n = []
n.append(self.name_.text())
n.append(self.num_.text())
n.append(self.age_.text())
n.append(self.sex_box.currentText())
print(n)
print(sql_insert)
try:
cur.execute(sql_insert, n)
QMessageBox.about(MainWindow, "提示", "添加信息成功")
# self.dbviewer.append("添加成功!")
db.commit()
except pymysql.Error as e:
# self.dbviewer.append("添加失败!")
print("数据添加失败" + str(e))
db.rollback()
db.close()
cur.close()
删除学生信息
def delete(self):
db = pymysql.connect(host="localhost", user="root", passwd="Pyf757620", database="linux")
numb = self.num_.text()
sql = 'DELETE FROM Student WHERE stu_num=%s'
cur = db.cursor()
print(sql % numb)
try:
cur.execute(sql, numb)
db.commit()
print('删除成功!')
QMessageBox.about(MainWindow, "提示", "删除学生信息成功")
db.close()
cur.close()
except pymysql.Error as e:
print("数据删除失败:" + str(e))
db.rollback()
修改学生信息,这一段写得并不好,不能只修改单个信息,想要实现很简单,但估计有点麻烦需要一些时间,有精力的朋友可试一试。
def update(self):
sql_insert = 'UPDATE STUDENT SET stu_name= %s,stu_age= %s, stu_sex=%s WHERE stu_num=%s'
db = pymysql.connect(host="localhost", user="root", passwd="Pyf757620", database="linux")
cur = db.cursor()
n = []
n.append(self.name_.text())
n.append(self.age_.text())
n.append(self.sex_box.currentText())
n.append(self.num_.text())
print(n)
print(sql_insert)
try:
cur.execute(sql_insert, n)
QMessageBox.about(MainWindow, "提示", "修改数据成功")
db.commit()
except pymysql.Error as e:
QMessageBox.Warning(MainWindow, "警告", "修改数据失败", QMessageBox.Yes)
print("数据修改失败" + str(e))
db.rollback()
db.close()
cur.close()
将数据库中的学生名单导出为excel的xls文件
def output(self):
db = pymysql.connect(host="localhost", user="root", passwd="Pyf757620", database="linux")
numb = self.num_.text()
sql = 'select * from student'
cur = db.cursor()
cur.execute(sql) # 返回受影响的行数
fields = [field[0] for field in cur.description] # 获取所有字段名
all_data = cur.fetchall() # 所有数据
# 写入excel
book = xlwt.Workbook()
sheet = book.add_sheet('sheet1')
for col, field in enumerate(fields):
sheet.write(0, col, field)
row = 1
for data in all_data:
for col, field in enumerate(data):
sheet.write(row, col, field)
row += 1
book.save("student.xls")
QMessageBox.about(MainWindow, "提示", "导出数据成功")
总结
本文简单介绍了基于PyQt5的学生信息管理系统的实现,因文章只贴出代码,具体的逻辑并没有说出,所以有疑问可以在评论区说出。
第一次写文章,有很多不足之处希望大家谅解。
最后贴出本文涉及到的资源
项目代码: 提取码:3cxo(已失效)
链接:https://pan.baidu.com/s/1ot9NAPCwV8LKsYu1Qd0-Yg
提取码:3czm
navicat工具: 提取码:h013