1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
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}")