1: #!/usr/local/bin/python
2: #
3:
4: import string
5: from xml.dom.pulldom import parse
6: import psycopg2 as psycopg
7: import logging
8: import sys
9:
10:
11: def getTextFromNode(nodename):
12: """get the cdata content of a node"""
13: if nodename is None:
14: return ""
15: nodelist=nodename.childNodes
16: rc = ""
17: for node in nodelist:
18: if node.nodeType == node.TEXT_NODE:
19: rc = rc + node.data
20: return rc
21:
22: def sql_quote(v):
23: # quote dictionary
24: quote_dict = {"\'": "''", "\\": "\\\\"}
25: for dkey in quote_dict.keys():
26: if string.find(v, dkey) >= 0:
27: v=string.join(string.split(v,dkey),quote_dict[dkey])
28: return "'%s'" % v
29:
30: def SimpleSearch(curs,query, args=None):
31: """execute sql query and return data"""
32: logging.debug("executing: "+query)
33: curs.execute(query, args)
34: logging.debug("sql done")
35: return curs.fetchall()
36:
37:
38: def importXMLFileFMP(dsn,table,filename,update_fields=None,id_field=None,sync_mode=False):
39: '''
40: Import FileMaker XML file (FMPXMLRESULT format) into the table.
41: @param table: name of the table the xml shall be imported into
42: @param filename: xmlfile filename
43: @param update_fields: (optional) list of fields to update; default is to create all fields
44: @param id_field: (optional) field which uniquely identifies an entry for updating purposes.
45: @param sync_mode: (optional) really synchronise, i.e. delete entries not in XML file
46: '''
47:
48: # connect database
49: dbCon = psycopg.connect(dsn)
50: db = dbCon.cursor()
51: assert db, "AIIEE no db cursor for %s!!"%dsn
52:
53: # read XML file
54: fh=file(filename)
55: logging.info("reading file "+filename)
56: doc=parse(fh)
57: logging.info("file read")
58:
59: dbIDs = {}
60: rowcnt = 0
61: ret = ""
62:
63: logging.debug("dsn: "+dsn)
64: logging.debug("table: "+table)
65: logging.debug("update_fields: "+repr(update_fields))
66: logging.debug("id_field: "+id_field)
67: logging.debug("sync_mode: "+repr(sync_mode))
68:
69: if id_field is not None:
70: # prepare a list of ids for sync mode
71: qstr="select %s from %s"%(id_field,table)
72: for id in SimpleSearch(db, qstr):
73: # value 0: not updated
74: dbIDs[id[0]] = 0;
75: rowcnt += 1
76:
77: logging.info("%d entries in DB to sync"%rowcnt)
78:
79: fieldNames = []
80: rowcnt = 0
81: id_val = ''
82:
83: while 1:
84: node=doc.getEvent()
85:
86: if node is None:
87: break;
88:
89: # METADATA tag defines number and names of fields in FMPXMLRESULT
90: if node[1].nodeName == 'METADATA':
91: doc.expandNode(node[1])
92:
93: names=node[1].getElementsByTagName('FIELD')
94:
95: for name in names:
96: fn = name.getAttribute('NAME')
97: fieldNames.append(fn)
98:
99: if update_fields is None:
100: # update all fields
101: update_fields = fieldNames
102:
103: logging.debug("fieldnames:"+repr(fieldNames))
104: # get list of fields in db table
105: qstr="""select attname from pg_attribute, pg_class where attrelid = pg_class.oid and relname = '%s'"""
106: columns=[x[0] for x in SimpleSearch(db, qstr%table)]
107:
108: # adjust db table to fields in XML and fieldlist
109: for fieldName in fieldNames:
110: logging.debug("db-fieldname:"+repr(fieldName))
111: if (fieldName not in columns) and (fieldName in update_fields):
112: qstr="alter table %s add %%s %%s"%table
113: logging.info("db add field:"+qstr%(fieldName,'text'))
114: db.execute(qstr, (fieldName,'text'))
115: db.commit()
116:
117:
118: # ROW tags (in RESULTSET tag) hold data
119: elif node[1].nodeName == 'ROW':
120: rowcnt += 1
121:
122: doc.expandNode(node[1])
123: cols=node[1].getElementsByTagName('COL')
124: dataSet={}
125: i = 0
126: # populate with data
127: for col in cols:
128: data=col.getElementsByTagName('DATA')
129: dataSet[fieldNames[i]] = getTextFromNode(data[0])
130: i += 1
131:
132: update=False
133:
134: # synchronize by id_field
135: if id_field:
136: id_val=dataSet[id_field]
137: if id_val in dbIDs:
138: dbIDs[id_val] += 1
139: update=True
140:
141: if update:
142: # update existing row (by id_field)
143: setvals=[]
144: for fieldName in update_fields:
145: setvals.append("%s = %s"%(fieldName,sql_quote(dataSet[fieldName])))
146: setStr=string.join(setvals, ',')
147: id_val=dataSet[id_field]
148: qstr="UPDATE %s SET %s WHERE %s = '%s' "%(table,setStr,id_field,id_val)
149: db.execute(qstr)
150: ret += "up: %s"%id_val
151: else:
152: # create new row
153: fields=string.join(update_fields, ',')
154: values=string.join([" %s "%sql_quote(dataSet[x]) for x in update_fields], ',')
155: qstr="INSERT INTO %s (%s) VALUES (%s)"%(table,fields,values)
156: db.execute(qstr)
157: ret += "ad: %s"%dataSet.get(id_field, rowcnt)
158:
159: #logging.info(" row:"+"%d (%s)"%(rowcnt,id_val))
160: if (rowcnt % 10) == 0:
161: logging.info(" row:"+"%d (%s)"%(rowcnt,id_val))
162: dbCon.commit()
163:
164: dbCon.commit()
165: if sync_mode:
166: # delete unmatched entries in db
167: for id in dbIDs.keys():
168: # find all not-updated fields
169: if dbIDs[id] == 0:
170: logging.info(" delete:"+id)
171: qstr = "DELETE FROM %s WHERE %%s = '%%s'"%table
172: db.execute(qstr, (id_field,id))
173:
174: elif dbIDs[id] > 1:
175: logging.info(" sync:"+"id used more than once?"+id)
176:
177: dbCon.commit()
178:
179: return ret
180:
181: ##
182: ## public static int main()
183: ##
184:
185: from optparse import OptionParser
186:
187: opars = OptionParser()
188: opars.add_option("-f", "--file",
189: dest="filename",
190: help="FMPXML file name", metavar="FILE")
191: opars.add_option("-c", "--dsn",
192: dest="dsn",
193: help="database connection string")
194: opars.add_option("-t", "--table",
195: dest="table",
196: help="database table name")
197: opars.add_option("--fields", default=None,
198: dest="update_fields",
199: help="list of fields to update (comma separated)", metavar="LIST")
200: opars.add_option("--id-field", default=None,
201: dest="id_field",
202: help="name of id field for synchronisation", metavar="NAME")
203: opars.add_option("--sync-mode", default=False, action="store_true",
204: dest="sync_mode",
205: help="do full sync (remove unmatched fields from db)")
206: opars.add_option("-d", "--debug", default=False, action="store_true",
207: dest="debug",
208: help="debug mode (more output)")
209:
210: (options, args) = opars.parse_args()
211:
212: if len(sys.argv) < 2 or options.filename is None or options.dsn is None:
213: opars.print_help()
214: sys.exit(1)
215:
216: if options.debug:
217: loglevel = logging.DEBUG
218: else:
219: loglevel = logging.INFO
220:
221: logging.basicConfig(level=loglevel,
222: format='%(asctime)s %(levelname)s %(message)s',
223: datefmt='%H:%M:%S')
224:
225: update_fields = None
226:
227: if options.update_fields:
228: update_fields = [string.strip(s) for s in options.update_fields.split(',')]
229:
230: importXMLFileFMP(dsn=options.dsn,table=options.table,filename=options.filename,
231: update_fields=update_fields,id_field=options.id_field,
232: sync_mode=options.sync_mode)
233:
234: 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