XML from SQL result

[Stefan Behnel]

Marcin Stępnicki wrote:
> 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>  

I'd use a two-step approach here. Collect the data in a dict of dicts, then
write it out into XML.

Something like this:

  c1d = {}
  for c1, c2, c3 in rows:
      c2d = c1d.get(c1)
      if c2d is None:
          c2d = c1d[c1] = {}
      c3_list = c2d.get(c2)
      if c3_list is None:
          c3d = c2d[c2] = []
      c3_list.append(c3)

  root = ET.Element("root") # in case "col1" has more than one value
  for c1, c2d in c1d.iteritems():
      el_c1 = ET.SubElement(root, "tag1", col1=c1)
      for c2, c3_list in c2d.iteritems():
          el_c2 = ET.SubElement(el_c1, "tag2", col2=c2)
          for c3 in c3_list:
              ET.SubElement(el_c2, "tag3", col3=c3)

Stefan