#!/usr/bin/env python3 """ 修复颜色/尺寸解析问题 重新解析和更新存在解析错误的订单 """ import asyncio import sys import os from datetime import datetime # 添加项目根目录到 Python 路径 sys.path.append(os.path.dirname(os.path.abspath(__file__))) from app.core.database import get_db from app.utils.text_parser import AttributeExtractor from app.utils.color_processor import process_color_attribute from sqlalchemy import text async def fix_color_size_parsing(): """修复颜色尺寸解析问题""" print("=== 修复颜色/尺寸解析问题 ===\n") extractor = AttributeExtractor() async for db in get_db(): try: # 1. 找出可能存在解析问题的订单 print("1. 查找可能存在解析问题的订单...") # 查找包含斜杠和下划线的销售属性（可能存在解析问题） result = await db.execute(text(""" SELECT id, 线上销售属性, 颜色, 尺寸, 线上宝贝名称 FROM order_items_norm WHERE 线上销售属性 LIKE '%/%_%' OR 线上销售属性 LIKE '%_[0-9]%' OR 线上销售属性 REGEXP '.*[A-Za-z]+/[A-Za-z]+_[A-Za-z0-9]+.*' ORDER BY id """)) potential_issues = result.fetchall() print(f" 找到 {len(potential_issues)} 个可能存在问题的订单") # 2. 逐个检查和修复 updated_count = 0 checked_count = 0 print(f"\n2. 开始检查和修复...") for order in potential_issues: checked_count += 1 order_id = order.id sales_attr = order.线上销售属性 current_color = order.颜色 current_size = order.尺寸 product_name = order.线上宝贝名称 # 使用修复后的逻辑重新解析 new_raw_color = extractor._extract_color(sales_attr) new_raw_size = extractor._extract_size(sales_attr) # 应用颜色处理逻辑 new_color = current_color # 默认保持原色 if new_raw_color and new_raw_color != current_color: # 去掉原有的英文参考，获取纯颜色 if current_color and " (" in current_color and ")" in current_color: current_pure_color = current_color.split(" (")[0] else: current_pure_color = current_color or "" # 如果新解析的颜色与当前纯颜色不同，则需要更新 if new_raw_color != current_pure_color: processed_color = process_color_attribute(new_raw_color) new_color = processed_color or new_raw_color new_size = new_raw_size if new_raw_size else current_size # 检查是否需要更新 color_needs_update = new_color != current_color size_needs_update = new_size != current_size if color_needs_update or size_needs_update: # 输出更新信息（前20个） if updated_count < 20: print(f" {updated_count+1:2d}. ID {order_id}: {sales_attr}") if color_needs_update: print(f" 颜色: {current_color} -> {new_color}") if size_needs_update: print(f" 尺寸: {current_size} -> {new_size}") elif updated_count == 20: print(" ... (更多更新记录，不再显示)") # 执行更新 await db.execute(text(""" UPDATE order_items_norm SET 颜色 = :new_color, 尺寸 = :new_size, updated_at = :now WHERE id = :order_id """), { 'new_color': new_color, 'new_size': new_size, 'order_id': order_id, 'now': datetime.now() }) updated_count += 1 # 进度提示 if checked_count % 100 == 0: print(f" 已检查 {checked_count}/{len(potential_issues)} 条记录，已更新 {updated_count} 条") # 提交所有更新 await db.commit() print(f"\n3. 修复完成") print(f" 总检查记录数: {checked_count}") print(f" 实际更新记录数: {updated_count}") print(f" 无需更新记录数: {checked_count - updated_count}") # 4. 验证修复结果 print(f"\n4. 验证修复结果...") # 检查之前的问题订单是否已修复 test_result = await db.execute(text(""" SELECT 原始订单编号, 线上销售属性, 颜色, 尺寸 FROM order_items_norm WHERE 原始订单编号 IN ('4693389768932983037', '2886548559516973465') """)) print(" 关键问题订单修复结果:") for row in test_result.fetchall(): print(f" {row.原始订单编号}: {row.线上销售属性} -> 颜色:{row.颜色}, 尺寸:{row.尺寸}") # 统计修复后的解析质量 quality_result = await db.execute(text(""" SELECT CASE WHEN 线上销售属性 LIKE '%/%_%' AND 颜色 LIKE '%/%' THEN '包含斜杠的颜色已正确解析' WHEN 线上销售属性 LIKE '%_[0-9]%' AND 尺寸 REGEXP '^[0-9]+$' THEN '数字尺寸已正确解析' ELSE '其他情况' END as fix_type, COUNT(*) as count FROM order_items_norm WHERE 线上销售属性 LIKE '%/%_%' OR 线上销售属性 LIKE '%_[0-9]%' GROUP BY fix_type """)) print(f"\n 解析质量统计:") for fix_type, count in quality_result.fetchall(): print(f" {fix_type}: {count} 条记录") except Exception as e: await db.rollback() print(f"修复过程中发生错误: {e}") import traceback traceback.print_exc() break async def preview_fixes(): """预览将要进行的修复""" print("=== 预览颜色/尺寸解析修复 ===\n") extractor = AttributeExtractor() async for db in get_db(): try: result = await db.execute(text(""" SELECT id, 线上销售属性, 颜色, 尺寸, 线上宝贝名称 FROM order_items_norm WHERE 线上销售属性 LIKE '%/%_%' OR 线上销售属性 LIKE '%_[0-9]%' ORDER BY id LIMIT 50 """)) potential_issues = result.fetchall() print("将要修复的问题订单预览（前50条）:") print("序号 | 销售属性 | 当前颜色 | 新颜色 | 当前尺寸 | 新尺寸 | 状态") print("-" * 120) need_fix_count = 0 for i, order in enumerate(potential_issues, 1): sales_attr = order.线上销售属性 current_color = order.颜色 current_size = order.尺寸 # 重新解析 new_raw_color = extractor._extract_color(sales_attr) new_raw_size = extractor._extract_size(sales_attr) # 处理颜色 new_color = current_color if new_raw_color: processed_color = process_color_attribute(new_raw_color) new_color = processed_color or new_raw_color new_size = new_raw_size if new_raw_size else current_size needs_fix = (new_color != current_color) or (new_size != current_size) if needs_fix: need_fix_count += 1 status = "需要修复" if needs_fix else "无需修复" print(f"{i:2d} | {sales_attr[:15]:15} | {current_color[:12]:12} | {new_color[:12]:12} | {current_size[:8]:8} | {new_size[:8]:8} | {status}") print(f"\n预览结果:") print(f" 检查记录数: {len(potential_issues)}") print(f" 需要修复数: {need_fix_count}") print(f" 无需修复数: {len(potential_issues) - need_fix_count}") except Exception as e: print(f"预览过程中发生错误: {e}") break async def main(): """主函数""" print("颜色/尺寸解析修复工具") print("=" * 60) if len(sys.argv) > 1 and sys.argv[1] == '--fix': # 执行修复 print("开始执行修复...") await fix_color_size_parsing() else: # 默认预览模式 print("使用说明:") print(" python fix_color_size_parsing.py # 预览修复") print(" python fix_color_size_parsing.py --fix # 执行修复") print("") await preview_fixes() if __name__ == "__main__": asyncio.run(main())