制作学生信息收集系统

要求：

1.有GUI界面

2.通过GUI界面来收集学生的姓名，学号等进数据库

3.可以通过GUI来擦汗寻已经保存好的学生信息

步骤：

1.利用QT制作GUI界面

以上为登录界面；

以上为学生信息查询界面；

以上为学生注册界面（实为收集学生信息的功能）。

2.连接数据库

这里本人先将已经得到的学生信息导入数据库中，具体代码如下：

# -*- coding: utf-8 -*-
'''
$ImpoData.py
2021/12/2 16:40
'''

'''
    将数据提前导入数据库中
'''
import pymysql
import openpyxl

#连接数据库
connection = pymysql.connect(host="localhost",user="root",password="密码",database='xuexi')
cursor = connection.cursor()
#打开excel
wb = openpyxl.load_workbook('20信计2.xlsx') #打开含有学生信息的excel表格
# 获取表
sheet = wb['20信计']
li = []
for i in range(2,57):
    #获取单元格
    id = sheet.cell(i,1).value
    name = sheet.cell(i,2).value
    old = sheet.cell(i,3).value
    zhanhao = sheet.cell(i,4).value
    mima = sheet.cell(i,5).value
    li.append((id,name,old,zhanhao,mima))


try:
    cursor.executemany('insert into he (id,姓名,年龄,学号,密码) values (%s,%s,%s,%s,%s)',li) # 将获取到的学生信息插入到数据库中
    connection.commit()
except:
    connection.rollback()
cursor.close()
connection.close()

3.接下来实现登录查询，注册学生信息功能，代码如下：

# -*- coding: utf-8 -*-
'''
$main.py
2021/12/1 23:08
'''
from PySide2.QtWidgets import QApplication,QMessageBox,QTableWidgetItem
from PySide2.QtUiTools import QUiLoader
from shilihua import IS

import pymysql

def lianjie_sql():
    connection = pymysql.connect(host="localhost",user="root",password="密码",database='xuexi')
    cursor = connection.cursor()
    return connection,cursor

def mysql():
    connection = pymysql.connect(host="localhost",user="root",password="密码",database='xuexi')
    cursor = connection.cursor()
    sql = '''select * from he '''
    cursor.execute(sql)
    # fetchall 获取所有查询的内容
    a = cursor.fetchall()
    cursor.close()
    connection.close()
    return a





class Win_Login():
    def __init__(self):
        self.ui = QUiLoader().load('untitled.ui')
        self.ui.denlu.clicked.connect(self.onSignIn)
        self.ui.nima.returnPressed.connect(self.onSignIn)
        self.ui.zhuce.clicked.connect(self.ZHUCE)
    def onSignIn(self):
        zhanhao = self.ui.zhahao.text().strip()
        mima = self.ui.nima.text().strip()
        for i in mysql():
            if zhanhao == "quanshi" and mima == 'abc123':
                IS.wim = Win_Main()
                IS.wim.ui.show()
                self.ui.zhahao.clear()
                self.ui.nima.clear()
                self.ui.close()
                break
            elif zhanhao == i[3] and mima == i[4]:
                IS.wim2 = Win_Main2(zhanhao)
                IS.wim2.ui.show()
                self.ui.zhahao.clear()
                self.ui.nima.clear()
                self.ui.close()
                break
        else:
            QMessageBox.critical(
                self.ui,
                '错误',
                '请输入正确的账号或密码')
    def ZHUCE(self):
        IS.zuce = Zhu_Ce()
        IS.zuce.ui.show()


class Win_Main():
    def __init__(self):
        self.ui = QUiLoader().load('xinxi.ui')
        self.ui.fanhui.clicked.connect(self.onSignIn2)
        self.shuju()
    def onSignIn2(self):
        IS.denlu.ui.show()
        self.ui.close()
    def shuju(self):
        xb = 0
        for i in mysql():
            self.ui.biaoge.insertRow(xb)
            item = QTableWidgetItem()
            item.setText(f'{i[0]}')
            self.ui.biaoge.setItem(xb, 0, item)

            item2 = QTableWidgetItem()
            item2.setText(i[1])
            self.ui.biaoge.setItem(xb, 1, item2)

            item3 = QTableWidgetItem()
            item3.setText(f'{i[2]}')
            self.ui.biaoge.setItem(xb, 2, item3)

            item4 = QTableWidgetItem()
            item4.setText(i[3])
            self.ui.biaoge.setItem(xb, 3, item4)

            item5 = QTableWidgetItem()
            item5.setText(i[4])
            self.ui.biaoge.setItem(xb, 4, item5)
            xb += 1

class Win_Main2():
    def __init__(self,zhanhao):
        self.ui = QUiLoader().load('xinxi.ui')
        self.ui.fanhui.clicked.connect(self.onSignIn4)
        self.shuju(zhanhao)
    def onSignIn4(self):
        IS.denlu.ui.show()
        self.ui.close()
    def shuju(self,zhanhao):
        for i in mysql():
            if i[3] == zhanhao:
                self.ui.biaoge.insertRow(0)
                item = QTableWidgetItem()
                item.setText(f'{i[0]}')
                self.ui.biaoge.setItem(0, 0, item)

                item2 = QTableWidgetItem()
                item2.setText(i[1])
                self.ui.biaoge.setItem(0, 1, item2)

                item3 = QTableWidgetItem()
                item3.setText(f'{i[2]}')
                self.ui.biaoge.setItem(0, 2, item3)

                item4 = QTableWidgetItem()
                item4.setText(i[3])
                self.ui.biaoge.setItem(0, 3, item4)

                item5 = QTableWidgetItem()
                item5.setText(i[4])
                self.ui.biaoge.setItem(0, 4, item5)
                break




class Zhu_Ce():
    def __init__(self):
        self.ui = QUiLoader().load('zzhuce.ui')
        self.ui.quer.clicked.connect(self.onSignIn3)
        self.ui.qrmima.returnPressed.connect(self.onSignIn3)
        self.ui.quxiao.clicked.connect(self.zhucee)
    def onSignIn3(self):
        name = self.ui.xinmin.text().strip()
        old = self.ui.old.text().strip()
        zhanhao = self.ui.zhanhao.text().strip()
        mima = self.ui.mima.text().strip()
        qmima = self.ui.qrmima.text().strip()
        for i in mysql():
            if zhanhao == i[3]:
                QMessageBox.critical(
                    self.ui,
                    '错误',
                    '该账号已被注册！！！')
            if mima=='' or qmima=='' or name == '' or old == '':
                QMessageBox.critical(
                    self.ui,
                    '错误',
                    '还有未填的选项！！！')
                break
            if mima!=qmima:
                QMessageBox.critical(
                    self.ui,
                    '错误',
                    '密码不一致！！！')
                break
        else:
            connection,cursor = lianjie_sql()
            sqlii="select max(id)+1 from he"
            cursor.execute(sqlii)
            a = cursor.fetchone()
            sql = '''insert into he(id,姓名,年龄,学号,密码)values(%s,%s,%s,%s,%s)'''
            val = (f'{a[0]}',name,f"{old}",zhanhao,mima)
            try:
                cursor.execute(sql,val)
                connection.commit()
                self.ui.close()
            except:
                connection.rollback()
            cursor.close()
            connection.close()
    def zhucee(self):
        self.ui.close()
app = QApplication([])
IS.denlu = Win_Login()
IS.denlu.ui.show()
app.exec_()