计算机软件实习项目四 —— 校园一卡通管理系统 (代码实现) 12-27

代码实现（校园一卡通管理系统）

  这个项目依旧选择用Python+PyQt5来进行开发，数据库部分则选择使用mysql。

  我主要负责实现主窗口部分的学生基本信息栏和交易记录栏的增删改查、批量插入、批量删除等功能，同时负责部分UI界面的制作以及后端mysql数据库交互方法的编写等。

一、主函数（main.py）

  主函数用来创建各个窗口，并且建立各个窗口之间的关系。

from PyQt5.QtWidgets import QApplication

from BatchInsertedWindow import BatchInsertedWindow
from ForgetWindow import ResetWindow, SecurityQuestionWindow
from LoginWindow import LoginWindow
from MainWindow import MainWindow
from InsertWindow import InsertWindow
import sys

if __name__ == "__main__":
    app = QApplication(sys.argv)

    login_widget = LoginWindow()  # 窗口控件
    login_widget.show()

    main_widget = MainWindow()  # 窗口控件
    forget_widget = SecurityQuestionWindow()  # 密保窗口
    reset_widget = ResetWindow()  # 重置密码窗口
    insert_widget = InsertWindow()  # 窗口控件
    batch_insert_widget = BatchInsertedWindow()  # 批量插入窗口

    login_widget.signal.connect(main_widget.show)  # 连接槽函数
    login_widget.forget_signal.connect(forget_widget.show)  # 显示忘记密码窗口
    forget_widget.signal.connect(reset_widget.show)  # 显示重置密码窗口
    forget_widget.back_signal.connect(login_widget.show)  # 显示登录窗口
    reset_widget.signal.connect(login_widget.show)  # 回到登录窗口
    reset_widget.back_signal.connect(login_widget.show)  # 回到登录窗口
    main_widget.batch_signal.connect(batch_insert_widget.show)  # 显示批量插入窗口

    main_widget.insert_signal.connect(insert_widget.show)  # 连接槽函数
    insert_widget.inserted_signal.connect(main_widget.query)  # 更新数据

    sys.exit(app.exec_())

二、登录窗口（LoginWindow.py）

  登录窗口负责用户身份验证，以及弹出忘记密码窗口等功能。

from PyQt5 import QtWidgets
from PyQt5.QtCore import *
from PyQt5.QtGui import *
import ctypes
from PyQt5.QtWidgets import QMainWindow, QLineEdit, QMessageBox, QWidget

from Functions import load
from Login_UI import Ui_LoginWindow


class LoginWindow(QtWidgets.QMainWindow, Ui_LoginWindow):
    signal = pyqtSignal()
    forget_signal = pyqtSignal()

    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.setWindowTitle('校园一卡通管理系统登录')  # 设置窗口标题
        self.setWindowIcon(QIcon('logo.png'))  # 设置窗口图标
        # self.setStyleSheet('QWidget{background-color:%s}' % QColor(222, 222, 222).name())  # 设置窗口背景色
        self.setWindowOpacity(0.95)  # 设置整个计算器窗口的透明度
        ctypes.windll.shell32.SetCurrentProcessExplicitAppUserModelID("myappid")  # 任务栏图标
        self.setWindowFlags(Qt.FramelessWindowHint)
        self.show()

        self.set_input()  # 输入账号和输入密码的设置

        # 将信号连接到对应槽函数
        self.login_button.clicked.connect(self.username_password)
        self.forget.clicked.connect(self.forget_password)
        self.login_close.clicked.connect(QCoreApplication.instance().quit)

    # 输入账号和输入密码的设置
    def set_input(self):
        self.input_username.setPlaceholderText("请输入账号")  # 灰色的占位符
        self.input_password.setPlaceholderText("请输入密码")  # 灰色的占位符
        self.input_username.setContextMenuPolicy(Qt.NoContextMenu)  # 禁止复制粘贴
        self.input_password.setContextMenuPolicy(Qt.NoContextMenu)  # 禁止复制粘贴
        self.input_password.setEchoMode(QLineEdit.Password)  # 输入时呈现圆点，不显示密码

    # 检查用户名与密码是否匹配
    def username_password(self):
        if load(self.input_username.text(), self.input_password.text()):
            self.hide()
            self.signal.emit()  # 发射信号
        else:
            QMessageBox.warning(self, "错误", "账号或密码错误！")
            self.input_password.setText('')
        pass

    # 忘记密码按钮触发的信号
    def forget_password(self):
        self.hide()
        self.forget_signal.emit()  # 发射忘记密码信号

    # 键盘登录
    def keyPressEvent(self, event):
        QWidget.keyPressEvent(self, event)
        key = event.key()
        if key == Qt.Key_Enter:
            self.username_password()

三、忘记密码窗口（ForgetWindow.py）

  忘记密码窗口负责校验密保问题，判断用户是否拥有修改密码权限。

在这里插入代码片from PyQt5 import QtWidgets
from PyQt5.QtCore import *
from PyQt5.QtGui import *
import ctypes
import re
from PyQt5.QtWidgets import QMessageBox, QWidget, QLineEdit
from Functions import SecurityQuestion, ResetPassword
from SecurityQuestion_UI import Ui_SecurityQuestionWindow
from Reset_UI import Ui_ResetWindow


