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	teichmann	231	#!/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: