genealogy-app/migrate_db.py

import pymysql

db_host = "rm-f8ze60yirdj8786u2.mysql.rds.aliyuncs.com"
db_user = "root"
db_pass = "csqz@20255"
db_name = "csqz-client"

def migrate():
    conn = pymysql.connect(
        host=db_host,
        user=db_user,
        password=db_pass,
        db=db_name,
        port=3306,
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    try:
        with conn.cursor() as cursor:
            # 1. Update genealogy_records
            print("Checking genealogy_records columns...")
            cursor.execute("DESCRIBE genealogy_records")
            columns = [col['Field'] for col in cursor.fetchall()]
            
            if 'status' not in columns:
                print("Adding 'status' column to genealogy_records...")
                cursor.execute("ALTER TABLE genealogy_records ADD COLUMN status VARCHAR(20) DEFAULT 'pending'")
            
            if 'ai_content' not in columns:
                print("Adding 'ai_content' column to genealogy_records...")
                cursor.execute("ALTER TABLE genealogy_records ADD COLUMN ai_content MEDIUMTEXT")

            # 2. Update family_member_info
            print("Checking family_member_info columns...")
            cursor.execute("DESCRIBE family_member_info")
            member_columns = [col['Field'] for col in cursor.fetchall()]

            if 'source_record_id' not in member_columns:
                print("Adding 'source_record_id' column to family_member_info...")
                cursor.execute("ALTER TABLE family_member_info ADD COLUMN source_record_id INT")

        conn.commit()
        print("Migration complete.")
    except Exception as e:
        print(f"Migration failed: {e}")
    finally:
        conn.close()

if __name__ == "__main__":
    migrate()