Python中Tkinter和SQLite设计商品信息管理系统（第15节）

pip install openpyxl
pip install pillow
pip install pandas

import os, sqlite3
import tkinter as tk
from tkinter import ttk, messagebox
import tkinter.font as tkFont
from tkinter.filedialog import askopenfilename, askdirectory
import openpyxl
import pandas as pd
from PIL import Image, ImageTk, ImageDraw

import os, sqlite3

# 定义数据库文件为“product_info.db”
name_file = 'product_info.db'

# 连接到数据库（如果数据库不存在，则会自动创建）
try:
    if not os.path.exists(name_file):
        # 创建表格（如果尚不存在）
        def get_db_connection():
            conn = sqlite3.connect(name_file)
            return conn

        # 插入数据
        def create_db():
            conn = get_db_connection()
            c = conn.cursor()
            # 创建商品信息product表，对程序中的商品信息进行存储
            c.execute('''CREATE TABLE IF NOT EXISTS product (
                            product_id int not null,
                            product_name char not null,
                            product_price char not null,
                            product_stock char not null)''')
            # 创建一张管理员user表
            c.execute('''CREATE TABLE IF NOT EXISTS user (
                            username char not null,
                            password char not null)''')

            # 向user表中直接插入账号“admin”和密码“123456”
            c.execute('''INSERT INTO user (username, password) VALUES ('admin', 123456)''')

            # 定义元组列表
            data = [
                (1, '苹果', '6', '100'),
                (2, '香蕉', '3', '200'),
                (3, '大白菜', '2', '50'),
                (4, '西红柿', '3', '60'),
                (5, '土豆', '2', '30')
            ]

            # 使用元组列表将默认的商品信息插入product表中
            c.executemany('INSERT INTO product (product_id, product_name, product_price, product_stock) VALUES (?, ?, ?, ?)', data)

            conn.commit()  # 提交事务以保存数据库的更改
            conn.close()   # 关闭连接
        # 执行插入数据
        create_db()
except:
    pass

import os, sqlite3
import tkinter as tk
from tkinter import ttk, messagebox
import tkinter.font as tkFont
from tkinter.filedialog import askopenfilename, askdirectory
import openpyxl
import pandas as pd
from PIL import Image, ImageTk, ImageDraw

# 获取当前脚本文件所在的目录
default_directory = os.path.dirname(os.path.abspath(__file__))
# 将当前脚本文件所在的目录设置为工作目录
os.chdir(default_directory)

# 定义数据库文件为“product_info.db”
name_file = 'product_info.db'

# 连接到数据库（如果数据库不存在，则会自动创建）
try:
    if not os.path.exists(name_file):
        # 创建表格（如果尚不存在）
        def get_db_connection():
            conn = sqlite3.connect(name_file)
            return conn

        # 插入数据
        def create_db():
            conn = get_db_connection()
            c = conn.cursor()
            # 创建商品信息product表，对程序中的商品信息进行存储
            c.execute('''CREATE TABLE IF NOT EXISTS product (
                            product_id int not null,
                            product_name char not null,
                            product_price char not null,
                            product_stock char not null)''')
            # 创建一张管理员user表
            c.execute('''CREATE TABLE IF NOT EXISTS user (
                            username char not null,
                            password char not null)''')

            # 向user表中直接插入账号“admin”和密码“123456”
            c.execute('''INSERT INTO user (username, password) VALUES ('admin', 123456)''')

            # 定义元组列表
            data = [
                (1, '苹果', '6', '100'),
                (2, '香蕉', '3', '200'),
                (3, '大白菜', '2', '50'),
                (4, '西红柿', '3', '60'),
                (5, '土豆', '2', '30')
            ]

            # 使用元组列表将默认的商品信息插入product表中
            c.executemany('INSERT INTO product (product_id, product_name, product_price, product_stock) VALUES (?, ?, ?, ?)', data)

            conn.commit()  # 提交事务以保存数据库的更改
            conn.close()   # 关闭连接
        # 执行插入数据
        create_db()
except:
    pass

# 定义函数，在数据库中匹配用户名和密码是否正确
def login(username, password):
    connection = sqlite3.connect(name_file)
    sql = 'select * from user where username=? and password=?'
    cursor = connection.cursor()
    cursor.execute(sql, (username, password))
    result = cursor.fetchall()
    connection.commit()
    connection.close()
    if len(result) == 0:
        return False
    else:
        return True

# 定义登录验证函数
def login_verify(username, password):
    errMessage = ""
    result = False
    if len(username) == 0:
        errMessage = errMessage + "用户名不能为空！"
    elif len(password) == 0:
        errMessage = errMessage + "密码不能为空！"
    else:
        result = login(username, password)

    if result == True:
        errMessage = errMessage + "登录成功"
    else:
        errMessage = errMessage + "用户名或密码错误"
    return errMessage

# 定义product类
class product:
    def __init__(self, product_id, product_name, product_price, product_stock):
        self.product_id = product_id
        self.product_name = product_name
        self.product_price = product_price
        self.product_stock = product_stock

    def get_product(self):
        com_dict = {
            'product_id': self.product_id,
            'product_name': self.product_name,
            'product_price': self.product_price,
            'product_stock': self.product_stock,
        }
        return com_dict

# 定义导入商品数据的excel类
class add_product_excel:
    def __init__(self, path):
        self.filepath = path
        self.excel_list = []
        self.result = None
        self.add_data()

    def add_data(self):
        inwb = openpyxl.load_workbook(self.filepath)
        ws = inwb[inwb.sheetnames[0]]
        excel_max_row = ws.max_row

        for row in range(2, excel_max_row + 1):
            product_id = ws.cell(row, 1).value,
            product_name = ws.cell(row, 2).value,
            product_price = ws.cell(row, 3).value,
            product_stock = ws.cell(row, 4).value,
            com = product(product_id, product_name, product_price, product_stock)
            com_dict = com.get_product()
            self.excel_list.append(com_dict)
        result_list = adds_product(self.excel_list)
        self.result = result_list[0]

# 定义导出excel类
class product_excel:
    def __init__(self, path):
        self.filepath = path
        self.result = None
        self.data_example()

    # 向excel表格中插入默认数据
    def data_example(self):
        com = product('请删除本行','商品编号不能重复','1','1')
        product_dict = com.get_product()
        excel_list = [product_dict]
        product_id = []
        product_name = []
        product_price = []
        product_stock = []

        for lists in excel_list:
            product_id.append(lists['product_id'])
            product_name.append(lists['product_name'])
            product_price.append(lists['product_price'])
            product_stock.append(lists['product_stock'])
        testData = [product_id, product_name, product_price, product_stock]
        name = '/商品批量导入模板.xlsx'
        filepath = self.filepath + name
        self.result = self.pd_toexcel(testData, filepath)

    # 利用pandas库保存数据到excel
    def pd_toexcel(self, data, filename): 
        dfData = {
            '商品编号': data[0],
            '商品名称': data[1],
            '商品价格': data[2],
            '商品库存': data[3],
        }
        # 创建DataFrame
        df = pd.DataFrame(dfData)  
        df.to_excel(filename, index=False)
        if len(df) != 0:
            return True
        else:
            return False

# 定义查询函数
def search_product(product_id):
    com =  search(product_id.get())
    if com == False:
        product_id.set('查询失败')
        product_name_label.config(text='查询失败')
        product_price_label.config(text='查询失败')
        product_stock_label.config(text='查询失败')
    else:
        product_name_label.config(text=com.get_product()['product_name'])
        product_price_label.config(text=com.get_product()['product_price'])
        product_stock_label.config(text=com.get_product()['product_stock'])

# 定义删除函数
def delete_id_product(product_id):
    result = delete(product_id.get())
    if result == True:
        messagebox.showinfo('提示', '删除成功')
    else:
        messagebox.showinfo('提示', '删除失败')

# 创建删除页面
def delete_product():
    delete = tk.Toplevel()
    global product_name
    global product_price
    global product_stock
    global product_name_label
    global product_price_label
    global product_stock_label
    product_name = '查询中'
    product_price = '查询中'
    product_stock = '查询中'
    delete.title('删除商品')

    # 设置窗口居中
    window_width = 600
    window_height = 400
    screen_width = delete.winfo_screenwidth()
    screen_height = delete.winfo_screenheight()
    x = (screen_width - window_width) / 2
    y = (screen_height - window_height) / 2
    delete.geometry('%dx%d+%d+%d' % (window_width, window_height, x, y))
    delete.resizable(width=False, height=False)

    delete_frame = tk.Frame(delete, bg='#56cdff',width=600,height=400)
    delete_frame.pack()
    delete_font_1 = tkFont.Font(family='宋体', size=25, weight=tkFont.BOLD)
    tk.Label(delete_frame, text='删除商品信息', font=delete_font_1, bg='#56cdff').place(x=200, y=20)
    delete_font_2 = tkFont.Font(family='宋体', size=15)
    tk.Label(delete_frame, text='商品编号：', font=delete_font_2, bg='#56cdff').place(x=200, y=90)
    tk.Label(delete_frame, text='商品名称：', font=delete_font_2, bg='#56cdff').place(x=200, y=150)
    tk.Label(delete_frame, text='商品价格：', font=delete_font_2, bg='#56cdff').place(x=200, y=210)
    tk.Label(delete_frame, text='商品库存：', font=delete_font_2, bg='#56cdff').place(x=200, y=270)
    product_id = tk.StringVar()
    tk.Entry(delete_frame, highlightthickness=1, font=('宋体', 15), bg='#F3F3F4',textvariable=product_id).place(x=300, y=90, width=200, height=30)
    product_name_label = tk.Label(delete_frame, text=product_name, font=delete_font_2, bg='#56cdff')
    product_name_label.place(x=300, y=150)
    product_price_label = tk.Label(delete_frame, text=product_price, font=delete_font_2, bg='#56cdff')
    product_price_label.place(x=300, y=210)
    product_stock_label = tk.Label(delete_frame, text=product_stock, font=delete_font_2, bg='#56cdff')
    product_stock_label.place(x=300, y=270)
    tk.Button(delete_frame, text='立即查询', font=('宋体', 15, 'bold'), fg='#000000', bg="#ffffff", command=lambda:search_product(product_id)).place(x=140, y=325, width=120, height=40)
    tk.Button(delete_frame, text='立即删除', font=('宋体', 15, 'bold'), fg='#000000', bg="#ffffff", command=lambda:delete_id_product(product_id)).place(x=340, y=325, width=120, height=40)
    delete.mainloop()

