手把手教你用python写个mysql查看器

mysql查看器效果图

1、mysql数据库操作类，基于pymsql，用于连接数据库、获取表信息、获取字段信息和表内数据

import pymysql

# 凌云

class mysqlUtil():
    def __init__(self,config):
        self.host=config['host']
        self.port=int(config['port'])
        self.user=config['user']
        self.passWord=config['passWord']
        self.database=config['database']

    # 初始化数据库连接
    def initDb(self):
        self.db=pymysql.connect(
            host=self.host, user=self.user, password=self.passWord,
            database=self.database, port=self.port, charset='utf8', autocommit=True
        )

    # 测试数据库连接
    def testConnect(self):
        try:
            self.initDb()
            self.db.close()
            return True
        except Exception  as e :
            print(e)
            return False

    # 获取指定数据库的所有表信息
    def getTableList(self):
        try:
            self.initDb()
            cur = self.db.cursor()
            cur.execute(
                'SELECT TABLE_NAME ,TABLE_COMMENT ,TABLE_ROWS  FROM information_schema.tables WHERE TABLE_SCHEMA =\'' + self.database + '\' ORDER BY TABLE_NAME')
            result = cur.fetchall()
            self.db.close()
            return changeTableList(result)
        except Exception  as e :
            return []

    # 获取指定表的所有字段信息
    def getTable(self,tableName):
        try:
            self.initDb()
            cur = self.db.cursor()
            cur.execute(
                'SELECT column_name,column_comment  FROM information_schema.columns  WHERE table_schema = \'' + self.database + '\' AND `table_name` = \'' + tableName + '\' ')
            result = cur.fetchall()
            self.db.close()
            return changeTable(result)
        except Exception  as e :
            return []

    # 获取指定表的所有字段信息
    def getTableData(self, tableName):
        try:
            self.initDb()
            cur = self.db.cursor(pymysql.cursors.DictCursor)
            cur.execute( 'select *  from  ' + tableName +' limit 1000')
            result = cur.fetchall()
            self.db.close()
            return result
        except Exception  as e:
            print(e)
            return []


# 格式化表名/视图名信息
def changeTableList(result):
    arr=[]
    for item in result:
        name = item[0]
        description = item[1]
        type = item[1]
        dataCount = item[2]

        if type != 'VIEW':
            type = 'Table'

        if description == '':
            description = "暂无描述"

        if type =='Table' :
            arr.append({
                'name' :name,
                'type' : type,
                'description' : description,
                'dataCount' :dataCount or 0
            })
    return arr

# 格式化表里面的字段信息
def changeTable(result):
    arr=[]
    for item in result:
        name = item[0]
        description = item[1]
        arr.append({
            'name' :name,
            'description' : description,
        })
    return arr

2、gui类，基于pyqt5

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

# Form implementation generated from reading ui file 'mysql浏览器.ui'
#
# Created by: PyQt5 UI code generator 5.15.6
#
# 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
from PyQt5.QtWidgets import QMessageBox
from mysqlUtil import mysqlUtil
from PyQt5.QtCore import QStringListModel
from PyQt5.QtGui import QStandardItemModel,QStandardItem

