Python Nested Dictionary With Sql Insert

I have generated a very large dictionary after processing an XML file, and I am looking extract from this dictionary and to insert columns and values into my mySQL database table.

Solution 1:

You can iterate over d.values() and use the .keys() and .values() methods on the nested dictionaries to get the columns and values:

for v in d.values():
    cols = v.keys()
    vals = v.values()

    sql = "INSERT INTO Parameters ({}) VALUES ({})".format(
        ', '.join(cols),
        ', '.join(['%s'] * len(cols)));
    try:
        cursor.execute(sql, vals)
    except Exception as e:
        pass

Note that in Python 3 dict.keys() and dict.values() return views of the dictionary’s keys and values (unlike lists in Python 2).

Solution 2:

Iterating over a dictionary actually iterates over the keys. for k in d: is equivalent to for k in d.keys():. You are looking for the values or items methods, which will actually return the key and the value as a tuple:

for k, v in d.items():
    # k will take the values 'Test1', 'Test2', etc.# v will take the values of the corresponding nested dicts.

or

for v in d.values():
    # v will take on the values of the nested dicts.

I would recommend using items over values since that way you will have a reference to which primary key (test) you are processing. I am going to go out on a limb here and guess that you will need this for the non-trivial version of your program.

From there, you use v as you are trying to use d[...], since that is exactly what it is:

for k, v in d.items():
    cols = v.keys()
    vals = v.values()

    sql = "INSERT INTO Parameters ({0}) VALUES ({1})".format(
                ', '.join(cols),
                ', '.join(['%s'] * len(v))
    )

    try:
        cursor.execute(sql, vals)
    except Exception as e:
        pass

connection.close()
return

Since v is a nested dictionary, you can get the number of elements in both cols and vals as just len(v).