trying to find out correct syntax to traverse through through the list to get all values and insert into oracle.

edit: Below is the json structure :

[{
  "publishTime" : "2021-11-02T20:18:36.223Z",
  "data" : {
    "DateTime" : "2021-11-01T15:10:17Z",
    "Name" : "x1",
    "frtb" : {
      "code" : "set1"
    },
    "mainAccounts" : [ {
      "System" : {
        "identifier" : {
          "domain" : "System",
          "id" : "xxx"
        },
        "name" : "TEST1"
      },
      "Account" : "acc1"
    }, {
      "System" : {
        "identifier" : {
          "domain" : "System",
          "id" : "xxx"
        },
        "name" : "TEST2"
      },
      "Account" : "acc2"
    }, {
      "System" : {
        "identifier" : {
          "domain" : "System",
          "id" : "xxx"
  
        },
        "name" : "TEST3"
      },
      "Account" : "acc3"
    }],
    "sub" : {
      "ind" : false,
      "identifier" : {
        "domain" : "ops",
        "id" : "1",
        "version" : "1"
      }]

My python code :

insert_statement = """INSERT INTO mytable VALUES (:1,:2)"""

r =requests.get(url, cert=(auth_certificate, priv_key), verify=root_cert, timeout=3600)

data=json.loads(r.text)

myrows = []


for item in data:
 
  try:
        name = (item.get("data").get("Name"))
  except AttributeError:
        name=''
  try:
        account= (item.get("data").get("mainAccounts")[0].get("Account") )
  except TypeError:
        account=''

  rows=(name,account)
  myrows.append(rows)

cursor.executemany(insert_statement,myrows)  
connection_target.commit()

with the above i only get first value for 'account' in list i.e. ("acc1") , how to get all the values i.e. (acc1,acc2,acc3) ?

I have tried below with no success :

try: Account = (item.get("data").get("mainAccounts")[0].get("Account") for item in data["mainAccounts") except TypeError: Account= ''

please advise.Appreciate your help always.

CodePudding user response：

import json
my_json = json.load(open("my_json.json", 'r'))

>>> mainAccounts = my_json['mainAccounts']
>>> for account in mainAccounts:
...    account_name = account['Account']
...    system_domain = account['System']['identifier']['domain']
...    system_id = account['System']['identifier']['id']
...    system_name = account['System']['name']
...    print(f"\n======={account_name}==========")
...    print(f"System domain: {system_domain}")
...    print(f"System id: {system_id}")
...    print(f"System name: {system_name}")




=======acc1==========
System domain: System
System id: xxx
System name: TEST1

=======acc2==========
System domain: System
System id: xxx
System name: TEST2

=======acc3==========
System domain: System
System id: xxx
System name: TEST3

CodePudding user response：

I have not worked with Oracle before so I'm going to assume your SQL statement is correct and requires a tuple of (name, account_number).

data = [
    {
        "publishTime" : "2021-11-02T20:18:36.223Z",
        "data" : {
            "DateTime" : "2021-11-01T15:10:17Z",
            "Name" : "x1",
            "frtb" : {
                "code" : "set1"
            },
            "mainAccounts" : [ 
                {
                    "System" : {
                        "identifier" : {
                            "domain" : "System",
                            "id" : "xxx"
                        },
                        "name" : "TEST1"
                    },
                    "Account" : "acc1"
                }, {
                    "System" : {
                        "identifier" : {
                            "domain" : "System",
                            "id" : "xxx"
                        },
                        "name" : "TEST2"
                    },
                    "Account" : "acc2"
                }, {
                    "System" : {
                        "identifier" : {
                            "domain" : "System",
                            "id" : "xxx"
                        },
                        "name" : "TEST3"
                    },
                    "Account" : "acc3"
                }
            ],
            "sub" : {
                "ind" : False,
                "identifier" : {
                    "domain" : "ops",
                    "id" : "1",
                    "version" : "1"
                }
            }
        }
    }
]

myrows = []
for item in data:
    accounts = [
        (act["System"]["name"], act["Account"]) 
        for act in item.get("data", {}).get("mainAccounts", [])
    ]
    myrows.extend(accounts)
    
print(myrows) # [('TEST1', 'acc1'), ('TEST2', 'acc2'), ('TEST3', 'acc3')]

And then assuming it's in the correct format.

cursor.executemany(insert_statement, myrows)

How it works:

This line gets your top level dict and assigns it to item. I used a for loop in case there are multiple dicts in the list.

for item in data:

The list comprehension is a bit more complex. But it can be translated to this.

accounts = []
for act in item.get("data", {}).get("mainAccounts", []):
    name = act["System"]["name"]
    account = act["Account"]
    accounts.append(name, account)

Or simplified even more:

data_dict = item.get("data", {})
main_accounts_list = data_dict.get("mainAccounts", [])
for act in main_accounts_list:
    # the rest of the code

I used dict.get() with an empty dict and list as a default so that even if the keys do not exist, it will still be able to process the subsequent .get and iteration without erroring.

Finally, you extend my_rows rather than append so you add don't get a list of list.

myrows.extend(accounts)