项目场景：python连接mysql利用streamlit实现web网页与数据库的交互
建立数据库
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `lx`;
CREATE TABLE `lx` (
`id_type` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`name_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id_type`) USING BTREE,
UNIQUE INDEX `name_type`(`name_type`) USING BTREE,
INDEX `id_type`(`id_type`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
DROP TABLE IF EXISTS `sp`;
CREATE TABLE `sp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`itime` datetime NULL DEFAULT NULL,
`price` double(10, 2) NULL DEFAULT NULL,
`type` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_lx`(`type`) USING BTREE,
CONSTRAINT `fk_lx` FOREIGN KEY (`type`) REFERENCES `lx` (`id_type`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
DROP VIEW IF EXISTS `st`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `st` AS select `sp`.`id` AS `id`,`sp`.`name` AS `name`,timestampdiff(YEAR,`sp`.`itime`,now()) AS `time`,`lx`.`name_type` AS `type`,`sp`.`price` AS `price` from (`sp` join `lx`) where (`sp`.`type` = `lx`.`id_type`);
SET FOREIGN_KEY_CHECKS = 1;
编写python文件：
import pymysql
import pandas as pd
import streamlit as st
import datetime
st.set_page_config(page_title="商品管理")
st.image('./LT.png')
st.markdown('### _商品_ **信息**')
def query(cursor):
sql = 'select * from st order by id;'
# 执行sql中的语句
try:
cursor.execute(sql)
# 获得列名
column = ['编号', '名称', '保存时间(年)', '商品类型', '价格']
# 获得数据
data = cursor.fetchall()
except Exception as e:
st.error(f'查询失败！原因：{str(e).split(",")[1][1:-1]}')
# 获得DataFrame格式的数据
data_df = pd.DataFrame(list(data), columns=column)
st.table(data_df)
def remove(cursor):
sql = 'select * from sp;'
# 执行sql中的语句
try:
cursor.execute(sql)
# 获得列名
column = [col[0] for col in cursor.description]
# 获得数据
data = cursor.fetchall()
except Exception as e:
st.error(f'查询失败！原因：{str(e).split(",")[1][1:-1]}')
# 获得DataFrame格式的数据
data_df = pd.DataFrame(list(data), columns=column)
# 执行sql中的语句
name = st.selectbox('你想删除哪个商品?', data_df['name'])
'你选择了: ', name
sql = "delete from sp where name=%s"
if st.button('删除'):
try:
cursor.execute(sql, name)
db.commit()
st.success("删除成功！")
except Exception as e:
db.rollback()
st.error(f'删除失败！原因：{str(e).split(",")[1][1:-1]}')
def add(cursor):
sql = 'select * from lx;'
# 执行sql中的语句
try:
cursor.execute(sql)
# 获得列名
column = ['编号', '类型名称']
# 获得数据
data = cursor.fetchall()
except Exception as e:
st.error(f'查询失败！原因：{str(e).split(",")[1][1:-1]}')
# 获得DataFrame格式的数据
df = pd.DataFrame(list(data), columns=column)
list1 = df.values.tolist()
dict_lx = {}
for i in list1:
dict_lx[i[1]] = i[0]
list_lx = list(dict_lx.keys())
col1, col2 = st.columns(2)
with col1:
name = st.text_input('商品名称')
itime = st.date_input("入库日期：", datetime.date(2020, 1, 1))
with col2:
price = st.text_input('商品价格')
type = st.selectbox('商品类型?', list_lx)
sql = "INSERT INTO sp(name,itime,price,type) VALUES (%s,%s,%s,%s)"
if st.button('添加'):
try:
cursor.execute(sql, (name,itime,price,
dict_lx[type]))
db.commit()
st.success("添加成功！")
except Exception as e:
db.rollback()
st.error(f'添加失败！原因：{str(e).split(",")[1][1:-1]}')
def modify(cursor):
sql = 'select * from lx;'
# 执行sql中的语句
try:
cursor.execute(sql)
# 获得列名
column = ['编号', '商品类型']
# 获得数据
data = cursor.fetchall()
except Exception as e:
st.error(f'查询失败！原因：{str(e).split(",")[1][1:-1]}')
# 获得DataFrame格式的数据
df = pd.DataFrame(list(data), columns=column)
list1 = df.values.tolist()
# 键为商品类型名，值为商品类型编号
dict_lx = {}
# 键为商品类型编号，值为商品类型名
dict_lx2 = {}
for i in list1:
dict_lx[i[1]] = i[0]
dict_lx2[i[0]] = i[1]
list_type = list(dict_lx.keys())
sql = 'select * from sp;'
try:
# 执行sql中的语句
cursor.execute(sql)
# 获得列名
column = [col[0] for col in cursor.description]
# 获得数据
data = cursor.fetchall()
except Exception as e:
st.error(f'查询失败！原因：{str(e).split(",")[1][1:-1]}')
# 获得DataFrame格式的数据
data_df = pd.DataFrame(list(data), columns=column)
# 执行sql中的语句
name = st.selectbox('你想修改哪个商品?', data_df['name'])
'你选择了: ', name
sql = "select * from sp where name=%s"
try:
cursor.execute(sql, name)
id_, name, price, type, itime=cursor.fetchone()
except Exception as e:
st.error(f'查询失败！原因：{str(e).split(",")[1][1:-1]}')
with st.container():
col1, col2 = st.columns(2)
with col1:
name = st.text_input('商品名称', name)
price = st.text_input('商品价格')
with col2:
type = st.selectbox('商品类型?', list_type)
itime= st.date_input("入库日期：",datetime.date(2020,1,1))
sql = "UPDATE sp set name=%s,itime=%s,price=%s,type=%s where id=%s"
if st.button('修改'):
try:
cursor.execute(sql, (name,itime,price,
dict_lx[type],id_))
db.commit()
st.success("修改成功！")
except Exception as e:
db.rollback()
st.error(f'修改失败！原因：{str(e).split(",")[1][1:-1]}')
def query_zy(cursor):
sql = 'select * from lx order by id_type;'
try:
# 执行sql中的语句
cursor.execute(sql)
column = ['编号', '商品类型']
# 获得数据
data = cursor.fetchall()
except Exception as e:
st.error(f'查询失败！原因：{str(e).split(",")[1][1:-1]}')
# 获得DataFrame格式的数据
data_df = pd.DataFrame(list(data), columns=column)
st.table(data_df)
def remove_zy(cursor):
sql = 'select * from lx;'
try:
# 执行sql中的语句
cursor.execute(sql)
# 获得列名
column = [col[0] for col in cursor.description]
# 获得数据
data = cursor.fetchall()
except Exception as e:
st.error(f'查询失败！原因：{str(e).split(",")[1][1:-1]}')
# 获得DataFrame格式的数据
data_df = pd.DataFrame(list(data), columns=column)
# 执行sql中的语句
col1, col2 = st.columns(2)
with col1:
name = st.selectbox('你想删除哪个商品类型?', data_df['name_type'])
'你选择了: ', name
sql = "delete from lx where name_type=%s"
if st.button('删除'):
try:
cursor.execute(sql, name)
db.commit()
st.success("删除成功！")
except Exception as e:
db.rollback()
st.error(f'删除失败！原因：{str(e).split(",")[1][1:-1]}')
def add_zy(cursor):
col1, col2 = st.columns(2)
with col1:
id_type = st.text_input('商品类型编号（2位数字）：')
name_type = st.text_input('商品类型名称：')
sql = "INSERT INTO lx(id_type,name_type) VALUES (%s,%s)"
if st.button('添加'):
try:
cursor.execute(sql, (id_type, name_type))
db.commit()
st.success("添加成功！")
except Exception as e:
db.rollback()
st.error(f'添加失败！原因：{str(e).split(",")[1][1:-1]}')
def modify_zy(cursor):
sql = 'select * from lx;'
try:
# 执行sql中的语句
cursor.execute(sql)
# 获得列名
column = [col[0] for col in cursor.description]
# 获得数据
data = cursor.fetchall()
except Exception as e:
st.error(f'查询失败！原因：{str(e).split(",")[1][1:-1]}')
# 获得DataFrame格式的数据
data_df = pd.DataFrame(list(data), columns=column)
# 执行sql中的语句
col1, col2 = st.columns(2)
with col1:
name = st.selectbox('你想修改哪个商品类型?', data_df['name_type'])
'你选择了: ', name
sql = "select * from lx where name_type=%s"
try:
cursor.execute(sql, name)
id_, name = cursor.fetchone()
except Exception as e:
st.error(f'查询失败！原因：{str(e).split(",")[1][1:-1]}')
with st.container():
col1, col2 = st.columns(2)
with col1:
nid = st.text_input('新商品类型编号：', id_)
name = st.text_input('新商品类型名称：', name)
sql = "UPDATE type set id_type=%s,name_type=%s where id_type=%s"
if st.button('修改'):
try:
cursor.execute(sql, (nid, name, id_))
db.commit()
st.success("修改成功！")
except Exception as e:
db.rollback()
st.error(f'修改失败！原因：{str(e).split(",")[1][1:-1]}')
if __name__ == '__main__':
action = st.sidebar.selectbox(
"你想进行的操作？",
['查看商品', '删除商品', '修改商品', '添加商品', '查看商品类型', '删除商品类型', '修改商品类型', '添加商品类型']
)
# 连接数据库
db = pymysql.connect(host='127.0.0.1', user='root',
passwd='******', port=3306, db='spgl')
# 开启一个游标cursor
cursor = db.cursor()
if action == '查看商品':
query(cursor)
elif action == '删除商品':
remove(cursor)
elif action == '添加商品':
add(cursor)
elif action == '修改商品':
modify(cursor)
elif action == '查看商品类型':
query_zy(cursor)
elif action == '删除商品类型':
remove_zy(cursor)
elif action == '添加商品类型':
add_zy(cursor)
elif action == '修改商品类型':
modify_zy(cursor)
else:
pass
cursor.close()
db.close()
939
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
