jwtGHCustomRepoProperties.py

import os
import jwt  # PyJWT
import requests
import pandas as pd
from dotenv import load_dotenv
from datetime import datetime, timedelta
import time

# Load environment variables
load_dotenv()

# Load the RSA private key from the file path (update the path as necessary)
private_key_file = os.getenv('GITHUB_RSA_PRIVATE_KEY_PATH')
app_id = os.getenv('GITHUB_APP_ID')
DX_PROXY_USER = os.getenv('DX_PROXY_USER')
DX_PROXY_PASS = os.getenv('DX_PROXY_PASS')
VERBOSE = os.getenv('VERBOSE', 'False').lower() in ('true', '1', 't')

# Ensure that the necessary credentials are loaded
if not private_key_file or not app_id:
    raise ValueError("GitHub App RSA private key path or App ID not found. Please set it in a .env file.")

# Load private key from file
with open(private_key_file, 'r') as f:
    private_key = f.read()

# Construct the proxy URL
proxy_url = f"http://{DX_PROXY_USER}:{DX_PROXY_PASS}@proxy.getdx.net:80"
proxies = {
    "http": proxy_url,
    "https": proxy_url
}

# Function to create a JWT
def create_jwt(app_id, private_key):
    # JWT token expires in 10 minutes
    payload = {
        "iat": int(time.time()),  # Issued at
        "exp": int(time.time()) + (10 * 60),  # Expires in 10 minutes
        "iss": app_id  # GitHub App ID
    }
    token = jwt.encode(payload, private_key, algorithm="RS256")
    return token

# Function to get an installation access token
def get_installation_token(jwt_token):
    # Replace with your GitHub App installation ID
    installation_id = os.getenv('GITHUB_INSTALLATION_ID')
    url = f"https://api.github.com/app/installations/{installation_id}/access_tokens"
    
    headers = {
        "Authorization": f"Bearer {jwt_token}",
        "Accept": "application/vnd.github+json"
    }

    response = requests.post(url, headers=headers, proxies=proxies)

    if response.status_code == 201:
        return response.json()['token']
    else:
        raise Exception(f"Failed to get installation token: {response.status_code}, {response.text}")

# Step 1: Generate the JWT for the app
jwt_token = create_jwt(app_id, private_key)

# Step 2: Retrieve the installation access token
access_token = get_installation_token(jwt_token)

# Step 3: Prepare GitHub API headers with the access token
headers = {
    "Authorization": f"Bearer {access_token}",
    "Accept": "application/vnd.github+json",
    "X-GitHub-Api-Version": "2022-11-28"
}

# Read CSV file as in your original code
csv_file_path = "ArchOrgRepos.csv"
df = pd.read_csv(csv_file_path)

df['url'] = "https://api.github.com/repos/" + df['login'] + "/" + df['name'] + "/properties/values"

# Function to handle GitHub rate limits
def handle_rate_limit(response):
    if response.status_code == 403 and 'X-RateLimit-Remaining' in response.headers:
        remaining = int(response.headers['X-RateLimit-Remaining'])
        if remaining == 0:
            reset_time = int(response.headers['X-RateLimit-Reset'])
            wait_time = max(reset_time - int(time.time()), 0)
            print(f"Rate limit exceeded. Waiting for {wait_time} seconds.")
            time.sleep(wait_time)
            return True
    return False

# Create an empty list to store the generated SQL insert statements
insert_statements = []

for index, row in df.iterrows():
    repo_id = row['repo ID']
    repo_name = row['name']
    url = row['url']
    
    if VERBOSE:
        print(f"\nRequesting custom properties for Repo ID: {repo_id} - URL: {url}")

    try:
        # Make the GET request to the GitHub API through the proxy
        response = requests.get(url, headers=headers, proxies=proxies)

        # Handle rate limit if necessary
        if handle_rate_limit(response):
            response = requests.get(url, headers=headers, proxies=proxies)

        if VERBOSE:
            print(f"Status Code: {response.status_code}")  # Print status code to verify connection

        # Check if the response was successful
        if response.status_code == 200:
            repo_data = response.json()
            if VERBOSE:
                print(f"Response for {repo_name}: {repo_data}")

            # Step 7: Create an insert statement for each custom property in the response
            if isinstance(repo_data, list) and repo_data:
                for property in repo_data:
                    custom_property_name = property.get('property_name', 'N/A').replace("'", "''")
                    custom_property_value = property.get('value', 'N/A').replace("'", "''")

                    sql_insert = f"""
                    INSERT INTO github_custom_repo_properties (repo_name, repository_id, custom_property_name, custom_property_value, retrieved_at)
                    VALUES ('{repo_name.replace("'", "''")}', {repo_id}, '{custom_property_name}', '{custom_property_value}', '{datetime.now().isoformat()}');
                    """
                    insert_statements.append(sql_insert)
                    if VERBOSE:
                        print(f"Generated SQL: {sql_insert}")
            else:
                if VERBOSE:
                    print(f"No custom properties found for {repo_name}.")
        else:
            print(f"Failed request for {repo_name}. HTTP Status: {response.status_code} - {response.text}")

    except requests.exceptions.RequestException as req_err:
        print(f"Request error occurred for Repo ID: {repo_id} - URL: {url} - Error: {req_err}")
    except Exception as err:
        print(f"Unexpected error occurred for Repo ID: {repo_id} - URL: {url} - Error: {err}")

# Step 8: Save the SQL statements to a text file for execution (if any SQL was generated)
if insert_statements:
    output_sql_path = "GithubCustomPropertiesInserts.sql"
    try:
        with open(output_sql_path, 'w') as f:
            for statement in insert_statements:
                f.write(statement + "\n")
        print(f"\nSQL insert statements saved to {output_sql_path}")
    except IOError as io_err:
        print(f"Error saving SQL insert statements to file '{output_sql_path}': {io_err}")
else:
    print("\nNo SQL insert statements were generated.")