Databases
StackOverflow2013
Postsdbo
POParsing Nested XML Data in Python Using ElementTree
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POParsing Nested XML Data in Python Using ElementTree
    primarykey
    data
    text
    <h2>Updates: Towards a Solution</h2> <p>This code:</p> <pre><code>tree = ET.parse(assetsfilename) root = tree.getroot() assets = {} def find_rows(rowset, container): for row in rowset.findall("row"): singleton = int((row.get('singleton'))) flag = int((row.get('flag'))) quantity = int((row.get('quantity'))) typeID = int((row.get('typeID'))) locationID = int((row.get('locationID', '0'))) itemID = int((row.get('itemID'))) dkey = (singleton, flag, quantity, typeID, locationID, itemID) container[dkey] = {} child_rowset = row.find("rowset") if child_rowset is not None: find_rows(child_rowset, container[dkey]) first_rowset = root.find('.//rowset[@name="assets"]') find_rows(first_rowset, assets) #print singleton, flag, quantity, typeID, locationID, itemID pp = pprint.PrettyPrinter(indent=4) pp.pprint(assets) </code></pre> <p>Gives this output:</p> <pre><code>{ (0, 4, 1, 3317, 61000419, 1000913922710L): { }, (0, 4, 1, 6159, 60003463, 1007025519384L): { }, (0, 4, 1, 7669, 60000361, 1007215573625L): { }, (0, 4, 1, 23566, 61000419, 1000992661686L): { }, (1, 4, 1, 51, 60001345, 1004073218074L): { }, (1, 4, 1, 51, 60001345, 1004073218075L): { }, (1, 4, 1, 596, 60003337, 1007908184113L): { (0, 5, 1, 34, 0, 1007908184132 L): { }, (1, 27, 1, 3634, 0, 1007908184 129L): { }, (1, 28, 1, 3651, 0, 1007908184 130L): { }}, (1, 4, 1, 3766, 61000419, 1000973178550L): { (0, 5, 25, 16273, 0, 10009731 88870L): { }, (1, 27, 1, 21096, 0, 10006872 93796L): { }}} </code></pre> <p>This basically adds a nested dict to the end of the dict I already had and fills it with the data from the children, if present. Ideally, though, both the parent and the children data would be in the main dict and the extra field at the end of the dict would contain the itemID of the parent (if that row is a child row) or be empty (if that item is a parent row or a row that doesn't have any children.)</p> <h2>The Question</h2> <p>I am trying to read in the data from a nested .xml file into some sort of dictionary so that I can output it in other formats (my current goal is sqlite3 and an sqlite .db file, but this isn't the point of my question.) I can read all of the primary level of the data but I can't figure out how to also read in the nested data (if present.)</p> <h2>The Data</h2> <p>Here is a sample .xml file:</p> <pre><code>&lt;?xml version='1.0' encoding='UTF-8'?&gt; &lt;eveapi version="2"&gt; &lt;currentTime&gt;2012-11-14 03:26:35&lt;/currentTime&gt; &lt;result&gt; &lt;rowset name="assets" key="itemID" columns="itemID,locationID,typeID,quantity,flag,singleton"&gt; &lt;row itemID="1007215573625" locationID="60000361" typeID="7669" quantity="1" flag="4" singleton="0" /&gt; &lt;row itemID="1004073218074" locationID="60001345" typeID="51" quantity="1" flag="4" singleton="1" rawQuantity="-1" /&gt; &lt;row itemID="1004073218075" locationID="60001345" typeID="51" quantity="1" flag="4" singleton="1" rawQuantity="-1" /&gt; &lt;row itemID="1007908184113" locationID="60003337" typeID="596" quantity="1" flag="4" singleton="1" rawQuantity="-1"&gt; &lt;rowset name="contents" key="itemID" columns="itemID,typeID,quantity,flag,singleton"&gt; &lt;row itemID="1007908184129" typeID="3634" quantity="1" flag="27" singleton="1" rawQuantity="-1" /&gt; &lt;row itemID="1007908184130" typeID="3651" quantity="1" flag="28" singleton="1" rawQuantity="-1" /&gt; &lt;row itemID="1007908184132" typeID="34" quantity="1" flag="5" singleton="0" /&gt; &lt;/rowset&gt; &lt;/row&gt; &lt;row itemID="1007025519384" locationID="60003463" typeID="6159" quantity="1" flag="4" singleton="0" /&gt; &lt;row itemID="1000913922710" locationID="61000419" typeID="3317" quantity="1" flag="4" singleton="0" /&gt; &lt;row itemID="1000973178550" locationID="61000419" typeID="3766" quantity="1" flag="4" singleton="1" rawQuantity="-1"&gt; &lt;rowset name="contents" key="itemID" columns="itemID,typeID,quantity,flag,singleton"&gt; &lt;row itemID="1000687293796" typeID="21096" quantity="1" flag="27" singleton="1" rawQuantity="-1" /&gt; &lt;row itemID="1000973188870" typeID="16273" quantity="25" flag="5" singleton="0" /&gt; &lt;/rowset&gt; &lt;/row&gt; &lt;row itemID="1000992661686" locationID="61000419" typeID="23566" quantity="1" flag="4" singleton="0" /&gt; &lt;/rowset&gt; &lt;/result&gt; &lt;cachedUntil&gt;2012-11-14 07:05:29&lt;/cachedUntil&gt; &lt;/eveapi&gt; </code></pre> <p>Note how some items have children items nested under them but some don't and the number of children (if present) is not fixed (so one item can have 3 children and another 2 children while many others have no children at all.)</p> <p>(For those curious, this data comes from the full id key Asset List API pull from the online game called EVE Online.)</p> <h2>What I Can Get</h2> <p>I can get this code:</p> <pre><code>import xml.etree.ElementTree as ET tree = ET.parse(assetsfilename) root = tree.getroot() singleton = [] flag = [] quantity = [] typeID = [] locationID = [] itemID = [] assets = {} for row in root.findall(".//*[@name='assets']/row"): singleton.append (int((row.get('singleton')))) flag.append (int((row.get('flag')))) quantity.append (int((row.get('quantity')))) typeID.append (int((row.get('typeID')))) locationID.append (int((row.get('locationID')))) itemID.append (int((row.get('itemID')))) assets = zip(singleton, flag, quantity, typeID, locationID, itemID) print singleton, flag, quantity, typeID, locationID, itemID print assets </code></pre> <p>To output this on the screen:</p> <pre><code>[0, 1, 1, 1, 0, 0, 1, 0] [4, 4, 4, 4, 4, 4, 4, 4] [1, 1, 1, 1, 1, 1, 1, 1] [7669 , 51, 51, 596, 6159, 3317, 3766, 23566] [60000361, 60001345, 60001345, 60003337, 60003463, 61000419, 61000419, 61000419] [1007215573625L, 1004073218074L, 100407 3218075L, 1007908184113L, 1007025519384L, 1000913922710L, 1000973178550L, 100099 2661686L] [(0, 4, 1, 7669, 60000361, 1007215573625L), (1, 4, 1, 51, 60001345, 100407321807 4L), (1, 4, 1, 51, 60001345, 1004073218075L), (1, 4, 1, 596, 60003337, 100790818 4113L), (0, 4, 1, 6159, 60003463, 1007025519384L), (0, 4, 1, 3317, 61000419, 100 0913922710L), (1, 4, 1, 3766, 61000419, 1000973178550L), (0, 4, 1, 23566, 610004 19, 1000992661686L)] </code></pre> <p>Note how this is reading in all the main level lines that start <code>&lt;row itemID=</code> but it doesn't get the nested lines (which I would preferably like to also show somehow as being tied to the parent itemID above it.)</p> <h2>Desired Output</h2> <p>I'm somewhat open to suggestions here, but this is one option. I could have the main level rows parsed into a dict (as I already have) and then create another dict that contains the data from the sublevel rows and adds in an extra piece that notes which itemID it is a child of. Another option would be to add the data from the sub-level rows into the main dict that I can already make and just add in an extra field the is something like <code>Null</code> or <code>None</code> for items that don't have a parent and gives the parent's itemID for items that do have a parent.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USQanthelas
    1. USQanthelas
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POParsing Nested XML Data in Python Using ElementTree
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload