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