【Python pyQt5 pySide6】交互MySQL数据在表格中展示【清空数据】【展示数据】【查询模糊数据】

自从大二学习了mysql之后，在mysql与python,java交互的连接池中，我觉得是时候做一个查询的python图形界面了，啊啊啊啊啊啊啊啊啊啊啊啊啊啊阿。

第一种（是【或】的闭包运算，查询编号，查询姓名互不干扰）

优点：适用于单一数据的查询，查询一个数据时，其他条件不会影响查询的数据

准备：python，python图形界面工具pycharm,python库：pymysql, pyside6(pyQt),mySQL数据库

第一步：准备数据库表（数据库表尽量规整，我这个数据库表试验太多了）

第二步：准备qtdesigner和ui文件，如果没有安装pyQt或者pySide6的同学，可以试试安装一下

第三步：创建一个SQL()类，用来写执行的方法(不含ui文件)

import pandas as pd
import pymysql as pm
con = pm.connect (
        host='localhost',
        port=3306,
        user='root',
        password='123456',
        database='flowers'
    )
cur = con.cursor ()
# 连接数据库--用户consumer表
class SQL:
    
   
    def select(self,tb):
        sql='select * from '+tb
        cur.execute(sql)
        return cur
    def select_consumer_no(self,no):
        # 查询编号
        # SELECT * FROM
        # consumer
        # WHERE
        # consumer_no like '96%'
        nos= '%'+str(no)+'%'
        sql='SELECT * FROM consumer WHERE consumer_no like %s'
        cur.execute (sql,nos)
        # print(cur.fetchall())
        return cur
    def select_consumer_name(self,name):
        nos = '%' + str (name) + '%'
        sql = 'SELECT * FROM consumer WHERE consumer_name like %s'
        cur.execute (sql, nos)
        # print (cur.fetchall ())
        return cur
    def select_consumer_phone(self,phone):
        nos = '%' + str (phone) + '%'
        sql = 'SELECT * FROM consumer WHERE consumer_phone like %s'
        cur.execute (sql, nos)
        # print (cur.fetchall ())
        return cur
    def select_consumer_address(self,add):
        nos = '%' + str (add) + '%'
        sql = 'SELECT * FROM consumer WHERE consumer_address like %s'
        cur.execute (sql, nos)
        # print (cur.fetchall ())
        return cur
    

第四步：创建py文件与SQL（）交互

# 展示顾客的信息表
class Add():
    def __init__(self):
        self.ui = QUiLoader().load("../UI/xs.ui")
//这是按钮控件点击出现
        self.ui.sub.clicked.connect(self.setData)
        self.ui.clear.clicked.connect(self.clear)
        self.ui.select.clicked.connect(self.select)
        self.ui.select_2.clicked.connect (self.select2)
        self.ui.select_3.clicked.connect (self.select3)
        self.ui.select_4.clicked.connect (self.select4)
        # self.ui.ad.clicked.connect(self.add)
//表格名为tb
        self.tb=self.ui.tb
 
    def setData(self,sql):
        sc=True
        name = self.ui.name.text ()
        cur=SQL().select('consumer')
        rows = cur.fetchall ()
        row=cur.rowcount
        vol=len(rows[0])
        self.readBase (row, vol, rows)
        sc=False
       
    def readBase(self,row,vol,rows):
        self.tb.setRowCount (row)
        self.tb.setColumnCount (vol)
        for i in range (row):
            for j in range (vol):
                temp_data = rows[i][j]  # 临时记录，不能直接插入表格
                data = QTableWidgetItem (str (temp_data))  # 转换后可插入表格
                self.tb.setItem (i, j, data)
//清空数据的方法
    def clear(self):
        sc=True
//只需要设置表格属性行列数据均为0
        self.ui.tb.setRowCount(0)
        self.ui.tb.clearContents()
        sc=False
 
    def select(self):
        sc = True
        name = self.ui.name.text ()
        cur=SQL().select_consumer_name(str(name))
        rows = cur.fetchall()
        row = cur.rowcount
        vol = len (rows[0])
        self.readBase (row, vol, rows)
        sc = False
        
    def select2(self):
        sc = True
        no = self.ui.no.text ()
        cur=SQL().select_consumer_no(str(no))
        rows = cur.fetchall()
        row = cur.rowcount
        vol = len (rows[0])
        self.readBase (row, vol, rows)
        sc = False
        
    def select3(self):
        sc = True
        phone = self.ui.phone.text ()
        cur=SQL().select_consumer_phone(str(phone))
        rows = cur.fetchall()
        row = cur.rowcount
        vol = len (rows[0])
        self.readBase (row, vol, rows)
        sc = False
       
    def select4(self):
        sc = True
        add = self.ui.addr.text ()
        cur=SQL().select_consumer_address(str(add))
        rows = cur.fetchall()
        row = cur.rowcount
        vol = len (rows[0])
        self.readBase (row, vol, rows)
        sc = False
       

那到此处，关于第二种查询就结束了。

第二种（输入任意数据，都可以在数据库中进行模糊查询，【且】的关系闭包）：

优点：可以查询满足多个条件的数据

第一步：创建ui文件（动态）

第二步：创建数据库表（就是上面那张数据库表的截图）

第三步：在我们创建的SQL类中加入一个函数

    def sc(self,no,name,phone,add):
        nos1 = '%' + str (no) + '%'
        nos4 = '%' + str (add) + '%'
        nos3 = '%' + str (phone) + '%'
        nos2 = '%' + str (name) + '%'
        sql = 'SELECT * FROM consumer WHERE consumer_no like %s' +'' \
               ' and consumer_name like %s'+'' \
               'and consumer_phone like %s'+'' \
               'and consumer_address like %s'
        cur.execute (sql,[nos1,nos2,nos3,nos4])
        return cur

第三步：创建py文件，创造Acd类

class Acd():
    def __init__(self):
        self.ui = QUiLoader().load("../UI/sc.ui")
        self.ui.select.clicked.connect(self.select)
#表单控件名为tb
        self.tb = self.ui.tb
    def select(self):
        SC=True
        no = self.ui.no.text ()
        name = self.ui.name.text ()
        phone = self.ui.phone.text ()
        addr = self.ui.addr.text ()
#调用SQL()的sc()方法
        cur = SQL ().sc(no,name,phone,addr)
#获取游标，将数据库的数据写在表单控件中
        rows = cur.fetchall ()
        print(rows)
        row = cur.rowcount
        vol = len (rows[0])
#将表单的行列长度设置与数据库相同
        self.tb.setRowCount (row)
        self.tb.setColumnCount (vol)
#遍历，添加数据
        for i in range (row):
            for j in range (vol):
                temp_data = rows[i][j]  # 临时记录，不能直接插入表格
                data = QTableWidgetItem (str (temp_data))  # 转换后可插入表格
                self.tb.setItem (i, j, data)
        sc=False