global username_changing


class SecurityQuestionWindow(QtWidgets.QMainWindow, Ui_SecurityQuestionWindow):
    signal = pyqtSignal()
    back_signal = pyqtSignal()

    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.setWindowTitle('忘记密码')  # 设置窗口标题
        self.setWindowIcon(QIcon('logo.png'))  # 设置窗口图标
        ctypes.windll.shell32.SetCurrentProcessExplicitAppUserModelID("myappid")  # 任务栏图标
        self.setWindowFlags(Qt.FramelessWindowHint)

        # 将信号连接到对应槽函数
        self.confirm_button.clicked.connect(self.security_question)
        self.back_button.clicked.connect(self.back_to_login)
        self.close_button.clicked.connect(QCoreApplication.instance().quit)

    # 检查用户名与密保是否匹配
    def security_question(self):
        if SecurityQuestion(self.input_username.text(), self.input_security.text()):
            global username_changing
            username_changing = self.input_username.text()
            self.input_username.setText('')
            self.input_security.setText('')
            self.hide()
            self.signal.emit()  # 发射信号
        else:
            QMessageBox.warning(self, "错误", "密保错误！")
            self.input_security.setText('')

    # 键盘确认
    def keyPressEvent(self, event):
        QWidget.keyPressEvent(self, event)
        key = event.key()
        if key == Qt.Key_Enter:
            self.security_question()

    def back_to_login(self):
        self.hide()
        self.back_signal.emit()  # 触发返回登陆界面的信号


class ResetWindow(QtWidgets.QMainWindow, Ui_ResetWindow):
    signal = pyqtSignal()
    back_signal = pyqtSignal()

    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.setWindowTitle('重置密码')  # 设置窗口标题
        self.setWindowIcon(QIcon('logo.png'))  # 设置窗口图标
        # self.setStyleSheet('QWidget{background-color:%s}' % QColor(222, 222, 222).name())  # 设置窗口背景色
        ctypes.windll.shell32.SetCurrentProcessExplicitAppUserModelID("myappid")  # 任务栏图标
        self.setWindowFlags(Qt.FramelessWindowHint)

        self.set_input()  # 输入密码的设置

        # 将信号连接到对应槽函数
        self.submit_button.clicked.connect(self.Reset)
        self.back_button.clicked.connect(self.back_to_login)
        self.close_button.clicked.connect(QCoreApplication.instance().quit)

    def set_input(self):
        self.input_pass1.setContextMenuPolicy(Qt.NoContextMenu)  # 禁止复制粘贴
        self.input_pass2.setContextMenuPolicy(Qt.NoContextMenu)  # 禁止复制粘贴
        self.input_pass1.setEchoMode(QLineEdit.Password)  # 输入时呈现圆点，不显示密码
        self.input_pass2.setEchoMode(QLineEdit.Password)  # 输入时呈现圆点，不显示密码

    # 检查用户名与密保是否匹配
    def Reset(self):
        regx = re.compile(r"^[a-zA-Z]\w{5,14}")
        global username_changing
        if username_changing != '' and self.input_pass1.text() == self.input_pass2.text() and regx.match(self.input_pass1.text()) is not None:
            if regx.match(self.input_pass1.text()).group() == self.input_pass1.text():
                ResetPassword(username_changing, self.input_pass1.text())
                QMessageBox.information(self, "信息", "修改密码成功！")
                self.input_pass1.setText('')
                self.input_pass2.setText('')
                self.hide()
                self.signal.emit()  # 发射信号
            else:
                QMessageBox.warning(self, "警告", "请输入符合条件的密码！")
                self.input_pass1.setText('')
                self.input_pass2.setText('')
        else:
            if self.input_pass1.text() != self.input_pass2.text():
                QMessageBox.warning(self, "警告", "两次输入的密码不一致，请重新输入！")
            else:
                QMessageBox.warning(self, "警告", "请输入符合条件的密码！")
            self.input_pass1.setText('')
            self.input_pass2.setText('')

    # 键盘确认
    def keyPressEvent(self, event):
        QWidget.keyPressEvent(self, event)
        key = event.key()
        if key == Qt.Key_Enter:
            self.Reset()

    def back_to_login(self):
        self.hide()
        self.back_signal.emit()  # 触发返回登陆界面的信号

四、主窗口（MainWindow.py）（重点）

  这部分是我重点负责的部分，我将比较详细的解释代码。

1. 成员变量

# 在PyQt5里创建的是QMainWindow，不是Widget，需要注意的是，创建的元素一定要调用正确。
class MainWindow(QMainWindow, Ui_MainWindow):
    insert_signal = pyqtSignal()    # 定义插入信号
    batch_signal = pyqtSignal()     # 定义批量插入信号
    query_flag = 0      # 查询标记
    check_list = []     # 确认列表
    delete_f = -1       # 批量删除选定初始行
    delete_t = -1       # 批量删除选定末尾行

