genealogy-app/clean_all_empty_suspected_errors.py

import pymysql

print('清理数据库中所有只包含空格和换行符的疑似错误标注...')
try:
    conn = pymysql.connect(
        host='rm-f8ze60yirdj8786u2wo.mysql.rds.aliyuncs.com',
        port=3306,
        user='root',
        password='csqz@20255',
        db='csqz-client',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    print('数据库连接成功')
    
    cursor = conn.cursor()
    
    # 1. 先查询需要清理的记录
    query = """
        SELECT id, name, suspected_error 
        FROM family_member_info 
        WHERE suspected_error IS NOT NULL AND TRIM(suspected_error) = ''
    """
    cursor.execute(query)
    records = cursor.fetchall()
    
    print(f'\n找到 {len(records)} 条需要清理的记录')
    
    if records:
        # 2. 清理这些记录，将suspected_error设置为NULL
        update_query = """
            UPDATE family_member_info 
            SET suspected_error = NULL 
            WHERE id = %s
        """
        
        count = 0
        for record in records:
            cursor.execute(update_query, (record['id'],))
            count += 1
        
        conn.commit()
        print(f'\n成功清理 {count} 条记录')
    else:
        print('\n没有需要清理的记录')
    
    # 3. 验证清理结果
    cursor.execute(query)
    remaining_records = cursor.fetchall()
    print(f'\n清理后剩余 {len(remaining_records)} 条需要清理的记录')
    
    # 4. 检查清理后的数据
    cursor.execute("SELECT COUNT(*) as count FROM family_member_info WHERE suspected_error IS NOT NULL")
    remaining_non_null = cursor.fetchone()['count']
    print(f'\n清理后suspected_error不为NULL的记录数: {remaining_non_null}')
    
    conn.close()
except Exception as e:
    print(f'Error: {e}')