spider_work/courtyard_spider/update_price.py

# -*- coding: utf-8 -*-
# Author : Charley
# Python : 3.10.8
# Date   : 2025/12/10 16:14
from mysql_pool import MySQLConnectionPool
from loguru import logger


def update_price_fields():
    """
    更新 courtyard_detail_record 表中 sale_price 和 mint_price 字段的值
    去除美元符号($)和逗号(,)
    """
    # 配置 MySQL 连接池
    sql_pool = MySQLConnectionPool(log=logger)
    if not sql_pool.check_pool_health():
        logger.error("数据库连接池异常")
        raise RuntimeError("数据库连接池异常")

    try:
        # 查询所有需要更新的记录
        query = "SELECT id, sale_price, mint_price FROM courtyard_detail_record WHERE sale_price IS NOT NULL OR mint_price IS NOT NULL"
        records = sql_pool.select_all(query)

        logger.info(f"共找到 {len(records)} 条记录需要更新")

        updated_count = 0
        for record in records:
            record_id = record[0]
            sale_price = record[1]
            mint_price = record[2]

            # 处理 sale_price
            if sale_price and isinstance(sale_price, str):
                cleaned_sale_price = sale_price.replace("$", "").replace(",", "")
                # 如果能转换为数字则更新，否则保持原值
                try:
                    float(cleaned_sale_price)
                except ValueError:
                    cleaned_sale_price = sale_price  # 无法转换则保持原值

            # 处理 mint_price
            if mint_price and isinstance(mint_price, str):
                cleaned_mint_price = mint_price.replace("$", "").replace(",", "")
                # 如果能转换为数字则更新，否则保持原值
                try:
                    float(cleaned_mint_price)
                except ValueError:
                    cleaned_mint_price = mint_price  # 无法转换则保持原值

            # 更新数据库记录
            update_query = "UPDATE courtyard_detail_record SET sale_price = %s, mint_price = %s WHERE id = %s"
            update_args = (cleaned_sale_price if 'cleaned_sale_price' in locals() else sale_price,
                           cleaned_mint_price if 'cleaned_mint_price' in locals() else mint_price,
                           record_id)

            try:
                sql_pool.update_one(update_query, update_args)
                updated_count += 1
            except Exception as e:
                logger.error(f"更新记录 ID {record_id} 失败: {e}")

        logger.info(f"成功更新 {updated_count} 条记录")

    except Exception as e:
        logger.error(f"更新价格字段时发生错误: {e}")
        raise e


if __name__ == '__main__':
    update_price_fields()