def test_get_invalid_columns(self):
url = "/geocoder/geocoding/columns"
table_dto = models.TableDto(10001, "no_table")
data = {"table": table_dto.to_json()}
response = self.get_resp(url, json_=data)
error_message = "No columns found for table with name undefined"
assert error_message in response
def test_get_columns(self):
url = "/geocoder/geocoding/columns"
table_dto = models.TableDto(
self.test_table.id,
self.test_table.table_name,
self.test_table.schema,
self.test_table.database_id,
)
data = {"table": table_dto.to_json()}
columns = reflection.Inspector.from_engine(db.engine).get_columns(
self.test_table.table_name)
response = self.get_resp(url, json_=data)
assert columns[0].get("name") in response
def _geocode_post(self):
table_dto = models.TableDto(
self.test_table.id,
self.test_table.table_name,
self.test_table.schema,
self.test_table.database_id,
)
return {
"datasource": table_dto.to_json(),
"streetColumn": "street",
"cityColumn": "city",
"countryColumn": "country",
"latitudeColumnName": "lat",
"longitudeColumnName": "lon",
"ifExists": "replace",
"saveOnErrorOrInterrupt": True,
}
def _get_editable_tables(self) -> list:
"""
Get tables which are allowed to create columns (allow dml on their database)
:return: list of dto table
"""
tables = []
for database in (db.session.query(
models.Database).filter_by(allow_dml=True).all()):
all_tables = (db.session.query(SqlaTable).filter_by(
database_id=database.id).all())
permitted_tables = security_manager.get_datasources_accessible_by_user(
database, all_tables)
for table in permitted_tables:
tables.append(
models.TableDto(table.id, table.table_name, table.schema,
table.database_id))
return tables
def columns(self) -> str:
"""
Get all column names from given table name
:return: list of column names or an error message if table with given name does not exist
"""
table_dto = request.json.get("table", models.TableDto())
error_message = "No columns found for table with name {0}".format(
table_dto.get("fullName", "undefined"))
try:
table = (db.session.query(SqlaTable).filter_by(
id=table_dto.get("table_id", "")).first())
if table and table.columns:
column_names = [column.column_name for column in table.columns]
else:
return json_error_response(error_message, status=400)
except Exception as e:
self.logger.exception(f"Failed getting columns {e}")
self.stats_logger.incr(error_message)
return json_error_response(error_message, status=400)
return json.dumps(column_names)
def geocode(self) -> Response:
"""
Geocode addresses in given columns from given table
:return: OK message or an error messge
"""
request_data = request.json
self._set_geocoder(request_data.get("geocoder", ""))
lat_column = request_data.get("latitudeColumnName", "lat")
lon_column = request_data.get("longitudeColumnName", "lon")
table_dto = request_data.get("datasource", models.TableDto())
table_id = table_dto.get("table_id", "")
if_exists = request_data.get("ifExists")
save_on_stop_geocoding = request_data.get("saveOnErrorOrInterrupt",
True)
message_suffix = ""
try:
table = self._get_table_with_columns(table_id)
columns = self._create_column_list(request_data, table)
lat_exists = self._does_column_name_exist(table, lat_column)
lon_exists = self._does_column_name_exist(table, lon_column)
if "fail" in if_exists and (lat_exists or lon_exists):
self.stats_logger.incr("geocoding_failed")
return json_error_response(
"At least one of the columns already exists!", status=400)
self._create_columns(lat_column, lat_exists, lon_column,
lon_exists, table)
message_suffix = f" but columns have been created please choose the 'Replace' or 'Append' option when trying again."
table_data = self._load_data_from_columns(table, columns,
lat_column, lon_column,
"append" in if_exists)
except (
NoColumnsException,
TableNotFoundException,
SqlSelectException,
SqlAddColumnException,
) as e:
self.stats_logger.incr("geocoding_failed")
return json_error_response(f"{e.args[0]} {message_suffix}",
status=400)
except Exception as e:
self.logger.exception(
f"Exception when preparing for geocoding {e}")
self.stats_logger.incr("geocoding_failed")
return json_error_response(f"{e.args[0]} {message_suffix}",
status=500)
try:
message_with_geocoded_values = self._geocode(table_data)
except NotImplementedError as e:
self.logger.exception(e.args[0])
self.stats_logger.inc("geocoding_failed")
return json_error_response(
"Geocoder is not implemented correctly. Please contact your administrator."
)
except NoAPIKeySuppliedException as e:
self.logger.exception(e.args[0])
self.stats_logger.incr("geocoding_failed")
return json_error_response(f"{e.args[0]} {message_suffix}")
if self.geocoder.interruptflag and not save_on_stop_geocoding:
return json_success(
json.dumps(f"geocoding interrupted {message_suffix}"))
# If there was an error, data[0] will be a message, otherwise it will be an empty string meaning we can proceed
if message_with_geocoded_values[0] and not save_on_stop_geocoding:
return json_error_response(
f"{message_with_geocoded_values[0]} {message_suffix}")
try:
geocoded_values = message_with_geocoded_values[1]
# It is possible that no exception occured but no geocoded values are returned check for this
if "append" in if_exists and geocoded_values[0] == 0:
return json_error_response(
f"No geocoded values received {message_suffix}")
self._insert_geocoded_data(table, lat_column, lon_column, columns,
geocoded_values[0])
except SqlUpdateException as e:
self.logger.exception(
f"Failed to add columns/ data after geocoding {e.orig}")
self.stats_logger.incr("geocoding_failed")
return json_error_response(f"{e.args[0]} {message_suffix}",
status=400)
except Exception as e:
self.logger.exception(
f"Exception when adding columns/ data after geocoding {e}")
self.stats_logger.incr("geocoding_failed")
return json_error_response(f"{e.args[0]} {message_suffix}",
status=500)
db.session.commit()
progress = self.geocoder.progress
message = (
f"Geocoded values, success: {progress['success_counter']}, doubt: {progress['doubt_counter']}, "
f"fail: {progress['failed_counter']}")
flash(message, "success")
self.stats_logger.incr("succesful_geocoding")
return json_success('"OK"')