pyqt5与mysql结合的图书数据管理系统

最新推荐文章于 2024-06-09 19:47:46 发布

置顶 leesky2019

最新推荐文章于 2024-06-09 19:47:46 发布

阅读量1.4k

点赞数 3

分类专栏：笔记文章标签： qt mysql 开发语言

本文链接：https://blog.csdn.net/lixingdetiankong1/article/details/121142727

版权

笔记专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1、UI界面和逻辑处理代码分离！

在1这里插入图片描述
在这里插入图片描述
上图是一开始程序设计界面，下图是最终设计界面。在代码设计初始阶段，做到UI和逻辑分离的设计，可以有效避免因UI界面更改造成的代码重写。

from PyQt5 import QtWidgets,QtGui,QtCore
from DB_DIS_R import Ui_MainWindow
#导入UI设计的代码段（由UI文件生成的.py文件）
from PyQt5.QtWidgets import QTableWidget, QTableWidgetItem, QMessageBox
from PyQt5.QtCore import Qt
import pymysql
class MainWindow(QtWidgets.QMainWindow,Ui_MainWindow):
    def __init__(self,parent=None):
        super(MainWindow,self).__init__(parent)
        self.setupUi(self)
        self.pb_wirte.clicked.connect(self.DB_Write)
        # self.pb_display.clicked.connect(self.DB_dis)
        self.PB_dengji.clicked.connect(self.dengji)
        # self.CB_price.setChecked(True)
        self.PB_search.clicked.connect(self.search)
        self.rb_priceasc.toggled.connect(self.order)
        self.rb_priced.toggled.connect(self.order)
        self.rb_publish.toggled.connect(self.order)
        #各种信号与函数的连接，不建议在qtdesigner中采用图形化的方式！！！
        self.tw_DB.verticalHeader().setVisible(False)
    #     取消tableview控件自动添加序号的功能
        self.tw_DB.setEditTriggers(QtWidgets.QAbstractItemView.NoEditTriggers)

…先省略中间处理代码…再看看程序最后的代码段：

import sys
if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    mainWindow = MainWindow()
    # 实例化一个MainWindow
    mainWindow.show()
    sys.exit(app.exec_())

上述两步是实现UI与逻辑代码分离的关键步骤。

来看看处理逻辑：

首先可以通过qt5中的基本控件实现MYsql数据写入
由写入数据按钮触发------def DB_Write(self):
这包含了MYsql数据库的基本操作-------增加记录

    def DB_Write(self):
        db = pymysql.connect(host="127.0.0.1", user="root", password="leesky2008", database="mr")
        cursor = db.cursor()
        data = [("零基础学Python", "python", "100.20", "2018-5-20"),
                ("Python入门到实践", "python", "112.20", "2019-5-30"),
                ("零基C语言入门", "C", "17.20", "2017-5-22"),
                ("快来一起学java", "JAVA", "0.20", "2018-6-20"),
                ("学PHP", "php", "100.20", "2018-5-20")]
        try:
            cursor.executemany("insert into books(name,category,price,publish_time) values (%s,%s,%s,%s)", data)
            # cursor.executemany("insert into books(name) values (%s)", "lixing")
            db.commit()
        except:
            db.rollback()
        db.close()

