def test_exec_sql_error():
db_dict = config()
with patch("data.sql_exec.connect", return_value=ConnectionExceptionMock()) as connection, patch(
"data.sql_exec.config", return_value=db_dict
):
assert connection.commit.called is False
exec_sql("SELECT abc FROM xyz", True)
assert connection.commit.called is False
def _notify_external_ils(dwh_sql: str,
post_base_url: str,
optional_path_param=None) -> bool:
"""Potentially updates an external endpoint with the non-empty result of the passed SQL query
as a path parameter and returns whether the notification was indeed executed.
Parameters
----------
dwh_sql: str
PostgreSQL query for the external DWH.
post_base_url: str
Base URL of the external service to be notified with the query result.
optional_path_param: str or None, default=None
Optional path parameter to append at the end.
Returns
-------
external_service_notified: bool
Whether the notification was executed.
"""
external_service_notified = False
result = exec_sql(dwh_sql, return_result=True)
if isinstance(result, str):
postfix = f"/{optional_path_param}" if optional_path_param is not None else ""
post(f"{post_base_url}/api/cities/{result}{postfix}")
external_service_notified = True
return external_service_notified
def _get_city_name_for_training() -> Optional[str]:
"""Returns the city name needed for the next training process, None if no training is required.
Returns
-------
train_city: str or None
Name of the city to train for, None if no training should take place.
"""
city_without_model_query = f"""
SELECT DISTINCT(dim_cities.city_name) AS city_name
FROM (
SELECT fs_inner.city_id AS city_id
FROM integration_layer.fact_sights AS fs_inner,
integration_layer.dim_sights_images AS di_inner
WHERE fs_inner.image_id = di_inner.image_id AND di_inner.image_labels IS NOT NULL
GROUP BY city_id
HAVING count(*) > {int(environ['MIN_LABELLED_IMAGES_NEEDED_FOR_TRAINING'])}
) AS trainable_cities, integration_layer.dim_sights_cities AS dim_cities
LEFT JOIN integration_layer.fact_models AS model_facts ON model_facts.city_id = dim_cities.city_id
WHERE dim_cities.city_id = trainable_cities.city_id AND model_facts.trained_model_id IS NULL
LIMIT 1
"""
train_city = exec_sql(city_without_model_query, return_result=True)
return train_city
def test_exec_sql():
db_dict = config()
with patch("data.sql_exec.connect", return_value=ConnectionMock()) as connection_mock, patch(
"data.sql_exec.config", return_value=db_dict
) as config_mock:
assert (connection_mock.called or config_mock.called) is False
result = exec_sql("SELECT abc FROM xyz", True)
assert connection_mock.called and config_mock.called
assert result == "Tokyo"
def trigger_data_marts_refresh() -> None:
"""Triggers an update of all data marts included in the DWH."""
postgres_fct_call = "SELECT RefreshAllMaterializedViews('data_mart_layer')"
exec_sql(postgres_fct_call)