Using Python, PyQt5, and MySQL to Display and Edit Database Content in a GUI Application

This article demonstrates, through a concrete project, how to use Python 3.7 together with MySQL 5 and PyQt5 to create a desktop application that can display, modify, and manage database records.

Environment : Python 3.7, MySQL 5, PyQt5. The target audience is beginners or anyone needing a reference implementation.

1. UI Construction – The interface is built with several PyQt5 widgets: QMainWindow for the main frame, QTreeWidget for the navigation pane, QTableWidgetItem for the central data table, QComboBox and QPushButton for controls, QSplitter for resizable layouts, and QMessageBox for dialogs. The detailed UI layout is shown in the accompanying screenshots.

2. Database Connection

First a MySQL database is created locally (Navicat is recommended for visual management). Data can be imported from Excel via Navicat’s import wizard. Connection parameters are stored in a sql_config.cfg file and loaded with ConfigParser. The connection class is defined as follows:

import pymysql
from configparser import ConfigParser

class MysqlDb():
    def __init__(self):
        self.cp = ConfigParser()
        self.cp.read('source/sql_config.cfg')
        host = self.cp.get('sql_connect', 'host')
        port = int(self.cp.get('sql_connect', 'port'))
        user = self.cp.get('sql_connect', 'user')
        passwd = self.cp.get('sql_connect', 'passwd')
        db = self.cp.get('sql_connect', 'db')
        self.conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db)
        self.cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

Alternatively, the parameters can be hard‑coded:

class MysqlDb():
    def __init__(self):
        self.conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='771222', db='1')
        self.cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

3. Displaying Database Content

The left‑hand tree widget is populated from a database table using the set_tree method, which creates hierarchical items based on the length of the code field:

def set_tree(self, fl):
    qd_lj = MysqlDb().select_db('SELECT * FROM 清单' + fl)
    root = QTreeWidgetItem(self.tree)
    root.setText(0, fl)
    root.setIcon(0, QIcon(r'source\3.png'))
    for i in range(0, len(qd_lj)):
        bm = qd_lj[i]['编码']
        mc = qd_lj[i]['名称']
        if len(bm) == 4:
            root1 = QTreeWidgetItem(root)
            root1.setText(0, mc)
            root1.setText(1, bm)
        elif len(bm) == 7:
            root2 = QTreeWidgetItem(root1)
            root2.setText(0, mc)
            root2.setText(1, bm)
        # ... additional levels omitted for brevity ...
    self.tree.expandAll()
    self.item = root

Clicking a tree item triggers showtreesql to load the corresponding table into the central QTableWidget:

self.tree.itemClicked.connect(self.showtreesql)

def showtreesql(self, item):
    self.inputtable.clearContents()
    self.inputtable.setRowCount(0)
    self.item = item
    if not item.child(0):
        self.decXcomb3.setDisabled(False)
        self.select_item = "n" + item.text(1).replace('-', '')
        self.decXcomb3.setCurrentText(self.select_item)
        if self.table_exists(MysqlDb(), self.select_item):
            self.inputtable.setRowCount(1)
            self.sql_to_input(self.select_item, self.inputtable)
    else:
        self.decXcomb3.setDisabled(True)

The helper sql_to_input fetches all rows and populates the table widget:

def sql_to_input(self, sql_table, inputtable):
    data = MysqlDb().select_db('SELECT * FROM ' + sql_table)
    if data:
        inputtable.setRowCount(len(data))
        for i in range(len(data)):
            inputtable.setItem(i, 0, QTableWidgetItem(data[i]['定额编号']))
            inputtable.setItem(i, 1, QTableWidgetItem(data[i]['定额名称']))
            inputtable.setItem(i, 2, QTableWidgetItem(data[i]['单位']))
            inputtable.setItem(i, 3, QTableWidgetItem(data[i]['数量']))
    else:
        pass

A utility table_exists checks whether a given table is present in the database:

def table_exists(self, sql, table_name):
    tables = sql.select_db('SHOW TABLES')
    tables_list = [i['Tables_in_1'] for i in tables]
    return 1 if table_name in tables_list else 0

4. Modifying Database Content

A "Save to Database" button is added and linked to input_to_sql, which clears the existing table data and inserts the current rows from the UI:

self.saveinput = QPushButton('保存到数据库')
self.saveinput.clicked.connect(self.input_to_sql)

def input_to_sql(self):
    if self.decXcomb3.currentText():
        MysqlDb().execute_db('DELETE FROM ' + self.decXcomb3.currentText())
        for i in range(self.inputtable.rowCount()):
            v = []
            for j in range(4):
                if self.inputtable.item(i, j):
                    v.append(self.inputtable.item(i, j).text())
                else:
                    v.append('')
            insert_sql = (
                'INSERT INTO ' + self.decXcomb3.currentText() +
                " (定额编号, 定额名称, 单位, 数量) VALUES('" + v[0] + "', '" + v[1] + "', '" + v[2] + "', '" + v[3] + "')"
            )
            MysqlDb().execute_db(insert_sql)

5. Table Widget Scroll Position

The combo box selection controls the vertical scroll bar of a secondary table so that the chosen item appears at the top:

self.decXcomb2.currentTextChanged[str].connect(self.set_table2)

def set_table2(self, fl):
    for i in range(self.table2.rowCount()):
        if self.table2.item(i, 0).text() == fl:
            break
    self.table2.verticalScrollBar().setValue(i)

6. Custom Right‑Click Menu

The central input table is given a custom context menu to delete or insert rows:

self.inputtable.setContextMenuPolicy(Qt.CustomContextMenu)
self.inputtable.customContextMenuRequested.connect(self.input_rightmenu)

def input_rightmenu(self):
    try:
        self.contextMenu = QMenu()
        self.actionA = self.contextMenu.addAction('删除')
        self.actionB = self.contextMenu.addAction('插入')
        self.actionA.setIcon(QIcon(r"source\4.png"))
        self.contextMenu.popup(QCursor.pos())
        self.actionA.triggered.connect(self.deletcurrow)
        self.actionB.triggered.connect(self.insertcurrow)
        self.contextMenu.show()
    except Exception as e:
        print(e)

def deletcurrow(self):
    self.inputtable.removeRow(self.inputtable.currentRow())

def insertcurrow(self):
    self.inputtable.insertRow(self.inputtable.currentRow())

Overall, the article provides a step‑by‑step guide, complete with code snippets and UI screenshots, for beginners to build a functional PyQt5‑based database editor that supports viewing, editing, scrolling, and context‑menu operations.