def test_prepare_results(self):
print("Testing preparing results....")
cursor_description = (('sql_column_1', None, 10, 10, 0,
True), ('sql_column_2', None, 50, 50, 0, True),
('sql_column_3', None, 50, 50, 0, True))
rows = [(6, 'Titus', 'Leggon')]
result = prepare_results(cursor_description, rows)
self.assertEqual(
result, {
"entries": [{
'sql_column_1': 6,
'sql_column_2': 'Titus',
'sql_column_3': 'Leggon'
}]
})
# test None and empty list rows value
result = prepare_results(cursor_description, None)
self.assertEqual(result, {"entries": None})
result = prepare_results(cursor_description, [])
self.assertEqual(result, {"entries": None})
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 _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()