回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>我试图通过一个API请求获取的json输出,然后将其加载到excel文件中
问题是我从api得到的响应,如果我将其转储到json.dumps()方法,它将变得不可解析。但如果我尝试将其解析为文本,然后尝试将其格式化为json格式化程序
虽然我写了下面的代码来写入csv,但我希望它是excel文件。。
下面是我实际代码中的示例<code>respone.text</code>变量,如下所示:</p>
<pre><code>{
"value": [
{
"correlationId": "xxxxxxxxxx",
"eventName": {
"value": "EndRequest",
"localizedValue": "EndRequest"
},
"id": "/subscriptions/xxxxxxxxxx/resourcegroups/xxxxxxxxx/providers/Microsoft.Compute/virtualMachines/xxxxxx/extensions/enablevmaccess/events/xxxxxxxxxx/ticks/xxxxxxxx",
"level": "Informational",
"resourceGroupName": "xxxxxx",
"resourceProviderName": {
"value": "Microsoft.Compute",
"localizedValue": "Microsoft.Compute"
},
"operationName": {
"value": "Microsoft.Compute/virtualMachines/extensions/write",
"localizedValue": "Microsoft.Compute/virtualMachines/extensions/write"
},
"status": {
"value": "Succeeded",
"localizedValue": "Succeeded"
},
"eventTimestamp": "2020-08-06T12:47:02.0657952Z",
"submissionTimestamp": "2020-08-06T12:49:03.137537Z"
},
{
"correlationId": "xxxxxxxxxx",
"eventName": {
"value": "EndRequest",
"localizedValue": "EndRequest"
},
"id": "/subscriptions/xxxxxxxxxx/resourcegroups/xxxxxxxxx/providers/Microsoft.Compute/virtualMachines/xxxxxx/extensions/enablevmaccess/events/xxxxxxxxxx/ticks/xxxxxxxx",
"level": "Informational",
"resourceGroupName": "xxxxxx",
"resourceProviderName": {
"value": "Microsoft.Compute",
"localizedValue": "Microsoft.Compute"
},
"operationName": {
"value": "Microsoft.Compute/virtualMachines/extensions/write",
"localizedValue": "Microsoft.Compute/virtualMachines/extensions/write"
},
"status": {
"value": "Succeeded",
"localizedValue": "Succeeded"
},
"eventTimestamp": "2020-08-06T12:47:02.0657952Z",
"submissionTimestamp": "2020-08-06T12:49:03.137537Z"
},
]
}
</code></pre>
<p>下面是我正在尝试的代码:</p>
<pre><code>d_date = datetime.datetime.now()
today = d_date.strftime('%Y-%m-%dT%H:%M:%S.%fZ')
print(today)
N = 10
date_N_days_ago = datetime.datetime.now() - timedelta(days=N)
start_date = date_N_days_ago.strftime('%Y-%m-%dT%H:%M:%S.%fZ')
print(start_date)
vm_list = compute_client.virtual_machines.list_all()
for vm_general in vm_list:
general_view = vm_general.id.split("/")
resource_group = general_view[4]
print(resource_group)
BASE_URL = f"https://management.azure.com/subscriptions/{subscription_id}/providers/microsoft.insights/eventtypes/management/values?api-version=2015-04-01&$filter=eventTimestamp ge {start_date} and eventTimestamp le {today} and resourceGroupName eq {resource_group}&$select=eventName,id,resourceGroupName,resourceProviderName,operationName,status,eventTimestamp,correlationId,submissionTimestamp,level"
BASE_URL = BASE_URL
headers = {
"Authorization": 'Bearer ' + credential.token["access_token"]
}
response = requests.get(BASE_URL, headers=headers)
# if i convert below line to df_json = response.json() it says AttributeError: 'str' object has no attribute 'json'
df_json = response.text # this is a string but i am able to parse it properly in json forammter
print(df_json)
with open('c:\csv\logs_test.csv', 'w') as f:
for key in df_json.keys():
f.write("%s,%s\n" % (key, df_json[key]))
break
</code></pre>
<p>我得到的错误如下:</p>
<pre><code>AttributeError: 'str' object has no attribute 'keys'
</code></pre>
<p>预期结果:</p>
<pre><code>Actually I need to to write to xls (excel) format having columns as "correlationId,eventName,id,resourceGroupName,resourceProviderName,operationName,status,eventTimestamp,submissionTimestamp
</code></pre>