def test_config_error(monkeypatch):
monkeypatch.delenv("PGHOST", raising=True)
monkeypatch.delenv("PGPORT", raising=True)
monkeypatch.delenv("PGDATABASE", raising=True)
monkeypatch.delenv("PGUSER", raising=True)
monkeypatch.delenv("PGPASSWORD", raising=True)
with raises(ReferenceError):
config()
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 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 exec_sql(postgres_sql_string: str,
return_result=False) -> Optional[object]:
"""Executes a given PostgreSQL string on the DWH and potentially returns the query result.
Parameters
----------
postgres_sql_string: str
PostgreSQL query to evaluate in the external DHW.
return_result: bool, default=False
Whether to return the query result.
Returns
-------
result: str or None
Query result.
"""
result = None
with connect(**config()) as connection:
connection.autocommit = True
with connection.cursor() as cursor:
try:
cursor.execute(postgres_sql_string)
connection.commit()
cursor_result = cursor.fetchone()
result = cursor_result[0] if (return_result and cursor_result
is not None) else return_result
except Exception as exc:
print("Error executing SQL: %s" % exc)
finally:
cursor.close()
return result
def test_config():
db_dict = config()
assert len(db_dict) == 5
for key in ["host", "port", "database", "user", "password"]:
assert key in db_dict