【基于python+streamlit+mysql的web网页/专业实践作业】

项目场景：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.Da