XML from SQL result

[Gabriel Genellina]

En Wed, 27 Jun 2007 04:25:16 -0300, Marcin Stępnicki  
<mstepnicki at gmail.com> escribió:

> I've skimmed through many Python&XML related books/articles but I am
> unable to find anything that is similar to my problem - but it seems to
> me that it should be common.
>
> Anyway: I've got the SQL query which returns:
>
> col1 | col2 | col3
> -----+------+-----
>   a  | a10  | b20
>   a  | a10  | b30
>   a  | a20  | b30
>
> I need to generate the following:
>
> <tag1 "col1"="a">
> 	<tag2 "col2"="a10">
> 		<tag3 "col3"="b20" />
> 		<tag3 "col3"="b30" />
> 	</tag2>
>
> 	<tag2 "col2"="a20">
> 		<tag3 "col3"="b30" />
> 	</tag2>
> </tag1>

That looks like a conventional report with totals and subtotals: while  
col1 is still the same, keep accumulating col2. While col1 and col2 are  
still the same, keep accumulating col3. Here, accumulating means "append  
item to current element".
If the number of columns is known in advance, you can nest some groupby  
calls:

 from itertools import groupby
def generate(rows):
   root = Element("xml")
   for col1,rows in groupby(rows, lambda row: row[0]):
     tag1 = SubElement(root, "tag1", col1=col1)
     for col2,rows in groupby(rows, lambda row: row[1]):
       tag2 = SubElement(tag1, "tag2", col2=col2)
       for col3,rows in groupby(rows, lambda row: row[2]):
         tag3 = SubElement(tag2, "tag3", col3=col3)
   return root
print tostring(generate(rows))

> Using Elementree it's not a problem when returned data is "flat", not
> hierarchical. However, that's not the case.

Uhm - ElementTree is designed precisely for a hierarchical structure (a  
"tree" :) )

-- 
Gabriel Genellina