# 创建商品修改页面
def product_information():
    global product_id
    global product_name
    global product_price
    global product_stock
    info = tk.Toplevel()
    info.title('商品信息')

    # 设置窗口居中
    window_width = 600
    window_height = 400
    screen_width = info.winfo_screenwidth()
    screen_height = info.winfo_screenheight()
    x = (screen_width - window_width) / 2
    y = (screen_height - window_height) / 2
    info.geometry('%dx%d+%d+%d' % (window_width, window_height, x, y))
    info.resizable(width=False, height=False)

    info_frame = tk.Frame(info, bg='#56cdff',width=600,height=55)
    info_frame.place(x=0,y=0)
    search_font_1 = tkFont.Font(family='宋体', size=25, weight=tkFont.BOLD)
    tk.Label(info_frame, text='商品信息', font=search_font_1, bg='#56cdff').place(x=230, y=10)

    entries = []
    product_list = select_product()
    for i in product_list:
        args = (i.get_product()['product_id'], i.get_product()['product_name'], i.get_product()['product_price'],
                i.get_product()['product_stock'])
        entries.append(args)
    tabel_frame = tk.Frame(info)
    tabel_frame.place(x=0, y=55, width=600, height=345)
    yscroll = tk.Scrollbar(tabel_frame, orient=tk.VERTICAL)
    style = ttk.Style()
    style.configure('Treeview.Heading', font=(None, 14))
    style.configure('Treeview', rowheight=30, font=(None,12))
    tree = ttk.Treeview(
        master=tabel_frame,
        columns=('商品编号', '商品名称', '商品价格', '商品库存'),
        yscrollcommand=yscroll.set,
    )
    yscroll.config(command=tree.yview)
    yscroll.pack(side=tk.RIGHT, fill=tk.Y)
    tree['show'] = 'headings'
    tree.heading('#1', text='商品编号')
    tree.heading('#2', text='商品名称')
    tree.heading('#3', text='商品价格')
    tree.heading('#4', text='商品库存')
    tree.column('#1', stretch=tk.YES, width=150, minwidth=150, anchor='center')
    tree.column('#2', stretch=tk.YES, width=150, minwidth=150, anchor='center')
    tree.column('#3', stretch=tk.YES, width=150, minwidth=150, anchor='center')
    tree.column('#4', stretch=tk.YES, width=150, minwidth=150, anchor='center')
    tree.pack(fill=tk.BOTH, expand=1)

    for entry in entries:
        tree.insert('', 'end', values=(entry[0], entry[1], entry[2], entry[3]))

    # 鼠标双击商品列表任意一行触发的函数
    def on_double_click(event):
        # 获取被双击的项的IID
        iid = tree.focus()
        if iid == '':
            return  # 如果没有选中任何一行，则直接返回

        # 获取该行的所有值
        values = tree.item(iid, "values")

        # 创建一个用于编辑的窗口
        edit_window = tk.Toplevel()
        edit_window.title("修改商品")

        # 设置窗口居中
        window_width = 400
        window_height = 400
        screen_width = edit_window.winfo_screenwidth()
        screen_height = edit_window.winfo_screenheight()
        x = (screen_width - window_width) / 2
        y = (screen_height - window_height) / 2
        edit_window.geometry('%dx%d+%d+%d' % (window_width, window_height, x, y))
        edit_window.resizable(width=False, height=False)

        # 创建标签和输入框，用于编辑每个值
        labels = []
        entries = []
        # 定义自定义的键
        keys = ['商品编号：', '商品名称：', '商品价格：', '商品库存：']
        for i, value in enumerate(values):
            # 设置“商品编号”不可编辑
            if i == 0:
                edit_font =  tkFont.Font(family='宋体', size=15, weight=tkFont.BOLD)
                lbl = tk.Label(edit_window, text=f"{keys[i]}", font=edit_font)
                lbl.grid(row=i, column=0, padx=20, pady=20)
                # 创建Entry控件，“商品编号”设置为不可更改
                var = tk.StringVar()
                var.set(value)
                ent = tk.Entry(edit_window, textvariable=var, font=('宋体', 15), state='disabled')
                ent.grid(row=i, column=1, padx=20, pady=20)
            else:
                edit_font =  tkFont.Font(family='宋体', size=15, weight=tkFont.BOLD)
                lbl = tk.Label(edit_window, text=f"{keys[i]}", font=edit_font)
                lbl.grid(row=i, column=0, padx=20, pady=20)
                ent = tk.Entry(edit_window, font=('宋体', 15))
                ent.insert(0, value)  # 默认显示当前值
                ent.grid(row=i, column=1, padx=20, pady=20)
            labels.append(lbl)
            entries.append(ent)

        # 定义“确认修改”按钮的处理函数
        def confirm_edit():
            product_id_data, product_name_data, product_price_data, product_stock_data = [ent.get() for ent in entries]
            result = change(product_id_data, product_name_data, product_price_data, product_stock_data)
            if result == True:
                messagebox.showinfo('提示', '修改成功')
            else:
                messagebox.showinfo('提示', '修改失败')
            edit_window.destroy()  # 关闭编辑窗口
            product_information()

        # 创建确认和取消按钮
        confirm_button = tk.Button(edit_window, text="确认修改", command=confirm_edit, font=('宋体', 15))
        confirm_button.grid(row=len(values), column=0, columnspan=2, padx=30, pady=10)
        cancel_button = tk.Button(edit_window, text="取消修改", command=edit_window.destroy, font=('宋体', 15))
        cancel_button.grid(row=len(values) + 1, column=0, columnspan=2, padx=30, pady=10)

    # 绑定双击事件
    tree.bind("<Double-1>", on_double_click)
    info.mainloop()

