库存管理系统的设计与实现（代码）_库存管理系统的数据库设计

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,