Getting keyerror while fetching JSON data from API using python

Question

I am getting keyerror in one while printing one of the json data fetched from API using python.

Error:

Except nagios_service, I am able to print other data

Traceback (most recent call last):

  File "<ipython-input-55-3a1eadbbe594>", line 1, in <module>
    runfile('Y:/_Temp/MEIPE/python/20190104_Script_Jason_APIv3.py', wdir='Y:/_Temp/MEIPE/python')

  File "C:\Users\MEIPE\AppData\Local\Continuum\anaconda2\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 704, in runfile
    execfile(filename, namespace)

  File "C:\Users\MEIPE\AppData\Local\Continuum\anaconda2\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 93, in execfile
    exec(compile(scripttext, filename, 'exec'), glob, loc)

  File "Y:/_Temp/MEIPE/python/20190104_Script_Jason_APIv3.py", line 68, in <module>
    print data[i]["_source"]["nagios_service"]

KeyError: 'nagios_service'

My code:

url1 = "http://nagiosdatagateway.vestas.net/esq/ITE1452552/logstash- 
2018.12.16/2/desc"
response = urllib.urlopen(url1)
data = json.loads(response.read())
#define db connection
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                  "Server=DKCDCVDCP42\DPA;"
                  "Database=VPDC;"
                  "Trusted_Connection=yes;")
cursor = cnxn.cursor()
sql="SELECT count(*)  as count_of_rows FROM [VPDC].[pa]. 
[ROC_Nagios_Reporting_RawData]"
cursor.execute(sql)
for row in cursor.fetchall():
    k = row.count_of_rows
i = 0  
j = len(data)#find length of data set
#print j
for i in range(0,j): #loop to insert date into SQL Server
    print data[i]["_source"]["nagios_service"]
    print data[i]["_source"]["nagios_host"]
    print data[i]["_source"]["nagios_author"]
    print data[i]["_source"]["nagios_severity_label"]
    print data[i]["_source"]["nagios_external_command"]
    print data[i]["_source"]["@timestamp"]
 cnxn.commit() #commit transaction
 cursor.close()
 cnxn.close()

I need help in fixing this keyerror on nagios_service. And should print all data.

Answer 1

I tried using try: and except KeyError: in my code after searching SO a little more and was able to insert JSON data into SQL table with out any errors.

url1 = "http://nagiosdatagateway.vestas.net/esq/ITE1452552/logstash-" + ysday1
#print url1 #test
#url = "http://nagiosdatagateway.vestas.net/esq/ITE1452552/logstash- 
2018.12.16/2/desc"
response = urllib.urlopen(url1)
data = json.loads(response.read())

#define db connection
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                  "Server=DKCDCVDCP42\DPA;"
                  "Database=VPDC;"
                  "Trusted_Connection=yes;")
cursor = cnxn.cursor()
sql= "SELECT count(*)  as count_of_rows FROM [VPDC].[pa]. 
[ROC_Nagios_Reporting_RawData]"
cursor.execute(sql)
for row in cursor.fetchall():
    k = row.count_of_rows
i = 0  
j = len(data)#find length of data set
#print j

#for each in data:    
for i in range(0,j): #loop to insert date into SQL Server
        try:
            print data[i]["_source"]["nagios_author"]
            print data[i]["_source"]["nagios_service"]
            cursor.execute("insert into [VPDC].[pa].[ROC_Nagios_Reporting_RawData] 
            (Nagios_Author,Nagios_service,Nagios_host,Nagios_comment) values 
            (?,?,?,?)",(data[i]["_source"]["nagios_author"],data[i]["_source"] 
            ["nagios_service"],data[i]["_source"]["nagios_host"],data[i]["_source"] 
            ["nagios_comment"] ))

        except KeyError:
            pass
cnxn.commit() #commit transaction
cursor.close()
cnxn.close() #close connection