基于PysimpleGUI+pymysql建立的简单管理系统（4）—删除功能

         紧接上文，我们接下来来讲delete.py。

        通过之前文章的menu.py登录界面，我们选择第二个功能，删除功能。

        我们在插入功能所创建的数据表的基础上，要对其进行删除操作的同时，我们需要注意的是如何设计这个用户界面。

        这里我们使用的是pysimplegui里面的listbox列表框元素，它可以在展示数据的同时，增添了用户的互动性，我们仅仅只需要将导出来的数据进行列表化就可以了。

        之后，进行insert.py的代码展示：

import pymysql                                       #导入库
import PySimpleGUI as sg  
import pandas as pd
import os
import time
import threading

conn = pymysql.connect(host="localhost",            #建立mysql的连接
                           user="root",  
                           password="******",   
                           db="user_information",       
                           charset="utf8")     
cursor = conn.cursor()  
sql ="select * from custome_information" 
cursor.execute(sql)
result = cursor.fetchall()
all = list(result)                                  #将数据列表化
cursor.close()
conn.close()                          
layout = [[sg.Text("客户名 订单名 时间 材料 尺寸 数量 单价 金额")],         #gui界面设计
          [sg.Listbox(all, key='-TEXT-', select_mode='single',size=(80, 6))],
          [sg.Button('返回'),sg.Button('删除'),sg.Button('查找'),sg.Button('退出')]]
window = sg.Window('删除客户账单', layout)
while True: 
    event, values = window.read()                         #提取用户动作以及值
    if event == sg.WINDOW_CLOSED or event == '退出':
        break 
    if  event == '返回':                                  #返回至菜单界面
        time.sleep(0.5)
        window.close()
        os.system('python menu.py')
        break    
    if event == '删除':                                   #删除操作
        values = pd.DataFrame.from_dict(values,orient='index')     #对用户所选的列进行清洗
        str1 = str(values.iat[0,0])                       
        custome_name = str1.split("\'")[1]               #提取对应的值
        order_name = str1.split("\'")[3]
        material = str1.split("\'")[5]
        size = str1.split("\'")[7]
        quantity = str1.split(",")[9]
        price = str1.split(",")[10]
        amount = str1.split(",")[11].strip(')')
        cursor = conn.cursor()  
        conn.ping(reconnect=True)              #添加互斥锁，以防mysql连接丢失
        thread_lock = threading.Lock()
        thread_lock.acquire()
        sql ="delete from custome_information where custome_name = '%s' and order_name = '%s' and material = '%s' and size = '%s' and quantity = '%s' and price = '%s' and amount = '%s'" %(custome_name,order_name,material,size,quantity,price,amount)
        cursor.execute(sql)
        conn.commit()
        conn.close()
        thread_lock.release()
        time.sleep(0.5)
        window.close()
        layout = [[sg.Text("已删除")],              #删除成功，给予一个反馈界面
                [sg.Button('返回'),sg.Button('继续')]]
        window = sg.Window('已删除', layout)
        while True:
            event, values = window.read() 
            if event == sg.WINDOW_CLOSED or event == '返回':   #返回主菜单
                time.sleep(0.5)
                window.close()
                os.system('python menu.py')
                break   
            if event == '继续':                   #继续删除对象
                time.sleep(0.5)
                window.close()
                os.system('python delete.py')
                break    
    if  event == '查找':                         #查找功能（之后在查找功能里会讲）
        window.close()
        layout = [[sg.Text("请输入要查找的客户名：")],
          [sg.Input(key='-INPUT-')],
          [sg.Button('确定'),sg.Button('返回'),sg.Button('退出')]]
        window = sg.Window('查找', layout)
        while True:
            event, values = window.read() 
            if event == sg.WINDOW_CLOSED or event == '退出':
                break 
            if  event == '返回':
                time.sleep(0.5)
                window.close()
                os.system('python delete.py')
                break    
            if event == '确定':
                window.close()
                values = pd.DataFrame([values])
                custome_name = str(values.iat[0,0])
                cursor = conn.cursor() 
                conn.ping(reconnect=True)
                thread_lock = threading.Lock()
                thread_lock.acquire() 
                sql ="select * from custome_information where custome_name = '%s'"%(custome_name)
                cursor.execute(sql)
                result = cursor.fetchall()
                all1 = list(result)
                cursor.close()
                conn.close()
                thread_lock.release()
                layout = [[sg.Text("客户名 订单名 时间 材料 尺寸 数量 单价 金额")],
                        [sg.Listbox(all1, key='-TEXT-', select_mode='single',size=(80, 6))],
                        [sg.Button('返回'),sg.Button('删除'),sg.Button('退出')]]
                window = sg.Window('查找', layout)
                while True:
                    event, values = window.read()
                    if event == sg.WINDOW_CLOSED or event == '退出':
                        break 
                    if  event == '返回':
                        time.sleep(0.5)
                        window.close()
                        os.system('python delete.py')
                        break 
                    if event == '删除':
                        values = pd.DataFrame.from_dict(values,orient='index')
                        str1 = str(values.iat[0,0])
                        custome_name = str1.split("\'")[1]
                        order_name = str1.split("\'")[3]
                        material = str1.split("\'")[5]
                        size = str1.split("\'")[7]
                        quantity = str1.split(",")[9]
                        price = str1.split(",")[10]
                        amount = str1.split(",")[11].strip(')')
                        cursor = conn.cursor()  
                        conn.ping(reconnect=True)
                        thread_lock = threading.Lock()
                        thread_lock.acquire()
                        sql ="delete from custome_information where custome_name = '%s' and order_name = '%s' and material = '%s' and size = '%s' and quantity = '%s' and price = '%s' and amount = '%s'" %(custome_name,order_name,material,size,quantity,price,amount)
                        cursor.execute(sql)
                        conn.commit()
                        conn.close()
                        thread_lock.release()
                        time.sleep(0.5)
                        window.close()
                        layout = [[sg.Text("已删除")],
                                [sg.Button('返回'),sg.Button('继续')]]
                        window = sg.Window('已删除', layout)
                        while True:
                            event, values = window.read() 
                            if event == sg.WINDOW_CLOSED or event == '返回':
                                time.sleep(0.5)
                                window.close()
                                os.system('python menu.py')
                                break   
                            if event == '继续':
                                time.sleep(0.5)
                                window.close()
                                os.system('python delete.py')
                                break   

        运行截图：

 

 

 

        之后将持续更新，revise.py修改操作。