ai_la/base7/check_h2_database.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
通过H2控制台检查数据库中的difficulty字段
"""

import requests
import json
from urllib.parse import urlencode

# H2控制台配置
H2_CONSOLE_URL = "http://localhost:8080/h2-console"
H2_LOGIN_URL = "http://localhost:8080/h2-console/login.do"
H2_QUERY_URL = "http://localhost:8080/h2-console/query.do"

def check_h2_database():
    """通过H2控制台检查数据库"""
    print("=== 检查H2数据库中的difficulty字段 ===")
    
    # 创建会话
    session = requests.Session()
    
    try:
        # 1. 访问H2控制台首页
        response = session.get(H2_CONSOLE_URL)
        if response.status_code != 200:
            print(f"✗ 无法访问H2控制台: {response.status_code}")
            return False
        
        print("✓ H2控制台可访问")
        
        # 2. 登录H2数据库
        login_data = {
            'language': 'en',
            'setting': 'Generic H2 (Embedded)',
            'name': 'Generic H2 (Embedded)',
            'driver': 'org.h2.Driver',
            'url': 'jdbc:h2:mem:testdb',
            'user': 'sa',
            'password': ''
        }
        
        response = session.post(H2_LOGIN_URL, data=login_data)
        if 'query.do' not in response.text:
            print("✗ H2数据库登录失败")
            return False
        
        print("✓ H2数据库登录成功")
        
        # 3. 查询goal_detail表结构
        print("\n--- 查询goal_detail表结构 ---")
        structure_query = "SHOW COLUMNS FROM goal_detail"
        query_data = {
            'sql': structure_query
        }
        
        response = session.post(H2_QUERY_URL, data=query_data)
        if response.status_code == 200:
            print("✓ 表结构查询成功")
            # 简单检查响应中是否包含difficulty字段
            if 'DIFFICULTY' in response.text.upper():
                print("✓ 发现DIFFICULTY字段")
            else:
                print("✗ 未发现DIFFICULTY字段")
        
        # 4. 查询goal_detail表数据
        print("\n--- 查询goal_detail表数据 ---")
        data_query = "SELECT id, goal_id, question, difficulty, created_at FROM goal_detail ORDER BY created_at DESC LIMIT 5"
        query_data = {
            'sql': data_query
        }
        
        response = session.post(H2_QUERY_URL, data=query_data)
        if response.status_code == 200:
            print("✓ 数据查询成功")
            # 检查响应内容
            response_text = response.text
            if 'No data available' in response_text or '没有数据' in response_text:
                print("  表中暂无数据")
            else:
                print("  表中有数据，请查看H2控制台获取详细信息")
                # 尝试从响应中提取一些信息
                if 'null' in response_text.lower():
                    print("  ⚠️  响应中包含null值，可能存在difficulty字段为null的情况")
        
        # 5. 统计difficulty字段分布
        print("\n--- 统计difficulty字段分布 ---")
        stats_query = "SELECT difficulty, COUNT(*) as count FROM goal_detail GROUP BY difficulty"
        query_data = {
            'sql': stats_query
        }
        
        response = session.post(H2_QUERY_URL, data=query_data)
        if response.status_code == 200:
            print("✓ 统计查询成功")
            if 'null' in response.text.lower():
                print("  ⚠️  发现difficulty字段为null的记录")
            else:
                print("  difficulty字段统计完成")
        
        return True
        
    except Exception as e:
        print(f"✗ 检查过程出错: {e}")
        return False

def main():
    """主函数"""
    print("开始检查H2数据库中的difficulty字段...")
    
    if check_h2_database():
        print("\n=== 检查完成 ===")
        print("详细信息请访问: http://localhost:8080/h2-console")
        print("连接信息:")
        print("  JDBC URL: jdbc:h2:mem:testdb")
        print("  用户名: sa")
        print("  密码: (空)")
        print("\n建议执行的SQL查询:")
        print("1. 查看表结构: SHOW COLUMNS FROM goal_detail")
        print("2. 查看数据: SELECT * FROM goal_detail ORDER BY created_at DESC LIMIT 10")
        print("3. 统计difficulty: SELECT difficulty, COUNT(*) FROM goal_detail GROUP BY difficulty")
    else:
        print("\n=== 检查失败 ===")
        print("请手动访问H2控制台进行检查")

if __name__ == "__main__":
    main()