· 2 years ago · Sep 26, 2023, 12:50 PM
1from venv import create
2import requests
3import json
4import boto3
5
6database_name = ''
7db_cluster_arn = ''
8db_credentials_secrets_store_arn = ''
9
10
11def get_data(url, region_name):
12 response = requests.get(url)
13 data_info = response.json()
14
15 if response.status_code == 200:
16 instances = []
17 region_data = data_info['regions'].get(region_name, {})
18
19 for instance_name, instance_attributes in region_data.items():
20 instance = {
21 'Instance Name': instance_name,
22 'Rate Code': instance_attributes.get('rateCode', ''),
23 'Price': instance_attributes.get('price', ''),
24 'Location': instance_attributes.get('Location', ''),
25 'Instance Family': instance_attributes.get('Instance Family', ''),
26 'vCPU': instance_attributes.get('vCPU', ''),
27 'Memory': instance_attributes.get('Memory', ''),
28 'Storage': instance_attributes.get('Storage', ''),
29 'Network Performance': instance_attributes.get('Network Performance', ''),
30 'Operating System': instance_attributes.get('Operating System', ''),
31 'Pre Installed S/W': instance_attributes.get('Pre Installed S/W', ''),
32 'License Model': instance_attributes.get('License Model', ''),
33 }
34 instances.append(instance)
35
36 return instances
37 else:
38 return None
39
40
41def north_america():
42 north_america_urls = [
43 ["US East (N. Virginia)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/US%20East%20(N.%20Virginia)/Linux/index.json?timestamp=1695335197182"],
44 ["US East (N. California)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/US%20West%20(N.%20California)/Linux/index.json?timestamp=1695335943885"],
45 ["US West (Oregon)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/US%20West%20(Oregon)/Linux/index.json?timestamp=1695336005767"],
46 ["Canada (Central)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Canada%20(Central)/Linux/index.json?timestamp=1695336038545"],
47 ["AWS GovCloud (US-East)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/AWS%20GovCloud%20(US-East)/Linux/index.json?timestamp=1695336076977"],
48 ["AWS GovCloud (US-West)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/AWS%20GovCloud%20(US)/Linux/index.json?timestamp=1695336143250"]
49 ]
50
51 north_america = []
52 for region_info in north_america_urls:
53 region = region_info[0]
54 url = region_info[1]
55 data = get_data(url, region)
56 north_america.append(data)
57
58 return north_america
59
60
61def africa():
62 africa_urls = [
63 ["Africa (Cape Town)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Africa%20(Cape%20Town)/Linux/index.json?timestamp=1695336201165"]
64 ]
65
66 africa = []
67 for region_info in africa_urls:
68 region = region_info[0]
69 url = region_info[1]
70 data = get_data(url, region)
71 africa.append(data)
72
73 return africa
74
75
76def asia_pacific():
77 asia_pacific = [
78 ["Asia Pacific (Hong Kong)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Asia%20Pacific%20(Hong%20Kong)/Linux/index.json?timestamp=1695336236566"],
79 ["Asia Pacific (Hyderabad)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Asia%20Pacific%20(Hyderabad)/Linux/index.json?timestamp=1695336267211"],
80 ["Asia Pacific (Jakarta)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Asia%20Pacific%20(Jakarta)/Linux/index.json?timestamp=1695336335452"],
81 ["Asia Pacific (Melbourne)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Asia%20Pacific%20(Melbourne)/Linux/index.json?timestamp=1695336377353"],
82 ["Asia Pacific (Mumbai)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Asia%20Pacific%20(Mumbai)/Linux/index.json?timestamp=1695336414559"],
83 ["Asia Pacific (Osaka)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Asia%20Pacific%20(Osaka)/Linux/index.json?timestamp=1695336448367"],
84 ["Asia Pacific (Seoul)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Asia%20Pacific%20(Seoul)/Linux/index.json?timestamp=1695336489384"],
85 ["Asia Pacific (Singapore)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Asia%20Pacific%20(Singapore)/Linux/index.json?timestamp=1695336516652"],
86 ["Asia Pacific (Sydney)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Australia%20(Sydney)/Linux/index.json?timestamp=1695336549342"],
87 ["Asia Pacific (Tokyo)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Australia%20(Tokyo)/Linux/index.json?timestamp=1695336572681"]
88 ]
89
90 asia_pacific = []
91 for region_info in asia_pacific:
92 region = region_info[0]
93 url = region_info[1]
94 data = get_data(url, region)
95 asia_pacific.append(data)
96
97 return asia_pacific
98
99
100def europe():
101 europe = [
102 ["Europe (Frankfurt)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/EU%20(Frankfurt)/Linux/index.json?timestamp=1695336606682"],
103 ["Europe (Ireland)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/EU%20(Ireland)/Linux/index.json?timestamp=1695336640824"],
104 ["Europe (London)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/EU%20(London)/Linux/index.json?timestamp=1695336671834"],
105 ["Europe (Milan)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/EU%20(Milan)/Linux/index.json?timestamp=1695336709113"],
106 ["Europe (Paris)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/EU%20(Paris)/Linux/index.json?timestamp=1695336734334"],
107 ["Europe (Spain)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/EU%20(Spain)/Linux/index.json?timestamp=1695336756525"],
108 ["Europe (Stockholm)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/EU%20(Stockholm)/Linux/index.json?timestamp=1695336795677"],
109 ["Europe (Zurich)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/EU%20(Zurich)/Linux/index.json?timestamp=1695336817871"]
110 ]
111
112 europe = []
113 for region_info in europe:
114 region = region_info[0]
115 url = region_info[1]
116 data = get_data(url, region)
117 europe.append(data)
118
119 return europe
120
121
122def middle_east():
123 middle_east = [
124 ["Middle East (Bahrain)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Middle%20East%20(Bahrain)/Linux/index.json?timestamp=1695336880976"],
125 ["Middle East (UAE)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/Middle%20East%20(UAE)/Linux/index.json?timestamp=1695336906621"]
126 ]
127
128 middle_east = []
129 for region_info in middle_east:
130 region = region_info[0]
131 url = region_info[1]
132 data = get_data(url, region)
133 middle_east.append(data)
134
135 return middle_east
136
137
138def south_america():
139 south_america = [
140 ["South America (Sao Paulo)", "https://b0.p.awsstatic.com/pricing/2.0/meteredUnitMaps/ec2/USD/current/ec2-ondemand-without-sec-sel/South%20America%20(Sao%20Paulo)/Linux/index.json?timestamp=1695336944637"]
141 ]
142
143 south_america = []
144 for region_info in south_america:
145 region = region_info[0]
146 url = region_info[1]
147 data = get_data(url, region)
148 south_america.append(data)
149
150 return south_america
151
152
153import boto3
154
155database_name = ''
156db_cluster_arn = ''
157db_credentials_secrets_store_arn = ''
158
159# Create an RDS client
160rds_client = boto3.client('rds')
161
162def excute_sql(sql):
163 response = rds_client.execute_statement(
164 secretArn=db_credentials_secrets_store_arn,
165 database=database_name,
166 resourceArn=db_cluster_arn,
167 sql=sql
168 )
169 return response
170
171
172def create_tables_if_not_exists():
173
174 regions = 'CREATE TABLE IF NOT EXISTS regions (region_id INT PRIMARY KEY,region_short_name VARCHAR(15),region_long_name VARCHAR(15));'
175 excute_sql(regions)
176
177 operating_systems = 'CREATE TABLE IF NOT EXISTS operating_systems (operating_system_id INT PRIMARY KEY,operating_system_name VARCHAR(25));'
178 excute_sql(operating_systems)
179
180 vcpu_cores = 'CREATE TABLE IF NOT EXISTS vcpu_cores (vcpu_id INT PRIMARY KEY,core_count INT);'
181 excute_sql(vcpu_cores)
182
183 ec2_instances = 'CREATE TABLE IF NOT EXISTS ec2_instances (instance_id INT PRIMARY KEY,vcpu_id INT,memory INT,storage VARCHAR(20),network_performance DECIMAL(10, 5),operating_system_id INT,instance_name VARCHAR(15),FOREIGN KEY (vcpu_id) REFERENCES vcpu_cores(vcpu_id),FOREIGN KEY (operating_system_id) REFERENCES operating_systems(operating_system_id));'
184 excute_sql(ec2_instances)
185
186 region_instances = 'CREATE TABLE IF NOT EXISTS region_instances (region_id INT,instance_id INT,price_per_hour DECIMAL(6, 4),FOREIGN KEY (region_id) REFERENCES regions(region_id),FOREIGN KEY (instance_id) REFERENCES ec2_instances(instance_id));'
187 excute_sql(region_instances)
188
189 return
190
191
192def save_data(data):
193 # variables
194 instance_name = data['Instance Name']
195 memory = float(data['Memory'].replace(' GiB', ''))
196 storage = data['Storage']
197 network_performance = float(data['Network Performance'].replace(' Gigabit', ''))
198 operating_system_name = data['Operating System']
199 vcpu_cores_count = int(data['vCPU'])
200 region_name = data['Location']
201 price = float(data['Price'].replace('$', ''))
202
203 # sql
204 insert_region_sql = "INSERT INTO regions (region_long_name) VALUES (:region_name)"
205 insert_os_sql = "INSERT INTO operating_systems (operating_system_name) VALUES (:os_name)"
206 insert_vcpu_sql = "INSERT INTO vcpu_cores (core_count) VALUES (:core_count)"
207
208 # parameters
209 region_params = [{"name": "region_long_name", "value": {"stringValue": region_name}}]
210 # {"name": "region_short_name", "value": {"stringValue": region_name}}]
211 os_params = [{"name": "os_name", "value": {"stringValue": operating_system_name}}]
212 vcpu_params = [{"name": "core_count", "value": {"longValue": vcpu_cores_count}}]
213
214 # excute_sql
215 excute_sql(insert_region_sql, region_params)
216 excute_sql(insert_os_sql, os_params)
217 excute_sql(insert_vcpu_sql, vcpu_params)
218
219 # sql
220 select_region_sql = "SELECT region_id FROM regions WHERE region_long_name = :region_name"
221 select_os_sql = "SELECT operating_system_id FROM operating_systems WHERE operating_system_name = :os_name"
222 select_vcpu_sql = "SELECT vcpu_id FROM vcpu_cores WHERE core_count = :core_count"
223
224 # excute_sql
225 region_id = excute_sql(select_region_sql, region_params)['records'][0][0]['longValue']
226 operating_system_id = excute_sql(select_os_sql, os_params)['records'][0][0]['longValue']
227 vcpu_id = excute_sql(select_vcpu_sql, vcpu_params)['records'][0][0]['longValue']
228
229 # sql
230 instance_data = (instance_name, vcpu_id, memory, storage, network_performance, operating_system_id)
231 insert_instance_sql = "INSERT INTO ec2_instances (instance_name, vcpu_id, memory, storage, network_performance, operating_system_id) VALUES (:instance_name, :vcpu_id, :memory, :storage, :network_performance, :os_id)"
232
233 # parameters
234 instance_params = [
235 {"name": "instance_name", "value": {"stringValue": instance_name}},
236 {"name": "vcpu_id", "value": {"longValue": vcpu_id}},
237 {"name": "memory", "value": {"doubleValue": memory}},
238 {"name": "storage", "value": {"stringValue": storage}},
239 {"name": "network_performance", "value": {"doubleValue": network_performance}},
240 {"name": "os_id", "value": {"longValue": operating_system_id}}
241 ]
242
243 # excute_sql
244 excute_sql(insert_instance_sql, instance_params)
245
246 # sql
247 instance_id = excute_sql("SELECT LAST_INSERT_ID()")['records'][0][0]['instance_id']
248 insert_region_instance_sql = "INSERT INTO region_instances (region_id, instance_id, price_per_hour) VALUES (:region_id, :instance_id, :price_per_hour)"
249
250 # parameters
251 region_instance_params = [
252 {"name": "region_id", "value": {"longValue": region_id}},
253 {"name": "instance_id", "value": {"longValue": instance_id}},
254 # {"name": "price_per_hour", "value": {"doubleValue": price_per_hour}}
255 # price_per_hour ???
256 ]
257
258 # excute_sql
259 excute_sql(insert_region_instance_sql, region_instance_params)
260
261 return
262
263
264
265
266def main():
267 create_tables_if_not_exists()
268 # north_america_data = north_america()
269 africa_data = africa()
270 # asia_pacific_data = asia_pacific()
271 # europe_data = europe()
272 # middle_east_data = middle_east()
273 # south_america_data = south_america()
274
275 save_data(africa_data)
276 # save_data(north_america_data)
277 # save_data(asia_pacific_data)
278 # save_data(europe_data)
279 # save_data(middle_east_data)
280 # save_data(south_america_data)
281
282
283
284
285main()
286