wuzilong
/
buff3.0


			
							12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364
							"""
修复 buff_selection_request_item 表中 steam_link 为空的数据
从 buff_goods_info 表获取 steam_url 填充
"""
from sqlalchemy import text
from app.database import SessionLocal


def fix_steam_links():
    """修复 steam_link 为空的记录"""
    db = SessionLocal()
    try:
        # 1. 查询 steam_link 为空的记录
        query = text("""
            SELECT DISTINCT sri.id, sri.goods_id, sri.goods_name
            FROM buff_selection_request_item sri
            LEFT JOIN buff_goods_info bgi ON sri.goods_id = bgi.goods_id
            WHERE sri.steam_link = '' OR sri.steam_link IS NULL
            HAVING bgi.steam_url IS NOT NULL AND bgi.steam_url != ''
        """)

        # 分步执行
        empty_items = db.execute(text("""
            SELECT id, goods_id, goods_name
            FROM buff_selection_request_item
            WHERE steam_link = '' OR steam_link IS NULL
        """)).fetchall()

        print(f"找到 {len(empty_items)} 条 steam_link 为空的记录")

        fixed_count = 0
        for item in empty_items:
            item_id, goods_id, goods_name = item

            # 从 buff_goods_info 查询 steam_url
            result = db.execute(text("""
                SELECT steam_url FROM buff_goods_info
                WHERE goods_id = :goods_id AND steam_url IS NOT NULL AND steam_url != ''
            """), {"goods_id": goods_id}).fetchone()

            if result:
                steam_url = result[0]
                db.execute(text("""
                    UPDATE buff_selection_request_item
                    SET steam_link = :steam_url
                    WHERE id = :item_id
                """), {"steam_url": steam_url, "item_id": item_id})
                fixed_count += 1
                print(f"  修复: id={item_id}, goods_id={goods_id}, steam_url={steam_url[:50]}...")

        db.commit()
        print(f"\n共修复 {fixed_count} 条记录")
        return fixed_count

    except Exception as e:
        db.rollback()
        print(f"修复失败: {e}")
        raise
    finally:
        db.close()


if __name__ == "__main__":
    fix_steam_links()