Python+mysql+pyqt5的示例

Python+mysql+pyqt5的示例

1.环境搭建（这里主要介绍mysql与Python连接）
安装mysql8.0（这个版本可以比较容易获取到navicat免费版pojie）
未来软件园(www.orsoon.com)
具体参考：https://www.php.cn/tool/navicat/428701.html
故障排除方法：
打开mysql自带的cmd命令窗口，登录成功后分别输入以下两行

ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘leesky2008（我的数据库密码，需要改动）’;
FLUSH PRIVILEGES；

经过这两步即可完成环境搭建，慢慢来。
2.与Python连接
示例代码

import pymysql
#导入pymysql模块`在这里插入代码片`
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()

运行成功的话，可以通过navicat查看数据表books写入情况。
3.设计一个pyqt5—tablewidget控件—写入并显示数据库内容
*

1. 设计ui-------界面与代码分离的思路！！！！！

from PyQt5 import QtCore, QtGui, QtWidgets
class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 546)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.pb_wirte = QtWidgets.QPushButton(self.centralwidget)
        self.pb_wirte.setGeometry(QtCore.QRect(520, 450, 111, 41))
        self.pb_wirte.setObjectName("pb_wirte")
        self.pb_display = QtWidgets.QPushButton(self.centralwidget)
        self.pb_display.setGeometry(QtCore.QRect(660, 450, 121, 41))
        self.pb_display.setObjectName("pb_display")
        self.tw_DB = QtWidgets.QTableWidget(self.centralwidget)
        self.tw_DB.setGeometry(QtCore.QRect(20, 10, 761, 431))
        self.tw_DB.setObjectName("tw_DB")
        # self.tw_DB.setColumnCount(0)
        # self.tw_DB.setRowCount(0)
        # self.tw_DB.setHorizontalHeaderLabels(['ID', '图书名称', "图书分类", "图书价格", "出版时间"])
        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)
        self.tw_DB.setHorizontalHeaderLabels(['ID','图书名称',"图书分类","图书价格","出版时间"])
    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
        self.pb_wirte.setText(_translate("MainWindow", "写入数据库"))
        self.pb_display.setText(_translate("MainWindow", "显示数据库"))

2. 处理代码：

from PyQt5 import QtWidgets,QtGui,QtCore
from DB_DIS import Ui_MainWindow
from PyQt5.QtWidgets import QTableWidget, QTableWidgetItem
#这步很重要，是将数据库中数据形式转化为TableWidget表格形式的重要类！！！！！
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)
    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 DB_dis(self):
        db = pymysql.connect(host="127.0.0.1", user="root", password="leesky2008", database="mr")
        cursor = db.cursor()
        cursor.execute("select * from books")
        result=cursor.fetchall()
        row=cursor.rowcount
        vol=len(result[0])
        cursor.close()
        db.close()
        print("没有问题")
        print(row)
        print(result[0])
        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)
import sys
if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    mainWindow = MainWindow()
    # 实例化一个MainWindow
    mainWindow.show()
    sys.exit(app.exec_())