def main(req: func.HttpRequest) -> func.HttpResponse:
logging.info('Starting SqlQuery.')
name = req.headers.get('name')
query = req.headers.get('query')
logging.info('Name: {}'.format(name))
logging.info('Query: {}'.format(query))
if name:
retrieved_secret = getConnectionString()
logging.info('Getting table from azure storage')
table_service = TableService(connection_string=retrieved_secret.value)
df = get_dataframe_from_table_storage_table(table_service, name)
logging.info('Applying query')
result = sqldf(query, locals())
return func.HttpResponse(
result.to_json(),
status_code=200
)
else:
return func.HttpResponse(
"Something went wrong! :(",
status_code=400
)
def main(req: func.HttpRequest) -> func.HttpResponse:
logging.info('Starting clean table.')
ret = dict()
name = req.headers.get('name')
if not name: #If name wasnt added as header, search for it in the parameters
name = req.params.get('name')
if name:
retrieved_secret = getConnectionString()
table_service = TableService(connection_string=retrieved_secret.value)
table_service.delete_table(name)
time.sleep(1)
existe = False
while (not existe):
logging.info("Intentando crearla...")
time.sleep(5)
existe = table_service.create_table(name)
logging.info("Done!!")
ret['result'] = 'Done!'
return func.HttpResponse(json.dumps(ret), status_code=200)
else:
ret['result'] = 'Please pass a name on the query string or in the request body!'
return func.HttpResponse(json.dumps(ret), status_code=400)
def main(req: func.HttpRequest) -> func.HttpResponse:
logging.info('Starting getAggFromTable.')
name= req.headers.get('name')
col = req.headers.get('column')
agg = req.headers.get('aggregation')
if not name: #If name wasnt added as header, search for it in the parameters
name= req.params.get('name')
col = req.params.get('column')
agg = req.params.get('aggregation')
if name:
retrieved_secret = getConnectionString()
table_service = TableService(connection_string=retrieved_secret.value)
df = get_dataframe_from_table_storage_table(table_service, name)
if agg == 'max' or agg=='maximum':
ret_val = df[col].max()
if agg == 'min' or agg=='minimum':
ret_val = df[col].min()
if agg == 'avg' or agg=='mean':
ret_val = df[col].mean()
ret = dict()
try:
dateMax = datetime.fromtimestamp(ret_val.timestamp())
dateRet = dateMax
ret['result'] = dateRet.strftime("%Y%m%d %H:%M:%S")
except:
ret['result'] = ret_val
return func.HttpResponse(
json.dumps(ret),
status_code=200
)
else:
ret = dict()
ret['result'] = "Please pass a name!!"
return func.HttpResponse(
json.dumps(ret),
status_code=400
)
def main(req: func.HttpRequest) -> func.HttpResponse:
logging.info('Starting create table.')
ret = dict()
name = req.headers.get('name')
if not name: #If name wasnt added as header, search for it in the parameters
name = req.params.get('name')
if name:
retrieved_secret = getConnectionString()
table_service = TableService(connection_string=retrieved_secret.value)
table_service.create_table(name)
ret['result'] = 'Success'
return func.HttpResponse(json.dumps(ret), status_code=200)
else:
ret['result'] = 'Please pass a name on the query string or in the request body!'
return func.HttpResponse(json.dumps(ret), status_code=400)
def main(req: func.HttpRequest) -> func.HttpResponse:
logging.info('Starting delete rows.')
table_name = req.headers.get('name')
column = req.headers.get('column')
pattern = req.headers.get('pattern')
if not table_name: #If name wasnt added as header, search for it in the parameters
table_name = req.params.get('name')
if not column: #If column wasnt added as header, search for it in the parameters
column = req.params.get('column')
if not pattern: #If pattern wasnt added as header, search for it in the parameters
pattern = req.params.get('pattern')
ret = dict()
if table_name:
retrieved_secret = getConnectionString()
table_service = TableService(connection_string=retrieved_secret.value)
df = get_dataframe_from_table_storage_table(table_service, table_name)
#Filter dataframe by pattern
to_delete = df[df[column].str.contains(pattern).fillna(value=False)]
#Loop over the dataframe and delete records
for i, o in to_delete.iterrows():
logging.info('Deleting {}'.format(i+1))
table_service.delete_entity(table_name,
partition_key=o['PartitionKey'],
row_key=o['RowKey'])
ret['result'] = "Deleted {} rows!".format(to_delete.shape[0])
return func.HttpResponse(
json.dumps(ret),
status_code=200
)
else:
ret['result'] = "Please pass a table name!!"
return func.HttpResponse(
json.dumps(ret),
status_code=400
)
def main(req: func.HttpRequest) -> func.HttpResponse:
logging.info('Starting insert row.')
table_name = req.headers.get('name')
if not table_name: #If name wasnt added as header, search for it in the parameters
table_name = req.params.get('name')
value = req.get_json()
if table_name:
retrieved_secret = getConnectionString()
table_service = TableService(connection_string=retrieved_secret.value)
if table_service.exists(table_name):
if 'PartitionKey' not in value.keys(): #This is mandatory
value['PartitionKey'] = 'reference'
if 'RowKey' not in value.keys(): #This is mandatory too
value['RowKey'] = '001'
try:
table_service.update_entity(table_name=table_name,
entity=value)
except:
table_service.insert_entity(table_name=table_name,
entity=value)
else:
ret = dict()
ret['result'] = "Please create the table!"
return func.HttpResponse(json.dumps(ret), status_code=400)
ret = dict()
ret['result'] = "Success"
return func.HttpResponse(json.dumps(ret), status_code=200)
else:
ret = dict()
ret['result'] = "Please pass a name!!"
return func.HttpResponse(json.dumps(ret), status_code=400)
def main(req: func.HttpRequest) -> func.HttpResponse:
logging.info('Starting bulk insert.')
ret = dict()
table_name = req.headers.get('name')
values = req.get_json()
if table_name:
retrieved_secret = getConnectionString()
table_service = TableService(connection_string=retrieved_secret.value)
batch = TableBatch()
for i in range(0, len(values['rows'])):
batch.insert_entity(values['rows'][i])
table_service.commit_batch(table_name, batch)
ret['result'] = "Success"
return func.HttpResponse(json.dumps(ret), status_code=200)
else:
ret['result'] = 'Error'
return func.HttpResponse(json.dumps(ret), status_code=400)