使用Python和Sqlite3实现商品出入库（进销存）管理

网上有许多关于商品出入库方面的程序，但大部分比较深奥难懂，今天笔者用最简单的方式整理了一篇简单易懂的商品出入库管理程序，欢迎大家参阅。

一、环境

操作系统：win7

软件：python 3.8、sqlite3

二、效果图

初始化数据库

入库明细查询

出库明细查询

库存查询

新增入库

新增出库

再看库存情况

delete

update

三、代码

1.初始化数据库

即先创建一个数据库，再创建一些基础表

数据库名称：mydata.db

入库表：InBound

出库表：OutBound

def create_db():

import sqlite3

import os

import pandas as pd

path = 'mydata.db'

print("正在进行数据库初始化...")

if os.path.exists('mydata.db'):

cn = sqlite3.connect(path)

cur = cn.cursor()

print("成功创建 mydata 数据库 ！")

else:

cn = sqlite3.connect(path)

cur = cn.cursor()

print('成功创建游标对象！')

sql1 = '''create table if not exists InBound(

id integer primary key AutoIncrement,

product_name varchar(20),

description varchar(25),

price decimal(4,2),

qty int)

'''

sql2 = '''create table if not exists OutBound(

id integer primary key AutoIncrement,

product_name varchar(20),

description varchar(25),

price decimal(4,2),

qty int)

'''

try:

cur.execute(sql1)

cur.execute(sql2)

print('成功创建 InBound 表 ！')

print('成功创建 OutBound 表 ！')

except Exception as e:

print(e)

cn.rollback()

print('Sorry,创建失败，已回滚 ！')

finally:

cur.close()

cn.close()

print('Closed sqlite3 successfully !')

print('数据库初始化完成 ！')

2、入库

def InBound():

import sqlite3

import os

import pandas as pd

path = 'mydata.db'

print("商品入库")

product_name = input("输入商品名称：")

description = input("输入商品描述：")

price = input("输入商品价格：")

qty = input("输入入库数量：")

sql = "insert into InBound(product_name,description,price,qty) values('%s','%s','%s','%s')" \

% (product_name, description, price, qty)

cn = None

curs = None

try:

cn = sqlite3.connect(path)

curs = cn.cursor()

curs.execute(sql)

cn.commit()

print("InBound successfully ! 入库成功 !")

except Exception as e:

print(e)

cn.rollback()

print('回滚成功 ！')

finally:

curs.close()

cn.close()

print('Closed sqlite3 !')

3、入库查询

def InBound_display():

import sqlite3

import os

import pandas as pd

path = 'mydata.db'

print("""

入库明细查询结果

""")

try:

sql = "select * from InBound ;"

cn1 = sqlite3.connect(path)

df = pd.read_sql(sql, cn1)

print(df)

print('Finished !')

except Exception as e:

print(e)

4、出库

def OutBound():

import sqlite3

import os

import pandas as pd

path = 'mydata.db'

print("商品出库")

product_name = input("输入商品名称：")

description = input("输入商品描述：")

price = input("输入商品价格：")

qty = input("输入出库数量：")

sql = "insert into OutBound(product_name,description,price,qty) values('%s','%s','%s','%s')" \

% (product_name, description, price, qty)

cn = None

curs = None

try:

cn = sqlite3.connect(path)

curs = cn.cursor()

curs.execute(sql)

cn.commit()

print("OutBound successfully ! 出库成功 !")

except Exception as e:

print(e)

cn.rollback()

print('回滚成功 ！')

finally:

curs.close()

cn.close()

print('Closed sqlite3 !')

5、出库查询

def OutBound_display():

import sqlite3

import os

import pandas as pd

path = 'mydata.db'

print("""

出库明细查询结果

""")

try:

sql = "select * from OutBound ;"

with sqlite3.connect(path) as cn:

df = pd.read_sql(sql, cn)

print(df)

print('Finished !')

except Exception as e:

print(e)

6、库存查询

def stock():

import sqlite3

import os

import pandas as pd

path = 'mydata.db'

print("""

库存查询结果

""")

try:

sql1 = "select product_name,description as desc,qty as 入库 from InBound;"

sql2 = "select product_name,qty as 出库 from OutBound;"

with sqlite3.connect(path) as cn:

df1 = pd.read_sql(sql1, cn)

df1.fillna(0, inplace=True)

df2 = pd.read_sql(sql2, cn)

df2.fillna(0, inplace=True)

df = df1.merge(df2, how='outer', left_on='product_name', right_on='product_name')

df.fillna(0, inplace=True)

df['库存'] = df['入库'] - df['出库']

print(df)

print('Finished !')

except Exception as e:

print(e)

7、删除商品

def delete():

import sqlite3

import os

import pandas as pd

print("Delete product")

product_id = input("请输入要删除的商品ID：")

paths = 'mydata.db'

try:

# sql = "delete from InBound where id = {} ".format(product_id)

sql = f"delete from InBound where id = {product_id}"

with sqlite3.connect(paths) as conn:

cur = conn.cursor()

cur.execute(sql)

# cn.commit()

print('Deleted successfully !')

except Exception as e:

print(e)

8、更新商品（商品修改）

def update():

import sqlite3

import os

import pandas as pd

path = 'mydata.db'

print("update product")

product_id = input("请输入要修改的商品ID：")

product_name = input("请输入要正确的商品名称：")

price = input("请输入要正确的商品价格：")

try:

sql = f"update InBound set product_name = '{product_name}',price = {price} where id = {product_id} "

with sqlite3.connect(path) as cn:

cur = cn.cursor()

cur.execute(sql)

cn.commit()

print('Updated successfully !')

except Exception as e:

print(e)

9、定义主函数

def main():

import sqlite3

import os

import pandas as pd

path = 'mydata.db'

pd.set_option('display.unicode.ambiguous_as_wide', True)

pd.set_option('display.unicode.east_asian_width', True)

pd.set_option('expand_frame_repr', False)

pd.set_option('display.max_columns', None, 'display.max_rows', None)

pd.set_option('display.max_rows', 5000)

path = 'mydata.db'

create_db()

msg = """

欢迎使用商品出入库管理系统 1.0

1. 入库明细查询

2. 出库明细查询

3. 库存查询

4. 新增入库

5. 新增出库

6. 删除商品

7. 修改商品

q. 退出系统 ！

"""

while True:

print(msg)

handle = input('请选择操作项目:')

if handle == 'q' or handle == 'Q' or handle == '0':

print('退出系统 ！')

break

elif handle == '1':

InBound_display()

elif handle == '2':

OutBound_display()

elif handle == '3':

stock()

elif handle == '4':

InBound()

elif handle == '5':

OutBound()

elif handle == '6':

delete()

elif handle == '7':

update()

else:

print('Error!,输入错误 ！')

10、运行主函数

main()

把上面的代码一起拷贝到 jupyter notebook, 点击运行吧！

喜欢的友友，记行点击关注、收藏、点赞哟！