MySQL模块
新建数据库'stock'
#仓库信息表
CREATE TABLE houses(
Hid VARCHAR(20) NOT NULL,
Hname VARCHAR(20) NOT NULL,
Sname VARCHAR(20) NOT NULL,
Phone VARCHAR(20) NOT NULL,
Hadd VARCHAR(20) NOT NULL
);
INSERT
INTO houses
VALUES('H01','仓库1','王雷','1234567','江苏南京');
INSERT
INTO houses
VALUES('H02','仓库2','李进','2345678','江苏常州');
INSERT
INTO houses
VALUES('H03','仓库3','张小虎','3456789','江苏盐城');
#管理人员信息表
CREATE TABLE staff(
Sid VARCHAR(20)NOT NULL,
Sname VARCHAR(20) NOT NULL,
Phone VARCHAR(20) NOT NULL
);
INSERT
INTO staff
VALUES('S01','王雷','1234567');
INSERT
INTO staff
VALUES('S02','李进','2345678');
INSERT
INTO staff
VALUES('S03','张小虎','3456789');
INSERT
INTO staff
VALUES('S03','张小虎','8888888');
#物资信息表
CREATE TABLE goods(
Gid VARCHAR(20) PRIMARY KEY,
Gname VARCHAR(20) NOT NULL,
Gnorm VARCHAR(20) NOT NULL,
Gtype VARCHAR(20) NOT NULL,
Gunit VARCHAR(20) NOT NULL
);
DELETE FROM goods
INSERT
INTO goods
VALUES('G01','医用消毒凝露','450ml','医护用品','瓶');
INSERT
INTO goods
VALUES('G02','康师傅方便面','500g','零食','袋');
INSERT
INTO goods
VALUES('G03','农夫山泉矿泉水','550ml','饮料','瓶');
INSERT
INTO goods
VALUES('G04','医用口罩','大号','医护用品','袋');
INSERT
INTO goods
VALUES('G05','压缩饼干','300g','零食','袋');
#入库信息表
CREATE TABLE imgoods(
Gid VARCHAR(20) PRIMARY KEY,
Gname VARCHAR(20) NOT NULL,
Gnorm VARCHAR(20) NOT NULL,
Gtype VARCHAR(20) NOT NULL,
Gunit VARCHAR(20) NOT NULL,
IMnum INT NOT NULL,
Gprice DOUBLE NOT NULL,
IMsum DOUBLE NOT NULL,
IMtime DATE NOT NULL,
IMman VARCHAR(20) NOT NULL,
Operator VARCHAR(20) NOT NULL,
Hname VARCHAR(20) NOT NULL,
IMelse VARCHAR(20) NOT NULL
);
#出库信息表
CREATE TABLE exgoods(
Gid VARCHAR(20) PRIMARY KEY,
Gname VARCHAR(20) NOT NULL,
Gnorm VARCHAR(20) NOT NULL,
Gtype VARCHAR(20) NOT NULL,
Gunit VARCHAR(20) NOT NULL,
EXnum INT NOT NULL,
Gprice DOUBLE NOT NULL,
EXsum DOUBLE NOT NULL,
EXtime DATE NOT NULL,
EXman VARCHAR(20) NOT NULL,
Operator VARCHAR(20) NOT NULL,
Hname VARCHAR(20) NOT NULL,
EXelse VARCHAR(20)NOT NULL
);
# @Time : 2021/12/19 21:12
# @Author : 金佩娴
# @File : 库存管理系统.py
# @Software: PyCharm
import pymysql
from tkinter import *
from tkinter.ttk import Treeview
def createdatabase():
conn = pymysql.connect(user="root", passwd="123456")
cursor=conn.cursor()
sql='''
create database if not exists stock;
'''
cursor.execute(sql)
cursor.close()
conn.commit()
conn.close()
# createdatabase()
def createtable():
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sqlworker = '''
CREATE TABLE if not exists worker(
empid VARCHAR(20) PRIMARY KEY NOT NULL,
deptid VARCHAR(20) NOT NULL,
name VARCHAR(20) NOT NULL,
sex VARCHAR(20) NOT NULL,
duty VARCHAR(20) NOT NULL,
password VARCHAR(20) NOT NULL,
authority VARCHAR(20) NOT NULL,
id VARCHAR(20) NOT NULL UNIQUE
);
'''
def createtable():
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sqlhouses = '''
CREATE TABLE if not exists houses(
Hid VARCHAR(20) PRIMARY KEY,
Hname VARCHAR(20) NOT NULL,
Sname VARCHAR(20) NOT NULL,
Phone VARCHAR(20) NOT NULL,
Hadd VARCHAR(20) NOT NULL
);
'''
cursor.execute(sqlhouses)#仓库信息表
sqlstaff='''
CREATE TABLE if not exists staff(
Sid VARCHAR(20) PRIMARY KEY,
Sname VARCHAR(20) NOT NULL,
Phone VARCHAR(20) NOT NULL
);
'''
cursor.execute(sqlstaff)#管理人员表
sqlgoods='''
CREATE TABLE if not exists goods(
Gid VARCHAR(20) PRIMARY KEY,
Gname VARCHAR(20) NOT NULL,
Gnorm VARCHAR(20) NOT NULL,
Gtype VARCHAR(20) NOT NULL,
Gunit VARCHAR(20) NOT NULL
);
'''
cursor.execute(sqlgoods)#物资信息表
sqlimgoods='''
CREATE TABLE if not exists imgoods(
Gid VARCHAR(20) PRIMARY KEY,
Gname VARCHAR(20) NOT NULL,
Gnorm VARCHAR(20) NOT NULL,
Gtype VARCHAR(20) NOT NULL,
Gunit VARCHAR(20) NOT NULL,
IMnum INT NOT NULL,
Gprice DOUBLE NOT NULL,
IMsum DOUBLE NOT NULL,
IMtime DATE NOT NULL,
IMman VARCHAR(20) NOT NULL,
Operator VARCHAR(20) NOT NULL,
Hname VARCHAR(20) NOT NULL,
IMelse VARCHAR(20) NOT NULL,
);
'''
cursor.execute(sqlimgoods)#入库物资信息表
sqlexgoods='''
CREATE TABLE if not exists exgoods(
Gid VARCHAR(20) PRIMARY KEY,
Gname VARCHAR(20) NOT NULL,
Gnorm VARCHAR(20) NOT NULL,
Gtype VARCHAR(20) NOT NULL,
Gunit VARCHAR(20) NOT NULL,
EXnum INT NOT NULL,
Gprice DOUBLE NOT NULL
EXsum DOUBLE NOT NULL,
EXtime DATE NOT NULL,
EXman VARCHAR(20) NOT NULL,
Operator VARCHAR(20) NOT NULL,
Hname VARCHAR(20) NOT NULL,
EXelse VARCHAR(20)NOT NULL,
);
'''
cursor.execute(sqlexgoods)#出库物资信息表
cursor.close()
conn.commit()
conn.close()
# createtable()
def showAllHouses():
d=['仓库编号','仓库名称','负责人','联系电话','仓库地址']
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sql='''
select Hid,Hname,Sname,Phone,Hadd from houses
'''
cursor.execute(sql)
a=cursor.fetchall()
# for i in a:
# print(i)
showAllHousesWin=Tk()
showAllHousesWin.geometry('1200x700+350+250')
showAllHousesWin.title("所有仓库信息")
b = Treeview(showAllHousesWin, height=len(a), show='headings', columns=[str(i) for i in range(len(d))])
for i in range(len(d)):
b.column(str(i), width=100, anchor='center')
b.heading(str(i), text=d[i])
for index in a:
b.insert('', 'end', values=index)
b.pack(side=LEFT, fill=BOTH, expand=True)
cursor.close()
conn.commit()
conn.close()
showAllHousesWin.mainloop()
def deleteOneHouse():
deletewin=Tk()
deletewin.geometry('600x300+550+350')
deletewin.title('删除仓库记录')
d1 = Label(deletewin, text="删除的仓库编号:", font=('宋体', 15))
d1.grid(row=0, column=1)
d2 = Entry(deletewin, font=('宋体', 15))
d2.grid(row=0, column=2)
def deleteone():
no = d2.get()
print (no)
print (type(no))
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sql = '''
delete from houses where Hid=
''' + no
cursor.execute(sql)
cursor.close()
conn.commit()
conn.close()
l3=Button(deletewin,text='查询', font=('宋体', 20), bd=5, width=20,command=deleteone)
l3.grid(row=2,column=2)
deletewin.mainloop()
def addHouse():
house = ['仓库编号','仓库名称','负责人','联系电话','仓库地址']
addHousewin=Tk()
addHousewin.geometry('600x500+350+250')
addHousewin.title("新增仓库")
entry = []
for i in range(len(house)):
iteml=Label(addHousewin,text=house[i]+':',font=('宋体', 20))
iteml.grid(row=i,column=0)
iteme=Entry(addHousewin,font=('宋体', 20))
iteme.grid(row=i,column=1)
entry.append(iteme)
def addonehouse():
data=[]
for i in entry:
data.append(i.get())
data=tuple(data)
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sql='''
insert into houses
values (%s,%s,%s,%s,%s)
'''
cursor.execute(sql,data)
cursor.close()
conn.commit()
conn.close()
addHousewin.destroy()
housebutton=Button(addHousewin,text='添加', font=('宋体', 20), bd=5, width=20,command=addonehouse)
housebutton.grid(row=len(house)+1,column=1)
addHousewin.mainloop()
def altHouse():
house = ['仓库编号','仓库名称','负责人','联系电话','仓库地址']
altHousewin=Tk()
altHousewin.geometry('600x500+350+250')
altHousewin.title("修改仓库信息")
entry = []
for i in range(len(house)):
iteml=Label(altHousewin,text=house[i]+':',font=('宋体', 20))
iteml.grid(row=i,column=0)
iteme=Entry(altHousewin,font=('宋体', 20))
iteme.grid(row=i,column=1)
entry.append(iteme)
def altonehouse():
data=[]
for i in entry:
data.append(i.get())
data=tuple(data)
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sql='''
insert into houses
values (%s,%s,%s,%s,%s);
DELETE
FROM goods
WHERE goods.Gid IN (SELECT Gid FROM exgoods)
'''
cursor.execute(sql,data)
cursor.close()
conn.commit()
conn.close()
addHousewin.destroy()
althousebutton=Button(altHousewin,text='修改', font=('宋体', 20), bd=5, width=20,command=altonehouse)
althousebutton.grid(row=len(house)+1,column=1)
altHousewin.mainloop()
def showAllStaff():
d = ['人员编号', '姓名', '电话']
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sql = '''
select Sid,Sname,Phone from staff
'''
cursor.execute(sql)
a = cursor.fetchall()
# for i in a:
# print(i)
showAllStaffWin = Tk()
showAllStaffWin.geometry('1200x700+350+250')
showAllStaffWin.title("所有管理人员信息")
b = Treeview(showAllStaffWin, height=len(a), show='headings', columns=[str(i) for i in range(len(d))])
for i in range(len(d)):
b.column(str(i), width=100, anchor='center')
b.heading(str(i), text=d[i])
for index in a:
b.insert('', 'end', values=index)
b.pack(side=LEFT, fill=BOTH, expand=True)
cursor.close()
conn.commit()
conn.close()
showAllStaffWin.mainloop()
def deleteOneStaff():
deleteOneStaffwin = Tk()
deleteOneStaffwin.geometry('600x300+550+350')
deleteOneStaffwin.title('删除仓库记录')
d1 = Label(deleteOneStaffwin, text="删除的仓库编号:", font=('宋体', 15))
d1.grid(row=0, column=1)
d2 = Entry(deleteOneStaffwin, font=('宋体', 15))
d2.grid(row=0, column=2)
def deletestaff():
no = d2.get()
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sql = '''
delete from staff where Sid=
''' + no
cursor.execute(sql)
cursor.close()
conn.commit()
conn.close()
l3 = Button(deleteOneStaffwin, text='查询', font=('宋体', 20), bd=5, width=20, command=deletestaff)
l3.grid(row=2, column=2)
deleteOneStaffwin.mainloop()
def addStaff():
staff = ['人员编号', '姓名', '电话']
addStaffwin = Tk()
addStaffwin.geometry('600x500+350+250')
addStaffwin.title("新增仓库")
entry = []
for i in range(len(staff)):
iteml = Label(addStaffwin, text=staff[i] + ':', font=('宋体', 20))
iteml.grid(row=i, column=0)
iteme = Entry(addStaffwin, font=('宋体', 20))
iteme.grid(row=i, column=1)
entry.append(iteme)
def addonestaff():
data = []
for i in entry:
data.append(i.get())
data = tuple(data)
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sql = '''
insert into staff
values (%s,%s,%s)
'''
cursor.execute(sql, data)
cursor.close()
conn.commit()
conn.close()
addStaffwin.destroy()
staffbutton = Button(addStaffwin, text='添加', font=('宋体', 20), bd=5, width=20, command=addonestaff)
staffbutton.grid(row=len(staff) + 1, column=1)
addStaffwin.mainloop()
def altStaff():
staff = ['人员编号', '姓名', '电话']
altStaffwin = Tk()
altStaffwin.geometry('600x500+350+250')
altStaffwin.title("修改管理人员信息")
entry = []
for i in range(len(staff)):
iteml = Label(altStaffwin, text=staff[i] + ':', font=('宋体', 20))
iteml.grid(row=i, column=0)
iteme = Entry(altStaffwin, font=('宋体', 20))
iteme.grid(row=i, column=1)
entry.append(iteme)
def altonestaff():
data = []
for i in entry:
data.append(i.get())
data = tuple(data)
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sql = '''
insert into staff
values (%s,%s,%s);
'''
cursor.execute(sql, data)
cursor.close()
conn.commit()
conn.close()
altStaffwin.destroy()
altstaffbutton = Button(altStaffwin, text='修改', font=('宋体', 20), bd=5, width=20, command=altonestaff)
altstaffbutton.grid(row=len(staff) + 1, column=1)
altStaffwin.mainloop()
def showAllGoods():
d=['物资编号','物资名称','规格型号','类别','计量单位']
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sql='''
select Gid,Gname,Gnorm,Gtype,Gunit from goods
'''
cursor.execute(sql)
a=cursor.fetchall()
# for i in a:
# print(i)
showAllGoodsWin=Tk()
showAllGoodsWin.geometry('1200x700+350+250')
showAllGoodsWin.title("所有仓库信息")
b = Treeview(showAllGoodsWin, height=len(a), show='headings', columns=[str(i) for i in range(len(d))])
for i in range(len(d)):
b.column(str(i), width=100, anchor='center')
b.heading(str(i), text=d[i])
for index in a:
b.insert('', 'end', values=index)
b.pack(side=LEFT, fill=BOTH, expand=True)
cursor.close()
conn.commit()
conn.close()
showAllGoodsWin.mainloop()
def addIMgoods():
imgoods = ['物资编号','物资名称','规格型号','类别','计量单位','数量','单价','金额','入库时间','入库人','经办人','仓库','备注']
addIMgoodswin=Tk()
addIMgoodswin.geometry('600x500+350+250')
addIMgoodswin.title("新增入库")
entry = []
for i in range(len(imgoods)):
iteml=Label(addIMgoodswin,text=imgoods[i]+':',font=('宋体', 20))
iteml.grid(row=i,column=0)
iteme=Entry(addIMgoodswin,font=('宋体', 20))
iteme.grid(row=i,column=1)
entry.append(iteme)
def addoneimgood():
data=[]
for i in entry:
data.append(i.get())
data=tuple(data)
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sql='''
insert into imgoods
values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
'''
cursor.execute(sql,data)
sql = '''
INSERT INTO goods
(Gid,Gname,Gnorm,Gtype,Gunit)
SELECT Gid,Gname,Gnorm,Gtype,Gunit
FROM imgoods
WHERE NOT EXISTS (SELECT * FROM goods
WHERE goods.Gid = imgoods.Gid);
'''
cursor.execute(sql)
cursor.close()
conn.commit()
conn.close()
addIMgoodswin.destroy()
imgoodsbutton=Button(addIMgoodswin,text='添加', font=('宋体', 20), bd=5, width=20,command=addoneimgood)
imgoodsbutton.grid(row=len(imgoods)+1,column=1)
addIMgoodswin.mainloop()
def addEXgoods():
exgoods = ['物资编号', '物资名称', '规格型号', '类别', '计量单位', '数量', '单价', '金额', '领用时间', '领用人', '经办人', '仓库', '备注']
addEXgoodswin = Tk()
addEXgoodswin.geometry('600x500+350+250')
addEXgoodswin.title("新增入库")
entry = []
for i in range(len(exgoods)):
iteml = Label(addEXgoodswin, text=exgoods[i] + ':', font=('宋体', 20))
iteml.grid(row=i, column=0)
iteme = Entry(addEXgoodswin, font=('宋体', 20))
iteme.grid(row=i, column=1)
entry.append(iteme)
def addoneexgood():
data = []
for i in entry:
data.append(i.get())
data = tuple(data)
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sql = '''
insert into exgoods
values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
'''
cursor.execute(sql, data)
sql = '''
DELETE
FROM goods
WHERE goods.Gid IN (SELECT Gid FROM exgoods);
'''
cursor.execute(sql)
cursor.close()
conn.commit()
conn.close()
addEXgoodswin.destroy()
exgoodsbutton = Button(addEXgoodswin, text='添加', font=('宋体', 20), bd=5, width=20, command=addoneexgood)
exgoodsbutton.grid(row=len(exgoods) + 1, column=1)
addEXgoodswin.mainloop()
def action():#操作界面
winShow=Tk()
winShow.geometry('600x300+500+300')
winShow.title('库存管理系统')
title=Label(winShow,text='欢迎使用',font=('宋体',40),width=10,height=1)
title.pack()
hou_act=Button(winShow, text="仓库信息", font=('宋体', 20), bd=5, width=20,command=Hou_act)
hou_act.pack()
sta_act=Button(winShow, text="管理人员信息", font=('宋体', 20), bd=5, width=20,command=Sta_act)
sta_act.pack()
goods_act=Button(winShow, text="物资信息", font=('宋体', 20), bd=5, width=20,command=Goods_act)
goods_act.pack()
winShow.mainloop()
def Hou_act():#仓库信息操作界面
winShow=Tk()
winShow.geometry('600x300+500+300')
winShow.title('仓库信息')
title=Label(winShow,text='仓库信息',font=('宋体',30),width=10,height=1)
title.grid(row=0)
addhouse=Button(winShow, text="增加记录", font=('宋体', 20), bd=5, width=20,command=addHouse)
addhouse.grid(row=1,column=0)
deleteOnehouse=Button(winShow, text="删除记录", font=('宋体', 20), bd=5, width=20,command=deleteOneHouse)
deleteOnehouse.grid(row=2,column=0)
showAllhouses=Button(winShow, text="查看所有记录", font=('宋体', 20), bd=5, width=20,command=showAllHouses)
showAllhouses.grid(row=1,column=1)
althouse=Button(winShow, text="修改记录", font=('宋体', 20), bd=5, width=20,command=altHouse)
althouse.grid(row=2,column=1)
winShow.mainloop()
def Sta_act():#管理人员信息操作界面
winShow=Tk()
winShow.geometry('600x300+500+300')
winShow.title('管理人员信息')
title=Label(winShow,text='人员信息',font=('宋体',30),width=10,height=1)
title.grid(row=0)
addstaff=Button(winShow, text="增加记录", font=('宋体', 20), bd=5, width=20,command=addStaff)
addstaff.grid(row=1,column=0)
deleteOnestaff=Button(winShow, text="删除记录", font=('宋体', 20), bd=5, width=20,command=deleteOneStaff)
deleteOnestaff.grid(row=2,column=0)
showAllstaff=Button(winShow, text="查看所有记录", font=('宋体', 20), bd=5, width=20,command=showAllStaff)
showAllstaff.grid(row=1,column=1)
altstaff=Button(winShow, text="修改记录", font=('宋体', 20), bd=5, width=20,command=altStaff)
altstaff.grid(row=2,column=1)
winShow.mainloop()
def Goods_act():#物资信息信息操作界面
winShow=Tk()
winShow.geometry('600x300+500+300')
winShow.title('物资信息')
title=Label(winShow,text='物资信息',font=('宋体',30),width=10,height=1)
title.pack()
showAllgoods=Button(winShow, text="查看所有物资", font=('宋体', 20), bd=5, width=20,command=showAllGoods)
showAllgoods.pack()
addimgoods=Button(winShow, text="增加入库记录", font=('宋体', 20), bd=5, width=20,command=addIMgoods)
addimgoods.pack()
addexgoods=Button(winShow, text="增加出库记录", font=('宋体', 20), bd=5, width=20,command=addEXgoods)
addexgoods.pack()
winShow.mainloop()
def login(): # d登陆功能
a, p = getAccountAndPassword()
# print(data)
if (a, p) == ("admin", "12345"):
win.destroy()
action()
else:
conn = pymysql.connect(user="root", passwd="123456", db='stock')
cursor = conn.cursor()
sql = '''
select empid,password from worker
'''
cursor.execute(sql)
data = cursor.fetchall()
cursor.close()
conn.commit()
conn.close()
if (a, p) in data:
win.destroy()
action()
else:
winalert = Tk()
winalert.geometry('400x100+600+450')
winalert.title('错误')
alert = Label(winalert, text='账号或密码输入错误', font=('宋体', 20), width=40, height=1)
alert.pack()
winalert.mainloop()
if __name__ == "__main__":
win = Tk()
win.geometry('600x300+500+300')
win.title('库存管理系统')
title=Label(win,text='登陆界面',font=('宋体',40),width=10,height=1)
title.grid(row=0,column=1)
account=Label(win,text='账号:',font=('宋体',20),width=10,height=2)
account.grid(row=1,column=0)
accountdata=Entry(win,font=('宋体', 20))
accountdata.grid(row=1,column=1)
password=Label(win,text='密码:',font=('宋体',20),width=10,height=2)
password.grid(row=2,column=0)
passworddata=Entry(win,font=('宋体', 20),show='*')
passworddata.grid(row=2,column=1)
def getAccountAndPassword():#获取输入的账号密码
a=accountdata.get()
p=passworddata.get()
# print(a,p)
return a,p
bt=Button(win, text="登陆", font=('宋体', 20), bd=5, width=10,command=login)
bt.grid(row=3,column=1)
win.mainloop()
版权声明：本文为weixin_52798927原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。