ygol ygol - 6 months ago 10
JSON Question

Iterate python dictionary to assign value to table's fields

Using an API I get a json dictionary as response in the form:

{"transacDet":[{"field1":6933434802,"field2":323499903,"field3":293483842},
{"field1":693433448,"field2":333400300,"field3":298334300}],"responseStatus"
:{"statusMessage":"success","statusCode":0}}


I am looking to create records in a table having the same corresponding fields as in the json response: field1, field2, field3.

From the above example I would have 2 records:

field1 = 6933434802
field2 = 333400298
field3 = 298334842


and then:

field1 = 693433448
field2 = 333400300
field3 = 298334300


I have to iterate the response but not clear how.

I try using:

for k in resp_json.items()[0][1][0]:
print k


but I'm stuck there.
TIA

Answer

With only json module from standard library you can iterate over them sorted by field names per "row" easily like so:

#! /usr/bin/env
from __future__ import print_function
import json


JSON_TEXT = '''
{"transacDet":[{"field1":6933434802,"field2":323499903,"field3":293483842},
{"field1":693433448,"field2":333400300,"field3":298334300}],"responseStatus"
:{"statusMessage":"success","statusCode":0}}'''

resp_json = json.loads(JSON_TEXT)
for d in resp_json['transacDet']:
    for k in sorted(d.keys()):
        print(k, d[k])

Which yields:

field1 6933434802
field2 323499903
field3 293483842
field1 693433448
field2 333400300
field3 298334300

Update to sketch how one could push these values into an INSERT call:

# inside the loop instead of iterating over the fields in the
# dict / row at hand, one could easily for instance do:
resp_json = json.loads(JSON_TEXT)
for d in resp_json['transacDet']:
    insert_row(**d)

Here insert_row would be a function defined as follows (sample):

def insert_row(**kwargs):
    """Not production ready ;-)."""
    # pick up the row key-value pairs from dict
    list_of_tuples = [
        (k, kwargs[k] for k in sorted(kwargs.keys())]
    pass  # here you might use this list of (k, v) tuples