def test_close_connections(self, mocked_pyodbc_connect):
print("Test close connections")
db_connection = PostgresODBCConnection()
mocked_pyodbc_connect.return_value = db_connection
OdbcConnection = None
try:
OdbcConnection = odbc_utils.OdbcConnection(
self.fake_sql_connection_string, True, 10)
except Exception:
assert False
db_cursor = FakeDBCursor()
OdbcConnection.set_db_cursor(db_cursor)
# Set closed to false initially
db_connection.closed = False
db_cursor.closed = False
OdbcConnection.close_connections()
if not db_connection.closed or not db_cursor.closed:
print("close() was not called in db_connection or db_cursor.")
assert False
assert True
def test_execute_odbc_query(self, mocked_pyodbc_connect):
print("Test execute odbc query")
db_connection = PostgresODBCConnection()
mocked_pyodbc_connect.return_value = db_connection
OdbcConnection = None
try:
OdbcConnection = odbc_utils.OdbcConnection(
self.fake_sql_connection_string, True, 10)
except Exception:
assert False
class FakeDBCursor:
def execute(self, sql_query, sql_params):
return None
@property
def rowcount(self):
return 0
db_cursor = FakeDBCursor()
OdbcConnection.set_db_cursor(db_cursor)
OdbcConnection.execute_odbc_query("", "")
assert True
def test_odbc_connection_setup_timeout_pyodbc_error_pass(
self, mocked_pyodbc_connect):
print("Test setup odbc connection set timeout pyodbc.Error pass")
mocked_pyodbc_connect.return_value = PostgresODBCConnection()
try:
odbc_utils.OdbcConnection(self.fake_sql_connection_string, True,
10)
assert True
except Exception:
assert False
def test_create_cursor(self, mocked_pyodbc_connect):
print("Test create cursor")
db_connection = PostgresODBCConnection()
mocked_pyodbc_connect.return_value = db_connection
OdbcConnection = None
try:
OdbcConnection = odbc_utils.OdbcConnection(
self.fake_sql_connection_string, True, 10)
except Exception:
assert False
OdbcConnection.create_cursor()
assert True
def test_set_db_cursor(self, mocked_pyodbc_connect):
print("Test set db cursor")
db_connection = PostgresODBCConnection()
mocked_pyodbc_connect.return_value = db_connection
OdbcConnection = None
try:
OdbcConnection = odbc_utils.OdbcConnection(
self.fake_sql_connection_string, True, 10, 'HY000')
except Exception:
assert False
OdbcConnection.set_db_cursor(None)
assert True
def test_get_cursor_description(self, mocked_pyodbc_connect):
print("Test get cursor description")
db_connection = PostgresODBCConnection()
mocked_pyodbc_connect.return_value = db_connection
OdbcConnection = None
try:
OdbcConnection = odbc_utils.OdbcConnection(
self.fake_sql_connection_string, True, 10)
except Exception:
assert False
db_cursor = FakeDBCursor()
OdbcConnection.set_db_cursor(db_cursor)
OdbcConnection.get_cursor_description()
assert True
def test_configure_unicode_settings(self, mocked_pyodbc_connect):
print("Test configure unicode settings")
db_connection = PostgresODBCConnection()
mocked_pyodbc_connect.return_value = db_connection
OdbcConnection = None
try:
OdbcConnection = odbc_utils.OdbcConnection(
self.fake_sql_connection_string, True, 10)
except Exception:
assert False
OdbcConnection.configure_unicode_settings(
"mariadb"
) # doesn't matter what arg is as long as it's mariadb, postgresql, or mysql same result
assert True
def connect_mariadb():
# Open odbc connection to test database MariaDB server, run locally in Docker.
# Create cursor and stop at breakpoint.
# Invoke function fn_odbc_query through Resilient (configured to connect to the same MariaDB db)
# to test if multiprocessing works.
# MariaDB Connector/ODBC 3.0.2 driver can handle multiple active connections.
sql_connection_string = "DRIVER={MariaDB ODBC 3.0 Driver};SERVER=127.0.0.1;PORT=3306;UID=root;PWD=password;Connection Timeout=60;"
odbc_connection = odbc_utils.OdbcConnection(sql_connection_string, True,
10, "HY000")
odbc_connection.configure_unicode_settings("mariadb")
odbc_connection.create_cursor()
sql_query = "SELECT incident_id AS sql_column_1, name AS sql_column_2, description AS sql_column_3 FROM test.incidents WHERE incident_id = ?"
sql_params = [1]
rows = odbc_connection.execute_select_statement(sql_query, sql_params, 10)
results = function_utils.prepare_results(
odbc_connection.get_cursor_description(), rows)
print(results)
def connect_postgreslq():
# Open odbc connection to test database PostgreSQL server, run locally in Docker.
# Create cursor and stop at breakpoint.
# Invoke function fn_odbc_query through Resilient (configured to connect to the same PostgreSQL db)
# to test if multiprocessing works.
# PostgreSQL psqlodbc driver can handle multiple active connections.
sql_connection_string = "Driver={PostgreSQL};Server=localhost;Port=5432;Database=postgres_test_database;Uid=postgres;Pwd=password;Timeout=60;"
odbc_connection = odbc_utils.OdbcConnection(sql_connection_string, True,
10, "HY000")
odbc_connection.configure_unicode_settings("postgresql")
odbc_connection.create_cursor()
sql_query = "SELECT id AS sql_column_1, first_name AS sql_column_2, last_name AS sql_column_3 FROM mock_data WHERE id = ?"
sql_params = [6]
rows = odbc_connection.execute_select_statement(sql_query, sql_params, 10)
results = function_utils.prepare_results(
odbc_connection.get_cursor_description(), rows)
print(results)
def test_execute_select_statement(self, mocked_pyodbc_connect):
print("Test execute select statement")
db_connection = PostgresODBCConnection()
mocked_pyodbc_connect.return_value = db_connection
OdbcConnection = None
try:
OdbcConnection = odbc_utils.OdbcConnection(
self.fake_sql_connection_string, True, 10)
except Exception:
assert False
db_cursor = FakeDBCursor()
OdbcConnection.set_db_cursor(db_cursor)
# calling twice to cover both if statements
OdbcConnection.execute_select_statement("", "", 1)
OdbcConnection.execute_select_statement("", "", None)
assert True
def test_odbc_connection_setup_timeout_unknown_error(
self, mocked_pyodbc_connect):
print("Test setup odbc connection set timeout pyodbc.Error pass")
class PostgresODBCConnection_customexception(object):
@property
def timeout(self):
return 1
@timeout.setter
def timeout(self, value):
raise pyodbc.Error('HY000',
"[HY000] Driver doesn't support this")
db_connection = PostgresODBCConnection_customexception()
mocked_pyodbc_connect.return_value = db_connection
try:
odbc_utils.OdbcConnection(self.fake_sql_connection_string, True,
10)
assert False
except Exception:
assert True
def _fn_odbc_query_function(self, event, *args, **kwargs):
"""Resilient Function: A function that makes ODBC queries
Using prepared SQL statements, where parameters are passed to the database separately,
protecting against SQL injection attacks.
Inputs:
Inputs: sql_query: a SQL query with set parameters using a question mark as a place holder,
SQL statements SELECT, INSERT, UPDATE and DELETE are supported
sql_condition_value1: value for the question mark - condition value 1
sql_condition_value2: value for the question mark - condition value 2
sql_condition_value3: value for the question mark - condition value 3
"""
odbc_connection = None
try:
# Get the function parameters:
if "sql_query" not in kwargs or kwargs.get("sql_query") == '':
LOG.error(u"Required field sql_query is missing or empty")
raise ValueError("Required field sql_query is missing or empty")
sql_query = self.get_textarea_param(kwargs.get("sql_query")) # textarea
LOG.info(u"sql_query: %s", sql_query)
# ------------------------------------------------------
# When adding more condition input fields to the function, you need to load them here
# and pass the new variable/s to the function_utils.prepare_sql_parameters().
# ------------------------------------------------------
sql_condition_value1 = kwargs.get("sql_condition_value1") # text
sql_condition_value2 = kwargs.get("sql_condition_value2") # text
sql_condition_value3 = kwargs.get("sql_condition_value3") # text
LOG.info(u"sql_condition_value1: %s", sql_condition_value1)
LOG.info(u"sql_condition_value2: %s", sql_condition_value2)
LOG.info(u"sql_condition_value3: %s", sql_condition_value3)
sql_params = function_utils.prepare_sql_parameters(sql_condition_value1, sql_condition_value2,
sql_condition_value3)
# Read configuration settings:
if "sql_connection_string" in self.options:
sql_connection_string = self.options["sql_connection_string"]
else:
LOG.error(u"Mandatory config setting 'sql_connection_string' not set.")
raise ValueError("Mandatory config setting 'sql_connection_string' not set.")
sql_restricted_sql_statements = self.options["sql_restricted_sql_statements"] \
if "sql_restricted_sql_statements" in self.options else None
sql_autocommit = function_utils.str_to_bool(self.options["sql_autocommit"]) \
if "sql_autocommit" in self.options else False
sql_query_timeout = int(self.options["sql_query_timeout"]) \
if "sql_query_timeout" in self.options else None
sql_database_type = self.options["sql_database_type"].lower() \
if "sql_database_type" in self.options else None
sql_number_of_records_returned = int(self.options["sql_number_of_records_returned"]) \
if "sql_number_of_records_returned" in self.options else None
yield StatusMessage("Starting...")
yield StatusMessage("Validating...")
function_utils.validate_data(sql_restricted_sql_statements, sql_query)
yield StatusMessage("Opening ODBC connection...")
odbc_connection = odbc_utils.OdbcConnection(sql_connection_string, sql_autocommit, sql_query_timeout)
odbc_connection.configure_unicode_settings(sql_database_type)
odbc_connection.create_cursor()
yield StatusMessage("Executing an ODBC query...")
# Check what SQL statement is executed, get the first word in sql_query
sql_statement = function_utils.get_type_sql_statement(sql_query)
if sql_statement == 'select':
LOG.debug(u"Query: %s. Params: %s. Fetching %s records.",
sql_query, sql_params, sql_number_of_records_returned)
rows = odbc_connection.execute_select_statement(sql_query, sql_params, sql_number_of_records_returned)
results = function_utils.prepare_results(odbc_connection.get_cursor_description(), rows)
LOG.info(json.dumps(results))
if results.get("entries") is None:
yield StatusMessage("No query results returned...")
else:
yield StatusMessage("Result contains {} entries...".format(len(results.get("entries"))))
elif sql_statement == 'update' or sql_statement == 'delete' \
or sql_statement == 'insert':
LOG.debug(u"Query: %s. Params: %s.", sql_query, sql_params)
# Return row count and set results to empty list
row_count = odbc_connection.execute_odbc_query(sql_query, sql_params)
results = function_utils.prepare_results(None, None)
LOG.info(u"%s rows processed", row_count)
yield StatusMessage("{} rows processed".format(row_count))
else:
LOG.error(u"SQL statement '%s' is not supported", sql_statement)
raise ValueError("SQL statement '{}' is not supported".format(sql_statement))
yield StatusMessage("Done...")
yield FunctionResult(results)
except Exception as err:
LOG.error(str(err))
raise FunctionError(str(err))
# Commit changes and tear down connection
finally:
yield StatusMessage("Closing ODBC connection...")
if odbc_connection:
odbc_connection.close_connections()
