紧接上文,我们接下来来讲revise.py
通过之前文章的menu.py登录界面,我们选择第三个功能,修改功能。
我们在已有的数据表和列表框元素的基础上,我们需要考虑的是如何去修改客户的信息。相对于让用户全部重新再输入一次信息,不如给出一条信息提示,让用户去选择修改什么地方。除此之外,对于数据量较大的情况下,我增添了查找功能,可以更快更迅速的找出某个客户的所有信息。
revise.py代码如下:(代码量较大,有很大一部分的结构是重复的)
import pymysql #导入库文件
import PySimpleGUI as sg
import pandas as pd
import os
import time
import threading
import datetime
conn = pymysql.connect(host="localhost", #建立mysql的连接
user="root",
password="",
db="user_information",
charset="utf8")
cursor = conn.cursor()
sql ="select * from custome_information" #导入mysql数据表信息
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(')')
time.sleep(0.5)
window.close()
layout = [[sg.Text("请输入要修改的序号:")], #修改位置界面
[sg.Text("1.客户名 2.订单名 3.时间 4.材料 ")],
[sg.Text("5.尺寸 6.数量 7.单价 8.金额 ")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回': #返回修改界面
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
if values.iat[0,0] == '': #判断为空报错
window.close()
layout = [[sg.Text("填写为空!")],
[sg.Button('返回')]]
window = sg.Window('错误', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED:
break
if event == '返回': #返回修改界面
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
choice = int(values.iat[0,0])
time.sleep(0.5)
window.close()
if choice == 1:
layout = [[sg.Text("请输入要修改的内容:")], #修改内容界面
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回': #返回修改界面
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = str(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True) #添加互斥锁,以防mysql连接丢失
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set custome_name = '%s' where order_name = '%s' and material = '%s' and size = '%s' and quantity = '%s' and price = '%s' and amount = '%s'" %(content,order_name,material,size,quantity,price,amount)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if choice == 2: #同上,唯一区别在于mysql语句不同
layout = [[sg.Text("请输入要修改的内容:")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = str(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set order_name = '%s' where custome_name = '%s' and material = '%s' and size = '%s' and quantity = '%s' and price = '%s' and amount = '%s'" %(content,custome_name,material,size,quantity,price,amount)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if choice == 3:
layout = [[sg.Text("请输入要修改的内容:(年.月.日)")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = str(values.iat[0,0])
format_str = '%Y.%m.%d'
content = datetime.datetime.strptime(content, format_str)
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set time = '%s' where custome_name = '%s'and order_name = '%s' and material = '%s' and size = '%s' and quantity = '%s' and price = '%s' and amount = '%s'" %(content,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()
os.system('python revise.py')
break
if choice == 4:
layout = [[sg.Text("请输入要修改的内容:")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = str(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set material = '%s' where custome_name = '%s' and order_name= '%s' and size = '%s' and quantity = '%s' and price = '%s' and amount = '%s'" %(content,custome_name,order_name,size,quantity,price,amount)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if choice == 5:
layout = [[sg.Text("请输入要修改的内容:")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = str(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set size = '%s' where custome_name = '%s' and order_name= '%s' and material= '%s' and quantity = '%s' and price = '%s' and amount = '%s'" %(content,custome_name,order_name,material,quantity,price,amount)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if choice == 6:
layout = [[sg.Text("请输入要修改的内容:")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = int(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set quantity = '%s' where custome_name = '%s' and order_name= '%s' and material= '%s' and size = '%s' and price = '%s' and amount = '%s'" %(content,custome_name,order_name,material,size,price,amount)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if choice == 7:
layout = [[sg.Text("请输入要修改的内容:")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = float(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set price = '%s' where custome_name = '%s' and order_name= '%s' and material= '%s' and size = '%s' and quantity= '%s' and amount = '%s'" %(content,custome_name,order_name,material,size,quantity,amount)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if choice == 8:
layout = [[sg.Text("请输入要修改的内容:")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = float(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set amount = '%s' where custome_name = '%s' and order_name= '%s' and material= '%s' and size = '%s' and quantity= '%s' and price = '%s'" %(content,custome_name,order_name,material,size,quantity,price)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.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 revise.py')
break
if event == '确定':
window.close()
values = pd.DataFrame([values])
custome_name = str(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True) #添加互斥锁,以防mysql连接丢失
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="select * from custome_information where custome_name = '%s'"%(custome_name)
cursor.execute(sql)
result = cursor.fetchall()
all = list(result)
cursor.close()
conn.close()
thread_lock.release()
layout = [[sg.Text("客户名 订单名 时间 材料 尺寸 数量 单价 金额")],
[sg.Listbox(all, 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 revise.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(')')
time.sleep(0.5)
window.close()
layout = [[sg.Text("请输入要修改的序号:")],
[sg.Text("1.客户名 2.订单名 3.时间 4.材料 ")],
[sg.Text("5.尺寸 6.数量 7.单价 8.金额 ")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
choice = int(values['-INPUT-'])
time.sleep(0.5)
window.close()
if choice == 1:
layout = [[sg.Text("请输入要修改的内容:")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = str(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set custome_name = '%s' where order_name = '%s' and material = '%s' and size = '%s' and quantity = '%s' and price = '%s' and amount = '%s'" %(content,order_name,material,size,quantity,price,amount)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if choice == 2:
layout = [[sg.Text("请输入要修改的内容:")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = str(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set order_name = '%s' where custome_name = '%s' and material = '%s' and size = '%s' and quantity = '%s' and price = '%s' and amount = '%s'" %(content,custome_name,material,size,quantity,price,amount)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if choice == 3:
layout = [[sg.Text("请输入要修改的内容:(年.月.日)")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = str(values.iat[0,0])
format_str = '%Y.%m.%d'
content = datetime.datetime.strptime(content, format_str)
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set time = '%s' where custome_name = '%s'and order_name = '%s' and material = '%s' and size = '%s' and quantity = '%s' and price = '%s' and amount = '%s'" %(content,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()
os.system('python revise.py')
break
if choice == 4:
layout = [[sg.Text("请输入要修改的内容:")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = str(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set material = '%s' where custome_name = '%s' and order_name= '%s' and size = '%s' and quantity = '%s' and price = '%s' and amount = '%s'" %(content,custome_name,order_name,size,quantity,price,amount)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if choice == 5:
layout = [[sg.Text("请输入要修改的内容:")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = str(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set size = '%s' where custome_name = '%s' and order_name= '%s' and material= '%s' and quantity = '%s' and price = '%s' and amount = '%s'" %(content,custome_name,order_name,material,quantity,price,amount)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if choice == 6:
layout = [[sg.Text("请输入要修改的内容:")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = int(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set quantity = '%s' where custome_name = '%s' and order_name= '%s' and material= '%s' and size = '%s' and price = '%s' and amount = '%s'" %(content,custome_name,order_name,material,size,price,amount)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if choice == 7:
layout = [[sg.Text("请输入要修改的内容:")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = float(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set price = '%s' where custome_name = '%s' and order_name= '%s' and material= '%s' and size = '%s' and quantity= '%s' and amount = '%s'" %(content,custome_name,order_name,material,size,quantity,amount)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if choice == 8:
layout = [[sg.Text("请输入要修改的内容:")],
[sg.Input(key='-INPUT-')],
[sg.Button('确定'),sg.Button('返回')]]
window = sg.Window('修改', layout)
while True:
event, values = window.read()
if event == sg.WINDOW_CLOSED :
break
if event == '返回':
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
if event == '确定':
values = pd.DataFrame([values])
content = float(values.iat[0,0])
cursor = conn.cursor()
conn.ping(reconnect=True)
thread_lock = threading.Lock()
thread_lock.acquire()
sql ="update custome_information set amount = '%s' where custome_name = '%s' and order_name= '%s' and material= '%s' and size = '%s' and quantity= '%s' and price = '%s'" %(content,custome_name,order_name,material,size,quantity,price)
cursor.execute(sql)
conn.commit()
conn.close()
thread_lock.release()
time.sleep(0.5)
window.close()
os.system('python revise.py')
break
else:
window['-OUTPUT-'].update("请输入正确的序号!")
代码运行截图:
之后将持续更新,search.py查找操作。