In <[EMAIL PROTECTED]>, Marcin Stępnicki wrote:
> 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>
>
> Using Elementree it's not a problem when returned data is "flat", not
> hierarchical. However, that's not the case.
>
> Questions:
>
> 1) (I'll ask that on PostgreSQL group too) I use PostgreSQL. I understand
> that in order to achieve what I want directly from RDBMS I need SQL/XML
> language support which will be included in 8.3 which is not yet available.
That question sounds more like a statement. :-)
> 3) I can generate series of select queries based on previous queries, but
> I can't imagine there isn't a better solution.
If the data can be queried sorted like shown above, should be possible to
iterate over it and use `itertools.groupby` and
`elementtree.SimpleXMLWriter.XMLWriter` to generate the XML on the fly:
import sys
from itertools import groupby
from operator import itemgetter
from elementtree.SimpleXMLWriter import XMLWriter
first, second, third = map(itemgetter, xrange(3))
def main():
data = (('a', 'a10', 'b20'),
('a', 'a10', 'b30'),
('a', 'a20', 'b30'))
writer = XMLWriter(sys.stdout)
writer.start('document')
for value, rows in groupby(data, first):
writer.start('tag1', col1=value)
for value, rows in groupby(rows, second):
writer.start('tag2', col2=value)
for value in imap(third, rows):
writer.element('tag3', col3=value)
writer.end('tag2')
writer.end('tag1')
writer.end('document')
Ciao,
Marc 'BlackJack' Rintsch
--
http://mail.python.org/mailman/listinfo/python-list
