使用 Python 将 XML 转换为 MYSQL [英] XML to MYSQL Using Python

问题描述

我有以下 test.xml

I have the folowing test.xml

<root>
<parent>
    <ID>1</ID>
    <child1>Value1</child1>
    <child2>value11</child2>
    <child3>
       <subchild>value111</subchild>
    </child3>
</parent>
<parent>
    <ID>2</ID>
    <child1>value2</child1>
    <child2>value22</child2>
    <child2>value333</child2>
</parent>
<parent>
    <ID>3</ID>
    <child1>value3</child1>
    <child2>value33</child2>
</parent>
<parent>
    <ID>4</ID>
    <child1>value4</child1>
    <child2>value44</child2>
</parent>
</root>

我想要完成的是以下内容:我想遍历 test.xml 并且对于每个父节点，我想将所有子节点放在字典中，其中标签是索引，文本是值和一旦我到达父级的末尾，将其添加到数据库并重置字典并移至下一个父级.

What Im trying to accomplish is the following: I want to iterate through the test.xml and for every parent I want to put all of the child nodes in a dictionary where the tag is the index and the text is the value and once i get to the end of the parent add that to the database and reset the dictionary and move onto the next parent.

所以对于我想要的第一个父母

So for the first parent I would want

    insert = {'ID':1,'child1':'value1','child2':'value11','subchild':'value111'}

在 SQL 查询中使用它，然后移动到下一个父级重置字典并执行相同的操作.不是每个父母都有相同数量的孩子，有些孩子有子孩子.

Use it in an SQL query, And then move onto the next parent reset the dictionary and do the same thing. Not every parent has the same amount of children, and some children have sub children.

我尝试过:

    value = []
    tag = []

    from elementtree import ElementTree as ET
    for parent in tree.getiterator():
        for child in parent:
             value.append(child.text)
             tag.append(child.tag)

但我不知道如何获得我想要的结果.为了使帖子尽可能简单，我省略了检索和打开 xml.这是我尝试使用的方法，但我认为它不是正确的方法，因为我无法在父标记的末尾停止迭代以插入.

But I couldn't figure out how to get my desired results. I left out retrieving and opening the xml in order to keep the post as simple as possible. This is the method I was attempting to use but I don't think its the right one because I haven't been able to stop the iteration at the end of the parent tag in order to insert.

任何帮助将不胜感激！谢谢

Any help would be greatly appreciated! thanks

推荐答案

尝试使用 lxml 图书馆:

from lxml import etree

source = """
<root>
<parent>
    <ID>1</ID>
    <child1>Value1</child1>
    <child2>value11</child2>
    <child3>
       <subchild>value111</subchild>
    </child3>
</parent>
<parent>
    <ID>2</ID>
    <child1>value2</child1>
    <child2>value22</child2>
    <child2>value333</child2>
</parent>
<parent>
    <ID>3</ID>
    <child1>value3</child1>
    <child2>value33</child2>
</parent>
<parent>
    <ID>4</ID>
    <child1>value4</child1>
    <child2>value44</child2>
</parent>
</root>
"""

document = etree.fromstring(source)
inserts = []

id_number = 3

for parent in document.findall('parent'):
    insert = {}
    cont = 0
    for element in parent.iterdescendants():
        if element.tag == 'ID':
            if element.text == str(id_number):
                cont = 1
        if element.getchildren() == []:
            insert[element.tag] = element.text
    if cont:
        inserts.append(insert)

print inserts

这篇关于使用 Python 将 XML 转换为 MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！