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};"
url = ''
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 = '' + f'{row[1]}' +'/versions'
    #L.append({row[1]: url})

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

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

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