其次，通过radiobutton的选择实现基本的排序显示
为什么不再控件中排序？？？控件的排序功能很弱，不好用，没有按照数值排序的功能！！！tablewidget类控件数值排序功能体验不好。有解决方法的同学欢迎指教。

    def order(self):
        if self.rb_priceasc.isChecked():
            self.sqltext = "select * from books order by price ASC"
            print("价格升序")
            self.DB_dis()
        if self.rb_priced.isChecked():
            self.sqltext = "select * from books order by price DESC"
            print("价格降序")
            self.DB_dis()
        if self.rb_publish.isChecked():
            self.sqltext = ""
            self.sqltext = "select * from books order by publish_time DESC "
            print(self.sqltext)
            self.DB_dis()
    def DB_dis(self):
        # self.sqltext = "select * from books order by price DESC"
        sqltext=self.sqltext
        # print(sqltext)
        db = pymysql.connect(host="127.0.0.1", user="root", password="leesky2008", database="mr")
        cursor = db.cursor()
        cursor.execute(sqltext)       # cursor.execute("select * from books order by publish_time desc")
        result=cursor.fetchall()
        row=cursor.rowcount
        vol=len(result[0])
        cursor.close()
        db.close()
        self.tw_DB.setRowCount(row)
        self.tw_DB.setColumnCount(vol)
        self.tw_DB.setHorizontalHeaderLabels(['ID', '图书名称', "图书分类", "图书价格", "出版时间"])
        for i in range(row):
            for j in range(vol):
                data = QTableWidgetItem(str(result[i][j]))
                self.tw_DB.setItem(i,j,data)
        self.tw_DB.resizeColumnsToContents()
        self.tw_DB.resizeRowsToContents()
        self.tw_DB.setAlternatingRowColors(True)

这是排序及在tablewidgets中显示的部分代码段。

在界面的右半部分，练习了数据的写入（获取控件中文本信息，写入数据库）
较为简单，代码放在最后的总代码中。

最后练习了数据查询并显示结果的代码操作，时间有限，只注重功能实现。
在图书名称中输入，点击查询按钮，可以查看搜索结果（模糊查询—注意查询语句的形式啊！！！！------血泪教训）

ef search(self):
        self.tw_DB.clear()
        print("kaishi")
        text=str(self.LE_bookname.text())
        selectsql="select * from books where name like "+"'%"+text+"%'"
        print(selectsql)
        db = pymysql.connect(host="127.0.0.1", user="root", password="leesky2008", database="mr")
        cursor = db.cursor()
        cursor.execute(selectsql)
        result = cursor.fetchall()
        if self.LE_bookname.text()=="":
            QMessageBox.information(self, "提示", "请输入图书名称", QMessageBox.Ok)
        elif cursor.rowcount > 0:
            row = cursor.rowcount
            vol = len(result[0])
            print(vol)
            cursor.close()
            db.close()
            self.tw_DB.setRowCount(row)
            self.tw_DB.setColumnCount(vol)
            self.tw_DB.setHorizontalHeaderLabels(['ID', '图书名称', "图书分类", "图书价格", "出版时间"])
            for i in range(row):
                for j in range(vol):
                    data = QTableWidgetItem(str(result[i][j]))
                    self.tw_DB.setItem(i, j, data)
            self.tw_DB.setItem(i, j, data)
            self.tw_DB.resizeColumnsToContents()
            self.tw_DB.resizeRowsToContents()
            self.tw_DB.setAlternatingRowColors(True)
        else:
            print("查无此书")
            QMessageBox.information(self,"提示","没有这本书",QMessageBox.Ok)

在这里插入图片描述

全部代码

1.UI部分

# -*- coding: utf-8 -*-

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


from PyQt5 import QtCore, QtGui, QtWidgets


