def lambda_handler(event, context):
"""Collects data on a passed in References from eight tables and combines
them into a single Json.
Parameters:
event (Dict):A series of key value pairs used in the search.
Returns:
out_json (Json):Nested Json responce of the eight tables data.
"""
logger.info("get_query_dashboard Has Started Running.")
try:
database, error = io_validation.Database(strict=True).load(os.environ)
except ValidationError as e:
logger.error("Database_Location Environment Variable" +
"Has Not Been Set Correctly: {}".format(e.messages))
return {
"statusCode": 500,
"body": {
"Error": "Configuration Error: {}".format(e)
}
}
try:
io_validation.QuerySearch(strict=True).load(event)
except ValidationError as e:
logger.error("Input: {}".format(event))
logger.error("Failed To Validate The Input: {}".format(e.messages))
return {"statusCode": 400, "body": {"Error": e.messages}}
search_list = [
'query_reference', 'survey_period', 'query_type', 'ru_reference',
'survey_code', 'query_status'
]
try:
logger.info("Connecting To The Database.")
engine = db.create_engine(database['Database_Location'])
session = Session(engine)
metadata = db.MetaData()
except db.exc.NoSuchModuleError as e:
logger.error("Driver Error, Failed To Connect: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error": "Driver Error, Failed To Connect."
}
}
except db.exc.OperationalError as e:
logger.error("Operational Error, Encountered: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error": "Operational Error, Failed To Connect."
}
}
except Exception as e:
logger.error(
"General Error, Failed To Connect To The Database: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error":
"General Error, " + "Failed To Connect To The Database."
}
}
try:
logger.info("Fetching Table Model: {}".format("query"))
table_model = alchemy_functions.table_model(engine, metadata, "query")
logger.info("Fetching Table Model: {}".format("contributor"))
other_model = alchemy_functions.table_model(engine, metadata,
"contributor")
logger.info("Building SQL Statement: {}".format("Combined Tables"))
all_query_sql = session.query(table_model, other_model)\
.join(other_model, table_model.columns.ru_reference ==
other_model.columns.ru_reference)
added_query_sql = 0
for criteria in search_list:
if criteria not in event.keys():
logger.info(
"No parameters have been passed for {}.".format(criteria))
continue
added_query_sql += 1
all_query_sql = all_query_sql.filter(
getattr(table_model.columns, criteria) == event[criteria])
if added_query_sql == 0:
all_query_sql = all_query_sql.filter(
table_model.columns.query_status == 'Open')
logger.info("Converting Data: {}".format("query"))
query = alchemy_functions.to_df(all_query_sql)
except db.exc.OperationalError as e:
logger.error("Operational Error, When Retrieving Data: {} {}".format(
"query", e))
return {
"statusCode": 500,
"body": {
"Error":
"Operational Error, Failed To Retrieve Data: {}".format(
"query")
}
}
except Exception as e:
logger.error("General Error, Problem Retrieving " +
"Data From The Table: {} {}".format("query", e))
return {
"statusCode": 500,
"body": {
"Error":
"General Error, Failed To Retrieve Data: {}".format("query")
}
}
table_list = {
'step_exception': None,
'question_anomaly': None,
'failed_vet': None,
'query_task': None,
'query_task_update': None
}
logger.info("Initialising JSON.")
out_json = '{"Queries":[ '
for index, query_row in query.iterrows():
curr_query = query[query['query_reference'] ==
query_row['query_reference']]
if curr_query.empty:
logger.info("No Queries Found.")
continue
ref = int(curr_query['query_reference'].iloc[0])
ru = curr_query['ru_reference'].iloc[0]
period = str(curr_query['survey_period'].iloc[0])
survey = curr_query['survey_code'].iloc[0]
try:
for current_table in table_list:
logger.info("Fetching Table Model: {}".format(current_table))
table_model = alchemy_functions.table_model(
engine, metadata, current_table)
logger.info("Fetching Table Data: {}".format(current_table))
if current_table in [
'step_exception', 'query_task', 'query_task_update'
]:
statement = session.query(table_model).filter(
table_model.columns.query_reference == ref)
elif current_table == "failed_vet":
other_model = alchemy_functions.table_model(
engine, metadata, "vet")
statement = session.query(table_model, other_model)\
.filter(
db.and_(table_model.columns.survey_period == period,
table_model.columns.survey_code == survey,
table_model.columns.ru_reference == ru))
else:
statement = session.query(table_model).filter(
db.and_(table_model.columns.survey_period == period,
table_model.columns.survey_code == survey,
table_model.columns.ru_reference == ru))
logger.info("Converting Data: {}".format(current_table))
table_list[current_table] = alchemy_functions.to_df(statement)
except db.exc.OperationalError as e:
logger.error(
"Operational Error, When Retrieving Data: {} {}".format(
current_table, e))
return {
"statusCode": 500,
"body": {
"Error":
"Operational Error, Failed To " +
"Retrieve Data: {}".format(current_table)
}
}
except Exception as e:
logger.error("General Error, Problem Retrieving Data " +
"From The Table: {} {}".format(current_table, e))
return {
"statusCode": 500,
"body": {
"Error":
"General Error, Failed " +
"To Retrieve Data: {}".format(current_table)
}
}
logger.info("Creating JSON.")
curr_query = json.dumps(curr_query.to_dict(orient='records'),
sort_keys=True,
default=str)
curr_query = curr_query[1:-2]
out_json += curr_query + ',"Exceptions":[ '
for index, step_row in table_list['step_exception'].iterrows():
row_step = step_row['step']
curr_step = table_list['step_exception'][
(table_list['step_exception']['step'] == row_step) &
(table_list['step_exception']['run_id'] == step_row['run_id'])]
if curr_step.empty:
logger.info("No Exceptions Found.")
continue
curr_step = json.dumps(curr_step.to_dict(orient='records'),
sort_keys=True,
default=str)
curr_step = curr_step[1:-2]
out_json += (curr_step + ',"Anomalies":[ ')
for index, ano_row in table_list['question_anomaly'].iterrows():
curr_ano = table_list['question_anomaly'][
(table_list['question_anomaly']['step'] == ano_row['step'])
& (table_list['question_anomaly']['question_number'] ==
ano_row['question_number']) &
(table_list['question_anomaly']['step'] == row_step)]
if curr_ano.empty:
logger.info("No Anomalies Found.")
continue
curr_ano = json.dumps(curr_ano.to_dict(orient='records'),
sort_keys=True,
default=str)
curr_ano = curr_ano[1:-2]
out_json += curr_ano + ',"FailedVETs":'
curr_per = table_list['failed_vet'][
(table_list['failed_vet']['step'] == row_step)
& (table_list['failed_vet']['question_number'] ==
ano_row['question_number'])]
curr_per = json.dumps(curr_per.to_dict(orient='records'),
sort_keys=True,
default=str)
out_json += (curr_per + '},')
out_json = out_json[:-1]
out_json += ']},'
out_json = out_json[:-1]
out_json += '],"QueryTasks":[ '
for index, tas_row in table_list['query_task'].iterrows():
curr_tas = table_list['query_task'][
(table_list['query_task']['query_reference'] ==
tas_row['query_reference'])
& (table_list['query_task']['task_sequence_number'] ==
tas_row['task_sequence_number'])]
if curr_tas.empty:
logger.info("No Tasks Found.")
continue
curr_tas = json.dumps(curr_tas.to_dict(orient='records'),
sort_keys=True,
default=str)
curr_tas = curr_tas[1:-2]
out_json += curr_tas
out_json = out_json + ',"QueryTaskUpdates":'
if not table_list['query_task_update'].empty:
curr_up = table_list['query_task_update'][
(table_list['query_task_update']['query_reference'] ==
tas_row['query_reference'])
& (table_list['query_task_update']['task_sequence_number']
== tas_row['task_sequence_number'])]
curr_up = json.dumps(curr_up.to_dict(orient='records'),
sort_keys=True,
default=str)
else:
curr_up = "[]"
out_json += curr_up + '},'
out_json = out_json[:-1]
out_json += ']},'
out_json = out_json[:-1]
out_json += ']}'
out_json = out_json.replace("NaN", "null")
try:
logger.info("Closing Session.")
session.close()
except db.exc.OperationalError as e:
logger.error(
"Operational Error, Failed To Close The Session: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error":
"Operational Error, Database " + "Session Closed Badly."
}
}
except Exception as e:
logger.error(
"General Error, Failed To Close The Session: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error": "General Error, Database " + "Session Closed Badly."
}
}
try:
io_validation.Queries(strict=True).loads(out_json)
except ValidationError as e:
logger.error("Output: {}".format(out_json))
logger.error("Failed To Validate The Output: {}".format(e.messages))
return {"statusCode": 500, "body": {"Error": e.messages}}
logger.info("get_query_dashboard Has Successfully Run.")
return {"statusCode": 200, "body": json.loads(out_json)}
def lambda_handler(event, context):
"""Collects data on a passed in Reference from a table and returns a single
Json.
Parameters:
event (Dict):Two key value pairs used in the search.
Returns:
out_json (Json):Json responce of the table data.
"""
logger.info("get_survey_periods Has Started Running.")
try:
database, error = io_validation.Database(strict=True).load(os.environ)
except ValidationError as e:
logger.error("Database_Location Environment Variable" +
"Has Not Been Set Correctly: {}".format(e.messages))
return {
"statusCode": 500,
"body": {
"Error": "Configuration Error: {}".format(e)
}
}
try:
io_validation.SurveySearch(strict=True).load(event)
except ValidationError as e:
logger.error("Input: {}".format(event))
logger.error("Failed To Validate The Input: {}".format(e.messages))
return {"statusCode": 400, "body": {"Error": e.messages}}
search_list = ['survey_period', 'survey_code']
try:
logger.info("Connecting To The Database.")
engine = db.create_engine(database['Database_Location'])
session = Session(engine)
metadata = db.MetaData()
except db.exc.NoSuchModuleError as e:
logger.error("Driver Error, Failed To Connect: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error": "Driver Error, Failed To Connect."
}
}
except db.exc.OperationalError as e:
logger.error("Operational Error, Encountered: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error": "Operational Error, Failed To Connect."
}
}
except Exception as e:
logger.error(
"General Error, Failed To Connect To The Database: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error":
"General Error, " + "Failed To Connect To The Database."
}
}
try:
logger.info("Fetching Table Model: {}".format("survey_period"))
table_model = alchemy_functions.table_model(engine, metadata,
"survey_period")
logger.info("Fetching Table Data: {}".format("survey_period"))
all_query_sql = session.query(table_model)
added_query_sql = 0
for criteria in search_list:
if criteria not in event.keys():
logger.info(
"No parameters have been passed for {}.".format(criteria))
continue
added_query_sql += 1
all_query_sql = all_query_sql.filter(
getattr(table_model.columns, criteria) == event[criteria])
if added_query_sql == 0:
all_query_sql = all_query_sql\
.filter(table_model.columns.survey_period == session.query(
func.max(table_model.columns.survey_period)))
logger.info("Converting Data: {}".format("survey_period"))
query = alchemy_functions.to_df(all_query_sql)
except db.exc.OperationalError as e:
logger.error("Operational Error, When Retrieving Data: {} {}".format(
"survey_period", e))
return {
"statusCode": 500,
"body": {
"Error":
"Operational Error, Failed To Retrieve Data: {} {}".format(
"survey_period", e)
}
}
except Exception as e:
logger.error("General Error, Problem Retrieving " +
"Data From The Table: {} {}".format("survey_period", e))
return {
"statusCode": 500,
"body": {
"Error":
"General Error, Failed To Retrieve Data: {}".format(
"survey_period")
}
}
logger.info("Creating JSON.")
out_json = json.dumps(query.to_dict(orient='records'),
sort_keys=True,
default=str)
try:
logger.info("Closing Session.")
session.close()
except db.exc.OperationalError as e:
logger.error(
"Operational Error, Failed To Close The Session: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error":
"Operational Error, " + "Database Session Closed Badly."
}
}
except Exception as e:
logger.error(
"General Error, Failed To Close The Session: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error": "General Error, Database " + "Session Closed Badly."
}
}
try:
io_validation.SurveyPeriod(strict=True, many=True).loads(out_json)
except ValidationError as e:
logger.error("Output: {}".format(out_json))
logger.error("Failed To Validate The Output: {}".format(e.messages))
return {"statusCode": 500, "body": {"Error": e.messages}}
logger.info("get_survey_periods Has Successfully Run.")
return {"statusCode": 200, "body": json.loads(out_json)}
def lambda_handler(event, context):
"""Collects data on a passed in Reference from six tables and combines
them into a single Json.
Parameters:
event (Dict):A single key value pair of ru_reference and a string number.
Returns:
out_json (Json):Nested Json responce of the six tables data.
"""
logger.info("get_contributor Has Started Running.")
try:
database, error = io_validation.Database(strict=True).load(os.environ)
except ValidationError as e:
logger.error("Database_Location Environment Variable" +
"Has Not Been Set Correctly: {}".format(e.messages))
return {
"statusCode": 500,
"body": {
"Error": "Configuration Error: {}".format(e)
}
}
try:
io_validation.ContributorSearch(strict=True).load(event)
except ValidationError as e:
logger.error("Input: {}".format(event))
logger.error("Failed To Validate The Input: {}".format(e.messages))
return {"statusCode": 400, "body": {"Error": e.messages}}
ref = event['ru_reference']
try:
logger.info("Connecting To The Database.")
engine = db.create_engine(database['Database_Location'])
session = Session(engine)
metadata = db.MetaData()
except db.exc.NoSuchModuleError as e:
logger.error("Driver Error, Failed To Connect: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error": "Driver Error, Failed To Connect."
}
}
except db.exc.OperationalError as e:
logger.error("Operational Error, Encountered: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error": "Operational Error, Failed To Connect."
}
}
except Exception as e:
logger.error(
"General Error, Failed To Connect To The Database: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error":
"General Error, " + "Failed To Connect To The Database."
}
}
try:
table_list = {
'contributor': None,
'survey_enrolment': None,
'survey_contact': "contact",
'contributor_survey_period': "survey_period"
}
for current_table in table_list:
logger.info("Fetching Table Model: {}".format(current_table))
table_model = alchemy_functions.table_model(
engine, metadata, current_table)
logger.info("Fetching Table Data: {}".format(current_table))
if current_table in [
"survey_contact", "contributor_survey_period"
]:
other_model = alchemy_functions\
.table_model(engine, metadata, table_list[current_table])
statement = session.query(table_model, other_model)\
.join(other_model)\
.filter(table_model.columns.ru_reference == ref).all()
else:
statement = session.query(table_model)\
.filter(table_model.columns.ru_reference == ref).all()
logger.info("Converting Data: {}".format(current_table))
table_list[current_table] = alchemy_functions.to_df(statement)
except db.exc.OperationalError as e:
logger.error("Operational Error, When Retrieving Data: {} {}".format(
current_table, e))
return {
"statusCode": 500,
"body": {
"Error":
"Operational Error, Failed To Retrieve Data: {}".format(
current_table)
}
}
except Exception as e:
logger.error("General Error, Problem Retrieving " +
"Data From The Table: {} {}".format(current_table, e))
return {
"statusCode": 500,
"body": {
"Error":
"General Error, Failed To Retrieve Data: {}".format(
current_table)
}
}
logger.info("Creating JSON.")
out_json = json.dumps(table_list["contributor"].to_dict(orient='records'),
sort_keys=True,
default=str)
if not out_json == "[]":
out_json = out_json[1:-2]
out_json += ',"Surveys":[ '
for index, row in table_list['survey_enrolment'].iterrows():
curr_row = table_list['survey_enrolment'][(
table_list['survey_enrolment']['survey_code'] ==
row['survey_code'])]
curr_row = json.dumps(curr_row.to_dict(orient='records'),
sort_keys=True,
default=str)
curr_row = curr_row[2:-2]
out_json = out_json + "{" + curr_row + ',"Contacts":'
curr_con = table_list['survey_contact'][(
table_list['survey_contact']['survey_code'] ==
row['survey_code'])]
curr_con = json.dumps(curr_con.to_dict(orient='records'),
sort_keys=True,
default=str)
out_json += curr_con
out_json = out_json + ',"Periods":'
curr_per = table_list['contributor_survey_period'][(
table_list['contributor_survey_period']['survey_code'] ==
row['survey_code'])]
curr_per = json.dumps(curr_per.to_dict(orient='records'),
sort_keys=True,
default=str)
out_json += curr_per + '},'
out_json = out_json[:-1]
out_json += ']}'
try:
logger.info("Closing Session.")
session.close()
except db.exc.OperationalError as e:
logger.error(
"Operational Error, Failed To Close The Session: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error":
"Operational Error, " + "Database Session Closed Badly."
}
}
except Exception as e:
logger.error(
"General Error, Failed To Close The Session: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error": "General Error, " + "Database Session Closed Badly."
}
}
try:
io_validation.Contributor(strict=True).loads(out_json)
except ValidationError as e:
logger.error("Output: {}".format(out_json))
logger.error("Failed To Validate The Output: {}".format(e.messages))
return {"statusCode": 500, "body": {"Error": e.messages}}
logger.info("get_contributor Has Successfully Run.")
return {"statusCode": 200, "body": json.loads(out_json)}
def lambda_handler(event, context):
"""Pull back from a database all reference data from five tables."""
logger.info("get_all_reference_data Has Started Running.")
try:
database, error = io_validation.Database(strict=True).load(os.environ)
except ValidationError as e:
logger.error("Database_Location Environment Variable" +
"Has Not Been Set Correctly: {}".format(e.messages))
return {
"statusCode": 500,
"body": {
"Error": "Configuration Error: {}".format(e)
}
}
try:
logger.info("Connecting To The Database.")
engine = db.create_engine(database['Database_Location'])
session = Session(engine)
metadata = db.MetaData()
except db.exc.NoSuchModuleError as e:
logger.error("Driver Error, Failed To Connect: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error": "Driver Error, Failed To Connect."
}
}
except db.exc.OperationalError as e:
logger.error("Operational Error Encountered: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error": "Operational Error, Failed To Connect."
}
}
except Exception as e:
logger.error(
"General Error, Failed To Connect To The Database: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error":
"General Error, " + "Failed To Connect To The Database."
}
}
try:
table_list = {
'query_type': None,
'vet': None,
'survey': None,
'gor_regions': None,
'ssr_old_regions': None
}
for current_table in table_list:
logger.info("Fetching Table Model: {}".format(current_table))
table_model = alchemy_functions.table_model(
engine, metadata, current_table)
logger.info("Fetching Table Data: {}".format(current_table))
statement = session.query(table_model).all()
logger.info("Converting Data: {}".format(current_table))
table_data = alchemy_functions.to_df(statement)
table_list[current_table] = table_data
except db.exc.OperationalError as e:
logger.error("Operational Error, When Retrieving Data: {} {}".format(
current_table, e))
return {
"statusCode": 500,
"body": {
"Error":
"Operational Error, Failed To Retrieve Data: {}".format(
current_table)
}
}
except Exception as e:
logger.error(
"General Error, Problem Retrieving Data From The Table: {} {}".
format(current_table, e))
return {
"statusCode": 500,
"body": {
"Error":
"General Error, Failed To Retrieve Data: {}".format(
current_table)
}
}
logger.info("Creating JSON.")
out_json = '{"QueryTypes":'
out_json += json.dumps(table_list["query_type"].to_dict(orient='records'),
sort_keys=True,
default=str)
out_json += ',"VETsCodes":'
out_json += json.dumps(table_list["vet"].to_dict(orient='records'),
sort_keys=True,
default=str)
out_json += ',"Surveys":'
out_json += json.dumps(table_list["survey"].to_dict(orient='records'),
sort_keys=True,
default=str)
out_json += ',"GorRegions":'
out_json += json.dumps(table_list["gor_regions"].to_dict(orient='records'),
sort_keys=True,
default=str)
out_json += '}'
try:
logger.info("Closing Session.")
session.close()
except db.exc.OperationalError as e:
logger.error(
"Operational Error, Failed To Close The Session: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error":
"Operational Error, " + "Database Session Closed Badly."
}
}
except Exception as e:
logger.error(
"General Error, Failed To Close The Session: {}".format(e))
return {
"statusCode": 500,
"body": {
"Error": "General Error, " + "Database Session Closed Badly."
}
}
try:
io_validation.AllReferenceData(strict=True).loads(out_json)
except ValidationError as e:
logger.error("Output: {}".format(out_json))
logger.error("Failed To Validate The Output: {}".format(e.messages))
return {"statusCode": 500, "body": {"Error": e.messages}}
logger.info("get_all_reference_data Has Successfully Run.")
return {"statusCode": 200, "body": json.loads(out_json)}