python - XML特定行配对python

Question

我有一个 XML 文件示例，我必须构建类似的 xml 文件。

<datasource formatted-name='federated.1819qwi0hys5391dzxhl70o95li4' inline='true' source-platform='win' version='18.1' xmlns:user='http://www.tableausoftware.com/xml/user'>
  <connection class='federated'>
    <named-connections>
      <named-connection caption='Sample - Superstore' name='excel.1ew9u4t0tggb9315darmm0nfz2kb'>
        <connection class='excel' driver='' filename='C:/Users/XXXX/Downloads/Sample - Superstore.xls' header='yes' imex='1' password='' server='' />
      </named-connection>
    </named-connections>
<relation connection='excel.1ew9u4t0tggb9315darmm0nfz2kb' name='Custom SQL Query' type='text'>SELECT [Orders$].[Category] AS [Category],&#13;&#10;[Orders$].[City] AS [City],&#13;&#10</relation>

我必须阅读关系连接sql查询并动态写入如下xml输出。

     <metadata-record class='column'>
        <remote-name>Category</remote-name>
        <remote-type>130</remote-type>
        <local-name>[Category]</local-name>
        <parent-name>[Custom SQL Query]</parent-name>
     </metadata-record>

      <metadata-record class='column'>
        <remote-name>City</remote-name>
        <remote-type>130</remote-type>
        <local-name>[City]</local-name>
        <parent-name>[Custom SQL Query]</parent-name>
     </metadata-record>

我已经建立了可以硬编码的关系连接，但是元数据记录应该通过读取关系连接中的 sql 查询来动态构建。那是期望。粘贴下面的完整代码

import xml.etree.cElementTree as et
import pandas as pd
colAtt[]

datasource = et.Element("datasource")
datasource.set("formatted-name",'federated.1819qwi0hys5391dzxhl70o95li4')
datasource.set("inline",'True')
datasource.set("source-platform",'win')
datasource.set("version",'18.1')
datasource.set("xmlns:user",'http://www.tableausoftware.com/xml/user')
connection = et.SubElement(datasource, "Connection")
connection.set("Class", "federated")
namedconnections = et.SubElement(connection, "named-connections")
namedconnection = et.SubElement(namedconnections, "namedconnection") 
namedconnection.set("caption", 'Sample - Superstore')
namedconnection.set("name", 'excel.1ew9u4t0tggb9315darmm0nfz2kb')
connection=et.SubElement(namedconnection, "connection")
connection.set("class", 'excel')
connection.set("driver", ' ')
connection.set("filename", 'C:/Users/XXXX/Downloads/Sample - Superstore.xls')
connection.set("header", 'yes')
connection.set("imex", '1')
connection.set("password", ' ')
connection.set("server", ' ')

relationconnection=et.SubElement(datasource, "relation")
relationconnection.set('Connection','excel.1ew9u4t0tggb9315darmm0nfz2kb')
relationconnection.set('name ','Custom SQL Query')
relationconnection.set('type','text')
relationconnection.text='SELECT [Orders$].[Category] AS [Category],&#13,&#10,  [Orders$].[City] AS [City],&#13,&#10
  
metadatarecords = et.SubElement(datasource, "metadata-records")
for x in datasource.findall('relation'):
         #print(x.tag,x.attrib,x.text)
         colAtt.append(x.text)

.

但它附加了整个文本，我不知道如何分别拆分类别和城市。有人可以帮忙吗？

标签： pythonxml