tools/anonym/xmlexport.py

#!/usr/bin/env python
#
# (c) 2008 by Intevation GmbH
# author: Sascha L. Teichmann ([email protected])
#
import sys
#import os
import codecs
#import getopt
import psycopg2        as dbapi
import psycopg2.extras as extras
from   types import StringTypes

dbapi.extensions.register_type(dbapi.extensions.UNICODE)

DBNAME   = "ka_inte00002_db"
HOST     = "laodameia"
PORT     = 5434
USER     = "postgres"
PASSWORD = ""

SELECT_MASTER_IDS = \
"""SELECT id from master_tbl_view"""

SELECT_MASTER_TBL = \
"""SELECT * FROM master_tbl_view WHERE id = %(id)s"""

RG_VIEWS = (
    "kompetenzfestellung",
    "angebote_berufliche_qualifizierung",
    "angebote_berufsvorbereitung",
    "angebote_bildenden_bereich",
    "angebote_lebensbewaeltigung")

SELECT_RG = \
"""SELECT * FROM %s WHERE master_id = %d"""

BLACK_LIST = set(['id', 'master_id', 'bearbeiter_id'])

def escape(value, tagname):
    if tagname == 'uuid_id':
        return "%s" % value
    if type(value) in StringTypes:
        value = value.replace(']]>', '')
        return "<![CDATA[%s]]>" % value
    if value is None:
        return ""
    return "%s" % value

def dumpColums(columns):
    out = []
    for tagname, value in columns:
        if tagname not in BLACK_LIST and not value is None:
            out.append("<%s>%s</%s>" % (tagname, escape(value, tagname), tagname))
    return out

class Case:

    def __init__(self, master_id):
        self.master_id = int(master_id)
        self.columns   = None
        self.rgs       = []

    def fetchData(self, cur):
        self.fetchMasterTable(cur)
        self.fetchRepeatGroups(cur)

    def fetchMasterTable(self, cur):
        cur.execute(SELECT_MASTER_TBL, { 'id': self.master_id })
        row = cur.fetchone()
        if not row: raise Exception("No dataset for id %d" % self.master_id)
        columns = row._index.items() # XXX: hackish!
        columns.sort(lambda a, b: cmp(a[1], b[1]))
        columns = [(a[0], row[a[1]]) for a in columns]
        self.columns = columns

    def fetchRepeatGroups(self, cur):
        for view in RG_VIEWS:
            viewname = "rg_%s_tbl_view" % view
            cur.execute(SELECT_RG % (viewname, self.master_id))
            rs = []
            while True:
                row = cur.fetchone()
                if not row: break
                cs = row._index.items() # XXX: hackish!
                cs.sort(lambda a, b: cmp(a[1], b[1]))
                cs = [(a[0], row[a[1]]) for a in cs]
                rs.append(cs)
            if rs:
                self.rgs.append((view, rs))


    def dumpMasterTable(self, out):
        fields = dumpColums(self.columns)
        if fields:
            out.write("<master>%s</master>" % "".join(fields))

    def dumpRepeatGroups(self, out):
        for view, rgs in self.rgs:
            for r in rgs:
                fields = dumpColums(r)
                if fields:
                    out.write("<%s>%s</%s>" % (view, "".join(fields), view))

    def dumpData(self, out):
        out.write("<case>")
        self.dumpMasterTable(out)
        self.dumpRepeatGroups(out)
        out.write("</case>")

def main():
    Writer = codecs.getwriter("utf-8")
    out = Writer(sys.stdout)
    con, cur = None, None
    try:
        PASSWORD = raw_input()
        con = dbapi.connect(
            database = DBNAME,
            host     = HOST,
            port     = PORT,
            user     = USER,
            password = PASSWORD)

        cur = con.cursor(cursor_factory=extras.DictCursor)

        cases = []

        cur.execute(SELECT_MASTER_IDS)

        while True:
            row = cur.fetchone()
            if not row: break
            cases.append(Case(row[0]))

        out.write('<?xml version="1.0" encoding="UTF-8"?>\n')
        out.write("<cases>")
        for case in cases:
            case.fetchData(cur)
            case.dumpData(out)
        out.write("</cases>")

    finally:
        if cur:
            try: cur.close()
            except: pass
        if con:
            try: con.close()
            except: pass


if __name__ == "__main__":
    main()

