import mysql.connector
import json

# 连接到数据库
db_config = {
    "host": "8.142.151.250",
    "user": "script",
    "password": "changfA123$",
    "database": "network_assets"
}

connection = mysql.connector.connect(**db_config)
cursor = connection.cursor()

# 设置 group_concat_max_len，设置返回字符串长度限制为 1,000,000 字符
cursor.execute("SET SESSION group_concat_max_len = 1000000")

# 执行SQL查询语句
sql_query = """
    SELECT 
    country_code AS countryCode,
    CONCAT(
        '{"countryCode": "', REPLACE(country_code, '"', '\\"'), '", ',
        '"ASInfoList": [', 
            GROUP_CONCAT(
                CONCAT(
                    '{"topology": false, "ASType": "', REPLACE(type, '"', '\\"'), 
                    '", "linkedNumber": ', connect_degree, 
                    ', "ASNumber": ', as_number, 
                    ', "ASDegrees": ', transmit_degree, 
                    ', "countryCode": "', REPLACE(country_code, '"', '\\"'), '"}'
                )
                SEPARATOR ', '
            ),
        '], ',
        '"countryName": "', REPLACE(country, '"', '\\"'), '"}'
    ) AS result
FROM as_info
GROUP BY country_code, country
"""

cursor.execute(sql_query)
query_result = cursor.fetchall()

# 关闭数据库连接
cursor.close()
connection.close()

# 将查询结果转换为正确格式的数据
formatted_result = []

for row in query_result:
    country_code = row[0]
    result_data = row[1]

    # 转换非字符串类型为字符串
    if isinstance(result_data, (list, tuple)):
        result_data = [str(item) for item in result_data]

    # # 构建 JSON 数据
    # json_data = {
    #     "countryCode": country_code,
    #     "ASInfoList": result_data
    # }
    data = json.loads(result_data)
    formatted_result.append(data)

# 将结果导出到 JSON 文件
output_file_path = "./output.json"
with open(output_file_path, "w", encoding="utf-8") as json_file:
    json.dump(formatted_result, json_file, indent=4, ensure_ascii=False)

print(f"查询结果已导出到 {output_file_path}")