def getDataSetFields(self, datasetName):
try:
if datasetName == None or datasetName == '':
raise Data_Exception(
Exception("Error delete dataset " + datasetName))
return self.repository.getDataSetFields(datasetName)
except Exception as e:
print(e)
raise Data_Exception(Exception("Error get dataset " + datasetName))
def getDataSet(self, datasetName, geojsonField):
try:
response = self.getDataSetFields(datasetName)
response.remove(geojsonField)
columns = ','.join(response)
query = "SELECT row_to_json(row) FROM " + \
"(SELECT " + columns + ", ST_AsGeoJSON(" + geojsonField + \
") as geojson FROM " + datasetName + ") row"
db = dbConnection()
cursor = db.cursor()
cursor.execute(query)
result = cursor.fetchall()
if result == None:
db.close()
raise Data_Exception
db.close()
return result
except Exception as e:
print(e)
raise Data_Exception(
Exception('Error! into fetch data set ' + datasetName))
def showAllDataSets(self):
try:
showTablesQuery = "SELECT * FROM pg_catalog.pg_tables WHERE schemaname = 'public'"
db = dbConnection()
cursor = db.cursor()
cursor.execute(showTablesQuery)
result = cursor.fetchall()
if result == None:
raise Data_Exception
response = []
for elem in result:
jsonStr = json.dumps(elem, ensure_ascii=False)
table = json.loads(jsonStr)[1]
if table != 'spatial_ref_sys' and table != 'dataset_history':
sqlCount = 'SELECT count(*) from ' + table
cursor.execute(sqlCount)
count = cursor.fetchone()
elem = {
'dataset': table,
'num_elements': count[0]
}
response.append(elem)
db.close()
return response
except Exception as e:
print(e)
raise Data_Exception(Exception('Error! into fetch all datasets'))
def deleteDatasetHistory(self, id):
try:
self.repository.deleteDatasetHistory(id)
except Exception as e:
print(e)
raise Data_Exception(
Exception("Error delete dataset history - " + id))
def createDatasetHistory(self, dataset):
try:
self.repository.createDatasetHistoryTable()
datasets = self.repository.showAllDataSets()
for elem in datasets:
if (dataset == elem['dataset']):
print(elem['dataset'], elem['num_elements'])
self.repository.insertDatasetHistory(
elem['dataset'], elem['num_elements'])
except Exception as e:
print(e)
raise Data_Exception(
Exception("Error create dataset history - " + dataset))
def getDataSetFields(self, datasetName):
try:
queryColumns = "SELECT column_name " +\
"FROM information_schema.columns " +\
"WHERE table_name = '" + datasetName + "'"
db = dbConnection()
cursor = db.cursor()
cursor.execute(queryColumns)
columns = cursor.fetchall()
if columns == None:
raise Data_Exception
response = []
for column in columns:
jsonStr = json.dumps(column, ensure_ascii=False)
response.append(json.loads(jsonStr)[0])
db.close()
return response
except Exception as e:
print(e)
raise Data_Exception(Exception('Error! into fetch dataset fields'))
def getAllDataSets(self):
try:
return self.repository.showAllDataSets()
except Exception as e:
print(e)
raise Data_Exception(Exception("Error get all datasets"))
def deleteAllDatasetHistory(self):
try:
self.repository.dropTableDatasetHistory()
except Exception as e:
print(e)
raise Data_Exception(Exception("Error delete all dataset history"))
def getDatasetsHistory(self, limit):
try:
return self.repository.selectDatasetsHistory(limit)
except Exception as e:
print(e)
raise Data_Exception(Exception("Error get datasets history"))