mysql 补全数据

-- 检测缺失数据
SELECT column_name, COUNT(*) AS missing_count
FROM table_name
WHERE column_name IS NULL
GROUP BY column_name;

-- 补全缺失数据
UPDATE table_name
SET column_name = 'default_value'
WHERE column_name IS NULL;

UPDATE users
SET age = TIMESTAMPDIFF(YEAR, birth_date, CURDATE())
WHERE age IS NULL;

from sqlalchemy import create_engine

engine = create_engine('mysql://user:password@host/database')

# 检测缺失数据
missing_data_query = """
SELECT column_name, COUNT(*) AS missing_count
FROM table_name
WHERE column_name IS NULL
GROUP BY column_name;
"""

# 补全缺失数据
update_query = """
UPDATE table_name
SET column_name = 'default_value'
WHERE column_name IS NULL;
"""

with engine.connect() as connection:
    missing_data = connection.execute(missing_data_query)
    for row in missing_data:
        print(f"Missing data in {row.column_name}: {row.missing_count}")
    
    connection.execute(update_query)