Mercurial > hg > ChinaGisRestApi
diff RestDbInterface.py @ 26:2b73f868d34f
NEW - # 12: create table and upload data
https://it-dev.mpiwg-berlin.mpg.de/tracs/GIS/ticket/12
implemented checkTable
| author | casties |
|---|---|
| date | Mon, 23 Aug 2010 17:14:38 +0200 |
| parents | cef1bfa821cb |
| children | a2e4ca3f1cff |
line wrap: on
line diff
--- a/RestDbInterface.py Mon Aug 23 10:57:59 2010 +0200 +++ b/RestDbInterface.py Mon Aug 23 17:14:38 2010 +0200 @@ -181,31 +181,31 @@ # ReST path was stored in request path = REQUEST.get('restdb_path',[]) - # type and format are real parameter - format = REQUEST.get('format','HTML').upper() - type = REQUEST.get('type',None) + # queryType and resultFormat are real parameter + resultFormat = REQUEST.get('format','HTML').upper() + queryType = REQUEST.get('queryType',None) - logging.debug("index_html path=%s format=%s type=%s"%(path,format,type)) + logging.debug("index_html path=%s resultFormat=%s queryType=%s"%(path,resultFormat,queryType)) - if type is not None: - # non-empty type -- look for template - pt = getattr(self.template, "%s_%s"%(format,type), None) + if queryType is not None: + # non-empty queryType -- look for template + pt = getattr(self.template, "%s_%s"%(resultFormat,queryType), None) if pt is not None: - return pt(format=format,type=type,path=path) + return pt(format=resultFormat,type=queryType,path=path) if len(path) == 1: # list of schemas - return self.showListOfSchemas(format=format) + return self.showListOfSchemas(resultFormat=resultFormat) elif len(path) == 2: # list of tables - return self.showListOfTables(format=format,schema=path[1]) + return self.showListOfTables(resultFormat=resultFormat,schema=path[1]) elif len(path) == 3: # table if REQUEST.get("method") == "POST" and REQUEST.get("create_table_file",None) is not None: # POST to table to check - return self.checkTable(format=format,schema=path[1],table=path[2]) + return self.checkTable(resultFormat=resultFormat,schema=path[1],table=path[2]) # else show table - return self.showTable(format=format,schema=path[1],table=path[2]) + return self.showTable(resultFormat=resultFormat,schema=path[1],table=path[2]) # don't know what to do return str(REQUEST) @@ -230,23 +230,24 @@ logging.debug("put with schema=%s table=%s file=%s"%(schema,tablename,file)) ret = self.createTableFromXML(schema, tablename, file) - resultType = REQUEST.get("result_type","application/json") - if resultType == "application/json": + # return the result as JSON + format = REQUEST.get("format","JSON") + if format == "JSON": RESPONSE.setHeader("Content-Type", "application/json") json.dump(ret, RESPONSE) - elif resultType == "html/json": + + elif format == "JSONHTML": RESPONSE.setHeader("Content-Type", "text/html") RESPONSE.write("<html>\n<body>\n<pre>") json.dump(ret, RESPONSE) RESPONSE.write("</pre>\n</body>\n</html>") - else: # 400 Bad Request RESPONSE.setStatus(400) return - def showTable(self,format='XML',schema='public',table=None,REQUEST=None,RESPONSE=None): + def showTable(self,resultFormat='XML',schema='public',table=None,REQUEST=None,RESPONSE=None): """returns PageTemplate with tables""" logging.debug("showtable") # should be cross-site accessible @@ -254,15 +255,15 @@ RESPONSE = self.REQUEST.RESPONSE RESPONSE.setHeader('Access-Control-Allow-Origin', '*') # GIS gets special treatment - if format=="GIS": + if resultFormat=="GIS": id = REQUEST.get('id',[]) doc = REQUEST.get('doc',None) return self.showGoogleMap(schema=path[1],table=path[2],id=id,doc=doc) # everything else has its own template - pt = getattr(self.template, '%s_schema_table'%format, None) + pt = getattr(self.template, '%s_schema_table'%resultFormat, None) if pt is None: - return "ERROR!! template %s_schema_table not found"%format + return "ERROR!! template %s_schema_table not found"%resultFormat data = self.getTable(schema,table) return pt(data=data,tablename=table) @@ -273,7 +274,14 @@ data = self.executeSQL('select * from "%s"."%s"'%(schema,table)) return data - def showListOfTables(self,format='XML',schema='public',REQUEST=None,RESPONSE=None): + def hasTable(self,schema='public',table=None,username='guest'): + """return if table exists""" + logging.debug("hastable") + data = self.executeSQL('select 1 from information_schema.tables where table_schema=%s and table_name=%s',(schema,table)) + ret = bool(data['rows']) + return ret + + def showListOfTables(self,resultFormat='XML',schema='public',REQUEST=None,RESPONSE=None): """returns PageTemplate with list of tables""" logging.debug("showlistoftables") # should be cross-site accessible @@ -281,9 +289,9 @@ RESPONSE = self.REQUEST.RESPONSE RESPONSE.setHeader('Access-Control-Allow-Origin', '*') - pt = getattr(self.template, '%s_schema'%format, None) + pt = getattr(self.template, '%s_schema'%resultFormat, None) if pt is None: - return "ERROR!! template %s_schema not found"%format + return "ERROR!! template %s_schema not found"%resultFormat data = self.getListOfTables(schema) return pt(data=data,schema=schema) @@ -300,7 +308,7 @@ data=self.executeSQL(qstr) return data - def showListOfSchemas(self,format='XML',REQUEST=None,RESPONSE=None): + def showListOfSchemas(self,resultFormat='XML',REQUEST=None,RESPONSE=None): """returns PageTemplate with list of schemas""" logging.debug("showlistofschemas") # should be cross-site accessible @@ -308,9 +316,9 @@ RESPONSE = self.REQUEST.RESPONSE RESPONSE.setHeader('Access-Control-Allow-Origin', '*') - pt = getattr(self.template, '%s_index'%format, None) + pt = getattr(self.template, '%s_index'%resultFormat, None) if pt is None: - return "ERROR!! template %s_index not found"%format + return "ERROR!! template %s_index not found"%resultFormat data = self.getListOfSchemas() return pt(data=data) @@ -337,6 +345,47 @@ self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") json.dump(data, self.REQUEST.RESPONSE) + def checkTable(self,resultFormat,schema,table,REQUEST=None,RESPONSE=None): + """check the table. + returns valid data fields and table name.""" + if REQUEST is None: + REQUEST = self.REQUEST + RESPONSE = REQUEST.RESPONSE + + file = REQUEST.get("create_table_file",None) + res = self.checkTableFromXML(schema, table, file) + logging.debug("checkTable result=%s"%repr(res)) + # return the result as JSON + if resultFormat == "JSON": + RESPONSE.setHeader("Content-Type", "application/json") + json.dump(res, RESPONSE) + + elif resultFormat == "JSONHTML": + RESPONSE.setHeader("Content-Type", "text/html") + RESPONSE.write("<html>\n<body>\n<pre>") + json.dump(res, RESPONSE) + RESPONSE.write("</pre>\n</body>\n</html>") + + else: + return "ERROR: invalid resultFormat" + + def checkTableFromXML(self,schema,table,data,REQUEST=None,RESPONSE=None): + """check the table with the given XML data. + returns valid data fields and table name.""" + logging.debug("checkTableFromXML schema=%s table=%s"%(schema,table)) + # clean table name + tablename = sqlName(table) + tableExists = self.hasTable(schema, table) + if data is None: + fieldNames = [] + else: + # get list of field names from upload file + fields = self.importExcelXML(schema,tablename,data,fieldsOnly=True) + + res = {'tablename': tablename, 'table_exists': tableExists} + res['fields'] = fields + return res + def createEmptyTable(self,schema,table,fields): """create a table with the given fields returns list of created fields""" @@ -366,10 +415,9 @@ fieldString = ", ".join(['"%s" %s'%(f['name'],f['type']) for f in sqlFields]) sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString) logging.debug("createemptytable: SQL=%s"%sqlString) - ret = self.executeSQL(sqlString,hasResult=False) - + self.executeSQL(sqlString,hasResult=False) return sqlFields - + def createTableFromXML(self,schema,table,data): """create or replace a table with the given XML data""" logging.debug("createTableFromXML schema=%s table=%s data=%s"%(schema,table,data)) @@ -377,12 +425,12 @@ self.importExcelXML(schema,tablename, data) return {"tablename": tablename} - def importExcelXML(self,schema,table,xmldata,fieldNamesOnly=False): + def importExcelXML(self,schema,table,xmldata,fieldsOnly=False): ''' Import XML file in Excel format into the table @param table: name of the table the xml shall be imported into @param containerTagName: XML-Tag which describes a dataset - @param data: data to be imported + @param data: data to be imported (string or filehandle) @param identify: (optional) field res. tag which identifies a entry uniquely for updating purposes. @param RESPONSE: (optional) ''' @@ -393,7 +441,7 @@ rowTagName = "Row" colTagName = "Cell" dataTagName = "Data" - fieldNames = [] + xmlFields = [] sqlFields = [] numFields = 0 sqlInsert = None @@ -432,19 +480,19 @@ names=node[1].getElementsByTagNameNS(namespace, dataTagName) for name in names: fn = getTextFromNode(name) - fieldNames.append(fn) + xmlFields.append({'name':sqlName(fn),'type':'text'}) - if fieldNamesOnly: + if fieldsOnly: # return just field names - return fieldNames + return xmlFields # create table - sqlFields = self.createEmptyTable(schema, table, fieldNames) + sqlFields = self.createEmptyTable(schema, table, xmlFields) numFields = len(sqlFields) fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields]) valString = ", ".join(["%s" for f in sqlFields]) sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString) - logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert) + #logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert) else: # following rows are data