2. 构造函数

  注释写的比较明了，就不过多赘述。

    def __init__(self):
        # 初始化窗口
        super().__init__()
        self.setupUi(self)
        self.setWindowTitle('校园一卡通管理系统')  # 设置窗口标题
        self.setWindowIcon(QIcon('logo.png'))  # 设置窗口图标
        self.setWindowOpacity(1)  # 设置整个窗口的透明度为不透明
        # 初始化编辑框
        self.lineEdit_id.setText("")
        self.lineEdit_name.setText("")
        self.lineEdit_phonenumber.setText("")
        # 初始化下拉框
        self.comboBox_sex.addItems(["", "男", "女"])
        self.comboBox_college.addItems(["", "计算机学院", "机械学院", "外国语学院"])
        self.comboBox_major.addItems(["", "物联网工程", "计算机科学与技术", "软件工程", "通信工程", "信息安全"])
        self.comboBox_sex_2.addItems(["", "充值", "饮食", "电费", "水费", "车费"])
        # 初始化标记
        self.query_flag = 0
        self.check_list = []
        self.delete_f = -1
        self.delete_t = -1

        # 信号和槽函数绑定
        # 栏目1（学生基本信息）
        self.pushButton_query.clicked.connect(self.query)  # 为按钮添加点击事件
        self.comboBox_college.activated.connect(self.update_ComboBox)   # 如果下拉框被激活就更新子下拉框
        self.pushButton_insert.clicked.connect(self.insert_button_signal)  # 弹出插入窗口
        self.pushButton_delete.clicked.connect(self.deleteConfirm)  # 删除操作
        self.tableWidget.itemChanged.connect(self.update_tableWidget)  # 查找操作
        self.tableWidget.cellPressed.connect(self.update_flag)  # 更新查找标记
        self.tableWidget.itemSelectionChanged.connect(self.update_selected) # 更新选择区域
        self.pushButton_batch.clicked.connect(self.batch_button_signal) # 批量插入
        # 栏目2（交易记录）
        self.pushButton_query_2.clicked.connect(self.upadate_tableWidget2)  # 查记录
        # 栏目3（充值界面按钮绑定）
        self.top_up_button.clicked.connect(self.top_up) # 绑定充值

3. 更新选择区域

  当表格中选中区域发生改变时，更新当前选择区域。

    # 更新选择区域
    def update_selected(self):
        list = self.tableWidget.selectedRanges()
        for temp in list:
            self.delete_f = temp.topRow()
            self.delete_t = temp.bottomRow()

4. 便捷修改记录

  获取当前选中行列，更新当前行列中的数据。

    # 修改表信息
    def update_tableWidget(self):
        # print(self.tableWidget.selectedItems()==None)
        # print("x: " + str(self.tableWidget.selectedIndexes()[0].row()) + ",  y: " + str(self.tableWidget.selectedIndexes()[0].column()))
        # print("请求修改信息")
        if self.query_flag == 1:
            return
        i = self.tableWidget.currentIndex().row()
        j = self.tableWidget.currentIndex().column()
        lable = ['学号', '姓名', '性别', '学院', '专业', '手机号', '余额']
        # print(self.tableWidget.item(i,0).text(),lable[j],self.tableWidget.item(i,j).text())
        DataUpdate(self.tableWidget.item(i, 0).text(), lable[j], self.tableWidget.item(i, j).text())

5. 更新标记 & 发射信号

  当表格中有元素被按下就更新查询标记，防止查询bug。另外两个信号是用来弹出窗口的。

# 更新标志
    def update_flag(self):
        # print("flag")
        self.query_flag = 0

    # 批量插入按钮发射信号
    def batch_button_signal(self):
        self.batch_signal.emit()  # 发射信号

    # 插入按钮发射信号
    def insert_button_signal(self):
        self.insert_signal.emit()  # 发射信号

6. 单个删除 & 批量删除

  单个选择删除和批量删除二者通过当前选择行列来判断是哪一种。同时删除之前会弹出消息框来确认是否删除。批量删除的实现是通过检查查看复选框的选择状态来实现批量删除的。

    # 批量删除
    def delete2(self):
        if self.delete_f == -1 and self.delete_t == -1:
            # QMessageBox.warning(self, "失败", "请框选需要删除的记录！")
            return False
        for i in range(self.delete_f,self.delete_t + 1):
            DeleteData(str(self.tableWidget.item(i, 0).text()))
        self.query()
        QMessageBox.information(self, "信息", "删除记录成功！")
        self.delete_t = -1
        self.delete_f = -1
        return True

    # 删除确认
    def deleteConfirm(self):
        message_box = QMessageBox()
        message_box.setStandardButtons(QMessageBox.Yes | QMessageBox.No)
        message_box.setWindowTitle("提示")
        message_box.setText("确认删除？")
        message_box.setWindowIcon(QIcon('logo.png'))
        yes = message_box.button(QMessageBox.Yes)
        yes.clicked.connect(self.delete)  # 点击此按钮则退出程序
        message_box.exec()
        
	# 删除按钮发射信号
    def delete(self):
        if self.delete2():
            return
        flag = 0
        for i in range(0, self.tableWidget.rowCount()):
            if self.check_list[i].checkState() == 2:
                flag = 1
                DeleteData(str(self.tableWidget.item(i, 0).text()))
        if flag == 1:
            self.query()
            QMessageBox.information(self, "提示", "删除记录成功！")
            self.delete_t = -1
            self.delete_f = -1
        else:
            QMessageBox.critical(self, "失败", "请勾选需要删除的记录！")

7. 下拉框元素更新

    # 下拉框增加元素
    def update_ComboBox(self):
        coll = self.comboBox_college.currentText()
        self.comboBox_major.clear()
        if coll == "计算机学院":
            self.comboBox_major.addItems(["物联网工程", "计算机科学与技术", "软件工程", "通信工程", "信息安全"])
        elif coll == "机械学院":
            self.comboBox_major.addItems(["机械电子工程", "测控技术与仪器", "机械设计制造", "工业设计"])
        elif coll == "外国语学院":
            self.comboBox_major.addItems(["英语", "俄语", "日语", "法语", "西班牙语"])

8. 模糊查找

  通过获取编辑框和下拉框的数据来进行查找，通过输入的字段来改变sql语句进行模糊查询。

    # 模糊查找
    def query(self):
        self.query_flag = 1
        id = self.lineEdit_id.text()
        name = self.lineEdit_name.text()
        sex = self.comboBox_sex.currentText()
        coll = self.comboBox_college.currentText()
        major = self.comboBox_major.currentText()
        number = self.lineEdit_phonenumber.text()
        result = informationInput(id, name, sex, coll, major, number)

        self.check_list.clear()
        for i in range(0, result.__len__()):
            # 下面六行用于生成居中的checkbox，不知道有没有别的好方法
            ck = QCheckBox()
            self.check_list.append(ck)

        # 调用数据库
        self.tableWidget.setRowCount(result.__len__())
        self.tableWidget.setColumnCount(8)
        self.tableWidget.setHorizontalHeaderLabels(['学号', '姓名', '性别', '学院', '专业', '手机号', '余额', '选择'])
        self.tableWidget.setColumnWidth(0, 200)
        self.tableWidget.setColumnWidth(1, 100)
        self.tableWidget.setColumnWidth(2, 50)
        self.tableWidget.setColumnWidth(3, 160)
        self.tableWidget.setColumnWidth(4, 160)
        self.tableWidget.setColumnWidth(5, 150)
        self.tableWidget.setColumnWidth(6, 120)
        self.tableWidget.setColumnWidth(7, 87)
        for i in range(0, self.tableWidget.rowCount()):
            self.tableWidget.setRowHeight(i, 44)

        lable1 = ['学号', '姓名', '性别', '学院', '专业', '手机号', '余额', '选择']
        for i in range(result.__len__()):
            temp = result[i]
            for j in range(0, lable1.__len__()):
                if j < 7:
                    if temp[lable1[j]] is not None:
                        self.tableWidget.setItem(i, j, QTableWidgetItem(str(temp[lable1[j]])))
                    else:
                        self.tableWidget.setItem(i, j, QTableWidgetItem(" "))
                else:
                    h = QHBoxLayout()
                    h.setAlignment(Qt.AlignCenter)
                    h.addWidget(self.check_list[i])
                    w = QWidget()
                    w.setLayout(h)
                    self.tableWidget.setCellWidget(i, j, w)

9. 更新交易记录

  根据输入字段模糊查找交易记录。

    # 更新交易记录
    def upadate_tableWidget2(self):
        id2 = self.lineEdit_id_2.text()
        name2 = self.lineEdit_name_2.text()
        type2 = self.comboBox_sex_2.currentText()
        result = TransactionRecords(id2, name2, type2)

        # 调用数据库
        lable1 = ['学号', '姓名', '消费类型', '消费时间', '金额变动', '余额']
        self.tableWidget_2.setRowCount(result.__len__())
        self.tableWidget_2.setColumnCount(6)
        self.tableWidget_2.setHorizontalHeaderLabels(lable1)
        self.tableWidget_2.setColumnWidth(0, 200)
        self.tableWidget_2.setColumnWidth(1, 150)
        self.tableWidget_2.setColumnWidth(2, 100)
        self.tableWidget_2.setColumnWidth(3, 300)
        self.tableWidget_2.setColumnWidth(4, 138)
        self.tableWidget_2.setColumnWidth(5, 138)
        for i in range(0, self.tableWidget_2.rowCount()):
            self.tableWidget_2.setRowHeight(i, 45)

        for i in range(result.__len__()):
            temp = result[i]
            for j in range(0, lable1.__len__()):
                if temp[j] is not None:
                    if j == 4:
                        money = str(temp[j])
                        if money[0] != '-':
                            money = "+ " + money
                        else:
                            money = "- " + money[1:]
                        self.tableWidget_2.setItem(i, 4, QTableWidgetItem(money))
                    else:
                        self.tableWidget_2.setItem(i, j, QTableWidgetItem(str(temp[j])))
                else:
                    self.tableWidget_2.setItem(i, j, QTableWidgetItem(" "))

10. 充值

  通过学号和充值金额进行更新数据。

    # 充值
    def top_up(self):
        username = self.top_up_id.text()
        money = self.top_up_money.text()
        result = Recharge(money, username)
        if result == 0:
            QMessageBox.warning(self, "错误", "请输入有效学号！")
            self.top_up_id.setText('')
        elif result == 1:
            QMessageBox.warning(self, "错误", "请输入正确金额！")
            self.top_up_money.setText('')
        else:
            QMessageBox.information(self, "成功", "充值成功！")
            self.top_up_id.setText('')
            self.top_up_money.setText('')

五、单条插入窗口（InsertWindow.py）

from PyQt5.QtCore import pyqtSignal
from PyQt5.QtGui import QStandardItemModel, QStandardItem, QIcon
from PyQt5.QtWidgets import *
from Insert_UI import Ui_InsertWindow
from Functions import *


class InsertWindow(QMainWindow, Ui_InsertWindow):
    inserted_signal = pyqtSignal()

    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.setWindowTitle('校园一卡通管理系统')  # 设置窗口标题
        self.setWindowIcon(QIcon('logo.png'))  # 设置窗口图标
        # self.setWindowIcon(QIcon('1.jpg'))  # 设置窗口图标
        # self.setStyleSheet('QWidget{background-color:%s}' % QColor(222, 222, 222).name())  # 设置窗口背景色
        self.setWindowOpacity(1)  # 设置整个计算器窗口的透明度
        # ctypes.windll.shell32.SetCurrentProcessExplicitAppUserModelID("myappid")  # 任务栏图标
        self.comboBox_sex.addItems([" ", "男", "女"])
        self.comboBox_college.addItems([" ", "计算机学院", "机械学院", "外国语学院"])
        self.comboBox_major.addItems([" ", "物联网工程", "计算机科学与技术", "软件工程", "通信工程", "信息安全"])
        # self.show()

        # 按钮绑定
        self.pushButton_insert.clicked.connect(self.insert)  # 插入事件
        self.comboBox_college.activated.connect(self.update_ComboBox)

    # 下拉框增加元素
    def update_ComboBox(self):
        coll = self.comboBox_college.currentText()
        self.comboBox_major.clear()
        if coll == "计算机学院":
            self.comboBox_major.addItems(["物联网工程", "计算机科学与技术", "软件工程", "通信工程", "信息安全"])
        elif coll == "机械学院":
            self.comboBox_major.addItems(["机械电子工程", "测控技术与仪器", "机械设计制造", "工业设计"])
        elif coll == "外国语学院":
            self.comboBox_major.addItems(["英语", "俄语", "日语", "法语", "西班牙语"])

    def insert(self):
        # 数据库交互
        id = self.lineEdit_id.text()
        name = self.lineEdit_name.text()
        sex = self.comboBox_sex.currentText()
        coll = self.comboBox_college.currentText()
        major = self.comboBox_major.currentText()
        number = self.lineEdit_phonenumber.text()
        Imformation(id, name, sex, coll, major, number)
        self.inserted_signal.emit()
        QMessageBox.information(self, "成功", "插入记录成功！")
        pass

六、批量导入窗口（BatchInsertedWindow.py）

import os

from PyQt5 import QtWidgets
from PyQt5.QtCore import *
from PyQt5.QtGui import *
import ctypes
import time
from PyQt5.QtWidgets import QLineEdit, QMessageBox, QWidget

from BatchInserted_UI import Ui_BatchInsertedWindow


class BatchInsertedWindow(QtWidgets.QMainWindow, Ui_BatchInsertedWindow):
    step = 0
    first = 0
    last = 0
    number = 1

    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.setWindowTitle('校园一卡通管理系统')  # 设置窗口标题
        self.setWindowIcon(QIcon('logo.png'))  # 设置窗口图标
        ctypes.windll.shell32.SetCurrentProcessExplicitAppUserModelID("myappid")  # 任务栏图标

        # 将信号连接到对应槽函数
        self.batch_insert_button.clicked.connect(self.BatchInsert)
        # self.batch_insert_button.clicked.connect(self.timerEvent)

        self.timer = QBasicTimer
        self.pbar.setValue(self.step)

    # 键盘登录
    def keyPressEvent(self, event):
        QWidget.keyPressEvent(self, event)
        key = event.key()
        if key == Qt.Key_Enter:
            self.BatchInsert()

    def BatchInsert(self):
        path = self.insert_path.text()
        self.first = self.lineEdit.text()
        self.last = self.lineEdit_2.text()
        self.StudentInformation(path, self.first, self.last)

    def StudentInformation(self, path, start, last):
        import pymysql
        from openpyxl import load_workbook
        from datetime import date, datetime
        db = pymysql.connect("localhost", "root", "123456", "onecartoon")
        # 使用cursor()方法获取操作游标
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        if path == "":
            path = ".//student.xlsx"
        if os.path.exists(path):
            workbook = load_workbook(filename=path)
            sheet1 = workbook["sheet1"]
            if start == "":
                start = 1
            if last == "":
                last = 1
            start = int(start)
            last = int(last)
            self.step = 0
            self.first = start
            self.last = last
            from builtins import str
            list1 = []
            # from builtins import range
            for q in range(start, last + 1):
                list1.append([])
                # print(q)
                for row in sheet1.iter_rows(min_row=q, max_row=q):
                    # print(row)
                    for cell in row:
                        # from builtins import str
                        string = str(cell.value)
                        # print(string)
                        # print(string)
                        list1[q - start].append(string)
            # print(list1)
            cnt = 0
            for i in range(0, len(list1)):
                date = list1[i]
                # print(i, date)
                # print(date)
                str1 = ""
                self.number = i + 1
                for j in range(6):
                    str1 += "'" + date[j] + "',"

                str1 = str1[:-1]
                sql_str = "INSERT INTO 学生基本信息(学号, 姓名, 性别, 学院, 专业, 手机号, 余额) VALUES(%s,%s)" % (str1, int(date[6]))
                try:
                    # 执行sql语句
                    db.ping(reconnect=True)
                    cursor.execute(sql_str)
                    db.commit()
                    cnt += 1
                except Exception as e:
                    # 如果发生错误则回滚
                    print("修改失败！")
                    print(e)
                    db.rollback()
                    # 关闭数据库连接
                    db.close()

                # print(len(list1))
                self.step = self.number / (self.last + 1 - self.first) * 100
                self.pbar.setValue(self.step)
                if self.step == 100.0:
                    time.sleep(0.3)
                    text = '成功插入' + str(cnt) + '条记录！'
                    QMessageBox.warning(self, "成功", text)
                    self.step = 0
                    self.pbar.setValue(self.step)

        else:
            QMessageBox.warning(self, "错误", "请输入正确路径！")

七、与数据库交互的各种方法（Functions.py）

  与数据库进行增删改查等操作的各种方法。

from datetime import datetime
from multiprocessing import connection

Cno = '0'  # 用于最后的注销


# 注册账号密码
# 对应参数分别是学号,密码,密保
def register(studentNumber, password, SecretGuard):
    import pymysql
    db = pymysql.connect("localhost", "root", "123456", "onecartoon")
    # 使用cursor()方法获取操作游标
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    ch = "select 学号 from 学生基本信息 where 学号='%s'" % studentNumber
    cursor.execute(ch)
    ch1 = cursor.fetchone()
    if ch1 == None:
        print("学号输入错误")
    else:
        sql = """INSERT INTO 注册(学号,
                      密码,密保)
                      VALUES ('%s', '%s', '%s')""" % (studentNumber, password, SecretGuard)
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 提交到数据库执行
            db.commit()
        except:
            # 如果发生错误则回滚
            db.rollback()
            # 关闭数据库连接
            db.close()
        else:
            print("注册成功！")
        return


# 登录
# 需要对应的变量来存放登录的账号
def load(studentNumber, password):
    global Cno
    import pymysql.cursors
    connection = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='onecartoon',
                                 charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = connection.cursor()
    # pattern = re.compile()
    sqlDL = "select 学号  from 注册 where 注册.学号='%s' and 注册.密码='%s'" % (studentNumber, password)
    cur.execute(sqlDL)
    jilu = cur.fetchone()  # 注册表中没有对应账号和密码
    if jilu == None:
        return False
    elif jilu != None:
        Cno = studentNumber
        return True


# 信息的录入
# 对应参数分别是 学号 性别 姓名 学院 专业 手机号 余额
def Imformation(studentNumber, name, gender, College, major, mobileNumber):
    import pymysql
    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "123456", "onecartoon")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    money = 100
    lable = [studentNumber, name, gender, College, major, mobileNumber]
    lable1 = ['学号', '姓名', '性别', '学院', '专业', '手机号']
    str = ''  # 初始化为空串
    str1 = ''
    flag = 0
    for i in range(4, 6):
        # print(i)
        if lable[i] != '':
            str1 += lable1[i] + ","
            flag = 1
            str += "'" + lable[i] + "',"
    str = str[:-1]
    str1 = str1[:-1]
    # print(studentNumber)
    # # SQL 插入语句
    if flag == 0:
        sql = "INSERT INTO 学生基本信息(学号,姓名,性别,学院,余额) VALUES('%s','%s','%s','%s',%s)" % (
        studentNumber, name, gender, College, money)
    else:
        sql = "INSERT INTO 学生基本信息(学号,姓名,性别,学院,%s,余额) VALUES ('%s','%s','%s','%s',%s, %s)" % (
            str1, studentNumber, name, gender, College, str, money)
    try:
        # 执行sql语句
        print(sql)
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # 如果发生错误则回滚
        print("信息录入错误")
        db.rollback()
        # 关闭数据库连接
        db.close()
    else:
        print('信息录入成功')
        return


# 对应参数是 属性和具体的值   比如性别，男
def Check(fun, natural):
    import pymysql.cursors
    connection = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='onecartoon',
                                 charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = connection.cursor()
    if fun == '性别':
        sqlJiaoYi = "select * from 交易记录 where 性别='%s'" % (natural)
    elif fun == '学号':
        sqlJiaoYi = "select * from 交易记录 where 学号='%s'" % (natural)
    elif fun == '消费类型':
        sqlJiaoYi = "select * from 交易记录 where 消费类型='%s'" % (natural)
    elif fun == '1':
        sqlJiaoYi = "select * from 交易记录"
    cur.execute(sqlJiaoYi)
    result = cur.fetchall()
    for data in result:
        print(data)
    cur.close()
    connection.close()


# Check('性别', '女')
# 充值校园卡   要改
# 对应参数是 钱数 学号 时间
def Recharge(money, nameNumber):
    import pymysql
    db = pymysql.connect("localhost", "root", "123456", "onecartoon")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    time = datetime.now()
    ch = "select 学号 from 学生基本信息 where 学号='%s'" % (nameNumber)
    cursor.execute(ch)  # 加载到数据库中
    ch1 = cursor.fetchone()  # 将数据拿出来
    if ch1 is None:
        return 0
    else:
        sql1 = "select 学号,余额 from 学生基本信息 where 学号='%s'" % (nameNumber)
        cursor.execute(sql1)
        ch3 = cursor.fetchone()
        print(ch3)
        sql = "update 学生基本信息 set 余额=余额+%s where 学号='%s'" % (money, nameNumber)
        sql3 = "INSERT INTO 交易记录(学号,消费类型,金额变动,消费时间,余额) VALUES ('%s','充值',%s,'%s',%s)" % (ch3[0], money, time, ch3[1] + float(money))
        print(sql)
        print(sql3)
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 提交到数据库执行
            db.commit()
            cursor.execute(sql3)
            db.commit()
            return 2
        except:
            # 如果发生错误则回滚
            db.rollback()
            # 关闭数据库连接
            db.close()
            return 1


#  修改信息
# 对应参数是学号
def Exchange(name):
    import pymysql.cursors
    connection = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='onecartoon',
                                 charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = connection.cursor()
    secretGuard = input("请输入密保：")
    sqlXH = "select 密保 from 注册 where 注册.学号='%s'" % (name)
    cur.execute(sqlXH)
    jilu = cur.fetchone()
    # for data in jilu:
    if jilu == {'密保': secretGuard}:
        NewpPassword = input("请输入新密码：")
        sqlNewPassword = "update 注册 set 密码='%s' where 学号='%s'" % (NewpPassword, name)
        print("修改成功")
    elif jilu != {'密码': secretGuard}:
        print("密保错误")
        Exchange(name)
    cur.close()
    connection.close()


# 基本信息表的输出
# 对应参数是要输出的表 比如要输出学生基本信息  str=学生基本信息
def inputInformation(str):
    import pymysql.cursors
    connection = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='onecartoon',
                                 charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    try:
        cursor = connection.cursor()
        sql = "select * from %s" % (str)
        cursor.execute(sql)
        result = cursor.fetchall()
        # 信息的输出
        for data in result:
            print(data)
    except Exception as e:
        print("信息输入错误")
        # 提示错误信息
        # print(e)
        cursor.close()
        connection.close()
    return


# str = input("请输入所要输出的表的名字：")
# inputInformation(str)


# 注销账号
def logOut():
    import pymysql
    db = pymysql.connect("localhost", "root", "123456", "onecartoon")
    # 使用cursor()方法获取操作游标
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    sql = "delete from 学生基本信息 where 学号='%s'" % Cno
    sql1 = "delete from 注册 where 学号='%s'" % Cno
    sql2 = "delete from 交易记录 where 学号='%s'" % Cno
    try:
        # 执行sql语句
        cursor.execute(sql1)
        # 提交到数据库执行
        db.commit()
        cursor.execute(sql2)
        db.commit()
        cursor.execute(sql)
        db.commit()
    except:
        # 如果发生错误则回滚
        print("注销失败！")
        db.rollback()
        # 关闭数据库连接
        db.close()
    else:
        print("注销成功！")


# 学生基本信息查询
def informationInput(studentNumber, name, gender, College, major, mobileNumber):
    lable = [studentNumber, name, gender, College, major, mobileNumber]
    lable1 = ['学号', '姓名', '性别', '学院', '专业', '手机号']
    str = ""  # 初始化为空串
    flag = 0

    for i in range(6):
        if lable[i] != '':
            flag = 1
            # str += (lable1[i] + "='" + lable[i] + "' and ")
            str += (lable1[i] + " LIKE '%" + lable[i] + "%' and ")

    str = str[:-5]
    # print(str)

    import pymysql.cursors
    connection = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='onecartoon',
                                 charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    try:
        cursor = connection.cursor()
        if flag == 0:
            sql = "select * from 学生基本信息"
        else:
            sql = "select * from 学生基本信息 where %s" % str
        cursor.execute(sql)
        result = cursor.fetchall()
        # 信息的输出
        return result
    except Exception as e:
        print("信息输入错误")
        # 提示错误信息
        print(e)
        cursor.close()
        connection.close()


def DataUpdate(studentNumber, attribute, val):
    import pymysql
    db = pymysql.connect("localhost", "root", "123456", "onecartoon")
    # 使用cursor()方法获取操作游标
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    sql = "update 学生基本信息 set %s='%s' where 学号='%s'" % (attribute, val, studentNumber)
    print(sql)
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # 如果发生错误则回滚
        print("修改失败！")
        db.rollback()
        # 关闭数据库连接
        db.close()
    else:
        print("修改成功！")


def DeleteData(studentNumber):
    import pymysql
    db = pymysql.connect("localhost", "root", "123456", "onecartoon")
    # 使用cursor()方法获取操作游标
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    # print(studentNumber)
    sql = "DELETE FROM 学生基本信息 WHERE 学号='%s'" % (studentNumber)
    sql1 = "DELETE FROM 交易记录 WHERE 学号='%s'" % (studentNumber)
    try:
        # 执行sql语句
        cursor.execute(sql1)
        # 提交到数据库执行
        db.commit()
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # 如果发生错误则回滚
        print("删除信息失败！")
        #         db.rollback()
        #         # 关闭数据库连接
        db.close()
    else:
        print("删除成功！")


# 交易记录输出
def TransactionRecords(studentNumber, name, ConsumptionType):
    lable = [studentNumber, name, ConsumptionType]
    lable1 = ['学号', '姓名', '消费类型']
    str = ""  # 初始化为空串
    flag = 0
    for i in range(3):
        if lable[i] != "":
            flag = 1
            if i == 0:
                str += "学生基本信息." + lable1[i] + " LIKE '%" + lable[i] + "%' and "
            elif i == 1:
                str += "学生基本信息." + lable1[i] + " LIKE '%" + lable[i] + "%' and "
            elif i == 2:
                str += lable1[i] + " LIKE '%" + lable[i] + "%' and "

    if str.__len__()>5:
        str = str[:-5]
    import pymysql.cursors
    connection = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='onecartoon')
    cur = connection.cursor()
    if flag == 0:
        sql = "select 学生基本信息.学号,学生基本信息.姓名,消费类型,消费时间,金额变动,交易记录.余额 from 学生基本信息,交易记录 where 学生基本信息.学号=交易记录.学号"
    else:
        sql = "select 学生基本信息.学号,学生基本信息.姓名,消费类型,消费时间,金额变动,交易记录.余额 from 交易记录,学生基本信息 where 学生基本信息.学号=交易记录.学号 and %s" % str
    # print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    # print(result)
    # for data in result:
    #     print(data)
    # 更改字段
    list = []
    for i in range(result.__len__()):
        temp = result[i]
        list.append([])
        for j in range(0, 6):
            list[i].append(temp[j])
    return list
    cur.close()
    connection.close()

# 检查密保问题
def SecurityQuestion(name, secretGuard):
    import pymysql.cursors
    connection = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='onecartoon',
                                 charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = connection.cursor()
    sqlXH = "select 密保 from 注册 where 注册.学号='%s'" % name
    cur.execute(sqlXH)
    jilu = cur.fetchone()
    # for data in jilu:
    cur.close()
    connection.close()
    if jilu == {'密保': secretGuard}:
        return True
    else:
        return False


# 重置密码
def ResetPassword(name, new_password):
    import pymysql
    db = pymysql.connect("localhost", "root", "123456", "onecartoon")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    sqlNewPassword = "update 注册 set 密码='%s' where 学号='%s'" % (new_password, name)
    cursor.execute(sqlNewPassword)  # 提交到数据库执行
    db.commit()
    # 关闭数据库连接
    db.close()


def StudentInformation(path, start, last):
    import pymysql
    from openpyxl import load_workbook
    from datetime import date, datetime
    db = pymysql.connect("localhost", "root", "123456", "onecartoon")
    # 使用cursor()方法获取操作游标
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    print("666")
    if path == "":
        path = "student.xlsx"
    workbook = load_workbook(filename=".\\" + path)
    # print(workbook.sheetnames)
    sheet1 = workbook["sheet1"]
    print("999")
    if start == "":
        start = 1
    if last == "":
        last = 1
    start = int(start)
    last = int(last)
    print("start:" + str(start) + ",  end:" + str(last))
    list1 = []
    # from builtins import range
    for q in range(start, last+1):
        list1.append([])
        # print(q)
        for row in sheet1.iter_rows(min_row=q, max_row=q):
            # print(row)
            for cell in row:
                # from builtins import str
                string = str(cell.value)
                # print(string)
                # print(string)
                list1[q - start].append(string)
    print(list1)
    for i in range(0, len(list1)):
        date = list1[i]
        # print(i, date)
        # print(date)
        str1 = ""

        for j in range(6):
            str1 += "'" + date[j] + "',"

        str1 = str1[:-1]
        sql_str = "INSERT INTO 学生基本信息(学号, 姓名, 性别, 学院, 专业, 手机号, 余额) VALUES(%s,%s)" % (str1, int(date[6]))
        print(sql_str)
        try:
            # 执行sql语句
            db.ping(reconnect=True)
            cursor.execute(sql_str)
            db.commit()
        except Exception as e:
            # 如果发生错误则回滚
            print("修改失败！")
            print(e)
            db.rollback()
            # 关闭数据库连接
            db.close()

———2020.12.27（罗涵）

THE END