class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 567)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.pb_wirte = QtWidgets.QPushButton(self.centralwidget)
        self.pb_wirte.setGeometry(QtCore.QRect(20, 450, 111, 41))
        self.pb_wirte.setObjectName("pb_wirte")
        self.pb_display = QtWidgets.QPushButton(self.centralwidget)
        self.pb_display.setGeometry(QtCore.QRect(270, 450, 121, 41))
        self.pb_display.setObjectName("pb_display")
        self.tw_DB = QtWidgets.QTableWidget(self.centralwidget)
        self.tw_DB.setGeometry(QtCore.QRect(20, 10, 371, 431))
        self.tw_DB.setAutoScroll(True)
        self.tw_DB.setObjectName("tw_DB")
        self.tw_DB.setColumnCount(0)
        self.tw_DB.setRowCount(0)
        self.calendarWidget = QtWidgets.QCalendarWidget(self.centralwidget)
        self.calendarWidget.setGeometry(QtCore.QRect(520, 180, 248, 197))
        self.calendarWidget.setObjectName("calendarWidget")
        self.layoutWidget = QtWidgets.QWidget(self.centralwidget)
        self.layoutWidget.setGeometry(QtCore.QRect(520, 40, 251, 131))
        self.layoutWidget.setObjectName("layoutWidget")
        self.gridLayout = QtWidgets.QGridLayout(self.layoutWidget)
        self.gridLayout.setContentsMargins(0, 0, 0, 0)
        self.gridLayout.setObjectName("gridLayout")
        self.label = QtWidgets.QLabel(self.layoutWidget)
        self.label.setObjectName("label")
        self.gridLayout.addWidget(self.label, 3, 0, 1, 1)
        self.LE_bookprice = QtWidgets.QLineEdit(self.layoutWidget)
        self.LE_bookprice.setObjectName("LE_bookprice")
        self.gridLayout.addWidget(self.LE_bookprice, 1, 1, 1, 1)
        self.LE_bookname = QtWidgets.QLineEdit(self.layoutWidget)
        self.LE_bookname.setObjectName("LE_bookname")
        self.gridLayout.addWidget(self.LE_bookname, 0, 1, 1, 1)
        self.LE_publishtime = QtWidgets.QLineEdit(self.layoutWidget)
        self.LE_publishtime.setObjectName("LE_publishtime")
        self.gridLayout.addWidget(self.LE_publishtime, 3, 1, 1, 1)
        self.label_2 = QtWidgets.QLabel(self.layoutWidget)
        self.label_2.setObjectName("label_2")
        self.gridLayout.addWidget(self.label_2, 0, 0, 1, 1)
        self.label_3 = QtWidgets.QLabel(self.layoutWidget)
        self.label_3.setObjectName("label_3")
        self.gridLayout.addWidget(self.label_3, 1, 0, 1, 1)
        self.CB_bookskind = QtWidgets.QComboBox(self.layoutWidget)
        self.CB_bookskind.setObjectName("CB_bookskind")
        self.CB_bookskind.addItem("")
        self.CB_bookskind.addItem("")
        self.CB_bookskind.addItem("")
        self.CB_bookskind.addItem("")
        self.CB_bookskind.addItem("")
        self.CB_bookskind.addItem("")
        self.gridLayout.addWidget(self.CB_bookskind, 2, 1, 1, 1)
        self.label_4 = QtWidgets.QLabel(self.layoutWidget)
        self.label_4.setObjectName("label_4")
        self.gridLayout.addWidget(self.label_4, 2, 0, 1, 1)
        self.layoutWidget1 = QtWidgets.QWidget(self.centralwidget)
        self.layoutWidget1.setGeometry(QtCore.QRect(520, 400, 241, 31))
        self.layoutWidget1.setObjectName("layoutWidget1")
        self.horizontalLayout = QtWidgets.QHBoxLayout(self.layoutWidget1)
        self.horizontalLayout.setContentsMargins(0, 0, 0, 0)
        self.horizontalLayout.setObjectName("horizontalLayout")
        self.PB_dengji = QtWidgets.QPushButton(self.layoutWidget1)
        self.PB_dengji.setTabletTracking(False)
        self.PB_dengji.setObjectName("PB_dengji")
        self.horizontalLayout.addWidget(self.PB_dengji)
        self.PB_search = QtWidgets.QPushButton(self.layoutWidget1)
        self.PB_search.setObjectName("PB_search")
        self.horizontalLayout.addWidget(self.PB_search)
        self.rb_priceasc = QtWidgets.QRadioButton(self.centralwidget)
        self.rb_priceasc.setGeometry(QtCore.QRect(140, 450, 89, 16))
        self.rb_priceasc.setObjectName("rb_priceasc")
        self.rb_priced = QtWidgets.QRadioButton(self.centralwidget)
        self.rb_priced.setGeometry(QtCore.QRect(140, 470, 89, 16))
        self.rb_priced.setObjectName("rb_priced")
        self.rb_publish = QtWidgets.QRadioButton(self.centralwidget)
        self.rb_publish.setGeometry(QtCore.QRect(140, 490, 89, 16))
        self.rb_publish.setObjectName("rb_publish")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 23))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

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

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "图书登记查询系统"))
        self.pb_wirte.setText(_translate("MainWindow", "写入数据库"))
        self.pb_display.setText(_translate("MainWindow", "显示数据库"))
        self.label.setText(_translate("MainWindow", "出版时间"))
        self.label_2.setText(_translate("MainWindow", "图书名称"))
        self.label_3.setText(_translate("MainWindow", "图书价格"))
        self.CB_bookskind.setItemText(0, _translate("MainWindow", "python"))
        self.CB_bookskind.setItemText(1, _translate("MainWindow", "java"))
        self.CB_bookskind.setItemText(2, _translate("MainWindow", "c#"))
        self.CB_bookskind.setItemText(3, _translate("MainWindow", "php"))
        self.CB_bookskind.setItemText(4, _translate("MainWindow", "labview"))
        self.CB_bookskind.setItemText(5, _translate("MainWindow", "c++"))
        self.label_4.setText(_translate("MainWindow", "图书种类"))
        self.PB_dengji.setText(_translate("MainWindow", "登记新书"))
        self.PB_search.setText(_translate("MainWindow", "查询书籍信息"))
        self.rb_priceasc.setText(_translate("MainWindow", "价格升序"))
        self.rb_priced.setText(_translate("MainWindow", "价格降序"))
        self.rb_publish.setText(_translate("MainWindow", "出版时间降序"))

