Python GUI Application for Displaying and Editing MySQL Data with PyQt5

This tutorial walks through creating a PyQt5 desktop program in Python 3.7 that connects to a MySQL 5 database, displays table contents, enables editing, custom right‑click menus, and scroll‑position control, providing a complete example for beginners and intermediate developers.

Environment : Python 3.7, MySQL 5, PyQt5.

1. Build the GUI – The interface is created with several PyQt5 widgets: QMainWindow (main frame with menu, toolbar, status bar), QTreeWidget (left navigation), QTableWidgetItem (central data table), QComboBox and QPushButton (dropdowns and buttons), QSplitter (draggable layout), and QMessageBox (dialog boxes). The detailed layout code is omitted for brevity.

2. Connect to the database

First a MySQL database is created locally (e.g., using Navicat for MySQL). A configuration file sql_config.cfg stores the connection parameters (host, port, user, password, database). The connection class reads this file and establishes a pymysql connection:

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 five parameters can be hard‑coded directly in the class.

3. Display database content

The left tree widget is populated from a database table (e.g., "清单"). Clicking a leaf node triggers showtreesql(), which clears the central table, determines the corresponding table name, checks its existence, and loads its rows into the QTableWidget:

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)

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

A helper table_exists() runs SHOW TABLES and checks the target name.

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

4. Modify database content

A "Save to database" button is added; its click handler iterates over the rows of the table widget, builds an INSERT statement for each row, and executes it after clearing the existing data:

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

5. Table scroll‑position control

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

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 input table is set to accept custom context menus. A menu with "Delete" and "Insert" actions is shown at the cursor position, and the actions call deletcurrow() and insertcurrow() respectively:

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

def input_rightmenu(self):
    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)

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

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

The article concludes with a note that the content is for learning purposes only.