genealogy-app/clean_empty_suspected_errors_python.py

import pymysql

print('使用Python清理数据库中所有只包含空格和换行符的疑似错误标注...')
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. 查询所有suspected_error字段不为NULL的成员
    query = """
        SELECT id, name, suspected_error 
        FROM family_member_info 
        WHERE suspected_error IS NOT NULL 
        ORDER BY name
    """
    cursor.execute(query)
    members = cursor.fetchall()
    
    print(f'\n找到 {len(members)} 个成员的suspected_error字段不为NULL')
    
    # 2. 检查并清理只包含空白字符的记录
    to_clean = []
    for member in members:
        se = member['suspected_error']
        if isinstance(se, str):
            # 使用Python的strip()函数来去除空白字符
            if se.strip() == '':
                to_clean.append(member['id'])
                print(f'需要清理: ID={member["id"]}, 姓名={member["name"]}, 疑似错误标注={repr(se)}')
    
    print(f'\n找到 {len(to_clean)} 条需要清理的记录')
    
    if to_clean:
        # 3. 清理这些记录，将suspected_error设置为NULL
        update_query = """
            UPDATE family_member_info 
            SET suspected_error = NULL 
            WHERE id = %s
        """
        
        count = 0
        for member_id in to_clean:
            cursor.execute(update_query, (member_id,))
            count += 1
        
        conn.commit()
        print(f'\n成功清理 {count} 条记录')
    else:
        print('\n没有需要清理的记录')
    
    # 4. 验证清理结果
    cursor.execute(query)
    remaining_members = cursor.fetchall()
    remaining_empty = 0
    for member in remaining_members:
        se = member['suspected_error']
        if isinstance(se, str) and se.strip() == '':
            remaining_empty += 1
    
    print(f'\n清理后剩余 {remaining_empty} 条需要清理的记录')
    print(f'清理后suspected_error不为NULL的记录数: {len(remaining_members)}')
    
    conn.close()
except Exception as e:
    print(f'Error: {e}')