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