資料庫課程設計—超市零售資訊管理系統（Python實現）

import pymssql

import tkinter as tk

import tkinter.messagebox

# 資料庫新增操作

def add():

     # 連線資料庫

     connect = pymssql.connect(host = "127.0.0.1:1433",database = "Manager",charset="utf8")

     # 建立游標

     cursor = connect.cursor()

     # 編寫 SQL 語句

     sql = "insert into Goods(Gnum,Gname,Gtype,Gprice,Gbid,Gstock,Galarm,Gplan,Vnum) values('%s','%s','%s','%s','%s','%s','%s','%s',%s)" % (v1.get(), v2.get(), v3.get(), v4.get(), v5.get(), v6.get(), v7.get(), v8.get(), v9.get())

     # 執行 SQL 語句，並且輸出完成提示資訊，否則回滾

     try:

         cursor.execute(sql)

         connect.commit()

         tkinter.messagebox.showinfo(" 提示 ", " 資料新增成功 ")

     except:

         connect.rollback()

     # 關閉資料庫連線，防止洩露

     connect.close()

# 資料庫刪除操作

def delete():

     connect = pymssql.connect(host = "127.0.0.1:1433",database = "Manager",charset="utf8")

     cursor=connect.cursor()

     sql = "delete from Goods where Gnum='%s'" % (v10.get())

     try:

         cursor.execute(sql)

         connect.commit()

         tkinter.messagebox.showinfo(" 提示 "," 資料刪除成功 ")

     except:

         connect.rollback()

     connect.close()

# 資料庫更新操作

def update():

     connect = pymssql.connect(host = "127.0.0.1:1433",database = "Manager",charset="utf8")

     cursor = connect.cursor()

     sql="update Goods set Gstock='%s' where Gnum='%s'"%(v11.get(),v12.get())

     try:

         cursor.execute(sql)

         connect.commit()

         tkinter.messagebox.showinfo(" 提示 "," 資料更新成功！ ")

     except:

         connect.rollback()

     connect.close()

# 資料庫模糊條件查詢

def select():

     connect = pymssql.connect(host = "127.0.0.1:1433",database = "Manager",charset="utf8")

     cursor =跟單網gendan5.com connect.cursor()

     sql = "select Vname from Vendor,Goods where Goods.Vnum=Vendor.Vnum and Goods.Gnum like'%s'"%('%'+v13.get()+'%')

     try:

         cursor.execute(sql)

         results = cursor.fetchall()

         for row in results:

             Vname = row[0]

             tkinter.messagebox.showinfo(" 提示 ","Vname=%s" % (Vname))

     except:

         return

# 新增商品介面

def Staff_add():

     # 構建全集變數，方便上面的函式呼叫

     global window_function

     global v1,v2,v3,v4,v5,v6,v7,v8,v9

     # 生成視窗

     window_function=tk.Tk()

     # 視窗標題

     window_function.title(" 超市零售資訊管理系統 ")

     # 視窗大小

     window_function.geometry('400x700')

     # 生成標籤

     tk.Label(window_function, text=" 新增新商品 ", font=(" 黑體 ", 20)).grid(row=0,column=1,pady=10)

     tk.Label(window_function, text=" 請輸入商品編號： ").grid(row=1, column=0, padx=20, pady=20)

     tk.Label(window_function,text=" 請輸入商品名稱： ").grid(row = 2,column =0,padx=20,pady=20)

     tk.Label(window_function,text=" 請輸入商品類別： ").grid(row = 3,column =0,padx=20,pady=20)

     tk.Label(window_function,text=" 請輸入商品售價： ").grid(row = 4,column =0,padx=20,pady=20)

     tk.Label(window_function, text=" 請輸入商品成本： ").grid(row=5, column=0, padx=20, pady=20)

     tk.Label(window_function, text=" 請輸入庫存量： ").grid(row=6, column=0, padx=20, pady=20)

     tk.Label(window_function, text=" 請輸入告警量： ").grid(row=7, column=0, padx=20, pady=20)

     tk.Label(window_function, text=" 請輸入計劃庫存量： ").grid(row=8, column=0, padx=20, pady=20)

     tk.Label(window_function, text=" 請輸入供貨商編號： ").grid(row=9, column=0, padx=20, pady=20)

     # 定義變數記錄輸入資訊

     v1 = tk.StringVar()

     v2 = tk.StringVar()

     v3 = tk.StringVar()

     v4 = tk.StringVar()

     v5 = tk.StringVar()

     v6 = tk.StringVar()

     v7 = tk.StringVar()

     v8 = tk.StringVar()

     v9 = tk.StringVar()

     # 生成輸入框

     entry1 = tk.Entry(window_function,show=None,textvariable=v1).grid(row = 1,column =1)

     entry2 = tk.Entry(window_function,show=None,textvariable=v2).grid(row = 2,column =1)

     entry3 = tk.Entry(window_function,show=None,textvariable=v3).grid(row = 3,column =1)

     entry4 = tk.Entry(window_function, show=None, textvariable=v4).grid(row=4, column=1)

     entry5 = tk.Entry(window_function, show=None, textvariable=v5).grid(row=5, column=1)

     entry6 = tk.Entry(window_function, show=None, textvariable=v6).grid(row=6, column=1)

     entry7 = tk.Entry(window_function, show=None, textvariable=v7).grid(row=7, column=1)

     entry8 = tk.Entry(window_function, show=None, textvariable=v8).grid(row=8, column=1)

     entry9 = tk.Entry(window_function, show=None, textvariable=v9).grid(row=9, column=1)

     # 生成按鈕

     button = tk.Button(window_function, text=" 新增 ", command=add).place(relx=0.3,rely=0.9)

     button2 = tk.Button(window_function, text=" 返回 ", command=chaneg_main).place(relx=0.5,rely=0.9)

     # 顯示視窗

     window_function.mainloop()

# 刪除商品介面

def Staff_delete():

     global window_function

     global v10

     window_function=tk.Tk()

     window_function.title(" 超市零售資訊管理系統 ")

     window_function.geometry('500x400')

     tk.Label(window_function, text=" 刪除商品 ", font=(" 黑體 ", 20)).grid(row=0,column=1,pady=20)

     tk.Label(window_function,text=" 請輸入商品編號： ").grid(row = 1,column =0,padx=20)

     v10 =tk.StringVar()

     entry1=tk.Entry(window_function,show=None,textvariable=v10).grid(row = 1,column =1,pady=40)

     button = tk.Button(window_function, text=" 刪除 ", command=delete,anchor = 's').place(relx=0.2,rely=0.5)

     button2 = tk.Button(window_function, text=" 返回 ", command=chaneg_main).place(relx=0.4,rely=0.5)

     window_function.mainloop()

# 更新商品資訊介面

def Staff_update():

     global window_function

     global v11,v12

     window_function=tk.Tk()

     window_function.title(" 超市零售資訊管理系統 ")

     window_function.geometry('500x400')

     tk.Label(window_function, text=" 更新商品資訊 ", font=(" 黑體 ", 20)).grid(row=0,column=1,pady=20)

     tk.Label(window_function,text=" 請輸入商品庫存： ").grid(row = 1,column =0,padx=20,pady=20)

     tk.Label(window_function,text=" 請輸入商品編號： ").grid(row = 2,column =0,padx=20,pady=20)

     v11=tk.StringVar()

     v12=tk.StringVar()

     entry1=tk.Entry(window_function,show=None,textvariable=v11).grid(row = 1,column =1)

     entry2=tk.Entry(window_function,show=None,textvariable=v12).grid(row = 2,column =1)

     button = tk.Button(window_function, text=" 更新 ", command=update).place(relx=0.3,rely=0.5)

     button2 = tk.Button(window_function, text=" 返回 ", command=chaneg_main).place(relx=0.5,rely=0.5)

     window_function.mainloop()

# 條件查詢商品介面

def Staff_select():

     global window_function

     global v13

     window_function=tk.Tk()

     window_function.title(" 超市零售資訊管理系統 ")

     window_function.geometry('500x400')

     tk.Label(window_function, text=" 查詢商品的供貨商名稱 ", font=(" 黑體 ", 20)).grid(row=0,column=1,pady=20)

     tk.Label(window_function,text=" 請輸入商品編號： ").grid(row = 1,column =0,padx=20)

     v13 =tk.StringVar()

     entry1=tk.Entry(window_function,show=None,textvariable=v13).grid(row = 1,column =1,pady=40)

     button = tk.Button(window_function, text=" 查詢 ", command=select).place(relx=0.3,rely=0.5)

     button2 = tk.Button(window_function, text=" 返回 ", command=chaneg_main).place(relx=0.5,rely=0.5)

     window_function.mainloop()

# 新增商品介面跳轉

def change_add():

     # 銷燬畫布

     window.destroy()

     # 生成新介面

     Staff_add()

# 刪除商品介面跳轉

def change_delete():

     window.destroy()

     Staff_delete()

# 更新商品介面跳轉

def change_update():

     window.destroy()

     Staff_update()

# 條件查詢商品介面跳轉

def change_select():

     window.destroy()

     Staff_select()

# 主介面跳轉

def chaneg_main():

     window_function.destroy()

     mainpage()

# 主介面

def mainpage():

     global window

     window = tk.Tk()

     window.title(" 超市零售資訊管理系統 ")

     window.geometry('500x400')

     # 生成畫布，銷燬後生成新的畫布實現跳轉

     page = tk.Frame(window)

     page.pack()

     tk.Label(window, text=" 歡迎使用超市零售資訊管理系統 ", font=(" 黑體 ", 20)).pack(pady=10)

     button1 = tk.Button(window, text=" 新增商品資訊 ", command=change_add).pack(pady=10)

     button2 = tk.Button(window, text=" 刪除商品資訊 ", command=change_delete).pack(pady=10)

     button3 = tk.Button(window, text=" 修改商品資訊 ", command=change_update).pack(pady=10)

     button4 = tk.Button(window, text=" 查詢商品供貨商名稱 ", command=change_select).pack(pady=10)

     window.mainloop()

# 主函式生成主介面

if __name__ == '__main__':

     mainpage()