【PyQt5】连接 mysql 查询数据并显示在 tableWidget 表格

末蓝、 2023-02-18 08:12 101阅读 0赞

### PyQt5 ###

*  数据库建表 + SQL 语句
 *  Qt designer 制作界面
 *  连接 MySQL 相关知识
 *  查询并获取数据库的数据
 *  运行效果

# 数据库建表 + SQL 语句 #

![20200616161508548.png][]  
![在这里插入图片描述][20200616161529407.png]  
建立一个数据库：`test`

建立一张数据表：`tab`，SQL 语句如下：

SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for tab
    -- ----------------------------
    DROP TABLE IF EXISTS `tab`;
    CREATE TABLE `tab` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of tab
    -- ----------------------------
    INSERT INTO `tab` VALUES ('172101', '小明');
    INSERT INTO `tab` VALUES ('172102', '小红');
    INSERT INTO `tab` VALUES ('172103', '小黄');
    INSERT INTO `tab` VALUES ('172104', '小黑');
    INSERT INTO `tab` VALUES ('172105', '小三');
    INSERT INTO `tab` VALUES ('172106', '小白');

# Qt designer 制作界面 #

Qt designer 基本使用可以参考：[【PyQt5】使用 designer 开发 python GUI 界面][PyQt5_ designer _ python GUI]

我们随意做一个有 `tableWidget` 的界面保存为 `hello.ui` 并转为 `hello.py`：（代码下面有）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70]  
`hello.ui` 界面文件转为的 `hello.py` 代码如下：

# -*- coding: utf-8 -*-
    
    # Form implementation generated from reading ui file 'hello.ui'
    #
    # Created by: PyQt5 UI code generator 5.15.0
    #
    # 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(316, 372)
            self.centralwidget = QtWidgets.QWidget(MainWindow)
            self.centralwidget.setObjectName("centralwidget")
            self.tableWidget = QtWidgets.QTableWidget(self.centralwidget)
            self.tableWidget.setGeometry(QtCore.QRect(11, 11, 301, 271))
            self.tableWidget.setObjectName("tableWidget")
            self.tableWidget.setColumnCount(2)
            self.tableWidget.setRowCount(9)
            item = QtWidgets.QTableWidgetItem()
            self.tableWidget.setVerticalHeaderItem(0, item)
            item = QtWidgets.QTableWidgetItem()
            self.tableWidget.setVerticalHeaderItem(1, item)
            item = QtWidgets.QTableWidgetItem()
            self.tableWidget.setVerticalHeaderItem(2, item)
            item = QtWidgets.QTableWidgetItem()
            self.tableWidget.setVerticalHeaderItem(3, item)
            item = QtWidgets.QTableWidgetItem()
            self.tableWidget.setVerticalHeaderItem(4, item)
            item = QtWidgets.QTableWidgetItem()
            self.tableWidget.setVerticalHeaderItem(5, item)
            item = QtWidgets.QTableWidgetItem()
            self.tableWidget.setVerticalHeaderItem(6, item)
            item = QtWidgets.QTableWidgetItem()
            self.tableWidget.setVerticalHeaderItem(7, item)
            item = QtWidgets.QTableWidgetItem()
            self.tableWidget.setVerticalHeaderItem(8, item)
            item = QtWidgets.QTableWidgetItem()
            self.tableWidget.setHorizontalHeaderItem(0, item)
            item = QtWidgets.QTableWidgetItem()
            self.tableWidget.setHorizontalHeaderItem(1, item)
            item = QtWidgets.QTableWidgetItem()
            item.setTextAlignment(QtCore.Qt.AlignCenter)
            self.tableWidget.setItem(0, 0, item)
            self.button_read = QtWidgets.QPushButton(self.centralwidget)
            self.button_read.setGeometry(QtCore.QRect(90, 300, 121, 41))
            self.button_read.setObjectName("button_read")
            MainWindow.setCentralWidget(self.centralwidget)
            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", "tableDemo"))
            item = self.tableWidget.verticalHeaderItem(0)
            item.setText(_translate("MainWindow", "1"))
            item = self.tableWidget.verticalHeaderItem(1)
            item.setText(_translate("MainWindow", "2"))
            item = self.tableWidget.verticalHeaderItem(2)
            item.setText(_translate("MainWindow", "3"))
            item = self.tableWidget.verticalHeaderItem(3)
            item.setText(_translate("MainWindow", "4"))
            item = self.tableWidget.verticalHeaderItem(4)
            item.setText(_translate("MainWindow", "5"))
            item = self.tableWidget.verticalHeaderItem(5)
            item.setText(_translate("MainWindow", "6"))
            item = self.tableWidget.verticalHeaderItem(6)
            item.setText(_translate("MainWindow", "7"))
            item = self.tableWidget.verticalHeaderItem(7)
            item.setText(_translate("MainWindow", "8"))
            item = self.tableWidget.verticalHeaderItem(8)
            item.setText(_translate("MainWindow", "9"))
            item = self.tableWidget.horizontalHeaderItem(0)
            item.setText(_translate("MainWindow", "ID"))
            item = self.tableWidget.horizontalHeaderItem(1)
            item.setText(_translate("MainWindow", "姓名"))
            __sortingEnabled = self.tableWidget.isSortingEnabled()
            self.tableWidget.setSortingEnabled(False)
            self.tableWidget.setSortingEnabled(__sortingEnabled)
            self.button_read.setText(_translate("MainWindow", "读取"))

