Read list of dictionaries with nested dictionaries

Question

I have a file as content a list of dictionaries (Around 75000). For instance, this is an example of first line I got when reading the file (value for v):

{
 "id": 1,
 "name": "Explosives",
 "category_id": 1,
 "average_price": 294,
 "is_rare": 0,
 "max_buy_price": 755,
 "max_sell_price": 1774,
 "min_buy_price": 99,
 "min_sell_price": 18,
 "buy_price_lower_average": 176,
 "sell_price_upper_average": 924,
 "is_non_marketable": 0,
 "ed_id": 128049204,
 "category": {
   "id": 1,
   "name": "Chemicals"
 } 
}

My actual working code is :

for v in d:
    commodities_reference = []
    for k, g in v.items():
        if isinstance(g, dict):
            dict1 = g
            my_value1 = dict1.get("id")
            my_value2 = dict1.get("name")

    for s, i in v.items():
        if not isinstance(i, dict):
            commodities_reference.append(i)
    commodities_reference.append(my_value1)
    commodities_reference.append(my_value2) 

Output wanted = All the values in same list in the same order for doing a SQL INSERT Statement afterwards (Meaning values from the nested dict must be also at the end.)

[1, 'Explosives', 1, 294, 0, 755, 1774, 99, 18, 176, 924, 0, 128049204, 1, 'Chemicals']

From a performance perspective, with SQLITE3/python 3.7, it is a catastrophe. I am looking for some advices in order to make it more efficient. I am thinking about using executemany statement but it seems it takes tuple instead of list.

Answer 1

Currently you iterate over each dictionary twice. You can do it in one pass:

for v in d:
    commodities_reference = []
    for k, g in v.items():
        if isinstance(g, dict):
            commodities_reference.append(g["id"])
            commodities_reference.append(g["name"])
        else:
            commodities_reference.append(g)

Note that this appends the values when it encounters it. This means that in Python < 3.7 (cPython < 3.6) it is not guaranteed that the dictionary is actually the last item to be looked at, since dictionaries were not to guaranteed to be in insertion order.

You could even make this a generator and slightly more general:

def get_values_recursive(x):
    for value in x.values():
        if isinstance(value, dict):
            yield from get_values_recursive(value)
        else:
            yield value

for v in d:
    commodities_reference = list(get_values_recursive(v))
    # do something with it...
    print(commodities_reference)

When using the given example, this is the result:

>>> list(get_values_recursive(v))
[1, 'Explosives', 1, 294, 0, 755, 1774, 99, 18, 176, 924, 0, 128049204, 1, 'Chemicals']

When putting your code into a function, this generator is almost twice as fast with the given v:

In [13]: %timeit op(v)
5.32 µs ± 43.3 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)

In [14]: %timeit list(get_values_recursive(v))
3.64 µs ± 10.8 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)

Note that both take on the order of micro seconds, so unless you need to process more than 100000 items per second your bottleneck is probably in those SQL statements and how you execute them.