Python nested dictionary with SQL insert
<p>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.</p>

<p>I am using Python 3.</p>

<p>The dictionary is nested; here's a simplistic example of what I have:</p>

<pre><code>d ={'Test1'{'TestID':'first','Dev_Type':'this device','Version':'v1_0','Address':'some Address'}
    'Test2'{'TestID':'second','Dev_Type':'that device','Version':'v1_0','Address':'other Address'}
    'Test3'{'TestID','third','Dev_Type':'other device','Version':'v1_0','Address':'another Address'}
} 
</code></pre>

<p>Essentially I want to iterate over each primary Key in this dictionary (e.g. Test1,Test2,Test3) and extract the secondary keys as a column name tuple and the associated seconday key values as a values tuple, a bit like this:</p>

<pre><code>cols = ('TestID','Dev_Type','Version','Address')
vals = ('first','this device','v1_0','some Address')
</code></pre>

<p>On iterating over each primary key I will add the two tuples to my mySQL table using this command:</p>

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

<p>Then repeat the process on the next primary key ('Test2').</p>

<p>I have made an initial attempt, but have hard coded the Primary key in this instance:</p>

<pre><code>for k, v in d:
    #Missing appropriate method here
    cols = tuple(d['Test1'].keys())
    vals = tuple(d['Test1'].values())

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

connection.close()
return
</code></pre>

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

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

<p>or</p>

<pre><code>for v in d.values():
    # v will take on the values of the nested dicts.
</code></pre>

<p>I would recommend using <code>items</code> over <code>values</code> 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.</p>

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

<pre><code>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
</code></pre>

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

python python-3.x dictionary