API tools faq
paste
Advertisement
sasa2742002

Untitled

sasa2742002
Sep 27th, 2023
679
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 11.06 KB | None | 0 0
raw download clone embed print report
  1. import requests
  2. import json
  3. import boto3
  4. import concurrent.futures
  5.  
  6. # Constants for database connection and URLs
  7. DATABASE_NAME = ''
  8. DB_CLUSTER_ARN = ''
  9. DB_CREDENTIALS_SECRETS_STORE_ARN = ''
  10. RDS_CLIENT = boto3.client('rds')
  11.  
  12. # Define URL templates for different regions
  13. REGION_URLS = {
  14.     ["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"],
  15.         ["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"],
  16.         ["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"],
  17.         ["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"],
  18.         ["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"],
  19.         ["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"]
  20.         ["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"]
  21.         ["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"],
  22.         ["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"],
  23.         ["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"],
  24.         ["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"],
  25.         ["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"],
  26.         ["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"],
  27.         ["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"],
  28.         ["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"],
  29.         ["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"],
  30.         ["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"]
  31.         ["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"],
  32.         ["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"],
  33.         ["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"],
  34.         ["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"],
  35.         ["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"],
  36.         ["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"],
  37.         ["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"],
  38.         ["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"]
  39.         ["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"],
  40.         ["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"]
  41.         ["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"]
  42. }
  43.  
  44.  
  45. def create_tables_if_not_exists():
  46.     # Define SQL statements for table creation
  47.     # Execute these statements using excute_sql function with parameters
  48.     regions = 'CREATE TABLE IF NOT EXISTS regions (region_id INT PRIMARY KEY,region_long_name VARCHAR(15));'
  49.     execute_sql(regions)
  50.  
  51.     operating_systems = 'CREATE TABLE IF NOT EXISTS operating_systems (operating_system_id INT PRIMARY KEY,operating_system_name VARCHAR(25));'
  52.     execute_sql(operating_systems)
  53.  
  54.     vcpu_cores = 'CREATE TABLE IF NOT EXISTS vcpu_cores (vcpu_id INT PRIMARY KEY,core_count INT);'
  55.     execute_sql(vcpu_cores)
  56.  
  57.     ec2_instances = 'CREATE TABLE IF NOT EXISTS ec2_instances (instance_id INT PRIMARY KEY,vcpu_id INT,memory FLOAT,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));'
  58.     execute_sql(ec2_instances)
  59.  
  60.     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));'
  61.     execute_sql(region_instances)
  62.  
  63.  
  64. def execute_sql(sql, parameters=[]):
  65.     response = RDS_CLIENT.execute_statement(
  66.         secretArn=DB_CREDENTIALS_SECRETS_STORE_ARN,
  67.         database=DATABASE_NAME,
  68.         resourceArn=DB_CLUSTER_ARN,
  69.         sql=sql,
  70.         parameters=parameters
  71.     )
  72.     return response
  73.  
  74.  
  75. def fetch_data(region_name, url):
  76.     response = requests.get(url)
  77.     if response.status_code == 200:
  78.         data_info = response.json()
  79.         region_data = data_info['regions'].get(region_name, {})
  80.         instances = []
  81.  
  82.         for instance_name, instance_attributes in region_data.items():
  83.             # Process and insert data into the database
  84.             save_data(instance_name, instance_attributes, region_name)
  85.  
  86.         return instances
  87.     else:
  88.         return None
  89.  
  90.  
  91. def save_data(instance_name, instance_attributes, region_name):
  92.     # Extract data from instance_attributes
  93.     memory = float(instance_attributes.get('Memory', '').replace(' GiB', ''))
  94.     storage = instance_attributes.get('Storage', '')
  95.     network_performance = float(instance_attributes.get('Network Performance', '').replace(' Gigabit', ''))
  96.     operating_system_name = instance_attributes.get('Operating System', '')
  97.     vcpu_cores_count = int(instance_attributes.get('vCPU', ''))
  98.     price = float(instance_attributes.get('Price', '').replace('$', ''))
  99.  
  100.     # sql
  101.     insert_region_sql = "INSERT INTO regions (region_long_name) VALUES (:region_name)"
  102.     insert_os_sql = "INSERT INTO operating_systems (operating_system_name) VALUES (:os_name)"
  103.     insert_vcpu_sql = "INSERT INTO vcpu_cores (core_count) VALUES (:core_count)"
  104.  
  105.     # parameters
  106.     insert_region_params = [region_name]
  107.     insert_os_params = [operating_system_name]
  108.     insert_vcpu_params = [vcpu_cores_count]
  109.  
  110.     # execute
  111.     execute_sql(insert_region_sql, insert_region_params)
  112.     execute_sql(insert_os_sql, insert_os_params)
  113.     execute_sql(insert_vcpu_sql, insert_vcpu_params)
  114.  
  115.     # sql
  116.     select_region_sql = "SELECT region_id FROM regions WHERE region_long_name = :region_name"
  117.     select_os_sql = "SELECT operating_system_id FROM operating_systems WHERE operating_system_name = :os_name"
  118.     select_vcpu_sql = "SELECT vcpu_id FROM vcpu_cores WHERE core_count = :core_count"
  119.  
  120.     # parameters
  121.     select_region_params = [region_name]
  122.     select_os_params = [operating_system_name]
  123.     select_vcpu_params = [vcpu_cores_count]
  124.  
  125.     # execute
  126.     region_id = execute_sql(select_region_sql, select_region_params)
  127.     os_id = execute_sql(select_os_sql, select_os_params)
  128.     vcpu_id = execute_sql(select_vcpu_sql, select_vcpu_params)
  129.  
  130.     # sql
  131.     insert_instance_sql = "INSERT INTO ec2_instances (vcpu_id, memory, storage, network_performance, operating_system_id, instance_name) VALUES (:vcpu_id, :memory, :storage, :network_performance, :os_id, :instance_name)"
  132.  
  133.     # parameters
  134.     insert_instance_params = [vcpu_id, memory, storage, network_performance, os_id, instance_name]
  135.  
  136.     # execute
  137.     execute_sql(insert_instance_sql, insert_instance_params)
  138.  
  139.     # sql
  140.     select_instance_sql = "SELECT instance_id FROM ec2_instances WHERE instance_name = :instance_name"
  141.  
  142.     # parameters
  143.     select_instance_params = [instance_name]
  144.  
  145.     # execute
  146.     instance_id = execute_sql(select_instance_sql, select_instance_params)
  147.  
  148.     # sql
  149.     insert_region_instance_sql = "INSERT INTO region_instances (region_id, instance_id, price_per_hour) VALUES (:region_id, :instance_id, :price)"
  150.  
  151.     # parameters
  152.     insert_region_instance_params = [region_id, instance_id, price]
  153.    
  154.     # execute
  155.     execute_sql(insert_region_instance_sql, insert_region_instance_params)
  156.  
  157.  
  158.  
  159.  
  160.  
  161. def main():
  162.     create_tables_if_not_exists()
  163.  
  164.     # Fetch data from different regions in parallel
  165.     with concurrent.futures.ThreadPoolExecutor() as executor:
  166.         futures = [executor.submit(fetch_data, region_name, url) for region_name, url in REGION_URLS.items()]
  167.  
  168.     # Wait for all futures to complete
  169.     concurrent.futures.wait(futures)
  170.  
  171.  
  172. if __name__ == "__main__":
  173.     main()
  174.  
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!