alterBook.py

# Form implementation generated from reading ui file 'alterBook.ui'
#
# Created by: PyQt6 UI code generator 6.4.2
#
# WARNING: Any manual changes made to this file will be lost when pyuic6 is
# run again.  Do not edit this file unless you know what you are doing.


from PyQt6 import QtCore, QtGui, QtWidgets


class Ui_Form(QtWidgets.QWidget):
    def __init__(self, cursor):
        super(Ui_Form, self).__init__()
        self.cursor = cursor
        self.img = None
        self.setupUi(self)
        self.initBookTable()

    def setupUi(self, Form):
        Form.setObjectName("Form")
        Form.resize(800, 600)
        self.BookTable = QtWidgets.QTableWidget(parent=Form)
        self.BookTable.setGeometry(QtCore.QRect(40, 111, 721, 211))
        self.BookTable.setObjectName("BookTable")
        self.BookTable.setColumnCount(5)
        self.BookTable.setRowCount(0)
        item = QtWidgets.QTableWidgetItem()
        self.BookTable.setHorizontalHeaderItem(0, item)
        item = QtWidgets.QTableWidgetItem()
        self.BookTable.setHorizontalHeaderItem(1, item)
        item = QtWidgets.QTableWidgetItem()
        self.BookTable.setHorizontalHeaderItem(2, item)
        item = QtWidgets.QTableWidgetItem()
        self.BookTable.setHorizontalHeaderItem(3, item)
        item = QtWidgets.QTableWidgetItem()
        self.BookTable.setHorizontalHeaderItem(4, item)
        self.groupBox = QtWidgets.QGroupBox(parent=Form)
        self.groupBox.setGeometry(QtCore.QRect(40, 20, 721, 71))
        self.groupBox.setObjectName("groupBox")
        self.label = QtWidgets.QLabel(parent=self.groupBox)
        self.label.setGeometry(QtCore.QRect(20, 30, 53, 15))
        self.label.setObjectName("label")
        self.selectBid = QtWidgets.QLineEdit(parent=self.groupBox)
        self.selectBid.setGeometry(QtCore.QRect(70, 30, 51, 21))
        self.selectBid.setObjectName("selectBid")
        self.label_2 = QtWidgets.QLabel(parent=self.groupBox)
        self.label_2.setGeometry(QtCore.QRect(140, 30, 53, 15))
        self.label_2.setObjectName("label_2")
        self.selectBname = QtWidgets.QLineEdit(parent=self.groupBox)
        self.selectBname.setGeometry(QtCore.QRect(190, 30, 111, 21))
        self.selectBname.setObjectName("selectBname")
        self.label_3 = QtWidgets.QLabel(parent=self.groupBox)
        self.label_3.setGeometry(QtCore.QRect(320, 30, 53, 15))
        self.label_3.setObjectName("label_3")
        self.selectAuthor = QtWidgets.QLineEdit(parent=self.groupBox)
        self.selectAuthor.setGeometry(QtCore.QRect(370, 30, 111, 21))
        self.selectAuthor.setObjectName("selectAuthor")
        self.label_4 = QtWidgets.QLabel(parent=self.groupBox)
        self.label_4.setGeometry(QtCore.QRect(500, 30, 53, 15))
        self.label_4.setObjectName("label_4")
        self.selectBstatus = QtWidgets.QComboBox(parent=self.groupBox)
        self.selectBstatus.setGeometry(QtCore.QRect(550, 30, 69, 22))
        self.selectBstatus.setObjectName("selectBstatus")
        self.selectBstatus.addItem("")
        self.selectBstatus.addItem("")
        self.selectBstatus.addItem("")
        self.selectBstatus.addItem("")
        self.searchSure = QtWidgets.QPushButton(parent=self.groupBox)
        self.searchSure.setGeometry(QtCore.QRect(640, 20, 81, 31))
        self.searchSure.setObjectName("searchSure")
        self.groupBox_2 = QtWidgets.QGroupBox(parent=Form)
        self.groupBox_2.setGeometry(QtCore.QRect(40, 330, 721, 251))
        self.groupBox_2.setObjectName("groupBox_2")
        self.formLayoutWidget = QtWidgets.QWidget(parent=self.groupBox_2)
        self.formLayoutWidget.setGeometry(QtCore.QRect(10, 20, 311, 221))
        self.formLayoutWidget.setObjectName("formLayoutWidget")
        self.formLayout = QtWidgets.QFormLayout(self.formLayoutWidget)
        self.formLayout.setContentsMargins(10, 10, 10, 10)
        self.formLayout.setHorizontalSpacing(10)
        self.formLayout.setVerticalSpacing(20)
        self.formLayout.setObjectName("formLayout")
        self.label_5 = QtWidgets.QLabel(parent=self.formLayoutWidget)
        self.label_5.setObjectName("label_5")
        self.formLayout.setWidget(0, QtWidgets.QFormLayout.ItemRole.LabelRole, self.label_5)
        self.alterBid = QtWidgets.QLineEdit(parent=self.formLayoutWidget)
        self.alterBid.setObjectName("alterBid")
        self.formLayout.setWidget(0, QtWidgets.QFormLayout.ItemRole.FieldRole, self.alterBid)
        self.label_6 = QtWidgets.QLabel(parent=self.formLayoutWidget)
        self.label_6.setObjectName("label_6")
        self.formLayout.setWidget(1, QtWidgets.QFormLayout.ItemRole.LabelRole, self.label_6)
        self.alterBname = QtWidgets.QLineEdit(parent=self.formLayoutWidget)
        self.alterBname.setObjectName("alterBname")
        self.formLayout.setWidget(1, QtWidgets.QFormLayout.ItemRole.FieldRole, self.alterBname)
        self.label_7 = QtWidgets.QLabel(parent=self.formLayoutWidget)
        self.label_7.setObjectName("label_7")
        self.formLayout.setWidget(2, QtWidgets.QFormLayout.ItemRole.LabelRole, self.label_7)
        self.alterAuthor = QtWidgets.QLineEdit(parent=self.formLayoutWidget)
        self.alterAuthor.setObjectName("alterAuthor")
        self.formLayout.setWidget(2, QtWidgets.QFormLayout.ItemRole.FieldRole, self.alterAuthor)
        self.label_8 = QtWidgets.QLabel(parent=self.formLayoutWidget)
        self.label_8.setObjectName("label_8")
        self.formLayout.setWidget(3, QtWidgets.QFormLayout.ItemRole.LabelRole, self.label_8)
        self.alterPublisher = QtWidgets.QLineEdit(parent=self.formLayoutWidget)
        self.alterPublisher.setObjectName("alterPublisher")
        self.formLayout.setWidget(3, QtWidgets.QFormLayout.ItemRole.FieldRole, self.alterPublisher)
        self.label_9 = QtWidgets.QLabel(parent=self.formLayoutWidget)
        self.label_9.setObjectName("label_9")
        self.formLayout.setWidget(4, QtWidgets.QFormLayout.ItemRole.LabelRole, self.label_9)
        self.alterBstatus = QtWidgets.QComboBox(parent=self.formLayoutWidget)
        self.alterBstatus.setObjectName("alterBstatus")
        self.alterBstatus.addItem("")
        self.alterBstatus.addItem("")
        self.alterBstatus.addItem("")
        self.formLayout.setWidget(4, QtWidgets.QFormLayout.ItemRole.FieldRole, self.alterBstatus)
        self.alterCover = QtWidgets.QGraphicsView(parent=self.groupBox_2)
        self.alterCover.setGeometry(QtCore.QRect(350, 60, 150, 150))
        self.alterCover.setObjectName("alterCover")
        self.alterCoverSure = QtWidgets.QPushButton(parent=self.groupBox_2)
        self.alterCoverSure.setGeometry(QtCore.QRect(350, 30, 75, 23))
        self.alterCoverSure.setObjectName("alterCoverSure")
        self.alterSure = QtWidgets.QPushButton(parent=self.groupBox_2)
        self.alterSure.setGeometry(QtCore.QRect(640, 180, 81, 31))
        self.alterSure.setObjectName("alterSure")
        self.deleteSure = QtWidgets.QPushButton(parent=self.groupBox_2)
        self.deleteSure.setGeometry(QtCore.QRect(640, 120, 81, 31))
        self.deleteSure.setObjectName("deleteSure")

        self.retranslateUi(Form)
        QtCore.QMetaObject.connectSlotsByName(Form)

        # 表格点击事件
        self.BookTable.clicked.connect(self.clickTable)
        # 搜索按钮点击事件
        self.searchSure.clicked.connect(self.selectBook)
        # 设置bid不可编辑
        self.alterBid.setReadOnly(True)
        self.alterBid.setStyleSheet("background-color: #eee;")
        # 更换封面按钮点击事件
        self.alterCoverSure.clicked.connect(self.uploadImg)
        # 修改按钮点击事件
        self.alterSure.clicked.connect(self.alter)
        # 删除按钮点击事件
        self.deleteSure.clicked.connect(self.delete)

    def retranslateUi(self, Form):
        _translate = QtCore.QCoreApplication.translate
        Form.setWindowTitle(_translate("Form", "图书信息维护"))
        item = self.BookTable.horizontalHeaderItem(0)
        item.setText(_translate("Form", "编号"))
        item = self.BookTable.horizontalHeaderItem(1)
        item.setText(_translate("Form", "书名"))
        item = self.BookTable.horizontalHeaderItem(2)
        item.setText(_translate("Form", "作者"))
        item = self.BookTable.horizontalHeaderItem(3)
        item.setText(_translate("Form", "出版社"))
        item = self.BookTable.horizontalHeaderItem(4)
        item.setText(_translate("Form", "状态"))
        self.groupBox.setTitle(_translate("Form", "筛选"))
        self.label.setText(_translate("Form", "编   号："))
        self.label_2.setText(_translate("Form", "书   名："))
        self.label_3.setText(_translate("Form", "作   者："))
        self.label_4.setText(_translate("Form", "状   态："))
        self.selectBstatus.setItemText(0, _translate("Form", "全部"))
        self.selectBstatus.setItemText(1, _translate("Form", "在馆"))
        self.selectBstatus.setItemText(2, _translate("Form", "已预约"))
        self.selectBstatus.setItemText(3, _translate("Form", "已借出"))
        self.searchSure.setText(_translate("Form", "搜索"))
        self.groupBox_2.setTitle(_translate("Form", "表单"))
        self.label_5.setText(_translate("Form", "编   号："))
        self.label_6.setText(_translate("Form", "书   名："))
        self.label_7.setText(_translate("Form", "作   者："))
        self.label_8.setText(_translate("Form", "出版社："))
        self.label_9.setText(_translate("Form", "状   态："))
        self.alterBstatus.setItemText(0, _translate("Form", "在馆"))
        self.alterBstatus.setItemText(1, _translate("Form", "已预约"))
        self.alterBstatus.setItemText(2, _translate("Form", "已借出"))
        self.alterCoverSure.setText(_translate("Form", "更换封面"))
        self.alterSure.setText(_translate("Form", "修改"))
        self.deleteSure.setText(_translate("Form", "删除"))

    # 初始化图书信息列表
    def initBookTable(self):
        self.BookTable.setRowCount(0)
        self.cursor.execute("SELECT bid, bname, author, publisher, status FROM book")
        for row in self.cursor.fetchall():
            rowPosition = self.BookTable.rowCount()
            self.BookTable.insertRow(rowPosition)
            for i in range(4):
                self.BookTable.setItem(rowPosition, i, QtWidgets.QTableWidgetItem(str(row[i])))
            if row[4] == 0:
                self.BookTable.setItem(rowPosition, 4, QtWidgets.QTableWidgetItem("在馆"))
            elif row[4] == 1:
                self.BookTable.setItem(rowPosition, 4, QtWidgets.QTableWidgetItem("已预约"))
            elif row[4] == 2:
                self.BookTable.setItem(rowPosition, 4, QtWidgets.QTableWidgetItem("已借出"))
        # 隐藏行号
        self.BookTable.verticalHeader().setVisible(False)
        # 不可编辑
        self.BookTable.setEditTriggers(QtWidgets.QAbstractItemView.EditTrigger.NoEditTriggers)
        # 均匀拉伸列宽
        self.BookTable.horizontalHeader().setSectionResizeMode(QtWidgets.QHeaderView.ResizeMode.Stretch)

    # 搜索按钮点击事件
    def selectBook(self):
        bid = self.selectBid.text()
        bname = self.selectBname.text()
        author = self.selectAuthor.text()
        status = self.selectBstatus.currentIndex() - 1
        if status == -1:
            status = ""
        sql = "select bid, bname, author, publisher, status from book where bid like '%{}%' and bname like '%{}%' and author like '%{}%' and status like '%{}%'".format(bid, bname, author, status)
        print(sql)
        self.cursor.execute(sql)
        self.BookTable.setRowCount(0)
        for row in self.cursor.fetchall():
            rowPosition = self.BookTable.rowCount()
            self.BookTable.insertRow(rowPosition)
            for i in range(4):
                self.BookTable.setItem(rowPosition, i, QtWidgets.QTableWidgetItem(str(row[i])))
            if row[4] == 0:
                self.BookTable.setItem(rowPosition, 4, QtWidgets.QTableWidgetItem("在馆"))
            elif row[4] == 1:
                self.BookTable.setItem(rowPosition, 4, QtWidgets.QTableWidgetItem("已预约"))
            elif row[4] == 2:
                self.BookTable.setItem(rowPosition, 4, QtWidgets.QTableWidgetItem("已借出"))

    # 表格点击事件
    def clickTable(self):
        row = self.BookTable.currentRow()
        self.alterBid.setText(self.BookTable.item(row, 0).text())
        self.alterBname.setText(self.BookTable.item(row, 1).text())
        self.alterAuthor.setText(self.BookTable.item(row, 2).text())
        self.alterPublisher.setText(self.BookTable.item(row, 3).text())
        status = self.BookTable.item(row, 4).text()
        if status == "在馆":
            self.alterBstatus.setCurrentIndex(0)
        elif status == "已预约":
            self.alterBstatus.setCurrentIndex(1)
        elif status == "已借出":
            self.alterBstatus.setCurrentIndex(2)
        # 显示封面
        self.cursor.execute("SELECT cover FROM book WHERE bid = " + self.alterBid.text())
        cover = self.cursor.fetchone()[0]
        # print(cover)
        img = QtGui.QImage.fromData(cover)
        # print(img)
        img = QtGui.QPixmap.fromImage(img).scaled(self.alterCover.width() - 10, self.alterCover.height() - 10)
        scene = QtWidgets.QGraphicsScene()
        scene.addPixmap(img)
        self.alterCover.setScene(scene)

    # 更换封面按钮点击事件
    def uploadImg(self):
        imgName, imgType = QtWidgets.QFileDialog.getOpenFileName(self, "选择图片", "", "Image Files(*.jpg *.png)")
        if not imgName:
            return
        # print(imgName)
        # 根据路径获得图片
        img = QtGui.QPixmap(imgName).scaled(self.alterCover.width() - 10, self.alterCover.height() - 10)
        # 用 QGraphicsView 显示图片
        scene = QtWidgets.QGraphicsScene()
        scene.addPixmap(img)
        self.alterCover.setScene(scene)
        self.imgtype = imgName.split(".")[-1]
        # 转为二进制
        with open(imgName, "rb") as f:
            self.img = f.read()

    # 修改按钮点击事件
    def alter(self):
        bid = self.alterBid.text()
        bname = self.alterBname.text()
        author = self.alterAuthor.text()
        publisher = self.alterPublisher.text()
        status = self.alterBstatus.currentIndex()
        if self.img:
            try:
                self.cursor.execute("UPDATE book SET bname = %s, author = %s, publisher = %s, status = %s, cover = %s WHERE bid = %s",
                                    (bname, author, publisher, status, self.img, bid))
                QtWidgets.QMessageBox.information(self, "成功", "修改成功")
            except Exception as e:
                print(e)
                QtWidgets.QMessageBox.warning(self, "错误", "修改失败")
        else:
            try:
                self.cursor.execute("UPDATE book SET bname = %s, author = %s, publisher = %s, status = %s WHERE bid = %s",
                                    (bname, author, publisher, status, bid))
                QtWidgets.QMessageBox.information(self, "成功", "修改成功")
            except Exception as e:
                print(e)
                QtWidgets.QMessageBox.warning(self, "错误", "修改失败")
        self.cursor.connection.commit()
        self.initBookTable()

    # 删除按钮点击事件
    def delete(self):
        bid = self.alterBid.text()
        try:
            self.cursor.execute("CALL deleteBook(" + bid + ")")
            QtWidgets.QMessageBox.information(self, "成功", "删除成功")
        except Exception as e:
            print(e)
            QtWidgets.QMessageBox.warning(self, "错误", "删除失败")
        self.cursor.connection.commit()
        self.initBookTable()


if __name__ == "__main__":
    import sys
    import pymysql
    app = QtWidgets.QApplication(sys.argv)
    db = pymysql.connect(
        host="localhost",
        user="root",
        password="10086",
        database="library",
        charset="utf8"
    )
    cursor = db.cursor()
    ui = Ui_Form(cursor)
    ui.show()
    sys.exit(app.exec())