# 连接 MySQL 相关知识 #

Python 连接 MySQL 请看这个：[【Python】PyMySQL 连接 MySQL数据库][Python_PyMySQL _ MySQL]

# 查询并获取数据库的数据 #

由于我们界面与逻辑分离，界面代码前面已经有了，现在要写逻辑代码；  
新建一个 `main.py`，然后写入下面代码，即可在 `tableWidget` 中展示数据库中的数据。

主要了解以下几个知识点：

*  连接 mysql 并 查询数据库，`cur.fetchall()` 返回的是二维元组

#数据库连接对象
    conn = pymysql.connect(host='localhost', port=3306, user='root', password="1234", db="test")
    # 游标对象
    cur = conn.cursor()
    # 查询的sql语句
    sql = "SELECT * FROM tab"
    cur.execute(sql)
    # 获取查询到的数据, 是以二维元组的形式存储的, 所以读取需要使用 data[i][j] 下标定位
    data = cur.fetchall()
    # 打印测试
    print(data)
    # print(data[0][1]) # 打印第1行第2个数据, 也就是小明

*  遍历二维元组并将数据显示到表格上：

# 遍历二维元组, 将 id 和 name 显示到界面表格上
    x = 0
    for i in data:
        y = 0
        for j in i:
            self.tableWidget.setItem(x, y, QtWidgets.QTableWidgetItem(str(data[x][y])))
            y = y + 1
        x = x + 1

完整代码：

import sys
    from PyQt5 import QtGui, QtWidgets
    from PyQt5.QtCore import *
    from PyQt5.QtWidgets import QApplication, QMainWindow, QHeaderView
    
    from hello import Ui_MainWindow
    
    import pymysql
    
    class MyMainWindow(QMainWindow, Ui_MainWindow):
        def __init__(self):
            super(MyMainWindow, self).__init__()
            self.setupUi(self)
            # 【读取】功能
            self.button_read.clicked.connect(self.read)
    
        # 【读取】按钮功能
        def read(self):
            # 数据库连接对象
            conn = pymysql.connect(host='localhost', port=3306, user='root', password="1234", db="test")
            # 游标对象
            cur = conn.cursor()
            
            # 查询的sql语句
            sql = "SELECT * FROM tab"
            cur.execute(sql)
            # 获取查询到的数据, 是以二维元组的形式存储的, 所以读取需要使用 data[i][j] 下标定位
            data = cur.fetchall()
            # 打印测试
            print(data)
            # print(data[0][1]) # 打印第1行第2个数据, 也就是小明
    
            # 遍历二维元组, 将 id 和 name 显示到界面表格上
            x = 0
            for i in data:
                y = 0
                for j in i:
                    self.tableWidget.setItem(x, y, QtWidgets.QTableWidgetItem(str(data[x][y])))
                    y = y + 1
                x = x + 1
    
            cur.close()
            conn.close()
    
    if __name__ == "__main__":
        QApplication.setAttribute(Qt.AA_EnableHighDpiScaling)
        app = QApplication(sys.argv)
        win = MyMainWindow()
        win.show()
        sys.exit(app.exec())

# 运行效果 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70 1]

[20200616161508548.png]: https://img-blog.csdnimg.cn/20200616161508548.png
[20200616161529407.png]: https://img-blog.csdnimg.cn/20200616161529407.png
[PyQt5_ designer _ python GUI]: https://blog.csdn.net/weixin_43734095/article/details/106761680
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200616161151500.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ==,size_16,color_FFFFFF,t_70
[Python_PyMySQL _ MySQL]: https://blog.csdn.net/weixin_43734095/article/details/106785024
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200616164504223.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ==,size_16,color_FFFFFF,t_70