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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
# import mysql.connector
# from mysql.connector import Error
# import json
#
#
# def create_connection():
# with open('config.json') as config_file:
# config = json.load(config_file)
#
# connection = None
# try:
# connection = mysql.connector.connect(
# host=config['host_name'],
# port=config['mysql_port'],
# user=config['user_name'],
# password=config['user_password'],
# database=config['db_name']
# )
# print("Connection to MySQL DB successful")
# except Error as e:
# print(f"The error '{e}' occurred")
#
# return connection
#
#
# def execute_query(connection, query):
# if connection is not None:
# try:
# cursor = connection.cursor(dictionary=True)
# cursor.execute(query)
# results = cursor.fetchall()
# return results
# except Error as e:
# print(f"The error '{e}' occurred")
# finally:
# cursor.close()
#
#
# def save_results(results):
# data_dict = {}
# for row in results:
# company_id = row['roommaster_company_id']
# if company_id not in data_dict:
# data_dict[company_id] = []
# data_dict[company_id].append(row)
# return data_dict
#
#
# def get_data_dict():
# connection = create_connection()
# query = """
# SELECT
# rd.udid AS roomdevice_udid,
# rd.name AS roomdevice_name,
# rm.id AS roommaster_id,
# rm.name AS roommaster_name,
# rm.udid AS roommaster_udid,
# rm.company_id AS roommaster_company_id
# FROM
# cmdb_roomdevice rd
# JOIN
# cmdb_roommaster rm
# ON
# rd.master_id = rm.id;
# """
# results = execute_query(connection, query)
# data_dict = save_results(results)
# if connection.is_connected():
# connection.close()
# return data_dict
#
#
# def main():
# data_dict = get_data_dict()
# print(data_dict)
#
# # 打印数据字典的键，确保数据保存正确
# print("Data dictionary keys:", list(data_dict.keys()))
#
# # 打印每个公司ID对应的数据，确保数据保存正确
# for company_id, rows in data_dict.items():
# print(f"Company ID {company_id} has {len(rows)} records:")
# for row in rows:
# print(row)
#
#
# if __name__ == "__main__":
# main()
import mysql.connector
from mysql.connector import Error
import json
from flask import Flask, request, jsonify
import logging
app = Flask(__name__)
logging.basicConfig(level=logging.DEBUG, format='%(asctime)s - %(levelname)s - %(message)s')
def create_connection():
with open('config.json') as config_file:
config = json.load(config_file)
connection = None
try:
connection = mysql.connector.connect(
host=config['host_name'],
port=config['mysql_port'],
user=config['user_name'],
password=config['user_password'],
database=config['db_name']
)
print("Connection to MySQL DB successful")
except Error as e:
print(f"The error '{e}' occurred")
return connection
def execute_query(connection, query):
if connection is not None:
try:
cursor = connection.cursor(dictionary=True)
cursor.execute(query)
results = cursor.fetchall()
return results
except Error as e:
print(f"The error '{e}' occurred")
finally:
cursor.close()
def save_results(results):
data_dict = {}
for row in results:
company_id = row['roommaster_company_id']
if company_id not in data_dict:
data_dict[company_id] = []
data_dict[company_id].append(row)
return data_dict
def get_data_dict():
connection = create_connection()
query = """
SELECT
rd.udid AS roomdevice_udid,
rd.name AS roomdevice_name,
rm.id AS roommaster_id,
rm.name AS roommaster_name,
rm.udid AS roommaster_udid,
rm.company_id AS roommaster_company_id
FROM
cmdb_roomdevice rd
JOIN
cmdb_roommaster rm
ON
rd.master_id = rm.id;
"""
results = execute_query(connection, query)
data_dict = save_results(results)
if connection.is_connected():
connection.close()
return data_dict
def main():
global data_dict
data_dict = get_data_dict()
print(data_dict)
# 打印数据字典的键，确保数据保存正确
print("Data dictionary keys:", list(data_dict.keys()))
# 打印每个公司ID对应的数据，确保数据保存正确
for company_id, rows in data_dict.items():
print(f"Company ID {company_id} has {len(rows)} records:")
for row in rows:
print(row)
@app.route('/get_regions', methods=['GET'])
def get_regions():
try:
company_id = int(request.args.get('company_id'))
logging.debug(f"收到公司ID: {company_id}")
regions = []
for row in data_dict.get(company_id, []):
region_info = {
'roommaster_id': row['roommaster_id'],
'roommaster_name': row['roommaster_name'],
'roommaster_udid': row['roommaster_udid']
}
regions.append(region_info)
if not regions:
logging.warning(f"未找到公司ID {company_id} 对应的区域信息")
return jsonify({"error": f"未找到公司ID {company_id} 对应的区域信息"}), 404
return jsonify(regions)
except (ValueError, TypeError) as e:
logging.error(f"无效的公司ID: {request.args.get('company_id')}")
return jsonify({"error": "无效的公司ID"}), 400
if __name__ == '__main__':
main()
app.run(debug=True)