Annotation of ZSQLExtend/importFMPXML.py, revision 1.6
1.1 casties 1: #!/usr/local/bin/python
2: #
3:
4: import string
5: import logging
6: import sys
7:
1.5 casties 8: from xml import sax
9: from amara import saxtools
10:
1.2 casties 11: try:
12: import psycopg2 as psycopg
13: psyco = 2
14: except:
15: import psycopg
16: psyco = 1
17:
1.5 casties 18: fm_ns = 'http://www.filemaker.com/fmpxmlresult'
1.1 casties 19:
20: def getTextFromNode(nodename):
21: """get the cdata content of a node"""
22: if nodename is None:
23: return ""
24: nodelist=nodename.childNodes
25: rc = ""
26: for node in nodelist:
27: if node.nodeType == node.TEXT_NODE:
28: rc = rc + node.data
29: return rc
30:
31: def sql_quote(v):
32: # quote dictionary
33: quote_dict = {"\'": "''", "\\": "\\\\"}
34: for dkey in quote_dict.keys():
35: if string.find(v, dkey) >= 0:
36: v=string.join(string.split(v,dkey),quote_dict[dkey])
1.5 casties 37: return "'%s'"%v
1.1 casties 38:
39: def SimpleSearch(curs,query, args=None):
40: """execute sql query and return data"""
41: logging.debug("executing: "+query)
1.2 casties 42: if psyco == 1:
43: query = query.encode("UTF-8")
1.6 ! casties 44: #if args is not None:
! 45: # args = [ sql_quote(a) for a in args ]
1.1 casties 46: curs.execute(query, args)
47: logging.debug("sql done")
1.4 casties 48: try:
49: return curs.fetchall()
50: except:
51: return None
1.1 casties 52:
53:
1.5 casties 54:
55: class xml_handler:
1.1 casties 56:
1.5 casties 57: def __init__(self,dsn,table,update_fields=None,id_field=None,sync_mode=False):
58: '''
59: SAX handler to import FileMaker XML file (FMPXMLRESULT format) into the table.
60: @param dsn: database connection string
61: @param table: name of the table the xml shall be imported into
62: @param filename: xmlfile filename
63: @param update_fields: (optional) list of fields to update; default is to create all fields
64: @param id_field: (optional) field which uniquely identifies an entry for updating purposes.
65: @param sync_mode: (optional) really synchronise, i.e. delete entries not in XML file
66: '''
67: # set up parser
68: self.event = None
69: self.top_dispatcher = {
70: (saxtools.START_ELEMENT, fm_ns, u'METADATA'):
71: self.handle_meta_fields,
72: (saxtools.START_ELEMENT, fm_ns, u'RESULTSET'):
73: self.handle_data,
74: }
75:
76: # connect database
77: self.dbCon = psycopg.connect(dsn)
78: self.db = self.dbCon.cursor()
79: assert self.db, "AIIEE no db cursor for %s!!"%dsn
1.1 casties 80:
1.5 casties 81: logging.debug("dsn: "+repr(dsn))
82: logging.debug("table: "+repr(table))
83: logging.debug("update_fields: "+repr(update_fields))
84: logging.debug("id_field: "+repr(id_field))
85: logging.debug("sync_mode: "+repr(sync_mode))
86:
87: self.table = table
88: self.update_fields = update_fields
89: self.id_field = id_field
90: self.sync_mode = sync_mode
91:
92: self.dbIDs = {}
93: self.rowcnt = 0
94:
95: if id_field is not None:
96: # prepare a list of ids for sync mode
97: qstr="select %s from %s"%(id_field,table)
98: for id in SimpleSearch(self.db, qstr):
99: # value 0: not updated
100: self.dbIDs[id[0]] = 0;
101: self.rowcnt += 1
102:
103: logging.info("%d entries in DB to sync"%self.rowcnt)
104:
105: self.fieldNames = []
106:
107: return
108:
109: def handle_meta_fields(self, end_condition):
110: dispatcher = {
111: (saxtools.START_ELEMENT, fm_ns, u'FIELD'):
112: self.handle_meta_field,
113: }
114: #First round through the generator corresponds to the
115: #start element event
116: logging.debug("START METADATA")
117: yield None
1.1 casties 118:
1.5 casties 119: #delegate is a generator that handles all the events "within"
120: #this element
121: delegate = None
122: while not self.event == end_condition:
123: delegate = saxtools.tenorsax.event_loop_body(
124: dispatcher, delegate, self.event)
125: yield None
126:
127: #Element closed. Wrap up
128: logging.debug("END METADATA")
129: if self.update_fields is None:
130: # update all fields
131: self.update_fields = self.fieldNames
132:
133: logging.debug("xml-fieldnames:"+repr(self.fieldNames))
134: # get list of fields in db table
135: qstr="""select attname from pg_attribute, pg_class where attrelid = pg_class.oid and relname = '%s'"""
136: columns=[x[0] for x in SimpleSearch(self.db, qstr%self.table)]
137:
138: # adjust db table to fields in XML and fieldlist
139: for fieldName in self.fieldNames:
140: logging.debug("db-fieldname:"+repr(fieldName))
141: if (fieldName not in columns) and (fieldName in self.update_fields):
142: qstr="alter table %s add %s %s"%(self.table,fieldName,'text')
143: logging.info("db add field:"+qstr)
144: self.db.execute(qstr)
145: self.dbCon.commit()
146:
147: # prepare sql statements for update
148: setStr=string.join(["%s = %%s"%f for f in self.update_fields], ', ')
149: self.updQuery="UPDATE %s SET %s WHERE %s = %%s"%(self.table,setStr,self.id_field)
150: # and insert
151: fields=string.join(self.update_fields, ',')
152: values=string.join(['%s' for f in self.update_fields], ',')
153: self.addQuery="INSERT INTO %s (%s) VALUES (%s)"%(self.table,fields,values)
154: #print "upQ: ", self.updQuery
155: #print "adQ: ", self.addQuery
156:
157: return
158:
159: def handle_meta_field(self, end_condition):
160: name = self.params.get((None, u'NAME'))
161: yield None
162: #Element closed. Wrap up
163: self.fieldNames.append(name)
164: logging.debug("FIELD name: "+name)
165: return
166:
167: def handle_data(self, end_condition):
168: dispatcher = {
169: (saxtools.START_ELEMENT, fm_ns, u'ROW'):
170: self.handle_row,
171: }
172: #First round through the generator corresponds to the
173: #start element event
174: logging.debug("START RESULTSET")
175: self.rowcnt = 0
176: yield None
1.1 casties 177:
1.5 casties 178: #delegate is a generator that handles all the events "within"
179: #this element
180: delegate = None
181: while not self.event == end_condition:
182: delegate = saxtools.tenorsax.event_loop_body(
183: dispatcher, delegate, self.event)
184: yield None
185:
186: #Element closed. Wrap up
187: logging.debug("END RESULTSET")
188: self.dbCon.commit()
1.1 casties 189:
1.5 casties 190: if self.sync_mode:
191: # delete unmatched entries in db
1.6 ! casties 192: delQuery = "DELETE FROM %s WHERE %s = %%s"%(self.table,self.id_field)
1.5 casties 193: for id in self.dbIDs.keys():
194: # find all not-updated fields
195: if self.dbIDs[id] == 0:
196: logging.info(" delete:"+id)
1.6 ! casties 197: SimpleSearch(self.db, delQuery, [id])
! 198: sys.exit(1)
1.1 casties 199:
1.5 casties 200: elif self.dbIDs[id] > 1:
201: logging.info(" sync:"+"id used more than once?"+id)
1.1 casties 202:
1.5 casties 203: self.dbCon.commit()
1.1 casties 204:
1.5 casties 205: return
206:
207: def handle_row(self, end_condition):
208: dispatcher = {
209: (saxtools.START_ELEMENT, fm_ns, u'COL'):
210: self.handle_col,
211: }
212: logging.debug("START ROW")
213: self.dataSet = {}
214: self.colIdx = 0
215: yield None
1.1 casties 216:
1.5 casties 217: #delegate is a generator that handles all the events "within"
218: #this element
219: delegate = None
220: while not self.event == end_condition:
221: delegate = saxtools.tenorsax.event_loop_body(
222: dispatcher, delegate, self.event)
223: yield None
224:
225: #Element closed. Wrap up
226: logging.debug("END ROW")
227: self.rowcnt += 1
228: # process collected row data
229: update=False
230: id_val=''
231: # synchronize by id_field
232: if self.id_field:
233: id_val=self.dataSet[self.id_field]
234: if id_val in self.dbIDs:
235: self.dbIDs[id_val] += 1
236: update=True
237:
238: if update:
239: # update existing row (by id_field)
240: #setvals=[]
241: #for fieldName in self.update_fields:
242: # setvals.append("%s = %s"%(fieldName,sql_quote(self.dataSet[fieldName])))
243: #setStr=string.join(setvals, ',')
244: id_val=self.dataSet[self.id_field]
245: #qstr="UPDATE %s SET %s WHERE %s = '%s' "%(self.table,setStr,self.id_field,id_val)
246: args = [self.dataSet[f] for f in self.update_fields]
247: args.append(id_val)
248: SimpleSearch(self.db, self.updQuery, args)
249: logging.debug("update: %s"%id_val)
250: else:
251: # create new row
252: #fields=string.join(update_fields, ',')
253: #values=string.join([" %s "%sql_quote(self.dataSet[x]) for x in self.update_fields], ',')
254: #qstr="INSERT INTO %s (%s) VALUES (%s)"%(self.table,fields,self.values)
255: args = [self.dataSet[f] for f in self.update_fields]
256: SimpleSearch(self.db, self.addQuery, args)
1.6 ! casties 257: logging.debug("add: %s"%self.dataSet.get(self.id_field, self.rowcnt))
1.5 casties 258:
259: #logging.info(" row:"+"%d (%s)"%(self.rowcnt,id_val))
260: if (self.rowcnt % 10) == 0:
261: logging.info(" row:"+"%d (%s)"%(self.rowcnt,id_val))
262: self.dbCon.commit()
263:
264: return
265:
266: def handle_col(self, end_condition):
267: dispatcher = {
268: (saxtools.START_ELEMENT, fm_ns, u'DATA'):
269: self.handle_data_tag,
270: }
271: #print "START COL"
272: yield None
273: #delegate is a generator that handles all the events "within"
274: #this element
275: delegate = None
276: while not self.event == end_condition:
277: delegate = saxtools.tenorsax.event_loop_body(
278: dispatcher, delegate, self.event)
279: yield None
280: #Element closed. Wrap up
281: #print "END COL"
282: self.colIdx += 1
283: return
284:
285: def handle_data_tag(self, end_condition):
286: #print "START DATA"
287: content = u''
288: yield None
289: # gather child elements
290: while not self.event == end_condition:
291: if self.event[0] == saxtools.CHARACTER_DATA:
292: content += self.params
293: yield None
294: #Element closed. Wrap up
295: field = self.fieldNames[self.colIdx]
296: self.dataSet[field] = content
297: #print " DATA(", field, ") ", repr(content)
298: return
299:
300:
301:
302:
1.1 casties 303:
304: ##
305: ## public static int main()
306: ##
307:
308: from optparse import OptionParser
309:
310: opars = OptionParser()
311: opars.add_option("-f", "--file",
312: dest="filename",
313: help="FMPXML file name", metavar="FILE")
314: opars.add_option("-c", "--dsn",
315: dest="dsn",
316: help="database connection string")
317: opars.add_option("-t", "--table",
318: dest="table",
319: help="database table name")
320: opars.add_option("--fields", default=None,
321: dest="update_fields",
322: help="list of fields to update (comma separated)", metavar="LIST")
323: opars.add_option("--id-field", default=None,
324: dest="id_field",
1.5 casties 325: help="name of id field for synchronisation (only appends data otherwise)", metavar="NAME")
1.1 casties 326: opars.add_option("--sync-mode", default=False, action="store_true",
327: dest="sync_mode",
1.5 casties 328: help="do full sync based on id field (remove unmatched fields from db)")
1.1 casties 329: opars.add_option("-d", "--debug", default=False, action="store_true",
330: dest="debug",
331: help="debug mode (more output)")
332:
333: (options, args) = opars.parse_args()
334:
335: if len(sys.argv) < 2 or options.filename is None or options.dsn is None:
336: opars.print_help()
337: sys.exit(1)
338:
339: if options.debug:
340: loglevel = logging.DEBUG
341: else:
342: loglevel = logging.INFO
343:
344: logging.basicConfig(level=loglevel,
345: format='%(asctime)s %(levelname)s %(message)s',
346: datefmt='%H:%M:%S')
347:
348: update_fields = None
349:
350: if options.update_fields:
351: update_fields = [string.strip(s) for s in options.update_fields.split(',')]
1.5 casties 352:
353: parser = sax.make_parser()
354: #The "consumer" is our own handler
355: consumer = xml_handler(dsn=options.dsn,table=options.table,
356: update_fields=update_fields,id_field=options.id_field,
357: sync_mode=options.sync_mode)
358: #Initialize Tenorsax with handler
359: handler = saxtools.tenorsax(consumer)
360: #Resulting tenorsax instance is the SAX handler
361: parser.setContentHandler(handler)
362: parser.setFeature(sax.handler.feature_namespaces, 1)
363: parser.parse(options.filename)
364:
1.1 casties 365:
366: print "DONE!"
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/cvs/cvsweb/icons/back.gif){kind=icon}
Return to importFMPXML.py CVS log ![[TXT]](/cvs/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvs/cvsweb/icons/dir.gif){kind=icon}
Up to [Repository] / ZSQLExtend