Annotation of ZSQLExtend/importFMPXML.py, revision 1.1
1.1 ! casties 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