crossborder/quanguo/yearData.py

from decimal import Decimal, InvalidOperation

import xlrd
import pymysql
from datetime import datetime

# 数据库配置（需根据实际情况修改）
DB_CONFIG = {
    'host': '10.130.75.149',
    'port': 3307,
    'user': 'yto_crm',
    'password': '%3sFUlsolaRI',
    'database': 'crm_uat',
    'charset': 'utf8mb4'
}

def convert_unit(value):
    """亿元转万元，处理空值"""
    try:
        # 如果 value 不是特殊的无效值，进行转换并保留4位小数
        return round(Decimal(value) * 10000, 4) if value not in ['-', ''] else None
    except (InvalidOperation, ValueError):
        # 捕获异常，返回 None
        return None

def parse_ratio(value):
    """处理百分比数据"""
    return value if value not in ['-', ''] else None

def get_upsert_sql():
    """生成带更新条件的SQL语句"""
    return """
    INSERT INTO t_yujin_crossborder_yearly_summary 
    (year, year_total, year_import, year_export, trade_balance, 
     yoy_import_export, yoy_import, yoy_export, create_time)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
    ON DUPLICATE KEY UPDATE
        year_total = VALUES(year_total),
        year_import = VALUES(year_import),
        year_export = VALUES(year_export),
        trade_balance = VALUES(trade_balance),
        yoy_import_export = VALUES(yoy_import_export),
        yoy_import = VALUES(yoy_import),
        yoy_export = VALUES(yoy_export),
        create_time = VALUES(create_time)
    """

def main():
    # 连接数据库
    try:
        conn = pymysql.connect(**DB_CONFIG)
        cursor = conn.cursor()
    except pymysql.Error as e:
        print(f"数据库连接失败: {e}")
        return

    # 读取Excel文件
    try:
        workbook = xlrd.open_workbook('../src/downloads/20250513/(1)2025年进出口商品总值表_A年度表_3月.xls')
        sheet = workbook.sheet_by_index(0)
    except Exception as e:
        print(f"文件读取失败: {e}")
        conn.close()
        return

    sql = get_upsert_sql()
    current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    processed = 0

    for row_idx in range(5, sheet.nrows):
        row = sheet.row_values(row_idx)
        if not row[1]:  # 跳过空年份
            continue

        # 准备数据
        params = (
            row[1],  # year
            convert_unit(row[2]),  # year_total
            convert_unit(row[4]),  # year_import
            convert_unit(row[3]),  # year_export
            convert_unit(row[5]),  # trade_balance
            parse_ratio(row[6]),   # yoy_import_export
            parse_ratio(row[7]),   # yoy_import
            parse_ratio(row[8]),   # yoy_export
            current_time          # create_time
        )

        # 执行数据库操作
        try:
            cursor.execute(sql, params)
            processed += 1
        except pymysql.Error as e:
            print(f"数据插入失败（行{row_idx}）: {e}")
            conn.rollback()
            break

    try:
        conn.commit()
        print(f"成功处理 {processed} 条数据，当前时间：{current_time}")
    except pymysql.Error as e:
        print(f"事务提交失败: {e}")
    finally:
        cursor.close()
        conn.close()

if __name__ == "__main__":
    main()