python pyqt5 QTableView连接mysql展示数据Pandas.DataFrame

 QTableView连接mysql展示数据Pandas.DataFrame

根据数据库返回的json数据展示

 效果图：

代码：

import sys
import pandas as pd
from PyQt5.QtWidgets import QApplication, QTableView
from PyQt5.QtCore import QAbstractTableModel, Qt
import pymysql
from pymysql import cursors

#df = pd.DataFrame({'a': ['Mary', 'Jim', 'John'],
#                   'b': [100, 200, 300],
#                   'c': ['a', 'b', 'c']})

class pandasModel(QAbstractTableModel):

    def __init__(self, data):
        QAbstractTableModel.__init__(self)
        self._data = data
        
    def rowCount(self, parent=None):
        return self._data.shape[0]

    def columnCount(self, parnet=None):
        return self._data.shape[1]

    def data(self, index, role=Qt.DisplayRole):
        if index.isValid():
            if role == Qt.DisplayRole:
                return str(self._data.iloc[index.row(), index.column()])
        return None

    def headerData(self, col, orientation, role):
        if orientation == Qt.Horizontal and role == Qt.DisplayRole:
            return self._data.columns[col]
        return None


class DBManager():

    def __init__(self, conname='default', hostname='127.0.0.1', port='3306', user='root', password='Topevery@2021'):
        self.conname = conname
        self.hostname = hostname
        if port == '':
            port = 3306
        self.port = int(port)
        self.user = user
        self.password = password


    def openDB(self):
        self.db = pymysql.connect(
            host=self.hostname,
            user=self.user,
            password=self.password,
            #database=self.database,
            port=self.port,
            charset='utf8',
            autocommit=True
        )
        return True

    def openDB_json(self):
        self.db = pymysql.connect(
            host=self.hostname,
            user=self.user,
            password=self.password,
            #database=self.database,
            port=self.port,
            charset='utf8',
            cursorclass=cursors.DictCursor,
            autocommit=True
        )
        return True
    
    def getList(self):
        self.openDB_json()
        cur = self.db.cursor(pymysql.cursors.DictCursor)
        cur.execute('select * from mysql.user')
        #cur.execute('select * from information_schema.%s where %s limit 3' % (table,where))
        re = cur.fetchall()

        return re


if __name__ == '__main__':
    app = QApplication(sys.argv)

    my_db = DBManager();
    dataModel = my_db.getList()


    df = pd.DataFrame(dataModel)
    print(df)

    model = pandasModel(df) 
    view = QTableView()
    view.setModel(model)
    view.resize(800, 600)
    view.show()
    sys.exit(app.exec_())