<p>我尝试在代码中使用try:and except KeyError:进行了更多搜索,并能够将JSON数据插入到SQL表中,没有任何错误。在</p>
<pre><code>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
</code></pre>