# vim:set ts=4 sw=4 si et sta sts=4:
1	#!/usr/bin/env python
2	#
3	# (c) 2008 by Intevation GmbH
4	# author: Sascha L. Teichmann ([email protected])
5	#
6	import sys
7	#import os
8	import codecs
9	#import getopt
10	import psycopg2 as dbapi
11	import psycopg2.extras as extras
12	from types import StringTypes
13
14	dbapi.extensions.register_type(dbapi.extensions.UNICODE)
15
16	DBNAME = "ka_inte00002_db"
17	HOST = "laodameia"
18	PORT = 5434
19	USER = "postgres"
20	PASSWORD = ""
21
22	SELECT_MASTER_IDS = \
23	"""SELECT id from master_tbl_view"""
24
25	SELECT_MASTER_TBL = \
26	"""SELECT * FROM master_tbl_view WHERE id = %(id)s"""
27
28	RG_VIEWS = (
29	"kompetenzfestellung",
30	"angebote_berufliche_qualifizierung",
31	"angebote_berufsvorbereitung",
32	"angebote_bildenden_bereich",
33	"angebote_lebensbewaeltigung")
34
35	SELECT_RG = \
36	"""SELECT * FROM %s WHERE master_id = %d"""
37
38	BLACK_LIST = set(['id', 'master_id', 'bearbeiter_id'])
39
40	def escape(value, tagname):
41	if tagname == 'uuid_id':
42	return "%s" % value
43	if type(value) in StringTypes:
44	value = value.replace(']]>', '')
45	return "<![CDATA[%s]]>" % value
46	if value is None:
47	return ""
48	return "%s" % value
49
50	def dumpColums(columns):
51	out = []
52	for tagname, value in columns:
53	if tagname not in BLACK_LIST and not value is None:
54	out.append("<%s>%s</%s>" % (tagname, escape(value, tagname), tagname))
55	return out
56
57	class Case:
58
59	def __init__(self, master_id):
60	self.master_id = int(master_id)
61	self.columns = None
62	self.rgs = []
63
64	def fetchData(self, cur):
65	self.fetchMasterTable(cur)
66	self.fetchRepeatGroups(cur)
67
68	def fetchMasterTable(self, cur):
69	cur.execute(SELECT_MASTER_TBL, { 'id': self.master_id })
70	row = cur.fetchone()
71	if not row: raise Exception("No dataset for id %d" % self.master_id)
72	columns = row._index.items() # XXX: hackish!
73	columns.sort(lambda a, b: cmp(a[1], b[1]))
74	columns = [(a[0], row[a[1]]) for a in columns]
75	self.columns = columns
76
77	def fetchRepeatGroups(self, cur):
78	for view in RG_VIEWS:
79	viewname = "rg_%s_tbl_view" % view
80	cur.execute(SELECT_RG % (viewname, self.master_id))
81	rs = []
82	while True:
83	row = cur.fetchone()
84	if not row: break
85	cs = row._index.items() # XXX: hackish!
86	cs.sort(lambda a, b: cmp(a[1], b[1]))
87	cs = [(a[0], row[a[1]]) for a in cs]
88	rs.append(cs)
89	if rs:
90	self.rgs.append((view, rs))
91
92
93	def dumpMasterTable(self, out):
94	fields = dumpColums(self.columns)
95	if fields:
96	out.write("<master>%s</master>" % "".join(fields))
97
98	def dumpRepeatGroups(self, out):
99	for view, rgs in self.rgs:
100	for r in rgs:
101	fields = dumpColums(r)
102	if fields:
103	out.write("<%s>%s</%s>" % (view, "".join(fields), view))
104
105	def dumpData(self, out):
106	out.write("<case>")
107	self.dumpMasterTable(out)
108	self.dumpRepeatGroups(out)
109	out.write("</case>")
110
111	def main():
112	Writer = codecs.getwriter("utf-8")
113	out = Writer(sys.stdout)
114	con, cur = None, None
115	try:
116	PASSWORD = raw_input()
117	con = dbapi.connect(
118	database = DBNAME,
119	host = HOST,
120	port = PORT,
121	user = USER,
122	password = PASSWORD)
123
124	cur = con.cursor(cursor_factory=extras.DictCursor)
125
126	cases = []
127
128	cur.execute(SELECT_MASTER_IDS)
129
130	while True:
131	row = cur.fetchone()
132	if not row: break
133	cases.append(Case(row[0]))
134
135	out.write('<?xml version="1.0" encoding="UTF-8"?>\n')
136	out.write("<cases>")
137	for case in cases:
138	case.fetchData(cur)
139	case.dumpData(out)
140	out.write("</cases>")
141
142	finally:
143	if cur:
144	try: cur.close()
145	except: pass
146	if con:
147	try: con.close()
148	except: pass
149
150
151	if __name__ == "__main__":
152	main()
153
154	# vim:set ts=4 sw=4 si et sta sts=4: