java一卡通项目 一卡通项目代码

软件实习项目4——校园一卡通管理系统（代码实现）

• 一、窗口的互相调用与各种功能的实现

• 1、main.py
• 2、LoginWindow.py
• 3、ForgetWindow.py
• 4、MainWindow.py
• 5、InsertWindow.py （单个记录的插入）
• 6、BatchInsertedWindow.py（Excel表格批量插入数据）

• 二、连接和操作数据库的方法

• Functions.py

(省去了各UI文件以及其转成的py文件)

一、窗口的互相调用与各种功能的实现

1、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_())

2、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()

3、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()  # 触发返回登陆界面的信号

4、MainWindow.py

import random

from PyQt5.QtCore import pyqtSignal, Qt
from PyQt5.QtGui import QStandardItemModel, QStandardItem, QIcon
from PyQt5.QtWidgets import *
from Main_UI import Ui_MainWindow
from Functions import *


# 在PyQt5里创建的是MainWindow，不是Widget，需要注意的是，创建的元素一定要调用正确。
class MainWindow(QMainWindow, Ui_MainWindow):
    insert_signal = pyqtSignal()
    batch_signal = pyqtSignal()
    query_flag = 0
    check_list = []
    delete_f = -1
    delete_t = -1

    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(1)  # 设置整个计算器窗口的透明度
        # ctypes.windll.shell32.SetCurrentProcessExplicitAppUserModelID("myappid")  # 任务栏图标
        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)

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

    # 更新交易记录
    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(" "))


    # 修改表信息
    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())

        # print("修改信息成功")
        # print(str(self.tableWidget.currentIndex().row()))
        # print(self.tableWidget.rowCount())

    # 更新标志
    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()  # 发射信号

    # 批量删除
    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, "失败", "请勾选需要删除的记录！")

    # 下拉框增加元素
    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 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)

    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('')

5、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

6、BatchInsertedWindow.py（Excel表格批量插入数据）

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 timerEvent(self, event):
    #     if self.step == 100:
    #         QMessageBox.warning(self, "成功", "批量插入成功！")
    #         self.step = 0
    #         self.pbar.setValue(self.step)
    #     else:
    #         self.step = self.number / (self.last - self.first)  * 100
    #         self.pbar.setValue(self.step)

    def StudentInformation(self, path, start, last):
        import pymysql
        from openpyxl import load_workbook
        from datetime import date, datetime
        db = pymysql.connect("localhost", "root", "161116", "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", "161116", "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='161116', 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", "161116", "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='161116', 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", "161116", "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='161116', 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='161116', 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", "161116", "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='161116', 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", "161116", "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", "161116", "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='161116', 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='161116', 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", "161116", "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", "161116", "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（殷越）