PyQt实现读取MySql数据库表数据将其显示在TableWidget并保存为excel表格

记录将数据从MySql数据库中读取表数据并制作报表

	 想要将mysql数据库中表的数据显示在ui上，需要用到tableWidget控件，这里我们在QtDesigner中新建一个Widget窗
	 口，然后再窗口中添加一个frame作为父容器，之后将TableWidget拖入进去就可以了。

1、利用QTDesigner生成一般界面，包含基本的控件信息。
qt中的界面如下图所示

# -*- coding: utf-8 -*-
from PyQt5 import QtCore, QtGui, QtWidgets
class Ui_Form(object):
    def setupUi(self, Form):
        Form.setObjectName("Form")
        Form.resize(828, 539)
        self.frame = QtWidgets.QFrame(Form)
        self.frame.setGeometry(QtCore.QRect(30, 20, 771, 471))
        self.frame.setFrameShape(QtWidgets.QFrame.StyledPanel)
        self.frame.setFrameShadow(QtWidgets.QFrame.Raised)
        self.frame.setObjectName("frame")
        self.tableWidget = QtWidgets.QTableWidget(self.frame)
        self.tableWidget.setGeometry(QtCore.QRect(30, 20, 691, 331))
        self.tableWidget.setObjectName("tableWidget")
        self.tableWidget.setColumnCount(0)
        self.tableWidget.setRowCount(0)
        self.tableWidget.horizontalHeader().setVisible(True)
        self.tableWidget.horizontalHeader().setCascadingSectionResizes(True)
        self.tableWidget.horizontalHeader().setSortIndicatorShown(False)
        self.tableWidget.verticalHeader().setVisible(False)
        self.lineEdit = QtWidgets.QLineEdit(self.frame)
        self.lineEdit.setGeometry(QtCore.QRect(100, 380, 211, 31))
        self.lineEdit.setObjectName("lineEdit")
        self.label = QtWidgets.QLabel(self.frame)
        self.label.setGeometry(QtCore.QRect(30, 380, 61, 31))
        self.label.setObjectName("label")
        self.pushButton = QtWidgets.QPushButton(self.frame)
        self.pushButton.setGeometry(QtCore.QRect(330, 380, 71, 31))
        self.pushButton.setObjectName("pushButton")
        self.pushButton_2 = QtWidgets.QPushButton(self.frame)
        self.pushButton_2.setGeometry(QtCore.QRect(330, 420, 71, 31))
        self.pushButton_2.setObjectName("pushButton_2")
        self.lineEdit_2 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_2.setGeometry(QtCore.QRect(100, 420, 211, 31))
        self.lineEdit_2.setObjectName("lineEdit_2")
        self.label_2 = QtWidgets.QLabel(self.frame)
        self.label_2.setGeometry(QtCore.QRect(30, 420, 51, 31))
        self.label_2.setObjectName("label_2")

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

    def retranslateUi(self, Form):
        _translate = QtCore.QCoreApplication.translate
        Form.setWindowTitle(_translate("Form", "Form"))
        self.label.setText(_translate("Form", "文件浏览"))
        self.pushButton.setText(_translate("Form", "浏览"))
        self.pushButton_2.setText(_translate("Form", "保存"))
        self.label_2.setText(_translate("Form", "文件名"))

2、书写控制调用程序，对QT生成的程序进行调用。完成mysql数据的读取，报表的形成，并可以将报表数据存储到excel数据表格中。

from PyQt5 import QtWidgets,QtCore, QtGui
from PyQt5.QtWidgets import QWidget, QApplication, QTableWidgetItem, QLineEdit, QMessageBox
import xlwt
import table001
import sys
import MySQLdb

class MyClass(QWidget,table001.Ui_Form):
    def __init__(self):
        super().__init__()
        self.InitUi()
        self.My_Sql()

    def InitUi(self):
        self.setupUi(self)
        self.setWindowTitle("轨道检测")
        self.show()
        self.pushButton.clicked.connect(self.setBrowerPath)
        self.pushButton_2.clicked.connect(self.savefile)
        self._translate = QtCore.QCoreApplication.translate
    def Table_Data(self,i,j,data):

        item = QtWidgets.QTableWidgetItem()
        self.tableWidget.setItem(i,j, item)
        item = self.tableWidget.item(i,j)
        item.setText(self._translate("Form", str(data)))

    def My_Sql(self):    #连接mysql数据库
        connection = MySQLdb.connect(host = 'localhost',port=3306 ,user = 'root',passwd = '123456',db = 'imooc',charset='utf8')
        print('successfully connect')
        cur = connection.cursor()
        cur.execute('select * from new_table')  # 将数据从数据库中拿出来
        total = cur.fetchall()
        col_result = cur.description
        self.row = cur.rowcount  # 取得记录个数，用于设置表格的行数
        self.vol = len(total[0])  # 取得字段数，用于设置表格的列数
        col_result = list(col_result)
        a = 0
        self.tableWidget.setColumnCount(self.vol)
        self.tableWidget.setRowCount(self.row)
        for i in col_result:   #设置表头信息，将mysql数据表中的表头信息拿出来，放进TableWidget中
            item = QtWidgets.QTableWidgetItem()
            self.tableWidget.setHorizontalHeaderItem(a,item)
            item = self.tableWidget.horizontalHeaderItem(a)
            item.setText(self._translate("Form", i[0]))
            a = a + 1

        total = list(total)          # 将数据格式改为列表形式，其是将数据库中取出的数据整体改为列表形式
        for i in range(len(total)):      #将相关的数据
            total[i] = list(total[i])  #将获取的数据转为列表形式
        for i in range(self.row):
            for j in range(self.vol):
                self.Table_Data(i,j,total[i][j])

    def setBrowerPath(self):   #选择文件夹进行存储
        download_path = QtWidgets.QFileDialog.getExistingDirectory(None, "浏览", "/home")
        self.lineEdit.setText(download_path)

    def savefile(self):
        print("hello")
        book=xlwt.Workbook()
        sheet=book.add_sheet('超限数据报表')
        for i in range(0,self.tableWidget.rowCount()):
            for j in range(0,self.tableWidget.columnCount()):
                try:
                    sheet.write(i,j,self.tableWidget.item(i,j).text())
                except:
                    continue
        if len(self.lineEdit_2.text())<1:
            QMessageBox.information(self.pushButton, ' ', '文件名不可为空', QMessageBox.Ok)
        else:
            try:
                book.save(self.lineEdit.text()+'/'+self.lineEdit_2.text()+'.xls')
                QApplication.instance().exit()

            except:
                QMessageBox.information(self.pushButton,' ','所选目录错误！',QMessageBox.Ok)

if __name__ == '__main__':
    app = QApplication(sys.argv)
    mc = MyClass()
    sys.exit(app.exec_())

运行程序生成界面

可以选择文件夹并保存数据到excel表格中。