Home > Back-end >  How to transform json structure in python?
How to transform json structure in python?

Time:04-01

I have a python code that takes some data from excel file and export`s it to json file.

import json
from collections import OrderedDict
from itertools import islice
from openpyxl import load_workbook


wb = load_workbook('E:\test.xlsx')


sheet = wb['Sheet1']


deviceData_list = []  

  
for row in islice(sheet.values, 1, sheet.max_row):
     
    deviceData = OrderedDict()
    deviceData['hostname'] = row[2]
    deviceData['ip'] = row[7]
    deviceData['username'] = row[13]
    deviceData['password'] = row[15]
    deviceData['secret'] = row[9]
    deviceData_list.append(deviceData)

j = json.dumps(deviceData_list)
print(j)

with open('data.json', 'w') as f:
    f.write(j)
it outputs json file like this:
    [{"hostname": "sw1", "ip": "8.8.8.8", "username": "contoso", "password": "contoso", "secret": "contoso"}, {"hostname": "sw2", "ip": "8.8.8.9", "username": "contoso", "password": "contoso2", "secret": "contoso2"}]

and what I would like is to make it look like this:

    {"switch_list": [{"hostname": "sw1","ip": "8.8.8.8","username": "contoso","password": "contoso","secret": "contoso"},{"hostname": "sw2","ip": "8.8.8.9","username": "contoso","password": "contoso2","secret": "contoso2"}]}

So basically I need to put "{ "switch_list":" in front of the current output and "]}" at the end, and whatever silly idea I had, I got different result. I figured out two ways to do it , first before json.dump , and second to just edit the json file after it is created, but i do not know what to target since "switch_list" is kind of outside :) This also means that I`m a dummy regarding Python or programming in general :) Any help is appreciated, I will not post what I tried since it is uselles. This is also my first post here so please forgive any stupidity. Cheers

CodePudding user response:

Instead of:

j = json.dumps(deviceData_list)
output = {"switch_list": deviceData_list}
j = json.dumps(output)

This creates a new dictionary where the only key is switch_list and its contents are your existing list. Then you dump that data.

CodePudding user response:

Change

j = json.dumps(deviceData_list)

to something like:

j = json.dumps({"switch_list": deviceData_list})
  • Related