# 创建添加商品页面
def begin_add_product():
    global product_id
    global product_name
    global product_price
    global product_stock
    add = tk.Toplevel()
    product_id = tk.StringVar()
    product_name = tk.StringVar()
    product_price = tk.StringVar()
    product_stock = tk.StringVar()
    add.title('添加商品')

    # 设置窗口居中
    window_width = 600
    window_height = 400
    screen_width = add.winfo_screenwidth()
    screen_height = add.winfo_screenheight()
    x = (screen_width - window_width) / 2
    y = (screen_height - window_height) / 2
    add.geometry('%dx%d+%d+%d' % (window_width, window_height, x, y))
    add.resizable(width=False, height=False)

    add_frame = tk.Frame(add, bg='#56cdff',width=600,height=400)
    add_frame.pack()
    add_font_1 = tkFont.Font(family='宋体', size=25, weight=tkFont.BOLD)
    tk.Label(add_frame, text='添加商品信息', font=add_font_1, bg='#56cdff').place(x=200, y=20)
    add_font_2 = tkFont.Font(family='宋体', size=15)
    tk.Label(add_frame, text='商品编号：', font=add_font_2, bg='#56cdff').place(x=100, y=90)
    change_font_3 = tkFont.Font(family='宋体', size=9)
    tk.Label(add_frame, text='注意：商品编号不可重复', font=change_font_3, bg='#56cdff').place(x=235, y=125)
    tk.Label(add_frame, text='商品名称：', font=add_font_2, bg='#56cdff').place(x=100, y=150)
    tk.Label(add_frame, text='商品价格：', font=add_font_2, bg='#56cdff').place(x=100, y=210)
    tk.Label(add_frame, text='商品库存：', font=add_font_2, bg='#56cdff').place(x=100, y=270)
    tk.Entry(add_frame, highlightthickness=1, font=('宋体', 15), bg='#F3F3F4',textvariable=product_id).place(x=300, y=90, width=200, height=30)
    tk.Entry(add_frame, highlightthickness=1, font=('宋体', 15), bg='#F3F3F4', textvariable=product_name).place(x=300, y=150, width=200, height=30)
    tk.Entry(add_frame, highlightthickness=1, font=('宋体', 15), bg='#F3F3F4', textvariable=product_price).place(x=300, y=210, width=200, height=30)
    tk.Entry(add_frame, highlightthickness=1, font=('宋体', 15), bg='#F3F3F4', textvariable=product_stock).place(x=300, y=270, width=200, height=30)
    tk.Button(add_frame, text='批量添加', font=('宋体', 15, 'bold'), fg='#000000', bg="#ffffff", command=import_product).place(x=70, y=325, width=120, height=40)
    tk.Button(add_frame, text='立即添加', font=('宋体', 15, 'bold'), fg='#000000', bg="#ffffff", command=add_product).place(x=230, y=325, width=120, height=40)
    tk.Button(add_frame, text='下载模板', font=('宋体', 15, 'bold'), fg='#000000', bg="#ffffff", command=export_product1).place(x=390, y=325, width=120, height=40)
    add.mainloop()

# 定义导入Excel函数
def import_product():
    f = askopenfilename(title="上传文件", initialdir="D:",filetypes=[("Excel表格",".xlsx")])
    try:
        excel1 = add_product_excel(f)
        if excel1.result == True:
            messagebox.showinfo('提示', '导入成功')
        else:
            messagebox.showinfo('提示', '导入失败')
    except:
        pass

# 定义添加商品函数
def add_product():
    get_product_number = product_id.get()
    get_product_name = product_name.get()
    get_product_price = product_price.get()
    get_product_stock = product_stock.get()
    try:
        get_product_number = int(get_product_number)
    except ValueError:
        messagebox.showwarning("警告", "请输入一个有效的整数！")
        return
    if get_product_number == '' or get_product_name == '' or get_product_price == '' or get_product_stock == '':
        messagebox.showwarning(title='提示', message="商品信息不能为空")
    else:
        # 查询商品编号是否存在
        conn = sqlite3.connect(name_file)
        # 创建一个Cursor
        cursor = conn.cursor()
        cursor.execute('select product_id from product')
        values = cursor.fetchall()
        userList = []
        for i in values:
            userList.append(i[0])
        if get_product_number in userList:
            messagebox.showwarning('提示', '该商品编号已存在，请输入其它编号！')
        else:
            try:
                result = add(product_id.get(), product_name.get(), product_price.get(), product_stock.get())
                if result == True:
                    messagebox.showinfo('提示', '添加成功')
                else:
                    messagebox.showinfo('提示', '添加失败')
            except:
                pass
        cursor.close()  # 关闭Cursor
        conn.commit()  # 提交事务
        conn.close()   # 关闭数据库连接
    return 'break'

# 选择Excel导出的文件路径
def export_product1():
    file = askdirectory(title="选择保存的文件夹")
    excel1 = product_excel(file)
    if excel1.result == True:
        messagebox.showinfo('提示', '保存成功')
    else:
        messagebox.showinfo('提示', '保存失败')

# 创建查询窗口
def begin_search_product():
    search = tk.Toplevel()
    search.title('查询商品')

    # 设置窗口居中
    window_width = 600
    window_height = 400
    screen_width = search.winfo_screenwidth()
    screen_height = search.winfo_screenheight()
    x = (screen_width - window_width) / 2
    y = (screen_height - window_height) / 2
    search.geometry('%dx%d+%d+%d' % (window_width, window_height, x, y))
    search.resizable(width=False, height=False)

    global product_name
    global product_price
    global product_stock
    global product_name_label
    global product_price_label
    global product_stock_label
    product_name = '查询中'
    product_price = '查询中'
    product_stock = '查询中'

    search_frame = tk.Frame(search, bg='#56cdff',width=600,height=400)
    search_frame.pack()
    search_font_1 = tkFont.Font(family='宋体', size=25, weight=tkFont.BOLD)
    tk.Label(search_frame, text='查询商品信息', font=search_font_1, bg='#56cdff').place(x=200, y=20)
    search_font_2 = tkFont.Font(family='宋体', size=15)
    tk.Label(search_frame, text='商品编号：', font=search_font_2, bg='#56cdff').place(x=200, y=90)
    tk.Label(search_frame, text='商品名称：', font=search_font_2, bg='#56cdff').place(x=200, y=150)
    tk.Label(search_frame, text='商品价格：', font=search_font_2, bg='#56cdff').place(x=200, y=210)
    tk.Label(search_frame, text='商品库存：', font=search_font_2, bg='#56cdff').place(x=200, y=270)
    product_id = tk.StringVar()
    search_frame_entry = tk.Entry(search_frame, highlightthickness=1, font=('宋体', 15), bg='#F3F3F4',textvariable=product_id)
    search_frame_entry.place(x=300, y=90, width=200, height=30)
    product_name_label = tk.Label(search_frame, text=product_name, font=search_font_2, bg='#56cdff')
    product_name_label.place(x=300, y=150)
    product_price_label = tk.Label(search_frame, text=product_price, font=search_font_2, bg='#56cdff')
    product_price_label.place(x=300, y=210)
    product_stock_label = tk.Label(search_frame, text=product_stock, font=search_font_2, bg='#56cdff')
    product_stock_label.place(x=300, y=270)
    search_frame_button = tk.Button(search_frame, text='立即查询', font=('宋体', 15, 'bold'), fg='#000000', bg="#ffffff", command=lambda:search_product(product_id))
    search_frame_button.place(x=180, y=325, width=240, height=40)
    search.mainloop()

# 创建程序介绍页面
def program_about():
    about = tk.Tk()
    about.title('关于程序')

    # 设置窗口居中
    window_width = 320
    window_height = 320
    screen_width = about.winfo_screenwidth()
    screen_height = about.winfo_screenheight()
    x = (screen_width - window_width) / 2
    y = (screen_height - window_height) / 2
    about.geometry('%dx%d+%d+%d' % (window_width, window_height, x, y))
    about.resizable(width=False, height=False)

    about_frame = tk.Frame(about, bg='#56cdff', width=320, height=320)
    about_frame.pack()
    about_font_1 = tkFont.Font(family='宋体', size=16, weight=tkFont.BOLD)
    tk.Label(about_frame, text='商品信息管理系统', font=about_font_1, bg='#56cdff').place(x=110, y=20)
    tk.Label(about_frame, text='使用编程语言：Python', font=about_font_1, bg='#56cdff').place(x=50, y=90)
    tk.Label(about_frame, text='使用数据库：SQLite数据库', font=about_font_1, bg='#56cdff').place(x=50, y=150)
    tk.Label(about_frame, text='数据库文件：product_info.db', font=about_font_1, bg='#56cdff').place(x=50, y=210)
    tk.Label(about_frame, text='创作者：www.pyhint.com', font=about_font_1, bg='#56cdff').place(x=50, y=270)
    about.mainloop()

def search(product_id):
    connection = sqlite3.connect(name_file)
    sql = """select * from product where product_id=?"""
    cursor = connection.cursor()
    cursor.execute(sql,(product_id,))
    result = cursor.fetchall()
    connection.commit()
    connection.close()
    if len(result) == 0:
        return False
    else:
        com = product(result[0][0], result[0][1], result[0][2], result[0][3])
        return com

def change(product_id, product_name, product_price, product_stock):
    connection = sqlite3.connect(name_file)
    sql = """update product set product_name = ? , product_price = ? , product_stock = ? where product_id = ?"""
    cursor = connection.cursor()
    cursor.execute(sql, (product_name, product_price, product_stock, product_id,))
    result = cursor.fetchall()
    connection.commit()
    connection.close()
    if len(result) == 0:
        return True
    else:
        return False

def add(product_id, product_name, product_price, product_stock):
    connection = sqlite3.connect(name_file)

    sql = """insert into product(product_id, product_name, product_price, product_stock) values (?,?,?,?)"""
    cursor = connection.cursor()
    cursor.execute(sql, (product_id, product_name, product_price, product_stock,))
    result = cursor.fetchall()
    connection.commit()
    connection.close()
    if len(result) == 0:
        return True
    else:
        return False

def adds_product(excel_list):
    result = []
    excel_list = excel_list
    for com in excel_list:
        product_id = com['product_id'][0]
        product_name = com['product_name'][0]
        product_price = com['product_price'][0]
        product_stock = com['product_stock'][0]
        resu = add(product_id, product_name, product_price, product_stock)
        result.append(resu)
    return result

def select_product():
    connection = sqlite3.connect(name_file)

    sql = """select * from product"""
    cursor = connection.cursor()
    cursor.execute(sql)
    results = cursor.fetchall()
    connection.commit()
    connection.close()
    product_list = []
    for result in results:
        product_li = product(result[0], result[1], result[2], result[3])
        product_list.append(product_li)
    return product_list

def delete(product_id):
    connection = sqlite3.connect(name_file)
    sql = """delete from product where product_id=?"""
    cursor = connection.cursor()
    cursor.execute(sql, (product_id,))
    result = cursor.fetchall()
    connection.commit()
    connection.close()
    if len(result) == 0:
        return True
    else:
        return False

def begin_change_product():
    global product_id
    global product_name
    global product_price
    global product_stock
    global product_name_label
    global product_price_label
    global product_stock_label
    product_id = tk.StringVar()
    product_name = tk.StringVar()
    product_price = tk.StringVar()
    product_stock = tk.StringVar()
    change = tk.Toplevel()
    change.title('修改商品')

    # 设置窗口居中
    window_width = 600
    window_height = 400
    screen_width = change.winfo_screenwidth()
    screen_height = change.winfo_screenheight()
    x = (screen_width - window_width) / 2
    y = (screen_height - window_height) / 2
    change.geometry('%dx%d+%d+%d' % (window_width, window_height, x, y))
    change.resizable(width=False, height=False)

    change_frame = tk.Frame(change, bg='#56cdff',width=600,height=400)
    change_frame.pack()
    change_font_1 = tkFont.Font(family='宋体', size=25, weight=tkFont.BOLD)
    tk.Label(change_frame, text='修改商品信息', font=change_font_1, bg='#56cdff').place(x=200, y=20)
    change_font_2 = tkFont.Font(family='宋体', size=15)
    change_font_3 = tkFont.Font(family='宋体', size=9)
    tk.Label(change_frame, text='商品编号：', font=change_font_2, bg='#56cdff').place(x=100, y=90)
    tk.Label(change_frame, text='注意：商品编号不可修改', font=change_font_3, bg='#56cdff').place(x=235, y=125)
    tk.Label(change_frame, text='商品名称：', font=change_font_2, bg='#56cdff').place(x=100, y=150)
    tk.Label(change_frame, text='商品价格：', font=change_font_2, bg='#56cdff').place(x=100, y=210)
    tk.Label(change_frame, text='商品库存：', font=change_font_2, bg='#56cdff').place(x=100, y=270)

    tk.Entry(change_frame, highlightthickness=1, font=('宋体', 15), bg='#F3F3F4',textvariable=product_id).place(x=300, y=90, width=200, height=30)
    tk.Entry(change_frame, highlightthickness=1, font=('宋体', 15), bg='#F3F3F4', textvariable=product_name).place(x=300, y=150, width=200, height=30)
    tk.Entry(change_frame, highlightthickness=1, font=('宋体', 15), bg='#F3F3F4', textvariable=product_price).place(x=300, y=210, width=200, height=30)
    tk.Entry(change_frame, highlightthickness=1, font=('宋体', 15), bg='#F3F3F4', textvariable=product_stock).place(x=300, y=270, width=200, height=30)
    tk.Button(change_frame, text='立即查询', font=('宋体', 15, 'bold'), fg='#000000', bg="#ffffff", command=search_product1).place(x=140, y=325, width=120, height=40)
    tk.Button(change_frame, text='立即修改', font=('宋体', 15, 'bold'), fg='#000000', bg="#ffffff", command=change_product).place(x=340, y=325, width=120, height=40)
    change.mainloop()

