API tools faq
paste
Advertisement
sasa2742002

Untitled

sasa2742002
Sep 26th, 2023
597
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 13.95 KB | None | 0 0
raw download clone embed print report
  1. from venv import create
  2. import requests
  3. import json
  4. import boto3
  5.  
  6. database_name = ''
  7. db_cluster_arn = ''
  8. db_credentials_secrets_store_arn = ''
  9.  
  10.  
  11. def 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.  
  41. def 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.  
  61. def 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.  
  76. def 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.  
  100. def 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.  
  122. def 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.  
  138. def 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.  
  153. import boto3
  154.  
  155. database_name = ''
  156. db_cluster_arn = ''
  157. db_credentials_secrets_store_arn = ''
  158.  
  159. # Create an RDS client
  160. rds_client = boto3.client('rds')
  161.  
  162. def 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.  
  172. def 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.  
  192. def 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.  
  266. def 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.  
  285. main()
  286.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!