带有SQL插入的Python嵌套字典 [英] Python nested dictionary with SQL insert

问题描述

在处理XML文件后，我生成了一个非常大的字典，我正在寻找从该字典中提取的内容，并将列和值插入到我的mySQL数据库表中.

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.

我正在使用Python 3.

I am using Python 3.

字典是嵌套的；这是我所拥有的一个简单例子:

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

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'}
} 

基本上，我想遍历此字典中的每个主键(例如Test1，Test2，Test3)，并将辅助键提取为列名元组，并将关联的seconday键值提取为值元组，有点像这样:/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:

cols = ('TestID','Dev_Type','Version','Address')
vals = ('first','this device','v1_0','some Address')

在遍历每个主键时，我将使用以下命令将两个元组添加到mySQL表中:

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

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

然后在下一个主键('Test2')上重复该过程.

Then repeat the process on the next primary key ('Test2').

我已经进行了初步尝试，但是在这种情况下已经对主键进行了硬编码:

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

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

推荐答案

您可以遍历 d.values()并使用 .keys()和 .values()方法以获取列和值:

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

请注意，在Python 3中 dict.keys() 和 dict.values() 返回字典键和值的视图(与Python 2中的列表不同).

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

这篇关于带有SQL插入的Python嵌套字典的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！