Recommand · October 22, 2021 0

Parse the json output in tabular format and insert it to backend database

Currently, I am trying to get the project keys from the MS SQL database, then build a dynamic rest API Url, and then call Jira API to get JSON output.
Then I would like to parse the JSON and in tabular format and put it back in the database.
As I am quite new in python programming, it is becoming difficult for me to get it working.
Below is the code, so far I achieved in parts and pieces.
I am using jupyter notebook.

import requests

from requests.auth import HTTPBasicAuth

import json

import pandas

import pandas as pd

import pyodbc
cnxn_str = ("Driver={SQL Server Native Client 11.0};"
            "Server=mylocalserver;"
            "Database=JIRA;"
            "UID=.....;"
            "PWD=.......")
url = 'https://jira.com/rest/api/2/project/'
cnxn = pyodbc.connect(cnxn_str)
#cursor = cnxn.cursor()
data = pd.read_sql("SELECT DISTINCT[jiraprojectKey]FROM [dbo].[issue]",cnxn)
newdict = {}
for row in data.itertuples():
    url = 'https://jira.com/rest/api/2/project/' + f'{row[1]}' +'/versions'
    newdict[row[1]]=url
    print(row[1])
    #L.append({row[1]: url})
    #print(newdict)
 


auth = HTTPBasicAuth("user", "lnPFGfKxWmO7XGvBlCb4WPWqgKA8wcnwi4Y73j")

headers = {
  "Accept": "application/json"
}

response = requests.request(
   "GET",
   url,
   headers=headers,
   auth=auth
)
data1 =json.loads(response.content)
print(data1)
df = pd.DataFrame(data,columns=['id','name','released','startDate','releaseDate'])

#df.to_json('example.json')
#print(df)