2.逻辑处理部分

from PyQt5 import QtWidgets,QtGui,QtCore
from DB_DIS_R import Ui_MainWindow
from PyQt5.QtWidgets import QTableWidget, QTableWidgetItem, QMessageBox
from PyQt5.QtCore import Qt
import pymysql
class MainWindow(QtWidgets.QMainWindow,Ui_MainWindow):
    def __init__(self,parent=None):
        super(MainWindow,self).__init__(parent)
        self.setupUi(self)
        self.pb_wirte.clicked.connect(self.DB_Write)
        # self.pb_display.clicked.connect(self.DB_dis)
        self.PB_dengji.clicked.connect(self.dengji)
        # self.CB_price.setChecked(True)
        self.PB_search.clicked.connect(self.search)
        self.rb_priceasc.toggled.connect(self.order)
        self.rb_priced.toggled.connect(self.order)
        self.rb_publish.toggled.connect(self.order)
        self.tw_DB.verticalHeader().setVisible(False)
    #     取消tableview控件自动添加序号的功能
        self.tw_DB.setEditTriggers(QtWidgets.QAbstractItemView.NoEditTriggers)
    #     设置表格内容不可编辑！！！！
    def DB_Write(self):
        db = pymysql.connect(host="127.0.0.1", user="root", password="leesky2008", database="mr")
        cursor = db.cursor()
        data = [("零基础学Python", "python", "100.20", "2018-5-20"),
                ("Python入门到实践", "python", "112.20", "2019-5-30"),
                ("零基C语言入门", "C", "17.20", "2017-5-22"),
                ("快来一起学java", "JAVA", "0.20", "2018-6-20"),
                ("学PHP", "php", "100.20", "2018-5-20")]
        try:
            cursor.executemany("insert into books(name,category,price,publish_time) values (%s,%s,%s,%s)", data)
            # cursor.executemany("insert into books(name) values (%s)", "lixing")
            db.commit()
        except:
            db.rollback()
        db.close()
        print("你最牛逼")
    def order(self):
        if self.rb_priceasc.isChecked():
            self.sqltext = "select * from books order by price ASC"
            print("价格升序")
            self.DB_dis()
        if self.rb_priced.isChecked():
            self.sqltext = "select * from books order by price DESC"
            print("价格降序")
            self.DB_dis()
        if self.rb_publish.isChecked():
            self.sqltext = ""
            self.sqltext = "select * from books order by publish_time DESC "
            print(self.sqltext)
            self.DB_dis()
    def DB_dis(self):
        # self.sqltext = "select * from books order by price DESC"
        sqltext=self.sqltext
        # print(sqltext)
        db = pymysql.connect(host="127.0.0.1", user="root", password="leesky2008", database="mr")
        cursor = db.cursor()
        cursor.execute(sqltext)       # cursor.execute("select * from books order by publish_time desc")
        result=cursor.fetchall()
        row=cursor.rowcount
        vol=len(result[0])
        cursor.close()
        db.close()
        self.tw_DB.setRowCount(row)
        self.tw_DB.setColumnCount(vol)
        self.tw_DB.setHorizontalHeaderLabels(['ID', '图书名称', "图书分类", "图书价格", "出版时间"])
        for i in range(row):
            for j in range(vol):
                data = QTableWidgetItem(str(result[i][j]))
                self.tw_DB.setItem(i,j,data)
        self.tw_DB.resizeColumnsToContents()
        self.tw_DB.resizeRowsToContents()
        self.tw_DB.setAlternatingRowColors(True)
    def dengji(self):
        booksname = str(self.LE_bookname.text())
        bookspice = str(self.LE_bookprice.text())
        bookskind = str(self.CB_bookskind.currentText())
        publish=QtCore.QDate(self.calendarWidget.selectedDate())
        publish_time=str(publish.year())+"-"+str(publish.month())+"-"+str(publish.day())
        print(booksname)
        print(bookspice)
        print(bookskind)
        print(publish_time)
        db = pymysql.connect(host="127.0.0.1", user="root", password="leesky2008", database="mr")
        cursor = db.cursor()
        datainput=[(booksname,bookskind,bookspice,publish_time)]
        print(datainput)
        try:
            cursor.executemany("insert into books(name,category,price,publish_time) values (%s,%s,%s,%s)", datainput)
            db.commit()
        except:
            db.rollback()
        db.close()
        print("你最牛逼")
    def search(self):
        self.tw_DB.clear()
        print("kaishi")
        text=str(self.LE_bookname.text())
        selectsql="select * from books where name like "+"'%"+text+"%'"
        print(selectsql)
        db = pymysql.connect(host="127.0.0.1", user="root", password="leesky2008", database="mr")
        cursor = db.cursor()
        cursor.execute(selectsql)
        result = cursor.fetchall()
        if self.LE_bookname.text()=="":
            QMessageBox.information(self, "提示", "请输入图书名称", QMessageBox.Ok)
        elif cursor.rowcount > 0:
            row = cursor.rowcount
            vol = len(result[0])
            print(vol)
            cursor.close()
            db.close()
            self.tw_DB.setRowCount(row)
            self.tw_DB.setColumnCount(vol)
            self.tw_DB.setHorizontalHeaderLabels(['ID', '图书名称', "图书分类", "图书价格", "出版时间"])
            for i in range(row):
                for j in range(vol):
                    data = QTableWidgetItem(str(result[i][j]))
                    self.tw_DB.setItem(i, j, data)
            self.tw_DB.setItem(i, j, data)
            self.tw_DB.resizeColumnsToContents()
            self.tw_DB.resizeRowsToContents()
            self.tw_DB.setAlternatingRowColors(True)
        else:
            print("查无此书")
            QMessageBox.information(self,"提示","没有这本书",QMessageBox.Ok)
import sys
if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    mainWindow = MainWindow()
    # 实例化一个MainWindow
    mainWindow.show()
    sys.exit(app.exec_())

写在最后，需要练习以下：1、不同窗体之间的切换及数据传递
2.界面的美化设计 3.数据库的基本操作强化------视图功能和聚类

再接再厉！！！欢迎交流----微信号：15248132073

leesky2019

关注

3
点赞
踩
31

收藏

觉得还不错? 一键收藏
0
评论
pyqt5与mysql结合的图书数据管理系统

1、UI界面和逻辑处理代码分离！上图是一开始程序设计界面，下图是最终设计界面。在代码设计初始阶段，做到UI和逻辑分离的设计，可以有效避免因UI界面更改造成的代码重写。from PyQt5 import QtWidgets,QtGui,QtCorefrom DB_DIS_R import Ui_MainWindow#导入UI设计的代码段（由UI文件生成的.py文件）from PyQt5.QtWidgets import QTableWidget, QTableWidgetItem, QMessa
复制链接

扫一扫