how to parse xml output of mysql in python

Question

I know there are several xml parsers for python, but I dont know which one would be good to parse outputs of mysql xml, I havent been successfully yet. The output looks like:

<resultset statement="select * from table where id > 5" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="name">first</field>
    <field name="login">2021-08-16 13:44:35</field>
  </row>

  <row>
    <field name="name">second</field>
    <field name="login">2021-08-18 13:44:35</field>
  </row>
</resultset>

because the structure is quite simple here, I come about to write my own parser, but I would guess there should be already something to cover this case?!

Output should be a list of dicts with columns as keys and the value as the content of the row/column

Take a look at ElementTree: docs.python.org/3/library/xml.etree.elementtree.html — mzjn
– mzjn, Commented Aug 24, 2021 at 9:14
@balderman a list with dicts like [{name:first,login:2021-08-16},{...}] — Asara
– Asara, Commented Aug 24, 2021 at 14:35

balderman · Accepted Answer · 2021-08-24 09:44:02Z

1

see below

import xml.etree.ElementTree as ET

xml = '''<resultset statement="select * from table where id > 5" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="name">first</field>
    <field name="login">2021-08-16 13:44:35</field>
  </row>

  <row>
    <field name="name">second</field>
    <field name="login">2021-08-18 13:44:35</field>
  </row>
</resultset>'''

data = []
root = ET.fromstring(xml)
for row in root.findall('.//row'):
    fields = []
    for field in row.findall('field'):
        fields.append((field.attrib['name'], field.text))
    data.append(fields)
print(data)

output

[[('name', 'first'), ('login', '2021-08-16 13:44:35')], [('name', 'second'), ('login', '2021-08-18 13:44:35')]]

answered Aug 24, 2021 at 9:44

balderman

24k8 gold badges39 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Asara Over a year ago

thanks, I changed the loop with

record = dict()         for field in row.findall('field'):             record[field.attrib['name']] = field.text         data.append(record)

to get a better output strucutre

Collectives™ on Stack Overflow

how to parse xml output of mysql in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related