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>You can iterate over <code>d.values()</code> and use the <code>.keys()</code> and <code>.values()</code> methods on the nested dictionaries to get the columns and values:</p>

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

<p>Note that in Python 3 <a href="https://docs.python.org/3/library/stdtypes.html#dict.keys" rel="nofollow"><code>dict.keys()</code></a> and <a href="https://docs.python.org/3/library/stdtypes.html#dict.values" rel="nofollow"><code>dict.values()</code></a> return <em>views</em> of the dictionaryâs keys and values (unlike lists in Python 2).</p>

python python-3.x dictionary