# 定义函数，绑定“立即查询”按钮
def search_product1():
    com =  search(product_id.get())
    if com == False:
        product_id.set('查询失败')
        product_name.set('查询失败')
        product_price.set('查询失败')
        product_stock.set('查询失败')
    else:
        product_name.set(com.get_product()['product_name'])
        product_price.set(com.get_product()['product_price'])
        product_stock.set(com.get_product()['product_stock'])

# 定义函数，绑定“立即修改”按钮
def change_product():
    result = change(product_id.get(), product_name.get(), product_price.get(), product_stock.get())
    product_name.set('')
    product_price.set('')
    product_stock.set('')
    if result == True:
        messagebox.showinfo('提示', '修改成功')
    else:
        messagebox.showinfo('提示', '修改失败')

# 通过Pillow库创建渐变效果函数，用于主页面的背景图像
def create_gradient_image(width, height, color1, color2):
    img = Image.new('RGB', (width, height))
    draw = ImageDraw.Draw(img)
    for i in range(width):
        color = tuple(int(c1 + (c2 - c1) * i / width) for c1, c2 in zip(color1, color2))
        draw.line((i, 0, i, height), fill=color)
    return img

# 创建主程序页面
class MainFrame(tk.Frame):
    def __init__(self, master=None):
        super().__init__(master)
        self.master = master
        self.pack()
        self.createWidget()
        win.title('商品信息管理系统')

        # 设置窗口居中
        window_width = 800
        window_height = 520
        screen_width = win.winfo_screenwidth()
        screen_height = win.winfo_screenheight()
        x = (screen_width - window_width) / 2
        y = (screen_height - window_height) / 2
        win.geometry('%dx%d+%d+%d' % (window_width, window_height, x, y))
        win.resizable(width=False, height=False)

    def createWidget(self):
        # 创建渐变图像并转换为PhotoImage对象
        gradient_image = create_gradient_image(800, 540, (187, 255, 255), (119, 221, 255))
        photo_login = ImageTk.PhotoImage(gradient_image)
        self.label_bg = tk.Label(self, image=photo_login)
        # 保持对photo的引用，防止被垃圾回收器回收
        self.label_bg.image = photo_login
        self.label_bg.pack()
        # 添加商品操作按钮控件，用于修改或显示商品信息
        Button1_Add = tk.Button(win, text = '商品信息', bg = '#56cdff', font = ('黑体', 15), command = self.index_info, width = 20)
        Button1_Add.place(x = 40, y = 30, anchor = 'nw')
        Button2_Del = tk.Button(win, text = '查询商品', bg = '#56cdff', font = ('黑体', 15), command = self.index_search, width = 20)
        Button2_Del.place(x = 40, y = 100, anchor = 'nw')
        Button3_Mod = tk.Button(win, text = '添加商品', bg = '#56cdff', font = ('黑体', 15), command = self.index_add, width = 20)
        Button3_Mod.place(x = 40, y = 170, anchor = 'nw')
        Button4_Ser = tk.Button(win, text = '修改商品', bg = '#56cdff', font = ('黑体', 15), command = self.index_change, width = 20)
        Button4_Ser.place(x = 40, y = 240, anchor = 'nw')
        Button5_Show = tk.Button(win, text = '删除商品', bg = '#56cdff', font = ('黑体', 15), command = self.index_delete, width = 20)
        Button5_Show.place(x = 40, y = 310, anchor = 'nw')
        Button6_Exit = tk.Button(win, text = '关于程序', bg = '#56cdff', font = ('黑体', 15), command = self.index_about, width = 20)
        Button6_Exit.place(x = 40, y = 380, anchor = 'nw')
        Button6_Exit = tk.Button(win, text = '退出', bg = '#56cdff', font = ('黑体', 15), command = self.index_quit, width = 20)
        Button6_Exit.place(x = 40, y = 450, anchor = 'nw')

        # 创建一个Text控件，用于输出程序说明信息
        txt = '''                 >>>欢迎使用商品信息管理系统<<<
            说明：本程序是一个基于Python开发的商品信息管理系统或者超市信息管理系统。
            使用了Python的Tkinter库构建GUI图形用户界面，并结合SQLite3库进行数据存储。
            运行该程序时，默认会在当前工作目录下创建一个名为“product_info.db”数据库文件，用于存储商品信息。
            支持查询、添加、修改、删除商品信息，并且支持Excel批量导入添加商品信息。
            在商品信息页面，鼠标双击任何一行商品信息，即可实现编辑商品信息功能。
            '''
        Show_result = tk.Text(win, spacing1=2, spacing2=2, spacing3=2, wrap='word', font=10, width=40, height=10)
        Show_result.place(x = "280", y = "30", width = "500", height = "456")
        Show_result.insert(tk.END, txt)

    # 商品信息
    def index_info(self):
        product_information()
    # 查询商品
    def index_search(self):
        begin_search_product()
    # 添加商品
    def index_add(self):
        begin_add_product()
    # 修改商品
    def index_change(self):
        begin_change_product()
    # 删除商品
    def index_delete(self):
        delete_product()
    # 关于本程序
    def index_about(self):
        program_about()
    # 退出-销毁界面
    def index_quit(self):
        self.master.destroy()

