chongqing-server/sql/excel_to_sql.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
旅检三部"三三"数字管理平台 Excel → SQL 导入脚本
运行: python excel_to_sql.py
输出: ledger_data_import.sql
"""

import os, re, sys
from datetime import datetime, date

try:
    import openpyxl
except ImportError:
    sys.exit("请先安装 openpyxl: pip install openpyxl")

# ──────────────────────────────────────────────
# 配置
# ──────────────────────────────────────────────
EXCEL_DIR  = r'C:\Users\linzo\Downloads'
OUTPUT_SQL = r'C:\Users\linzo\IdeaProjects\chongqing-server\sql\ledger_data_import.sql'
BATCH_NO   = datetime.now().strftime('BATCH_%Y%m%d_%H%M%S')
CREATE_BY  = 'admin'
CREATE_TIME = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

# ──────────────────────────────────────────────
# 工具函数
# ──────────────────────────────────────────────
def find_excel():
    for f in os.listdir(EXCEL_DIR):
        if '旅检' in f and f.endswith('.xlsx') and not f.startswith('~'):
            return os.path.join(EXCEL_DIR, f)
    raise FileNotFoundError('找不到 旅检*.xlsx 文件，请检查路径')

def esc(v, maxlen=None):
    """转义 SQL 字符串值，可选最大长度截断"""
    if v is None:
        return 'NULL'
    s = str(v).strip()
    if s == '' or s == 'None':
        return 'NULL'
    s = s.replace('\\', '\\\\').replace("'", "\\'").replace('\n', '\\n').replace('\r', '').replace('\t', ' ')
    if maxlen and len(s) > maxlen:
        s = s[:maxlen]
    return f"'{s}'"

def to_date(v):
    """将各种格式的日期转成 'YYYY-MM-DD' 或 NULL"""
    if v is None:
        return 'NULL'
    if isinstance(v, (datetime, date)):
        return f"'{v.strftime('%Y-%m-%d')}'"
    s = str(v).strip()
    if not s or s == 'None':
        return 'NULL'
    # 尝试多种格式
    for fmt in ('%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M', '%Y-%m-%d', '%Y/%m/%d', '%m/%d/%Y'):
        try:
            return f"'{datetime.strptime(s.split('.')[0].strip(), fmt).strftime('%Y-%m-%d')}'"
        except ValueError:
            pass
    # 返回原始字符串（可能有特殊格式）
    return esc(s[:10])

def to_decimal(v, default='NULL'):
    if v is None:
        return default
    s = str(v).strip().replace('¥', '').replace(',', '').strip()
    if not s or s == 'None':
        return default
    try:
        return str(float(s))
    except ValueError:
        return default

def to_int(v, default='NULL'):
    if v is None:
        return default
    try:
        return str(int(float(str(v))))
    except (ValueError, TypeError):
        return default

def row_vals(ws, row_num):
    """返回一行所有单元格值（列表）"""
    row = list(ws.iter_rows(min_row=row_num, max_row=row_num, values_only=True))
    return list(row[0]) if row else []

def iter_data_rows(ws, header_row=2):
    """从 header_row+1 开始迭代有效数据行"""
    for row in ws.iter_rows(min_row=header_row + 1, values_only=True):
        # 跳过全空行
        if all(v is None or str(v).strip() == '' for v in row):
            continue
        yield list(row)

def pad(lst, n):
    """把列表补齐到 n 长度"""
    return lst + [None] * max(0, n - len(lst))

# ──────────────────────────────────────────────
# 各 sheet 的映射函数
# 每个函数返回 list of SQL INSERT 语句
# ──────────────────────────────────────────────

def sheet_supervision_problem(ws):
    """部门监察问题记录表 → ledger_supervision_problem
    Excel R2: 时间(0) 区域(1) 工作点(2) 岗位(3) 责任人(4) 问题描述(5)
              问题类型(6) 整改措施(7) 依据(8) 问题层级(9) 班组(10)
              队室质控员(11) 质控推送队室负责人(12) 附件(13) 整改情况(14)
              佐证材料(15) 发送至相关人员(16) 本月内发生问题次数(17)
              上月质控问题超过三次人员(18) 检查人员(19) 分管质控经理(20)
              录入时间(21) 部门培训教员(22) 队室内勤(23) 队室负责人(24)
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, 25)
        stmts.append(
            f"INSERT INTO ledger_supervision_problem "
            f"(record_date,location,channel_no,inspected_name,problem_desc,problem_type,"
            f"result_handling,remark,team_name,inspector_name,evidence_file,"
            f"import_batch,source_type,create_by,create_time,del_flag) VALUES ("
            f"{to_date(c[0])},{esc(c[1])},{esc(c[2])},{esc(c[4])},{esc(c[5])},{esc(c[6])},"
            f"{esc(c[7])},{esc(c[8])},{esc(c[10])},{esc(c[11])},{esc(c[13])},"
            f"'{BATCH_NO}','1','{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


def sheet_patrol_inspection(ws):
    """队室三级质控巡查记录表 → ledger_patrol_inspection
    Excel R2: 巡查日期(0) 巡查区域(1) 巡查工作点(2) 巡查时间段(3) 巡查岗位(4)
              被检查人员(5) 有无问题(6) 检查情况描述(7) 类型(8) 整改措施(9)
              附件(10) 整改情况(11) 佐证材料(12) 班组(13) 责任组长(14)
              队室质控员(15) 队室负责人(16) 填报人(17) 队室内勤(18) 填报时间(19)
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, 20)
        stmts.append(
            f"INSERT INTO ledger_patrol_inspection "
            f"(record_date,location,channel_no,inspected_name,patrol_type,patrol_item,"
            f"problem_desc,result_handling,evidence_file,team_name,inspector_name,"
            f"import_batch,source_type,create_by,create_time,del_flag) VALUES ("
            f"{to_date(c[0])},{esc(c[1])},{esc(c[2])},{esc(c[5])},{esc(c[8])},{esc(c[4])},"
            f"{esc(c[7])},{esc(c[9])},{esc(c[10])},{esc(c[13])},{esc(c[14])},"
            f"'{BATCH_NO}','1','{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


def _parse_datetime(v):
    """返回 (date_str, time_str) tuple for SQL"""
    if v is None:
        return 'NULL', 'NULL'
    if isinstance(v, datetime):
        return f"'{v.strftime('%Y-%m-%d')}'", f"'{v.strftime('%H:%M:%S')}'"
    s = str(v).strip()
    if not s or s == 'None':
        return 'NULL', 'NULL'
    for fmt in ('%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M', '%Y-%m-%d'):
        try:
            dt = datetime.strptime(s.split('.')[0].strip(), fmt)
            return f"'{dt.strftime('%Y-%m-%d')}'", f"'{dt.strftime('%H:%M:%S')}'"
        except ValueError:
            pass
    return esc(s[:10]), 'NULL'


def sheet_realtime_interception(ws):
    """部门实时质控拦截情况记录表 → ledger_realtime_interception
    Excel R2: 时间(0) 区域(1) 工作点(2) 岗位(3) 责任人(4) 实时质控拦截物品(5)
              个数(6) 问题类型(7) 整改措施(8) 问题层级(9) 班组(10)
              附件(11) 问题类别(12) 个人复盘(13) 队室复盘(14)
              队室质控员(15) 质控推送队室负责人(16) 开机年限(17)
              责任人开机年龄(18) 发送至相关人员(19)
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, 20)
        d, t = _parse_datetime(c[0])
        stmts.append(
            f"INSERT INTO ledger_realtime_interception "
            f"(record_date,record_time,channel_no,inspector_name,item_name,"
            f"item_quantity,item_category,handling_method,team_name,evidence_file,remark,"
            f"import_batch,source_type,create_by,create_time,del_flag) VALUES ("
            f"{d},{t},{esc(c[2])},{esc(c[4])},{esc(c[5])},"
            f"{to_int(c[6])},{esc(c[7])},{esc(c[8])},{esc(c[10])},{esc(c[11])},{esc(c[12])},"
            f"'{BATCH_NO}','1','{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


def sheet_service_patrol(ws):
    """服务巡查 → ledger_service_patrol
    Excel R2: 时间(0) 区域(1) 工作点(2) 岗位(3) 责任人(4) 问题描述(5)
              问题类型(6) 整改措施(7) 班组(8) 问题层级(9)
              队室服务联络人(10) 服务推送队室负责人(11) 附件(12)
              整改情况(13) 佐证材料(14) 发送至相关人员(15)
              本月内发生问题次数(16) 检查人员(17) 分管服务经理(18) 队室内勤(19)
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, 20)
        stmts.append(
            f"INSERT INTO ledger_service_patrol "
            f"(record_date,location,inspected_name,problem_desc,service_type,"
            f"result_handling,team_name,evidence_file,inspector_name,"
            f"import_batch,source_type,create_by,create_time,del_flag) VALUES ("
            f"{to_date(c[0])},{esc(c[1])},{esc(c[4])},{esc(c[5])},{esc(c[6])},"
            f"{esc(c[7])},{esc(c[8])},{esc(c[12])},{esc(c[17])},"
            f"'{BATCH_NO}','1','{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


def sheet_complaint(ws):
    """投诉情况 → ledger_complaint
    Excel R2: 时间(0) 航班号(1) 旅客姓名(2) 班组(3) 责任人(4)
              投诉情况(5) 旅客诉求(6) 类别(7) 渠道来源(8)
              是否有责(9) 处理进度(10) 责任队长(11) 队室内勤(12)
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, 13)
        stmts.append(
            f"INSERT INTO ledger_complaint "
            f"(record_date,flight_no,passenger_name,team_name,responsible_name,"
            f"complaint_desc,complaint_type,result_handling,remark,"
            f"import_batch,source_type,create_by,create_time,del_flag) VALUES ("
            f"{to_date(c[0])},{esc(c[1])},{esc(c[2])},{esc(c[3])},{esc(c[4])},"
            f"{esc(c[5])},{esc(c[7])},{esc(c[10])},{esc(c[8])},"
            f"'{BATCH_NO}','1','{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


def sheet_security_test(ws):
    """安保测试记录表（部门）→ ledger_security_test
    Excel R2: 开展时间(0) 测试区域(1) 测试通道(2) 测试项目(3)
              被测试人员(4) 被测试岗位(5) 测试物品(6) 开展项目图片或视频(7)
              是否通过(8) 层级(9) 整改措施(10) 复查地点及时间(11)
              整改材料(12) 班组(13) 推送队室质控员(14) 推送质控队长(15)
              队室内勤(16) 点击推送至相关人员(17) 队室负责人(18)
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, 19)
        stmts.append(
            f"INSERT INTO ledger_security_test "
            f"(record_date,location,channel_no,test_item,tested_name,test_type,"
            f"evidence_file,test_result,result_handling,team_name,"
            f"import_batch,source_type,create_by,create_time,del_flag) VALUES ("
            f"{to_date(c[0])},{esc(c[1])},{esc(c[2])},{esc(c[3])},{esc(c[4])},{esc(c[6])},"
            f"{esc(c[7])},{esc(c[8])},{esc(c[10])},{esc(c[13])},"
            f"'{BATCH_NO}','1','{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


def sheet_channel_pass_rate(ws):
    """通道过检率 → ledger_channel_pass_rate
    Excel R2: 组长(0) 班组(1) 队室内勤(2) 2026年1月平均过检率(3) 2026年2月...(4) 2026年3月...(5) ...
    结构是 pivot：一行=一个班组，多列=多月；展开为多行
    """
    headers = row_vals(ws, 2)
    # 找月份列（列3起）
    month_cols = []
    for idx, h in enumerate(headers[3:], start=3):
        if h and str(h).strip():
            month_cols.append((idx, str(h).strip()))

    # 提取月份年份
    def parse_month(label):
        m = re.search(r'(\d{4})年(\d{1,2})月', label)
        if m:
            return f"{m.group(1)}-{int(m.group(2)):02d}-01"
        return None

    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, len(headers) + 5)
        team = esc(c[1])
        for col_idx, label in month_cols:
            rate = to_decimal(c[col_idx] if col_idx < len(c) else None)
            if rate == 'NULL':
                continue
            month_date = parse_month(label)
            rec_date = f"'{month_date}'" if month_date else 'NULL'
            stmts.append(
                f"INSERT INTO ledger_channel_pass_rate "
                f"(record_date,team_name,pass_rate,remark,"
                f"import_batch,source_type,create_by,create_time,del_flag) VALUES ("
                f"{rec_date},{team},{rate},{esc(label)},"
                f"'{BATCH_NO}','1','{CREATE_BY}','{CREATE_TIME}','0');"
            )
    return stmts


def sheet_unsafe_event(ws):
    """不安全事件 → ledger_unsafe_event
    Excel R2: 时间(0) 事件描述(1) 类别(2) 航班号(3) 责任人(4)
              涉及班组(5) 涉及物品(6) 岗位(7) 区域(8) 通道号(9)
              图像(10) 队室内勤(11)
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, 12)
        stmts.append(
            f"INSERT INTO ledger_unsafe_event "
            f"(record_date,event_desc,event_type,responsible_name,team_name,"
            f"evidence_file,remark,"
            f"import_batch,source_type,create_by,create_time,del_flag) VALUES ("
            f"{to_date(c[0])},{esc(c[1])},{esc(c[2])},{esc(c[4])},{esc(c[5])},"
            f"{esc(c[10])},{esc(c[8])},"
            f"'{BATCH_NO}','1','{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


def sheet_seizure_stats(ws):
    """2026查获违规品统计 → ledger_seizure_stats
    Excel R2: 查获时间(0) 信息提取(1) 信息提取2(2) 部门/队室(3) 工作区域(4)
              安检员(5) 安检资格证书等级(6) 岗位(7) 航班号(8) 目的地(9)
              进/出港(10) 违规主体(11) 旅客姓名(12) 旅客性别(13) 旅客民族(14)
              旅客年龄(15) 违规类别(16) 违规物品种类(17) 二级分类(18) 数量(19)
              ...（可能更多列）
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, 30)
        stmts.append(
            f"INSERT INTO ledger_seizure_stats "
            f"(record_date,team_name,inspector_name,flight_no,passenger_name,"
            f"item_category,item_name,item_quantity,"
            f"import_batch,source_type,create_by,create_time,del_flag) VALUES ("
            f"{to_date(c[0])},{esc(c[3])},{esc(c[5])},{esc(c[8])},{esc(c[12])},"
            f"{esc(c[16])},{esc(c[17])},{to_int(c[19])},"
            f"'{BATCH_NO}','1','{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


def sheet_terminal_bonus(ws):
    """航站楼加分 → ledger_terminal_bonus
    Excel R2: 审核日期(0) 姓名(1) 班组(2) 加分分数(3) 队室内勤(4) 总加分数(5) 队室负责人(6)
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, 7)
        stmts.append(
            f"INSERT INTO ledger_terminal_bonus "
            f"(approve_date,person_name,team_name,add_score,remark,"
            f"import_batch,source_type,create_by,create_time,del_flag) VALUES ("
            f"{to_date(c[0])},{esc(c[1])},{esc(c[2])},{to_decimal(c[3],'0')},{esc(c[4])},"
            f"'{BATCH_NO}','1','{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


def sheet_exam_score(ws):
    """成绩收集 → ledger_exam_score
    Excel R2: 类别(0) 期数(1) 考试人员(2) 理论成绩(3) 图像成绩(4) 班组(5) 分类(6) 备注（补考分数）(7) 队室教员(8)
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, 9)
        stmts.append(
            f"INSERT INTO ledger_exam_score "
            f"(exam_category,exam_period,person_name,score,team_name,remark,"
            f"import_batch,source_type,create_by,create_time,del_flag) VALUES ("
            f"{esc(c[0])},{esc(c[1])},{esc(c[2])},{to_decimal(c[3],'NULL')},{esc(c[5])},{esc(c[7])},"
            f"'{BATCH_NO}','1','{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


def sheet_reward_approval(ws):
    """小额奖励审批单-安检-质控 → ledger_reward_approval
    特殊：R1 就是表头（没有 title row）
    Excel R1: 员工编码(0) 姓名(1) 查获（事件）时间(2) 奖励类别(3) 查获物品(4)
              类别(5) 奖励事由（安全）(6) 人员类别(7) 主要事由简述(8)
    数据从 R2 开始
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=1):  # header_row=1 means data from row 2
        c = pad(cols, 9)
        stmts.append(
            f"INSERT INTO ledger_reward_approval "
            f"(approve_date,person_name,reward_type,approval_status,remark,"
            f"import_batch,source_type,create_by,create_time,del_flag) VALUES ("
            f"{to_date(c[2])},{esc(c[1])},{esc(c[3])},'待审批',{esc(c[8])},"
            f"'{BATCH_NO}','1','{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


def sheet_reward_penalty(ws):
    """部门奖惩记录表 → ledger_reward_penalty
    Excel R2: 姓名(0) 班组(1) 事由(2) 扣罚金额(3) 最后更新时间(4)
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, 5)
        # 扣罚金额转为负数分值（仅金额，非分值，存为负数）
        amt = to_decimal(c[3], 'NULL')
        score_change = f"-{amt}" if amt != 'NULL' else 'NULL'
        stmts.append(
            f"INSERT INTO ledger_reward_penalty "
            f"(record_date,person_name,team_name,type,event_desc,score_change,"
            f"create_by,create_time,del_flag) VALUES ("
            f"{to_date(c[4])},{esc(c[0])},{esc(c[1])},'2',{esc(c[2])},{score_change},"
            f"'{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


def sheet_leave_special(ws):
    """请、休假记录表（特殊）→ ledger_leave_special
    Excel R2: 姓名(0) 班组(1) 时间（起）(2) 时间（止）(3) 休假类别(4) 天数/时长(5)
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, 6)
        stmts.append(
            f"INSERT INTO ledger_leave_special "
            f"(person_name,team_name,leave_type,start_date,end_date,days,"
            f"create_by,create_time,del_flag) VALUES ("
            f"{esc(c[0])},{esc(c[1])},{esc(c[4])},{to_date(c[2])},{to_date(c[3])},{to_decimal(c[5],'NULL')},"
            f"'{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


def sheet_banner_letter(ws):
    """锦旗及感谢信 → ledger_banner_letter
    Excel R2: 时间(0) 姓名(1) 获得感谢信的具体内容(2) 类别(3) 图片和附件(4) 班组(5) 队室内勤(6)
    类别: 感谢信→'2', 锦旗→'1', 其他→'2'
    """
    stmts = []
    for cols in iter_data_rows(ws, header_row=2):
        c = pad(cols, 7)
        typ = '1' if str(c[3] or '').strip() in ('锦旗',) else '2'
        stmts.append(
            f"INSERT INTO ledger_banner_letter "
            f"(record_date,person_name,content_desc,type,evidence_file,team_name,remark,"
            f"create_by,create_time,del_flag) VALUES ("
            f"{to_date(c[0])},{esc(c[1])},{esc(c[2])},'{typ}',{esc(c[4])},{esc(c[5])},{esc(c[6])},"
            f"'{CREATE_BY}','{CREATE_TIME}','0');"
        )
    return stmts


# ──────────────────────────────────────────────
# sheet name → handler mapping
# ──────────────────────────────────────────────
SHEET_MAP = {
    '部门监察问题记录表':          ('ledger_supervision_problem',    sheet_supervision_problem),
    '队室三级质控巡查记录表':       ('ledger_patrol_inspection',      sheet_patrol_inspection),
    '部门实时质控拦截情况记录表':   ('ledger_realtime_interception',  sheet_realtime_interception),
    '服务巡查':                    ('ledger_service_patrol',         sheet_service_patrol),
    '投诉情况':                    ('ledger_complaint',              sheet_complaint),
    '安保测试记录表（部门）':       ('ledger_security_test',          sheet_security_test),
    '通道过检率':                  ('ledger_channel_pass_rate',      sheet_channel_pass_rate),
    '不安全事件':                  ('ledger_unsafe_event',           sheet_unsafe_event),
    '2026查获违规品统计':          ('ledger_seizure_stats',          sheet_seizure_stats),
    '航站楼加分':                  ('ledger_terminal_bonus',         sheet_terminal_bonus),
    '成绩收集':                    ('ledger_exam_score',             sheet_exam_score),
    '小额奖励审批单-安检-质控':    ('ledger_reward_approval',        sheet_reward_approval),
    '部门奖惩记录表':              ('ledger_reward_penalty',         sheet_reward_penalty),
    '请、休假记录表（特殊）':      ('ledger_leave_special',          sheet_leave_special),
    '锦旗及感谢信':               ('ledger_banner_letter',          sheet_banner_letter),
}

SKIP_SHEETS = {
    '旅检三部人员信息表', '日常培训记录', '组长履职情况记录表',
    '健康锐兵（2026_3）', '宿舍消防安全专项自查表（部门）', '培训台账问题通报',
}

# ──────────────────────────────────────────────
# 主程序
# ──────────────────────────────────────────────
def main():
    excel_path = find_excel()
    print(f'读取文件: {excel_path}')

    wb = openpyxl.load_workbook(excel_path, read_only=True, data_only=True)

    lines = [
        '-- =============================================',
        f'-- 台账数据导入 SQL  批次: {BATCH_NO}',
        f'-- 生成时间: {CREATE_TIME}',
        f'-- 来源文件: {os.path.basename(excel_path)}',
        '-- =============================================',
        'SET NAMES utf8mb4;',
        'SET FOREIGN_KEY_CHECKS=0;',
        '',
    ]

    total = 0
    for sheet_name in wb.sheetnames:
        if sheet_name in SKIP_SHEETS:
            print(f'  [跳过] {sheet_name}')
            continue

        if sheet_name not in SHEET_MAP:
            print(f'  [未映射] {sheet_name}')
            continue

        table_name, handler = SHEET_MAP[sheet_name]
        ws = wb[sheet_name]
        try:
            stmts = handler(ws)
        except Exception as e:
            print(f'  [ERROR] {sheet_name}: {e}')
            import traceback; traceback.print_exc()
            stmts = []

        lines.append(f'-- ── {sheet_name} → {table_name} ({len(stmts)} 行) ──')
        lines.extend(stmts)
        lines.append('')
        total += len(stmts)
        print(f'  [OK] {sheet_name} → {table_name}: {len(stmts)} 条')

    lines.append('SET FOREIGN_KEY_CHECKS=1;')
    lines.append(f'-- 共生成 {total} 条 INSERT 语句')

    wb.close()

    with open(OUTPUT_SQL, 'w', encoding='utf-8') as f:
        f.write('\n'.join(lines))

    print(f'\n✅ 完成！共 {total} 条，已写入:\n   {OUTPUT_SQL}')
    print(f'\n执行方式（在 MySQL 客户端中）:')
    print(f'  USE chongqing;  -- 替换为你的库名')
    print(f'  SOURCE {OUTPUT_SQL.replace(chr(92), "/")};')


if __name__ == '__main__':
    main()