class Ui_Dialog(object):
    def setupUi(self, Dialog):
        Dialog.setObjectName("Dialog")

        # 固定窗体大小
        Dialog.setFixedSize(QtCore.QSize(1371, 860))
        self.groupBox = QtWidgets.QGroupBox(Dialog)
        self.groupBox.setGeometry(QtCore.QRect(20, 420, 331, 421))
        self.groupBox.setObjectName("groupBox")
        self.listView = QtWidgets.QListView(self.groupBox)
        self.listView.setGeometry(QtCore.QRect(10, 20, 311, 391))
        self.listView.setObjectName("listView")
        self.groupBox_3 = QtWidgets.QGroupBox(Dialog)
        self.groupBox_3.setGeometry(QtCore.QRect(360, 20, 1001, 821))
        self.groupBox_3.setObjectName("groupBox_3")
        self.tableView = QtWidgets.QTableView(self.groupBox_3)
        self.tableView.setGeometry(QtCore.QRect(10, 20, 971, 791))
        self.tableView.setObjectName("tableView")
        self.groupBox_2 = QtWidgets.QGroupBox(Dialog)
        self.groupBox_2.setGeometry(QtCore.QRect(20, 20, 331, 391))
        self.groupBox_2.setObjectName("groupBox_2")
        self.lineEdit_5 = QtWidgets.QLineEdit(self.groupBox_2)
        self.lineEdit_5.setGeometry(QtCore.QRect(100, 260, 221, 31))
        self.lineEdit_5.setEchoMode(QtWidgets.QLineEdit.Normal)
        self.lineEdit_5.setObjectName("lineEdit_5")
        self.label_4 = QtWidgets.QLabel(self.groupBox_2)
        self.label_4.setGeometry(QtCore.QRect(20, 200, 71, 31))
        self.label_4.setObjectName("label_4")
        self.toolButton_2 = QtWidgets.QToolButton(self.groupBox_2)
        self.toolButton_2.setGeometry(QtCore.QRect(210, 330, 111, 41))
        self.toolButton_2.setObjectName("toolButton_2")
        self.lineEdit_2 = QtWidgets.QLineEdit(self.groupBox_2)
        self.lineEdit_2.setGeometry(QtCore.QRect(100, 80, 221, 31))
        self.lineEdit_2.setObjectName("lineEdit_2")
        self.lineEdit_3 = QtWidgets.QLineEdit(self.groupBox_2)
        self.lineEdit_3.setGeometry(QtCore.QRect(100, 140, 221, 31))
        self.lineEdit_3.setObjectName("lineEdit_3")
        self.label = QtWidgets.QLabel(self.groupBox_2)
        self.label.setGeometry(QtCore.QRect(20, 20, 61, 31))
        self.label.setObjectName("label")
        self.label_2 = QtWidgets.QLabel(self.groupBox_2)
        self.label_2.setGeometry(QtCore.QRect(20, 80, 71, 31))
        self.label_2.setObjectName("label_2")
        self.label_5 = QtWidgets.QLabel(self.groupBox_2)
        self.label_5.setGeometry(QtCore.QRect(20, 260, 71, 31))
        self.label_5.setObjectName("label_5")
        self.label_3 = QtWidgets.QLabel(self.groupBox_2)
        self.label_3.setGeometry(QtCore.QRect(20, 140, 71, 31))
        self.label_3.setObjectName("label_3")
        self.lineEdit = QtWidgets.QLineEdit(self.groupBox_2)
        self.lineEdit.setGeometry(QtCore.QRect(100, 20, 221, 31))
        self.lineEdit.setObjectName("lineEdit")
        self.lineEdit_4 = QtWidgets.QLineEdit(self.groupBox_2)
        self.lineEdit_4.setGeometry(QtCore.QRect(100, 200, 221, 31))
        self.lineEdit_4.setEchoMode(QtWidgets.QLineEdit.Password)
        self.lineEdit_4.setObjectName("lineEdit_4")
        self.toolButton = QtWidgets.QToolButton(self.groupBox_2)
        self.toolButton.setGeometry(QtCore.QRect(50, 330, 111, 41))
        self.toolButton.setObjectName("toolButton")

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

    def retranslateUi(self, Dialog):
        _translate = QtCore.QCoreApplication.translate
        Dialog.setWindowTitle(_translate("Dialog", "mysql数据库浏览器"))
        self.groupBox.setTitle(_translate("Dialog", "数据库表"))
        self.groupBox_3.setTitle(_translate("Dialog", "数据内容"))
        self.groupBox_2.setTitle(_translate("Dialog", "数据库配置"))
        self.label_4.setText(_translate("Dialog", "密码"))
        self.toolButton_2.setText(_translate("Dialog", "重置"))
        self.label.setText(_translate("Dialog", "服务器ip "))
        self.label_2.setText(_translate("Dialog", "服务器端口"))
        self.label_5.setText(_translate("Dialog", "实例名"))
        self.label_3.setText(_translate("Dialog", "用户名"))
        self.toolButton.setText(_translate("Dialog", "连接"))

        # 添加默认值
        self.lineEdit.setText('127.0.0.1')
        self.lineEdit_2.setText('3306')
        self.lineEdit_3.setText('root')
        self.lineEdit_4.setText('xxxx')
        self.lineEdit_5.setText('fw-soft-emall')

        # 绑定事件
        self.toolButton.clicked.connect(self.conn)

    # 连接数据库
    def conn(self):
        self.msq = mysqlUtil({
            'host': self.lineEdit.text(),
            'port': self.lineEdit_2.text(),
            'user': self.lineEdit_3.text(),
            'passWord': self.lineEdit_4.text(),
            'database': self.lineEdit_5.text()
        })
        boo = self.msq.testConnect()
        if boo == False:
            QMessageBox.warning(self.toolButton, "信息提醒", "数据库连接错误，请核对！")
        else:
            self.tableList = self.msq.getTableList()
            self.setListViewData()

    # 为listView设定值
    def setListViewData(self):
        model = QStringListModel()

        arr=[]
        for item in self.tableList :
            arr.append(item['name'])

        model.setStringList(arr)
        self.listView.setModel(model)
        self.listView.clicked.connect(self.clickListView)

    # 点击listView
    def clickListView(self,item):
        row=self.tableList[item.row()]
        table=row['name']
        dataCount=row['dataCount']

        # 表头
        tableColumns=self.msq.getTable(table)
        model = QStandardItemModel(dataCount,len(tableColumns), )
        arr=[]
        for column in tableColumns :
            arr.append(column['name']+'\n'+column['description'])
        model.setHorizontalHeaderLabels(arr)

        try:
            # 数据体
            tableData = self.msq.getTableData(table)
            if len(tableData) > 0 and len(tableColumns) > 0:
                for row in range(0,len(tableData)):
                    data = tableData[row]
                    for col in range(0,len(tableColumns)):
                        key = tableColumns[col]['name']
                        text =str( data[key] )
                        if   text is None:
                            text = ""
                        model.setItem(row, col, QStandardItem(text))

            self.tableView.setModel(model)
        except Exception as e:
            print(e)


# 下面是标准入口方法
if __name__ == '__main__':
    import sys

    # 获取窗体操作权限
    app = QtWidgets.QApplication(sys.argv)
    main = QtWidgets.QMainWindow()

    # 调用自定义界面
    ui = Ui_Dialog()
    ui.setupUi(main)

    # 界面显示
    main.show()
    sys.exit(app.exec_())