基于PysimpleGUI+pymysql建立的简单管理系统（3）—插入功能_pysimplegui读数据库数据

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

        通过上文的menu.py登录界面，我们输入1选择插入功能，并进入对应的insert.py插入文件中。

        在进行插入操作的之前，我们需要创建好对应的数据库表，存储插入的客户账单信息。

        首先，我们创建名为custome_information的客户信息表：

         其中，custome_name代表客户名，order_name代表订单名，time代表时间，material代表材料，size代表尺寸，quantity代表数量，price代表单价，amount代表金额。

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

import pymysql
import PySimpleGUI as sg
import pandas as pd
import time
import os
import datetime
 
conn = pymysql.connect(host="localhost",       #设置mysql的连接
                           user="root",  
                           password="...",   
                           db="user_information",       
                           charset="utf8")      
                         
layout = [[sg.Text("请输入客户名")],            #对交互窗口进行设计
          [sg.Input(key='-INPUT-')],           #每个文本之下，插入输入框
          [sg.Text("请输入订单名")],
          [sg.Input(key='-INPUT-')],
          [sg.Text("请输入时间(年.月.日)")],
          [sg.Input(key='-INPUT-')],
          [sg.Text("请输入材料")],
          [sg.Input(key='-INPUT-')],
          [sg.Text("请输入尺寸")],
          [sg.Input(key='-INPUT-')],
          [sg.Text("请输入数量")],
          [sg.Input(key='-INPUT-')],
          [sg.Text("请输入单价")],
          [sg.Input(key='-INPUT-')],
          [sg.Text("请输入金额")],
          [sg.Input(key='-INPUT-')],
          [sg.Text(size=(40,1), key='-OUTPUT-')],
          [sg.Button('ok'), sg.Button('返回'),sg.Button('退出')]]    #设置按钮
window = sg.Window('添加客户账单', layout)
while True:
    event, values = window.read()              #读取输入的值和动作
    if event == '返回':
        time.sleep(0.5)
        window.close()
        os.system('python menu.py')             #返回菜单界面
        break
    if event == sg.WINDOW_CLOSED or event == '退出':
        break 
    format_str = '%Y.%m.%d'                      #设置日期格式
    values = pd.DataFrame([values])
    custome_name = str(values.iat[0,0])          #提取出输入的信息
    if custome_name == '':                       #输入为空时，报错
        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 insert.py')    #返回插入界面
                break
    order_name = str(values.iat[0,1])        
    if order_name == '':
        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 insert.py')
    times = str(values.iat[0,2])
    if times == '':
        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 insert.py')
                break
    times = datetime.datetime.strptime(times, format_str)    #转换时间格式
    material = str(values.iat[0,3])
    if material == '':
        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 insert.py')
                break
    size = str(values.iat[0,4])
    if size == '':
        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 insert.py')
                break
    quantity = int(values.iat[0,5])
    if quantity == '':
        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 insert.py')
                break
    price = float(values.iat[0,6])
    if price== '':
        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 insert.py')
                break
    amount = float(values.iat[0,7])
    if amount == '':
        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 insert.py')
                break
    cursor = conn.cursor()
    #mysql插入语句
    sql ="insert into custome_information  (custome_name,order_name,time,material,size,quantity,price,amount) values ('%s','%s','%s','%s','%s','%s','%s','%s')"%(custome_name,order_name,times.strftime("%Y.%m.%d"),material,size,quantity,price,amount)
    cursor.execute(sql)
    conn.commit()
    cursor.close()
    conn.close()
    window.close()
    os.system('python menu.py')   #插入成功后返回菜单界面
    
 

接下来将持续更新，delete.py删除等功能的实现。

运行截图：