从API输出创建CSV

Question

我正在尝试从python的一些API调用创建CSV文件。你知道吗

我已经让它工作，但这不是一个非常实际的方式来解决我的问题。你知道吗

基本上我想做的是：

    csv_headers = ['Switch','Switchport Number','Port Name','VLAN']
    writer = csv.DictWriter(csv_file, fieldnames=csv_headers)
    writer.writeheader()
    get_devices = requests.get(base_url + '/networks/' + net_id + '/devices/', headers=headers).json()#Get all devices
    for device in get_devices:
        model = device['model']
        if 'MS' in model:
            serial = device['serial']
            name = device['name']
            switch_info = requests.get(base_url + '/devices/' + serial + '/switchPorts', headers=headers).json()#Get SwitchPortNumber, SwitchPortName and VLAN
            for switch_item in switch_info:
                switch_port = switch_item['number']
                switch_port_name = switch_item['name']
                vlan = switch_item['vlan']
                writer.writerow({csv_headers[0]:name,csv_headers[1]:switch_port,csv_headers[2]:switch_port_name,csv_headers[3]:vlan})

csv_headers2 = ['Switch Port','IP Address','Mac Address','Manufactor']
    writer2 = csv.DictWriter(csv_file2, fieldnames=csv_headers2)
    writer2.writeheader()
    get_client = requests.get(base_url + '/devices/' + serial + '/clients?timespan=180', headers=headers).json()
    for client_item in get_client:
        ip = client_item['ip']
        mac = client_item['mac']
        sp = int(client_item['switchport'])
        vendor = requests.get('https://api.macvendors.com/' + client_item['mac'])
        vendor = (format(str(vendor.text)))
        vendor = vendor.replace(",","")
        time.sleep(1)
        writer2.writerow({csv_headers2[0]:sp,csv_headers2[1]:ip,csv_headers2[2]:mac,csv_headers2[3]:vendor})
#CREATE CLIENT OUTPUT CSV#

#CREATE DATAFRAMES AND RENAME COLUMNS#
df1 = pd.read_csv(dirname+'/temp_switch.csv')
df2 = pd.read_csv(dirname+'/temp_client.csv')
df2.rename(columns={"Switch Port": "Switchport Number"}, inplace=True)
#CREATE DATAFRAMES AND RENAME COLUMNS#

#MERGE DATAFRAMES#
merged_df = pd.merge(df1, df2, on=["Switchport Number"], how="left")
#MERGE DATAFRAMES#

#REPLACE NO VALUES WITH DEFAULT VALUES#
default_values = {"IP Address": "No IP Address found", "Mac Address": "No MAC Address found", "Manufactor": "Could not resolve vendor"}
merged_df.fillna(value=default_values, inplace=True)
#REPLACE NO VALUES WITH DEFAULT VALUES#

#CREATE FINAL OUTPUT CSV
merged_df.to_csv(dirname+'/SwitchOutput-'+datetime.datetime.now().strftime("%d-%m-%y-%H-%M-%S")+'.csv', index=False)
#CREATE FINAL OUTPUT CSV

#REMOVE TEMP FILES#
os.remove(dirname+'/temp_switch.csv')
os.remove(dirname+'/temp_client.csv')

如您所见，我正在尝试进行一些API调用，并将这些数据“存储”到temp csv文件中。搜索该数据，如果switchport有IP，则合并该数据，如果！=然后只打印未找到的数据。你知道吗

我能把我的数据存储在熊猫的数据框里吗？像这样的

import pandas as pd
import requests

headers = ['Switch','Switchport Number','Port Name','VLAN',IP Address','Mac Address','Manufactory']



df1 = pd.DataFrame(output from switch_info)
df2 = pd.DataFrame(output from client_info)

compare data from df1 with df2 and create df3

merged_df.to_csv('output.csv')

希望有人能帮我！你知道吗

提前谢谢。你知道吗

Answer 1

根据您的代码，我假设您的API有一个正确的JSON响应。 为此，您可以使用

get_devices = requests.get(base_url + '/networks/' + net_id + '/devices/', headers=headers).json()#Get all devices
get_client = requests.get(base_url + '/devices/' + serial + '/clients?timespan=180', headers=headers).json()
df1 = pd.read_json(get_devices)
df2 = pd.read_json(get_client)

并应用相应的操作，例如

df1 = df1[df1['model'=='MS']]

设备和客户端之间的连接等等。你知道吗