According to an example of a project, this paper records and shares the process and relevant settings of python's displaying the content of database to the program interface, and explores the usage of python's operating database.

Main contents to be shared:

1. Displays the database contents.

2. Modify database content.

3. The table control specifies the slider position.

4. Customize the right-click menu.

Use environment: Python 3.7 + mysql5 + pyqt5

For the crowd: beginners or those who need to consult the information, senior people should not spray. Please give me your valuable opinions, and I accept them with open mind.

preparation in advance

1. Create software interface

The above figure shows the project cost system interface created by using PyQt5 related modules, modules and functions used:

1. QMainWindow module: draw the overall framework with menu, toolbar and status bar.

2. QTreeWidget module: draw the left navigation bar.

3. Qtablewidgettitem module: draw the middle table used to display data.

4. QComboBox, QPushButton module: draw drop-down box, button control.

5. QSplitter module: set the placement and combination of various controls, and drag the mouse to adjust the window size.

6. QMessageBox module: set interactive pop-up box.

The specific interface drawing process is not the focus of this paper, not detailed.

2. Establish connection with database

In order to establish the connection with the database, we must first have a specific database. In this case, we use mysql to establish our own database, and then connect with the software project.

2.1 self built database

After downloading and installing Mysql, it is recommended to install another visualization tool. My Navicat for Mysql feels good. The interface is as follows:

In this tool interface, you can easily create and modify data tables. With the tools, the next step is to fill in the key data. In many cases, customers or our own data are placed in excel. We can easily import excel data into the database through Navicat Import Wizard:

With the wizard, you can complete the data import in a few steps.

2.2 connection to database

Import the pymysql library, write the connection and other functions of the library into the self built class. In this case, by reading the. cfg configuration file (for the knowledge of the configuration file, please refer to the python development project. You have to understand the. cfg configuration file), the five parameters required by the connection library are obtained. When the initial class attribute is "init", the connection is established. The code is 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)

Among them, the contents of the SQL config.cfg file are:

If you don't need a configuration file, you can write five parameters in the program directly. The code is as follows, which seems simpler:

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)

Show database content

Function: in the software interface, click the left navigation bar to find the table corresponding to the expansion item in the database and display the data in the middle table. If the target table does not exist, prompt to create it.

Implementation steps:

Create navigation bar; → click navigation bar to get table name; → query database, find table and display content

Specific process:

1. Create navigation bar with tree control, data source is database

The data source style of the navigation bar in the database is:

It is not acceptable to manually input code item by item. According to the length characteristics of the first column of data, the following batch creation method is adopted, in which fl is the transmitted parameter, meaning "subgrade" or other specialties.

def set_tree(self, fl):
        qd_lj = MysqlDb().select_db('SELECT * FROM Detailed list' + fl)  # The target table in the database is obtained by the self built select DB function of Mysqlbd class
        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]['Code']
            mc = qd_lj[i]['Name']
            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)
            elif (len(bm) == 10):
                root3 = QTreeWidgetItem(root2)
                root3.setText(0, mc)
                root3.setText(1, bm)
            elif (len(bm) == 13):
                root4 = QTreeWidgetItem(root3)
                root4.setText(0, mc)
                root4.setText(1, bm)
            elif (len(bm) == 16):
                root5 = QTreeWidgetItem(root4)
                root5.setText(0, mc)
                root5.setText(1, bm)
            elif (len(bm) == 19):
                root6 = QTreeWidgetItem(root5)
                root6.setText(0, mc)
                root6.setText(1, bm)
            elif (len(bm) == 22):
                root7 = QTreeWidgetItem(root6)
                root7.setText(0, mc)
                root7.setText(1, bm)
            else:
                pass
        self.tree.expandAll()  # Set tree component to expand all
        self.item = root  # Custom settings initial selection root entry

2. Set the signal slot function of clicking the tree control entry to extract the database table content and display it in the table control.

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

When the left click navigation bar is set, the function showtreesql() is triggered. The specific data acquisition function is set in the function.

def showtreesql(self, item):  # Click the tree node to display the database contents in the table
				self.inputtable.clearContents()  #Empty middle table control
        self.inputtable.setRowCount(0)  # Initial table control
        self.item = item  # When clicking the navigation bar, the signal slot will automatically bring the information parameter item of the current clicked node
        if not item.child(0):  # Judge if the node has no child nodes, that is, the clicked node is the lowest node, and the non child node has no corresponding table
            self.decxcomb3.setDisabled(False)
            self.select_item = "n" + item.text(1).replace('-', '')  # According to the node code, get the corresponding table name
            if self.decxcomb3.findText(self.select_item) == -1:
                self.decxcomb3.addItem(self.select_item)
            self.decxcomb3.setCurrentText(self.select_item)  # Display the table name in the drop-down box control
            if self.table_exists(MysqlDb(),
                                 self.select_item):  # Query the database to determine whether there is a corresponding table,
                self.inputtable.setRowCount(1)  
                self.sql_to_input(self.select_item, self.inputtable)  #If a table exists, query the database and read the data to the table
        else:
            # self.decxcomb3.isEnabled()
            self.decxcomb3.setDisabled(True)

