Mercurial > hg > ChinaGisRestApi

comparison RestDbInterface.py @ 16:cbb73d103152

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
NEW - # 12: create table and upload data https://it-dev.mpiwg-berlin.mpg.de/tracs/GIS/ticket/12 works now!
author casties
date Mon, 16 Aug 2010 21:24:00 +0200
parents 5e3edf980813
children ed997e639cfd
comparison
equal deleted inserted replaced
15:5e3edf980813 16:cbb73d103152
7 from OFS.Folder import Folder 7 from OFS.Folder import Folder
8 from Products.PageTemplates.PageTemplateFile import PageTemplateFile 8 from Products.PageTemplates.PageTemplateFile import PageTemplateFile
9 from Products.ZSQLExtend import ZSQLExtend 9 from Products.ZSQLExtend import ZSQLExtend
10 import logging 10 import logging
11 import re 11 import re
12 import psycopg2
12 13
13 from zope.interface import implements 14 from zope.interface import implements
14 from zope.publisher.interfaces import IPublishTraverse 15 from zope.publisher.interfaces import IPublishTraverse
15 from ZPublisher.BaseRequest import DefaultPublishTraverse 16 from ZPublisher.BaseRequest import DefaultPublishTraverse
16 #from zope.publisher.interfaces import NotFound 17 #from zope.publisher.interfaces import NotFound
18 #from zope.component import queryMultiAdapter 19 #from zope.component import queryMultiAdapter
19 import Shared.DC.ZRDB.DA 20 import Shared.DC.ZRDB.DA
20 from Products.ZSQLMethods.SQL import SQLConnectionIDs 21 from Products.ZSQLMethods.SQL import SQLConnectionIDs
21 22
22 23
23 def getTextFromNode(nodename): 24 def getTextFromNode(node):
24 """get the cdata content of a XML node""" 25 """get the cdata content of a XML node"""
25 if nodename is None: 26 if node is None:
26 return "" 27 return ""
27 nodelist=nodename.childNodes 28
29 if isinstance(node, list):
30 nodelist = node
31 else:
32 nodelist=node.childNodes
33
28 rc = "" 34 rc = ""
29 for node in nodelist: 35 for node in nodelist:
30 if node.nodeType == node.TEXT_NODE: 36 if node.nodeType == node.TEXT_NODE:
31 rc = rc + node.data 37 rc = rc + node.data
32 return rc 38 return rc
33 39
34 def sqlName(s,lc=False): 40 def sqlName(s,lc=True):
35 """returns ASCII-only version of string""" 41 """returns restricted ASCII-only version of string"""
36 if s is None: 42 if s is None:
37 return "" 43 return ""
38 44
39 # blank -> "-"
40 s = re.sub(r' ','-',s)
41 # all else -> "_" 45 # all else -> "_"
42 s = re.sub(r'[^A-Za-z0-9_-]','_',s) 46 s = re.sub(r'[^A-Za-z0-9_]','_',s)
43 if lc: 47 if lc:
44 return s.lower() 48 return s.lower()
45 49
46 return s 50 return s
47 51
103 self._v_database_connection = conn 107 self._v_database_connection = conn
104 except Exception, e: 108 except Exception, e:
105 raise IOError("No database connection! (%s)"%str(e)) 109 raise IOError("No database connection! (%s)"%str(e))
106 110
107 cursor = conn.getcursor() 111 cursor = conn.getcursor()
112 cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
108 return cursor 113 return cursor
109 114
110 def executeSQL(self, query, *args): 115 def executeSQL(self, query, *args):
111 """execute query with args on database and return all results. 116 """execute query with args on database and return all results.
112 result format: {"fields":fields, "rows":data}""" 117 result format: {"fields":fields, "rows":data}"""
118 logging.debug("executeSQL query=%s args=%s"%(query,args))
113 cur = self.getCursor() 119 cur = self.getCursor()
114 cur.execute(query, args) 120 cur.execute(query, args)
115 # description of returned fields 121 # description of returned fields
116 fields = cur.description 122 fields = cur.description
117 # get all data in an array 123 # get all data in an array
118 data = cur.fetchall() 124 data = cur.fetchall()
119 cur.close() 125 cur.close()
120 return {"fields":fields, "rows":data} 126 return {"fields":fields, "rows":data}
127
128 def executeOnlySQL(self, query, *args):
129 """execute query with args on database that has no results.
130 result format: {"fields":fields, "rows":data}"""
131 logging.debug("executeOnlySQL query=%s args=%s"%(query,args))
132 cur = self.getCursor()
133 cur.execute(query, args)
134 cur.close()
135 return None
121 136
122 137
123 def publishTraverse(self,request,name): 138 def publishTraverse(self,request,name):
124 """change the traversal""" 139 """change the traversal"""
125 # get stored path 140 # get stored path
220 235
221 236
222 def getTable(self,schema='public',table=None,username='guest'): 237 def getTable(self,schema='public',table=None,username='guest'):
223 """return table data""" 238 """return table data"""
224 logging.debug("gettable") 239 logging.debug("gettable")
225 data = self.executeSQL("select * from %s"%table) 240 data = self.executeSQL('select * from "%s"."%s"'%(schema,table))
226 return data 241 return data
227 242
228 def showListOfTables(self,format='XML',schema='public'): 243 def showListOfTables(self,format='XML',schema='public'):
229 """returns PageTemplate with list of tables""" 244 """returns PageTemplate with list of tables"""
230 logging.debug("showlistoftables") 245 logging.debug("showlistoftables")
282 297
283 sqlFields.append({'name':name, 'type':type}) 298 sqlFields.append({'name':name, 'type':type})
284 299
285 # drop table if it exists 300 # drop table if it exists
286 try: 301 try:
287 res = executeSQL('drop table "%s.%s"'%(schema,table)) 302 res = self.executeSQL('select * from "%s"."%s" where 1=0'%(schema,table))
288 logging.debug("createemptytable: dropped table %s.%s"%(schema,table)) 303 logging.debug("createemptytable: table %s.%s exists"%(schema,table))
304 self.executeOnlySQL('drop table "%s"."%s"'%(schema,table))
289 except: 305 except:
290 pass 306 pass
291 307
292 fieldString = ", ".join(['"%s" %s'%(f['name'],f['type']) for f in sqlFields]) 308 fieldString = ", ".join(['"%s" %s'%(f['name'],f['type']) for f in sqlFields])
293 sqlString = 'create table "%s.%s" (%s)'%(schema,table,fieldString) 309 sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString)
294 loging.debug("createemptytable: SQL=%s"%sqlString) 310 logging.debug("createemptytable: SQL=%s"%sqlString)
295 ret = self.executeSQL(sqlString) 311 ret = self.executeOnlySQL(sqlString)
296 312
297 return sqlFields 313 return sqlFields
298 314
299 315
300 316
301 def createTableFromXML(self,schema,table,data): 317 def createTableFromXML(self,schema,table,data):
302 """create or replace a table with the given XML data""" 318 """create or replace a table with the given XML data"""
303 logging.debug("createTableFromXML schema=%s table=%s data=%s"%(schema,table,data)) 319 logging.debug("createTableFromXML schema=%s table=%s data=%s"%(schema,table,data))
304 self.importExcelXML(schema,table, data) 320 self.importExcelXML(schema,table, data)
305 321
306 def importExcelXML(self,schema,table,data,fieldNamesOnly=False): 322 def importExcelXML(self,schema,table,xmldata,fieldNamesOnly=False):
307 ''' 323 '''
308 Import XML file in Excel format into the table 324 Import XML file in Excel format into the table
309 @param table: name of the table the xml shall be imported into 325 @param table: name of the table the xml shall be imported into
310 @param containerTagName: XML-Tag which describes a dataset 326 @param containerTagName: XML-Tag which describes a dataset
311 @param data: data to be imported 327 @param data: data to be imported
315 from xml.dom.pulldom import parseString,parse 331 from xml.dom.pulldom import parseString,parse
316 332
317 namespace = "urn:schemas-microsoft-com:office:spreadsheet" 333 namespace = "urn:schemas-microsoft-com:office:spreadsheet"
318 containerTagName = "Table" 334 containerTagName = "Table"
319 rowTagName = "Row" 335 rowTagName = "Row"
336 colTagName = "Cell"
320 dataTagName = "Data" 337 dataTagName = "Data"
321 fieldNames = [] 338 fieldNames = []
322 sqlFields = [] 339 sqlFields = []
340 numFields = 0
323 sqlInsert = None 341 sqlInsert = None
324 342
325 logging.debug("import excel xml") 343 logging.debug("import excel xml")
326 344
327 ret="" 345 ret=""
328 if isinstance(data, str): 346 if isinstance(xmldata, str):
329 logging.debug("importXML reading string data") 347 logging.debug("importXML reading string data")
330 doc=parseString(data) 348 doc=parseString(xmldata)
331 else: 349 else:
332 logging.debug("importXML reading file data") 350 logging.debug("importXML reading file data")
333 doc=parse(data) 351 doc=parse(xmldata)
334 352
335 cnt = 0 353 cnt = 0
336 while True: 354 while True:
337 node=doc.getEvent() 355 node=doc.getEvent()
338 356
339 if node is None: 357 if node is None:
340 break 358 break
341 359
342 else: 360 else:
361 #logging.debug("tag=%s"%node[1].localName)
343 if node[1].localName is not None: 362 if node[1].localName is not None:
344 tagName = node[1].localName.lower() 363 tagName = node[1].localName.lower()
345 else: 364 else:
346 # ignore non-tag nodes 365 # ignore non-tag nodes
347 continue 366 continue
348 367
349 if tagName == containerTagName.lower(): # make everything case insensitive
350 # start of container -- do we need this?
351 cnt = 0
352 continue
353
354 if tagName == rowTagName.lower(): 368 if tagName == rowTagName.lower():
355 # start of row 369 # start of row
356 doc.expandNode(node[1]) 370 doc.expandNode(node[1])
357 cnt += 1 371 cnt += 1
358 if cnt == 1: 372 if cnt == 1:
366 # return just field names 380 # return just field names
367 return fieldNames 381 return fieldNames
368 382
369 # create table 383 # create table
370 sqlFields = self.createEmptyTable(schema, table, fieldNames) 384 sqlFields = self.createEmptyTable(schema, table, fieldNames)
385 numFields = len(sqlFields)
371 fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields]) 386 fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields])
372 valString = ", ".join(["%s" for f in sqlFields]) 387 valString = ", ".join(["%s" for f in sqlFields])
373 sqlInsert = 'insert into "%s.%s" (%s) values (%s)'%(schema,table,fieldString,valString) 388 sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString)
374 logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert) 389 logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert)
375 390
376 else: 391 else:
377 # following rows are data 392 # following rows are data
378 dataNodes=node[1].getElementsByTagNameNS(namespace, dataTagName) 393 colNodes=node[1].getElementsByTagNameNS(namespace, colTagName)
379 data = [] 394 data = []
380 for dataNode in dataNodes: 395 hasData = False
381 val = getTextFromNode(dataNodes) 396 for colNode in colNodes:
397 dataNodes=colNode.getElementsByTagNameNS(namespace, dataTagName)
398 if len(dataNodes) > 0:
399 val = getTextFromNode(dataNodes[0])
400 hasData = True
401 else:
402 val = None
403
382 data.append(val) 404 data.append(val)
383 405
384 self.executeSQL(sqlInsert, data) 406 if not hasData:
385 407 # ignore empty rows
386 408 continue
387 continue 409
388 410 # fix number of data fields
389 '''dataSet={} 411 if len(data) > numFields:
390 for col in node[1].childNodes: 412 del data[numFields:]
391 if col.nodeType is col.ELEMENT_NODE: 413 elif len(data) < numFields:
392 data=col.nodeName 414 missFields = numFields - len(data)
393 dataSet[data]=getTextFromNode(col) 415 data.extend(missFields * [None,])
394 416
395 update=False 417 logging.debug("importexcel sqlinsert=%s data=%s"%(sqlInsert,data))
396 418 self.executeOnlySQL(sqlInsert, *data)
397 if identify:
398
399 field=dataSet[identify]
400
401 searchStr="""select %s from %s where %s = '%s'"""%(identify,table,identify,field)
402 logger("import xml",logging.INFO,searchStr)
403 search=self.ZSQLSimpleSearch(searchStr)
404 if search:
405 update=True
406
407 if update:
408 tmp=[]
409 for fieldName in dataSet.keys():
410 tmp.append("""%s = %s"""%(fieldName,self.ZSQLQuote(dataSet[fieldName])))
411 setStr=",".join(tmp)
412
413 field=dataSet[identify]
414
415 queryStr="""UPDATE %s SET %s WHERE %s = '%s' """%(table,setStr,identify,field)
416 logger("update xml",logging.INFO,queryStr)
417 self.ZSQLSimpleSearch(queryStr)
418 ret+="ud: %s \n"%field
419
420 else:
421 fields=",".join(dataSet.keys())
422 values=",".join([""" %s """%self.ZSQLQuote(dataSet[x]) for x in dataSet.keys()])
423
424
425 queryStr="""INSERT INTO %s (%s) VALUES (%s)"""%(table,fields,values)
426 self.ZSQLSimpleSearch(queryStr)
427 logger("update xml",logging.INFO,queryStr)
428 '''
429 419
430 logging.debug("XML import field names: %s"%(fieldNames)) 420 return cnt
431 return ret
432 421
433 422
434 def manage_editRestDbInterface(self, title=None, connection_id=None, 423 def manage_editRestDbInterface(self, title=None, connection_id=None,
435 REQUEST=None): 424 REQUEST=None):
436 """Change the object""" 425 """Change the object"""