crossborder/crossborder/utils/crawl_gov_commodity.py

import pandas as pd

from crossborder.utils import base_mysql
from crossborder.utils.log import  get_logger

log = get_logger(__name__)


def generate_sql_from_excel(excel_file):
    # 读取 Excel 文件
    df = pd.read_excel(excel_file)

    sql_arr = []
    auto_increment = 0
    code = 0
    for index, row in df.iterrows():
        commodity_code = str(row.values[4])
        level = 0
        for i in range(4):
            if not pd.isna(row.values[i]):
                if 0 == i:
                    auto_increment += 1
                    code = 'C0' + str(auto_increment)
                level = i + 1
                commodity_name = str(row.values[i]).rstrip('*')
                commodity_name = commodity_name.replace('(', '（').replace(')', '）')
        sql = f"INSERT INTO t_yujin_crossborder_prov_commodity_category (code, level,commodity_code, commodity_name, create_time) VALUES ('{code}', {level}, '{commodity_code}', '{commodity_name}', now());"
        sql_arr.append(sql)
        log.info(sql)

    log.info(f"√ 成功生成 commodity category SQL 文件 size {len(sql_arr)} ")
    # 解析完后生成sql文件批量入库
    base_mysql.bulk_insert(sql_arr)
    log.info("trade SQL 存表完成!")

# 商品书数据初始化
excel_file = 'C:\\Users\\admin\\Desktop\\海关总署数据梳理.xlsx'  # 输入的 Excel 文件
generate_sql_from_excel(excel_file)