用Python Streamlit Sqlite3 写一个简单商品管理系统

1、图片

2、代码

import streamlit as st
import pandas as pd
import json
import os
import shutil
import sqlite3
from datetime import datetime
from PIL import Image
import io

# 页面配置
st.set_page_config(
    page_title="商品管理系统",
    page_icon="🛍️",
    layout="wide",
    initial_sidebar_state="expanded"
)

# 数据库文件路径
DB_FILE = "products.db"
UPLOAD_DIR = "uploads/images"

# 创建上传目录
os.makedirs(UPLOAD_DIR, exist_ok=True)

def init_database():
    """初始化数据库"""
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    
    # 创建商品表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            price REAL NOT NULL,
            stock INTEGER NOT NULL,
            category TEXT NOT NULL,
            brand TEXT,
            description TEXT,
            image TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        )
    ''')
    
    conn.commit()
    conn.close()

def get_db_connection():
    """获取数据库连接"""
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row  # 使查询结果可以像字典一样访问
    return conn

def load_products():
    """加载所有商品数据"""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM products ORDER BY id')
    products = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return products

def get_product_by_id(product_id):
    """根据ID获取商品"""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM products WHERE id = ?', (product_id,))
    product = cursor.fetchone()
    conn.close()
    return dict(product) if product else None

def save_uploaded_file(uploaded_file, product_id):
    """保存上传的图片文件"""
    if uploaded_file is not None:
        # 获取文件扩展名
        file_ext = os.path.splitext(uploaded_file.name)[1]
        # 生成文件名
        filename = f"product_{product_id}{file_ext}"
        file_path = os.path.join(UPLOAD_DIR, filename)
        
        # 保存文件
        with open(file_path, "wb") as f:
            f.write(uploaded_file.getbuffer())
        
        return filename
    return None

def get_image_path(filename):
    """获取图片文件路径"""
    if filename and os.path.exists(os.path.join(UPLOAD_DIR, filename)):
        return os.path.join(UPLOAD_DIR, filename)
    return None

def delete_product_image(filename):
    """删除商品图片"""
    if filename:
        file_path = os.path.join(UPLOAD_DIR, filename)
        if os.path.exists(file_path):
            os.remove(file_path)

def resize_image(image_path, max_size=(300, 300)):
    """调整图片大小"""
    try:
        image = Image.open(image_path)
        image.thumbnail(max_size, Image.Resampling.LANCZOS)
        return image
    except:
        return None

def image_to_base64(image):
    """将PIL图像转换为base64字符串"""
    try:
        from io import BytesIO
        import base64
        
        buffered = BytesIO()
        image.save(buffered, format="JPEG")
        img_str = base64.b64encode(buffered.getvalue()).decode()
        return img_str
    except:
        return ""

def add_product(product_data, image_file=None):
    """添加新商品"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # 处理图片
    image_filename = None
    if image_file:
        # 先插入商品获取ID
        cursor.execute('''
            INSERT INTO products (name, price, stock, category, brand, description, image, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            product_data['name'], product_data['price'], product_data['stock'],
            product_data['category'], product_data.get('brand', ''),
            product_data.get('description', ''), None, datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        ))
        product_id = cursor.lastrowid
        
        # 保存图片
        image_filename = save_uploaded_file(image_file, product_id)
        
        # 更新商品记录中的图片字段
        cursor.execute('UPDATE products SET image = ? WHERE id = ?', (image_filename, product_id))
    else:
        # 直接插入商品
        cursor.execute('''
            INSERT INTO products (name, price, stock, category, brand, description, image, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            product_data['name'], product_data['price'], product_data['stock'],
            product_data['category'], product_data.get('brand', ''),
            product_data.get('description', ''), None, datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        ))
        product_id = cursor.lastrowid
    
    conn.commit()
    conn.close()
    return product_id

def update_product(product_id, product_data, image_file=None, delete_image=False):
    """更新商品信息"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # 获取当前商品信息
    current_product = get_product_by_id(product_id)
    if not current_product:
        conn.close()
        return False
    
    # 处理图片
    image_filename = current_product.get('image')
    
    if delete_image:
        # 删除现有图片
        if image_filename:
            delete_product_image(image_filename)
        image_filename = None
    elif image_file:
        # 更新图片
        if image_filename:
            delete_product_image(image_filename)
        image_filename = save_uploaded_file(image_file, product_id)
    # else: 保留现有图片
    
    # 更新商品信息
    cursor.execute('''
        UPDATE products 
        SET name = ?, price = ?, stock = ?, category = ?, brand = ?, description = ?, image = ?, updated_at = ?
        WHERE id = ?
    ''', (
        product_data['name'], product_data['price'], product_data['stock'],
        product_data['category'], product_data.get('brand', ''),
        product_data.get('description', ''), image_filename,
        datetime.now().strftime("%Y-%m-%d %H:%M:%S"), product_id
    ))
    
    conn.commit()
    conn.close()
    return True

def delete_product(product_id):
    """删除商品"""
    # 获取商品信息
    product = get_product_by_id(product_id)
    if not product:
        return False
    
    # 删除关联的图片
    if product.get('image'):
        delete_product_image(product['image'])
    
    # 删除数据库记录
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM products WHERE id = ?', (product_id,))
    conn.commit()
    conn.close()
    return True

def search_products(query, category=None):
    """搜索商品"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    sql = 'SELECT * FROM products WHERE 1=1'
    params = []
    
    if query:
        sql += ' AND (name LIKE ? OR description LIKE ? OR brand LIKE ?)'
        search_term = f'%{query}%'
        params.extend([search_term, search_term, search_term])
    
    if category and category != "全部":
        sql += ' AND category = ?'
        params.append(category)
    
    sql += ' ORDER BY id'
    cursor.execute(sql, params)
    products = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return products

# 初始化数据库
init_database()

# 侧边栏导航
st.sidebar.title("🛍️ 商品管理系统")
st.sidebar.markdown("---")

menu_options = ["🏠 首页", "➕ 添加商品", "📋 商品列表", "🔍 搜索商品", "📊 数据统计", "📤 数据出入"]
selected_menu = st.sidebar.radio("导航菜单", menu_options)

# 添加卡片样式（全局）
st.markdown("""
<style>
.product-card {
    border: 1px solid #ddd;
    border-radius: 10px;
    padding: 10px;
    margin: 5px;
    background-color: white;
    box-shadow: 0 2px 4px rgba(0,0,0,0.1);
    transition: all 0.3s ease;
}
.product-card:hover {
    box-shadow: 0 4px 8px rgba(0,0,0,0.2);
    transform: translateY(-2px);
}
.product-image {
    width: 100%;
    height: 120px;
    object-fit: cover;
    border-radius: 5px;
}
.product-name {
    font-weight: bold;
    font-size: 14px;
    margin: 8px 0 4px 0;
    line-height: 1.2;
    height: 34px;
    overflow: hidden;
    display: -webkit-box;
    -webkit-line-clamp: 2;
    -webkit-box-orient: vertical;
}
.product-price {
    color: #e74c3c;
    font-weight: bold;
    font-size: 16px;
    margin: 4px 0;
}
.product-info {
    font-size: 12px;
    color: #666;
    margin: 2px 0;
}
.product-stock {
    font-size: 11px;
    padding: 2px 6px;
    border-radius: 10px;
    display: inline-block;
    margin-top: 4px;
}
.stock-low {
    background-color: #ffebee;
    color: #d32f2f;
}
.stock-normal {
    background-color: #e8f5e8;
    color: #2e7d32;
}
</style>
""", unsafe_allow_html=True)

# 主内容区域
if selected_menu == "🏠 首页":
    st.title("欢迎使用商品管理系统")
    st.markdown("---")
    
    products = load_products()
    
    # 筛选选项
    col1, col2, col3 = st.columns([2, 2, 1])
    
    with col1:
        categories = ["全部"] + list(set(p['category'] for p in products))
        selected_category = st.selectbox("按分类筛选", categories)
    
    with col2:
        search_query = st.text_input("搜索商品", placeholder="输入商品名称或描述")
    
    with col3:
        sort_option = st.selectbox("排序方式", ["默认", "价格从低到高", "价格从高到低", "库存从少到多", "库存从多到少"])
    
    # 应用筛选和排序
    filtered_products = products
    
    if selected_category != "全部":
        filtered_products = [p for p in filtered_products if p['category'] == selected_category]
    
    if search_query:
        filtered_products = [p for p in filtered_products if search_query.lower() in p['name'].lower() or search_query.lower() in p['description'].lower()]
    
    if sort_option == "价格从低到高":
        filtered_products.sort(key=lambda x: x['price'])
    elif sort_option == "价格从高到低":
        filtered_products.sort(key=lambda x: x['price'], reverse=True)
    elif sort_option == "库存从少到多":
        filtered_products.sort(key=lambda x: x['stock'])
    elif sort_option == "库存从多到少":
        filtered_products.sort(key=lambda x: x['stock'], reverse=True)
    
    st.write(f"**找到 {len(filtered_products)} 个商品**")
    
    st.markdown("### 商品列表")
    recent_products = filtered_products
    if recent_products:
        # 显示商品卡片网格
        num_products = len(recent_products)
        num_rows = (num_products + 4) // 5  # 每行5个商品，计算需要的行数
        
        for row in range(num_rows):
            cols = st.columns(5)  # 每行5列
            
            for col_idx in range(5):
                product_idx = row * 5 + col_idx
                
                if product_idx < num_products:
                    product = recent_products[product_idx]
                    
                    with cols[col_idx]:
                        # 创建完整的卡片HTML结构（使用单个st.markdown调用）
                        card_html = '<div class="product-card">'
                        
                        # 商品图片
                        if product.get('image'):
                            image_path = get_image_path(product['image'])
                            if image_path:
                                resized_image = resize_image(image_path, (200, 120))
                                if resized_image:
                                    img_str = image_to_base64(resized_image)
                                    card_html += f'<div style="width:100%;height:120px;overflow:hidden;border-radius:5px 5px 0 0;"><img src="data:image/jpeg;base64,{img_str}" style="width:100%;height:100%;object-fit:cover;" /></div>'
                        else:
                            card_html += '<div style="width:100%;height:120px;background:#f5f5f5;border-radius:5px 5px 0 0;display:flex;align-items:center;justify-content:center;color:#999;font-size:12px;">暂无图片</div>'
                        
                        # 商品信息
                        card_html += '<div style="padding:10px;">'
                        card_html += f'<div class="product-name">{product["name"]}</div>'
                        card_html += f'<div class="product-price">¥{product["price"]:.2f}</div>'
                        card_html += f'<div class="product-info">分类: {product["category"]}</div>'
                        if product.get('brand'):
                            card_html += f'<div class="product-info">品牌: {product["brand"]}</div>'
                        
                        # 库存状态
                        stock_class = "stock-low" if product['stock'] < 10 else "stock-normal"
                        stock_text = "库存紧张" if product['stock'] < 10 else f"库存: {product['stock']}"
                        card_html += f'<div class="product-stock {stock_class}">{stock_text}</div>'
                        card_html += '</div>'  # 关闭商品信息div
                        
                        card_html += '</div>'  # 关闭卡片容器div
                        
                        # 显示卡片
                        st.markdown(card_html, unsafe_allow_html=True)
    else:
        st.info("暂无商品数据，请先添加商品")

elif selected_menu == "➕ 添加商品":
    st.title("添加新商品")
    st.markdown("---")
    
    with st.form("add_product_form"):
        col1, col2 = st.columns(2)
        
        with col1:
            name = st.text_input("商品名称*", placeholder="请输入商品名称")
            price = st.number_input("价格*", min_value=0.0, format="%.2f")
            stock = st.number_input("库存数量*", min_value=0, step=1)
        
        with col2:
            category = st.selectbox("商品分类*", ["电子产品", "服装", "食品", "家居", "图书", "其他"])
            brand = st.text_input("品牌", placeholder="请输入品牌名称")
            
        # 图片上传
        st.subheader("商品图片")
        image_file = st.file_uploader("上传商品图片", type=['png', 'jpg', 'jpeg'], 
                                     help="支持 PNG、JPG、JPEG 格式，建议尺寸 300x300")
        
        if image_file:
            # 预览图片
            image = Image.open(image_file)
            st.image(image, caption="图片预览", width=200)
        
        description = st.text_area("商品描述", placeholder="请输入商品详细描述", height=100)
        
        submitted = st.form_submit_button("添加商品")
        
        if submitted:
            if not name or price <= 0:
                st.error("请填写必填字段（商品名称和价格）")
            else:
                product_data = {
                    'name': name,
                    'price': price,
                    'stock': stock,
                    'category': category,
                    'brand': brand,
                    'description': description
                }
                product_id = add_product(product_data, image_file)
                st.success(f"商品 '{name}' 添加成功！商品ID: {product_id}")
                st.balloons()

elif selected_menu == "📋 商品列表":
    st.title("商品列表")
    st.markdown("---")
    
    products = load_products()
    
    if not products:
        st.info("暂无商品数据")
    else:
        # 筛选选项
        col1, col2, col3 = st.columns([2, 2, 1])
        
        with col1:
            categories = ["全部"] + list(set(p['category'] for p in products))
            selected_category = st.selectbox("按分类筛选", categories)
        
        with col2:
            search_query = st.text_input("搜索商品", placeholder="输入商品名称或描述")
        
        with col3:
            sort_option = st.selectbox("排序方式", ["默认", "价格从低到高", "价格从高到低", "库存从少到多", "库存从多到少"])
        
        # 应用筛选和排序
        filtered_products = products
        
        if selected_category != "全部":
            filtered_products = [p for p in filtered_products if p['category'] == selected_category]
        
        if search_query:
            filtered_products = [p for p in filtered_products if search_query.lower() in p['name'].lower() or search_query.lower() in p['description'].lower()]
        
        if sort_option == "价格从低到高":
            filtered_products.sort(key=lambda x: x['price'])
        elif sort_option == "价格从高到低":
            filtered_products.sort(key=lambda x: x['price'], reverse=True)
        elif sort_option == "库存从少到多":
            filtered_products.sort(key=lambda x: x['stock'])
        elif sort_option == "库存从多到少":
            filtered_products.sort(key=lambda x: x['stock'], reverse=True)
        
        st.write(f"**找到 {len(filtered_products)} 个商品**")
        
        # 显示商品卡片网格
        num_products = len(filtered_products)
        num_rows = (num_products + 4) // 5  # 每行5个商品，计算需要的行数
        
        for row in range(num_rows):
            cols = st.columns(5)  # 每行5列
            
            for col_idx in range(5):
                product_idx = row * 5 + col_idx
                
                if product_idx < num_products:
                    product = filtered_products[product_idx]
                    
                    with cols[col_idx]:
                        # 创建完整的卡片HTML结构（使用单个st.markdown调用）
                        card_html = '<div class="product-card">'
                        
                        # 商品图片
                        if product.get('image'):
                            image_path = get_image_path(product['image'])
                            if image_path:
                                resized_image = resize_image(image_path, (200, 120))
                                if resized_image:
                                    img_str = image_to_base64(resized_image)
                                    card_html += f'<div style="width:100%;height:120px;overflow:hidden;border-radius:5px 5px 0 0;"><img src="data:image/jpeg;base64,{img_str}" style="width:100%;height:100%;object-fit:cover;" /></div>'
                        else:
                            card_html += '<div style="width:100%;height:120px;background:#f5f5f5;border-radius:5px 5px 0 0;display:flex;align-items:center;justify-content:center;color:#999;font-size:12px;">暂无图片</div>'
                        
                        # 商品信息
                        card_html += '<div style="padding:10px;">'
                        card_html += f'<div class="product-name">{product["name"]}</div>'
                        card_html += f'<div class="product-price">¥{product["price"]:.2f}</div>'
                        card_html += f'<div class="product-info">分类: {product["category"]}</div>'
                        if product.get('brand'):
                            card_html += f'<div class="product-info">品牌: {product["brand"]}</div>'
                        
                        # 库存状态
                        stock_class = "stock-low" if product['stock'] < 10 else "stock-normal"
                        stock_text = "库存紧张" if product['stock'] < 10 else f"库存: {product['stock']}"
                        card_html += f'<div class="product-stock {stock_class}">{stock_text}</div>'
                        card_html += '</div>'  # 关闭商品信息div
                        
                        # 操作按钮区域（使用Streamlit按钮）
                        card_html += '<div style="padding:10px;border-top:1px solid #f0f0f0;">'
                        card_html += '</div>'  # 关闭操作按钮div
                        
                        card_html += '</div>'  # 关闭卡片容器div
                        
                        # 显示卡片
                        st.markdown(card_html, unsafe_allow_html=True)
                        
                        # 在卡片下方添加Streamlit按钮
                        col_btn1, col_btn2 = st.columns(2)
                        with col_btn1:
                            if st.button("编辑", key=f"edit_{product['id']}", use_container_width=True):
                                st.session_state.edit_product_id = product['id']
                                st.rerun()
                        with col_btn2:
                            if st.button("删除", key=f"delete_{product['id']}", use_container_width=True):
                                st.session_state.delete_product_id = product['id']
                                st.rerun()

elif selected_menu == "🔍 搜索商品":
    st.title("搜索商品")
    st.markdown("---")
    
    col1, col2 = st.columns([3, 1])
    
    with col1:
        search_query = st.text_input("搜索关键词", placeholder="输入商品名称、描述或品牌")
    
    with col2:
        categories = ["全部"] + list(set(p['category'] for p in load_products()))
        selected_category = st.selectbox("分类", categories)
    
    if st.button("搜索"):
        results = search_products(search_query, selected_category)
        
        if results:
            st.success(f"找到 {len(results)} 个相关商品")
            
            for product in results:
                with st.expander(f"{product['name']} - ¥{product['price']:.2f}"):
                    col1, col2 = st.columns([3, 1])
                    with col1:
                        # 显示商品图片
                        if product.get('image'):
                            image_path = get_image_path(product['image'])
                            if image_path:
                                resized_image = resize_image(image_path)
                                if resized_image:
                                    st.image(resized_image, width=150)
                        
                        st.write(f"**分类:** {product['category']}")
                        st.write(f"**品牌:** {product.get('brand', '无')}")
                        st.write(f"**库存:** {product['stock']}")
                        st.write(f"**描述:** {product['description']}")
                    with col2:
                        if st.button("编辑", key=f"search_edit_{product['id']}"):
                            st.session_state.edit_product_id = product['id']
                            st.rerun()
        else:
            st.warning("未找到相关商品")

elif selected_menu == "📊 数据统计":
    st.title("数据统计")
    st.markdown("---")
    
    products = load_products()
    
    if not products:
        st.info("暂无商品数据")
    else:
        # 基本统计
        col1, col2, col3, col4 = st.columns(4)
        
        with col1:
            st.metric("商品总数", len(products))
        
        with col2:
            products_with_images = len([p for p in products if p.get('image')])
            st.metric("有图片商品", products_with_images)
        
        with col3:
            total_stock = sum(p['stock'] for p in products)
            st.metric("总库存", total_stock)
        
        with col4:
            total_value = sum(p['price'] * p['stock'] for p in products)
            st.metric("库存总价值", f"¥{total_value:,.2f}")
        
        # 分类统计
        st.subheader("分类统计")
        category_stats = {}
        for product in products:
            category = product['category']
            if category not in category_stats:
                category_stats[category] = {'count': 0, 'total_value': 0, 'with_images': 0}
            category_stats[category]['count'] += 1
            category_stats[category]['total_value'] += product['price'] * product['stock']
            if product.get('image'):
                category_stats[category]['with_images'] += 1
        
        for category, stats in category_stats.items():
            col1, col2, col3, col4 = st.columns([2, 1, 1, 2])
            with col1:
                st.write(f"**{category}**")
            with col2:
                st.write(f"商品数量: {stats['count']}")
            with col3:
                st.write(f"有图片: {stats['with_images']}")
            with col4:
                st.write(f"库存价值: ¥{stats['total_value']:,.2f}")
            st.progress(stats['count'] / len(products))
        
        # 低库存预警
        st.subheader("低库存预警")
        low_stock_products = [p for p in products if p['stock'] < 10]
        if low_stock_products:
            for product in low_stock_products:
                st.warning(f"{product['name']} - 库存仅剩 {product['stock']} 件")
        else:
            st.success("所有商品库存充足")

# 删除商品确认功能
if 'delete_product_id' in st.session_state:
    product_id = st.session_state.delete_product_id
    product_to_delete = get_product_by_id(product_id)
    
    if product_to_delete:
        st.title("确认删除商品")
        st.markdown("---")
        
        st.warning(f"您确定要删除商品 '{product_to_delete['name']}' 吗？此操作不可撤销！")
        
        col1, col2, col3 = st.columns([1, 1, 2])
        with col1:
            if st.button("确认删除", type="primary"):
                if delete_product(product_id):
                    st.success(f"商品 '{product_to_delete['name']}' 已成功删除")
                    del st.session_state.delete_product_id
                    st.rerun()
                else:
                    st.error("删除商品失败")
        
        with col2:
            if st.button("取消"):
                del st.session_state.delete_product_id
                st.rerun()

# 编辑商品功能
if 'edit_product_id' in st.session_state:
    product_id = st.session_state.edit_product_id
    product_to_edit = get_product_by_id(product_id)
    
    if product_to_edit:
        st.title(f"编辑商品: {product_to_edit['name']}")
        st.markdown("---")
        
        with st.form("edit_product_form"):
            col1, col2 = st.columns(2)
            
            with col1:
                name = st.text_input("商品名称*", value=product_to_edit['name'])
                price = st.number_input("价格*", min_value=0.0, value=float(product_to_edit['price']), format="%.2f")
                stock = st.number_input("库存数量*", min_value=0, value=product_to_edit['stock'], step=1)
            
            with col2:
                category = st.selectbox("商品分类*", ["电子产品", "服装", "食品", "家居", "图书", "其他"], 
                                      index=["电子产品", "服装", "食品", "家居", "图书", "其他"].index(product_to_edit['category']))
                brand = st.text_input("品牌", value=product_to_edit.get('brand', ''))
                
            # 图片管理
            st.subheader("商品图片管理")
            
            # 显示当前图片
            if product_to_edit.get('image'):
                image_path = get_image_path(product_to_edit['image'])
                if image_path:
                    resized_image = resize_image(image_path)
                    if resized_image:
                        st.image(resized_image, caption="当前图片", width=200)
                
                # 删除图片选项
                delete_image = st.checkbox("删除当前图片")
            else:
                st.info("当前没有商品图片")
                delete_image = False
            
            # 上传新图片
            new_image_file = st.file_uploader("上传新图片", type=['png', 'jpg', 'jpeg'], 
                                             help="支持 PNG、JPG、JPEG 格式，建议尺寸 300x300")
            
            if new_image_file:
                # 预览新图片
                image = Image.open(new_image_file)
                st.image(image, caption="新图片预览", width=200)
            
            description = st.text_area("商品描述", value=product_to_edit['description'], height=100)
            
            col1, col2 = st.columns(2)
            with col1:
                submitted = st.form_submit_button("保存修改")
            with col2:
                if st.form_submit_button("取消"):
                    del st.session_state.edit_product_id
                    st.rerun()
            
            if submitted:
                if not name or price <= 0:
                    st.error("请填写必填字段（商品名称和价格）")
                else:
                    product_data = {
                        'name': name,
                        'price': price,
                        'stock': stock,
                        'category': category,
                        'brand': brand,
                        'description': description
                    }
                    if update_product(product_id, product_data, new_image_file, delete_image):
                        st.success("商品信息更新成功！")
                        del st.session_state.edit_product_id
                        st.rerun()
                    else:
                        st.error("更新失败，请重试")
    else:
        st.error("商品不存在")
        del st.session_state.edit_product_id

elif selected_menu == "📤 数据出入":
    st.title("商品数据导出导入")
    st.markdown("---")
    
    # 导出功能
    st.subheader("📤 导出商品数据")
    
    col1, col2 = st.columns(2)
    
    with col1:
        # JSON格式导出
        if st.button("导出为JSON格式"):
            products = load_products()
            if products:
                # 创建导出数据（不包含图片文件，只包含图片文件名）
                export_data = []
                for product in products:
                    product_copy = product.copy()
                    # 移除数据库ID和创建时间等内部字段
                    product_copy.pop('id', None)
                    product_copy.pop('created_at', None)
                    product_copy.pop('updated_at', None)
                    export_data.append(product_copy)
                
                # 生成JSON文件
                json_data = json.dumps(export_data, ensure_ascii=False, indent=2)
                
                # 创建下载链接
                st.download_button(
                    label="下载JSON文件",
                    data=json_data,
                    file_name=f"products_export_{datetime.now().strftime('%Y%m%d_%H%M%S')}.json",
                    mime="application/json"
                )
                st.success(f"成功导出 {len(products)} 个商品数据")
            else:
                st.warning("暂无商品数据可导出")
    
    with col2:
        # Excel格式导出
        if st.button("导出为Excel格式"):
            products = load_products()
            if products:
                # 创建DataFrame
                df_data = []
                for product in products:
                    df_data.append({
                        '商品名称': product['name'],
                        '价格': product['price'],
                        '库存': product['stock'],
                        '分类': product['category'],
                        '品牌': product.get('brand', ''),
                        '描述': product.get('description', ''),
                        '图片文件': product.get('image', '')
                    })
                
                df = pd.DataFrame(df_data)
                
                # 创建Excel文件
                excel_buffer = io.BytesIO()
                with pd.ExcelWriter(excel_buffer, engine='openpyxl') as writer:
                    df.to_excel(writer, sheet_name='商品数据', index=False)
                
                excel_buffer.seek(0)
                
                # 创建下载链接
                st.download_button(
                    label="下载Excel文件",
                    data=excel_buffer,
                    file_name=f"products_export_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx",
                    mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                )
                st.success(f"成功导出 {len(products)} 个商品数据")
            else:
                st.warning("暂无商品数据可导出")
    
    st.markdown("---")
    
    # 导入功能
    st.subheader("📥 导入商品数据")
    
    # 导入选项
    import_option = st.radio("选择导入方式", ["JSON文件导入", "Excel文件导入"])
    
    if import_option == "JSON文件导入":
        uploaded_file = st.file_uploader("选择JSON文件", type=['json'])
        
        if uploaded_file is not None:
            try:
                # 读取JSON数据
                json_data = json.load(uploaded_file)
                
                if isinstance(json_data, list):
                    st.success(f"成功读取 {len(json_data)} 条商品数据")
                    
                    # 显示预览
                    st.subheader("数据预览")
                    preview_df = pd.DataFrame(json_data[:5])  # 只显示前5条
                    st.dataframe(preview_df)
                    
                    # 导入选项
                    import_mode = st.radio("导入模式", ["追加导入（保留现有数据）", "覆盖导入（清空现有数据）"])
                    
                    if st.button("开始导入", type="primary"):
                        with st.spinner("正在导入数据..."):
                            try:
                                if import_mode == "覆盖导入（清空现有数据）":
                                    # 清空现有数据
                                    conn = get_db_connection()
                                    cursor = conn.cursor()
                                    cursor.execute('DELETE FROM products')
                                    conn.commit()
                                    conn.close()
                                
                                # 导入新数据
                                success_count = 0
                                for product_data in json_data:
                                    try:
                                        # 验证必要字段
                                        if 'name' in product_data and 'price' in product_data and 'stock' in product_data and 'category' in product_data:
                                            add_product(product_data)
                                            success_count += 1
                                    except Exception as e:
                                        st.error(f"导入失败: {product_data.get('name', '未知商品')} - {str(e)}")
                                
                                st.success(f"成功导入 {success_count} 个商品")
                                st.balloons()
                                
                            except Exception as e:
                                st.error(f"导入过程中发生错误: {str(e)}")
                else:
                    st.error("JSON文件格式不正确，应该是一个商品数组")
                    
            except Exception as e:
                st.error(f"读取JSON文件失败: {str(e)}")
    
    else:  # Excel文件导入
        uploaded_file = st.file_uploader("选择Excel文件", type=['xlsx', 'xls'])
        
        if uploaded_file is not None:
            try:
                # 读取Excel数据
                df = pd.read_excel(uploaded_file)
                
                st.success(f"成功读取 {len(df)} 条商品数据")
                
                # 显示预览
                st.subheader("数据预览")
                st.dataframe(df.head())
                
                # 导入选项
                import_mode = st.radio("导入模式", ["追加导入（保留现有数据）", "覆盖导入（清空现有数据）"])
                
                if st.button("开始导入", type="primary"):
                    with st.spinner("正在导入数据..."):
                        try:
                            if import_mode == "覆盖导入（清空现有数据）":
                                # 清空现有数据
                                conn = get_db_connection()
                                cursor = conn.cursor()
                                cursor.execute('DELETE FROM products')
                                conn.commit()
                                conn.close()
                            
                            # 导入新数据
                            success_count = 0
                            for _, row in df.iterrows():
                                try:
                                    product_data = {
                                        'name': str(row.get('商品名称', '')),
                                        'price': float(row.get('价格', 0)),
                                        'stock': int(row.get('库存', 0)),
                                        'category': str(row.get('分类', '其他')),
                                        'brand': str(row.get('品牌', '')),
                                        'description': str(row.get('描述', ''))
                                    }
                                    
                                    # 验证必要字段
                                    if product_data['name'] and product_data['price'] > 0:
                                        add_product(product_data)
                                        success_count += 1
                                except Exception as e:
                                    st.error(f"导入失败: {row.get('商品名称', '未知商品')} - {str(e)}")
                            
                            st.success(f"成功导入 {success_count} 个商品")
                            st.balloons()
                            
                        except Exception as e:
                            st.error(f"导入过程中发生错误: {str(e)}")
                            
            except Exception as e:
                st.error(f"读取Excel文件失败: {str(e)}")
    
    # 数据备份说明
    st.markdown("---")
    st.info("💡 **使用说明：**\n"
            "- **导出功能**：可以将商品数据导出为JSON或Excel格式，便于备份或迁移\n"
            "- **导入功能**：支持从JSON或Excel文件导入商品数据\n"
            "- **追加导入**：在现有数据基础上添加新商品\n"
            "- **覆盖导入**：清空现有数据后导入新商品")

# 页脚
st.markdown("---")
st.markdown("<div style='text-align: center; color: gray;'>商品管理系统 © 2025</div>", unsafe_allow_html=True)

商品管理系统

基于Streamlit开发的商品管理系统，支持商品的增删改查、搜索和筛选功能。

功能特性

• 商品信息管理（名称、价格、库存、分类等）

• 商品搜索和筛选

• 数据持久化存储

• 响应式界面设计

安装和运行

1、安装依赖：

pip install streamlit

pip install ....

2、运行应用：

streamlit run app.py

使用说明

1. 启动应用后，可以在左侧导航栏选择不同功能

2. 添加商品：填写商品信息并保存

3. 查看商品：浏览所有商品列表

4. 编辑商品：点击商品记录进行修改

5. 删除商品：选择商品并确认删除

6. 搜索商品：使用搜索框快速查找商品