LQAdminPlatform/fix_db_indexes.py

import os
import pymysql
from urllib.parse import urlparse
from dotenv import load_dotenv

load_dotenv()

def fix_indexes():
    """执行索引添加 SQL"""
    database_url = os.getenv('DATABASE_URL', '')
    if not database_url:
        print("❌ 错误: 未在 .env 中找到 DATABASE_URL")
        return
        
    parsed = urlparse(database_url)
    config = {
        'host': parsed.hostname or 'localhost',
        'port': parsed.port or 3306,
        'user': parsed.username or 'root',
        'password': parsed.password or '',
        'database': parsed.path[1:] if parsed.path else 'sso_db',
        'charset': 'utf8mb4',
        'autocommit': True
    }
    
    print(f"📡 正在尝试连接数据库: {config['host']}...")
    
    conn = None
    try:
        conn = pymysql.connect(**config)
        cursor = conn.cursor()
        
        tables = ['t_basis_of_preparation', 't_work_of_preparation', 't_job_of_preparation']
        
        for table in tables:
            print(f"⚡ 正在为 {table} 添加索引...")
            try:
                # 检查索引是否已存在，防止重复添加报错
                cursor.execute(f"SHOW INDEX FROM {table} WHERE Key_name = 'idx_enter_status'")
                if cursor.fetchone():
                    print(f"   ✅ {table} 的索引已存在，跳过。")
                    continue
                    
                sql = f"ALTER TABLE {table} ADD INDEX idx_enter_status (whether_to_enter)"
                cursor.execute(sql)
                print(f"   ✅ {table} 索引添加成功！")
            except Exception as e:
                print(f"   ❌ {table} 处理失败: {e}")
        
        print("\n🎉 所有任务处理完成！现在您可以重新启动后端服务了。")
        
    except Exception as e:
        print(f"\n❌ 数据库连接失败: {e}")
        print("💡 提示: 请确保您已经关闭了 full_server.py，否则连接可能被占用。")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    fix_indexes()