4Em6WPqs

1import requests
2import snowflake.connector
3import json
4import sys
5from snowflake.connector.errors import ProgrammingError
6
7# Step 1: Define JIRA SM API endpoint and Snowflake connection details
8jira_api_url = "https://jirasm.<domain>.com.org/rest/api/2/search?jql=ORDER%20BY%20Created&maxResults=500&expand=changelog" 
9bearer_token = "xyz"
10snowflake_connection_params = {
11    'user': 'xyz',
12    'password': 'pwd',
13    'account': 'acc',
14    'host': 'acc.cn-northwest-1.aws.domain.org',
15    'warehouse': 'DASHBOARD_WH',
16    'database': 'JIRA_DASHBOARD',
17    'schema': 'xws_JIRA_INGEST',
18    'role': 'ACCOUNTADMIN'
19}
20
21# Step 2: Set up headers for the API request
22headers = {
23    'Authorization': f'Bearer {bearer_token}',
24    'Content-Type': 'application/json'
25}
26
27# Step 3: Fetch JSON payload from JIRA SM API
28response = requests.get(jira_api_url, headers=headers)
29response.raise_for_status()  # Check for request errors
30jira_data = response.json()
31# Pretty-print the JIRA response
32# print(json.dumps(jira_data, indent=4))
33
34# Print top-level keys and their types
35# print("Top-level keys and their types:")
36# for key, value in jira_data.items():
37#    print(f"{key}: {type(value).__name__}")
38
39# Step 4: Extract 'key' and 'changelog.history' fields
40issue_key = jira_data['key']
41histories = jira_data['changelog']['histories']
42# print(histories)
43
44# Step 5: Connect to Snowflake and insert the data
45conn = snowflake.connector.connect(**snowflake_connection_params)
46cursor = conn.cursor()
47
48# Define the SQL query to insert data
49insert_query = """
50    INSERT INTO jira_case_one (ID, HISTORY)
51    VALUES (%s, %s)
52"""
53
54# Convert histories array to JSON string for insertion
55# histories_json = json.dumps(histories)
56 
57
58try:
59    for history_item in histories:
60        # Convert the history item to a JSON string
61        history_json = json.dumps(history_item)
62
63        # Replace None with null
64        history_json = history_json.replace("None", "null")
65
66        # Execute the insert query with proper parameters
67        cursor.execute(insert_query, (issue_key, history_json))
68    print("Data inserted successfully.")
69except ProgrammingError as e:
70    print(f"Error occurred: {e}")
71finally:
72    cursor.close()
73    conn.close()
74