3. Read the function code from the database content to the table:

def sql_to_input(self, sql_tabel, inputtable):
        data = MysqlDb().select_db('SELECT * FROM ' + sql_tabel)
        if data:
            inputtable.setRowCount(len(data))
            for i in range(len(data)):
                inputtable.setItem(i, 0, QTableWidgetItem(data[i]['Norm No']))
                inputtable.setItem(i, 1, QTableWidgetItem(data[i]['Quota name']))
                inputtable.setItem(i, 2, QTableWidgetItem(data[i]['Company']))
                inputtable.setItem(i, 3, QTableWidgetItem(data[i]['Number']))
          #  print('Database write completed ')
        else:
          #  print('source table is empty, do not write ')
          pass

To determine whether a function block of a table exists in the database:

def table_exists(self, sql, table_name):  # This function is used to determine whether the database contains a table
        tables = sql.select_db('SHOW TABLES')  # Get all the table names in the database,
        tabels_list = []
        for i in tables:
            n = i['Tables_in_1']
            tabels_list.append(n)
        if table_name in tabels_list:
            return 1
        else:
            return 0

4. Double click the navigation bar with the left key to trigger the signal slot. When there is no target table, you will be prompted to create a new table:

def edittreesql(self, item):  # Double click the tree node to create a new database table
        if not item.child(0):  # Judge that this node has no child nodes
            self.select_item = "n" + item.text(1).replace('-', '')  # Record the node code
            self.decxcomb3.addItem(self.select_item)
            self.decxcomb3.setCurrentText(self.select_item)
            if self.table_exists(MysqlDb(), self.select_item):  # Determine whether there is a table named by this node
                # Judge whether the result exists and display the content in the table control
                self.inputtable.clearContents()
                self.sql_to_input(self.select_item, self.inputtable)
            else:
                if QMessageBox.information(self, "Create quota table",
                                           "You are about to create a quota input table for this list:" + self.select_item,
                                           QMessageBox.Yes | QMessageBox.No) == QMessageBox.Yes:
                    MysqlDb().biuldtabel(self.select_item)  # If not, create a new table in the database,
                    self.inputtable.clearContents()
                    self.inputtable.setRowCount(1)
                    print('Table created:', self.select_item)

Modify database content

1. Set save button

self.saveinput = QPushButton('Save to database')
self.saveinput.clicked.connect(self.input_to_sql)

2. Write save button click function block

def input_to_sql(self):
        if self.decxcomb3.currentText():
            MysqlDb().execute_db('DELETE FROM ' +
                                 self.decxcomb3.currentText())  # Clear the original content before writing to the database
            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(
                ) + " (Norm No, Quota name, Company, Number) VALUES('" + v[0] + "', '" + v[
                    1] + "', '" + v[2] + "', '" + v[3] + "')"
                MysqlDb().execute_db(insert_sql)

Table control specifying slider position

Set the drop-down box to select an item, and then move the upper and lower sliders of the lower middle table to display the current item first according to the item name:

Set signal slot: self.decxcomb2.currentTextChanged[str].connect(self.set_table2)

Set function block code:

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)

Customize the right-click menu

When entering a table, sometimes it is unavoidable to insert or delete rows. The most common is the right-click menu below.

Implementation process:

1. Set table control to respond to right click

self.inputtable.setContextMenuPolicy(Qt.CustomContextMenu)

2. Set right key signal slot function

self.inputtable.customContextMenuRequested.connect(self.input_rightmenu)
def input_rightmenu(self):  # Specify quota input table control right-click menu
        try:
            self.contextMenu = QMenu()
            self.actionA = self.contextMenu.addAction(u'delete')
            self.actionB = self.contextMenu.addAction(u'insert')
            self.actionA.setIcon(QIcon(r"source\4.png"))
            self.contextMenu.popup(QCursor.pos())  # Location of menu display
            self.actionA.triggered.connect(self.deletcurrow)
            self.actionB.triggered.connect(self.insertcurrow)
            self.contextMenu.show()
        except Exception as e:
            print(e)

3. Function block settings

def deletcurrow(self):
        self.inputtable.removeRow(self.inputtable.currentRow())# Delete current row

def insertcurrow(self):
        self.inputtable.insertRow(self.inputtable.currentRow()) #Insert a row in the current row