# 创建登录页面
class Application(tk.Frame):
    def __init__(self, master=None):
        super().__init__(master)
        self.master = master
        self.pack()
        self.username = tk.StringVar()
        self.password = tk.StringVar()
        self.createWidget()
        win.title('登录商品信息管理系统')

        # 设置窗口居中
        window_width = 560
        window_height = 290
        screen_width = win.winfo_screenwidth()
        screen_height = win.winfo_screenheight()
        x = (screen_width - window_width) / 2
        y = (screen_height - window_height) / 2
        win.geometry('%dx%d+%d+%d' % (window_width, window_height, x, y))
        win.resizable(width=False, height=False)

    def createWidget(self):
        # 创建左框架部分
        left_frame = tk.Frame(self, bg='#56cdff')
        left_frame.pack(side=tk.LEFT, fill=tk.Y)
        left_frame.pack_propagate(False)

        # 创建右框架部分
        right_frame = tk.Frame(self, bg='#ffffff')
        right_frame.pack(side=tk.RIGHT, fill=tk.Y)

        # 在左侧放置说明文本
        welcome_font_1 =  tkFont.Font(family='宋体', size=30, weight=tkFont.BOLD)
        label_welcome_1 = tk.Label(left_frame, text='Welcome', font=welcome_font_1, bg='#56cdff', justify=tk.LEFT)
        label_welcome_1.grid(row=0, column=0, padx=10, pady=30)
        welcome_font_2 = tkFont.Font(family='宋体', size=20, weight=tkFont.BOLD)
        label_welcome_2 = tk.Label(left_frame, text='欢迎登录商品信息', font=welcome_font_2, bg='#56cdff', justify=tk.LEFT)
        label_welcome_2.grid(row=1, column=0, padx=10, pady=30)
        label_welcome_3 = tk.Label(left_frame, text='管理系统', font=welcome_font_2, bg='#56cdff', justify=tk.LEFT)
        label_welcome_3.grid(row=2, column=0, padx=10, pady=30)

        # 在右侧设计登录窗口
        login_title_font = tkFont.Font(family='宋体', size=20, weight=tkFont.BOLD)
        login_title = tk.Label(right_frame, text='用户登录', font=login_title_font, bg='#ffffff', justify=tk.CENTER)
        login_title.grid(row=0, column=0, columnspan=2, padx=10, pady=20)
        login_font = tkFont.Font(family='宋体', size=14)
        user_name_label = tk.Label(right_frame, text='请输入账号:', font=login_font, bg='#ffffff')
        user_name_label.grid(row=1, column=0, padx=10, pady=20)
        user_name_entry = tk.Entry(right_frame, highlightthickness=1, font=('宋体', 15), bg='#F3F3F4',textvariable=self.username, width=15)
        user_name_entry.grid(row=1, column=1, padx=10, pady=20)
        user_password_label = tk.Label(right_frame, text='请输入密码:', font=login_font, bg='#ffffff')
        user_password_label.grid(row=2, column=0, padx=10, pady=20)
        user_password_entry = tk.Entry(right_frame, highlightthickness=1,font=('宋体', 15), bg='#F3F3F4', show='*', textvariable=self.password, width=15)
        user_password_entry.grid(row=2, column=1, padx=10, pady=20)
        login_button = tk.Button(right_frame, text='立即登录', font=('宋体', 15, 'bold'), fg='#000000', bg="#56cdff", command=self.login)
        login_button.grid(row=3, column=0, columnspan=2, padx=20, pady=20, sticky=tk.NSEW)

    def login(self):
        errMessage = ""
        username = self.username.get()
        password = self.password.get()
        new_errMessage = login_verify(username, password)
        errMessage = errMessage + new_errMessage
        if errMessage != "":
            messagebox.showinfo('提示', errMessage)
            self.password.set('')
        if new_errMessage == '登录成功':
            self.login_destroy()
            # 加载主程序界面
            MainFrame(self.master)

    # 销毁界面
    def login_destroy(self):
        self.destroy()

# 当前模块直接被执行
if __name__ == '__main__':
    win = tk.Tk()
    # 加载登录界面
    Application(master=win)
    # 开启主循环，让窗口处于显示状态
    win.mainloop()