API tools faq
paste
Advertisement
sasa2742002

Untitled

sasa2742002
Sep 27th, 2023
765
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 10.38 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.     parameters = {
  101.         "region_name": region_name,
  102.         "os_name": operating_system_name,
  103.         "core_count": vcpu_cores_count,
  104.         "instance_name": instance_name,
  105.         "memory": memory,
  106.         "storage": storage,
  107.         "network_performance": network_performance,
  108.         "price": price
  109.     }
  110.  
  111.     sql_statements = {
  112.         "insert_region_sql": "INSERT INTO regions (region_long_name) VALUES (:region_name)",
  113.         "insert_os_sql": "INSERT INTO operating_systems (operating_system_name) VALUES (:os_name)",
  114.         "insert_vcpu_sql": "INSERT INTO vcpu_cores (core_count) VALUES (:core_count)",
  115.         "select_region_sql": "SELECT region_id FROM regions WHERE region_long_name = :region_name",
  116.         "select_os_sql": "SELECT operating_system_id FROM operating_systems WHERE operating_system_name = :os_name",
  117.         "select_vcpu_sql": "SELECT vcpu_id FROM vcpu_cores WHERE core_count = :core_count",
  118.         "insert_instance_sql": "INSERT INTO ec2_instances (vcpu_id, memory, storage, network_performance, operating_system_id, instance_name) VALUES (:core_count, :memory, :storage, :network_performance, :os_id, :instance_name)",
  119.         "select_instance_sql": "SELECT instance_id FROM ec2_instances WHERE instance_name = :instance_name",
  120.         "insert_region_instance_sql": "INSERT INTO region_instances (region_id, instance_id, price_per_hour) VALUES (:region_id, :instance_id, :price)"
  121.     }
  122.  
  123.     # Insert data into the database
  124.     # Use execute_sql function to execute SQL statements
  125.     for sql_statement in sql_statements.values():
  126.         execute_sql(sql_statement, parameters)
  127.  
  128.  
  129.  
  130.  
  131.  
  132.  
  133.  
  134. def main():
  135.     create_tables_if_not_exists()
  136.  
  137.     # Fetch data from different regions in parallel
  138.     with concurrent.futures.ThreadPoolExecutor() as executor:
  139.         futures = [executor.submit(fetch_data, region_name, url) for region_name, url in REGION_URLS.items()]
  140.  
  141.     # Wait for all futures to complete
  142.     concurrent.futures.wait(futures)
  143.  
  144.  
  145. if __name__ == "__main__":
  146.     main()
  147.  
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!