def run_mysql_executemany_query(mysql_conn: CMySQLConnection, sql_query: str, values: List[Dict[str, str]]) -> None:
"""Writes the sample testing information into the MLWH.
Arguments:
mysql_conn {CMySQLConnection} -- a client used to interact with the database server
sql_query {str} -- the SQL query to run (see sql_queries.py)
values {List[Dict[str, str]]} -- array of value hashes representing documents inserted into
the Mongo DB
"""
# fetch the cursor from the DB connection
cursor: CMySQLCursor = mysql_conn.cursor()
try:
# executing the query with values
num_values = len(values)
# BN. If ROWS_PER_QUERY value is too high, you may get '2006 (HY000): MySQL server has
# gone away' error indicating you've exceeded the max_allowed_packet size for MySQL
ROWS_PER_QUERY = 15000
values_index = 0
total_rows_affected = 0
logger.debug(
f"Attempting to insert or update {num_values} rows in the MLWH database in batches of {ROWS_PER_QUERY}"
)
while values_index < num_values:
logger.debug(f"Inserting records between {values_index} and {values_index + ROWS_PER_QUERY}")
cursor.executemany(sql_query, values[values_index : (values_index + ROWS_PER_QUERY)]) # noqa: E203
logger.debug(
f"{cursor.rowcount} rows affected in MLWH. (Note: each updated row increases the "
"count by 2, instead of 1)"
)
total_rows_affected += cursor.rowcount
values_index += ROWS_PER_QUERY
logger.debug("Committing changes to MLWH database.")
mysql_conn.commit()
# number of rows affected using cursor.rowcount - not easy to interpret:
# reports 1 per inserted row,
# 2 per updated existing row,
# and 0 per unchanged existing row
logger.info(
f"A total of {total_rows_affected} rows were affected in MLWH. (Note: each updated row "
"increases the count by 2, instead of 1)"
)
except Exception:
logger.error("MLWH database executemany transaction failed")
raise
finally:
# close the cursor
logger.debug("Closing the cursor.")
cursor.close()
# close the connection
logger.debug("Closing the MLWH database connection.")
mysql_conn.close()
def test_run_mysql_executemany_query_execute_error(config):
conn = CMySQLConnection()
conn.cursor = MagicMock()
conn.commit = MagicMock()
conn.rollback = MagicMock()
conn.close = MagicMock()
cursor = conn.cursor.return_value
cursor.executemany = MagicMock(side_effect=Exception("Boom!"))
cursor.close = MagicMock()
with pytest.raises(Exception):
run_mysql_executemany_query(
mysql_conn=conn,
sql_query=SQL_MLWH_MULTIPLE_INSERT,
values=["test"] # type: ignore
)
# check transaction is not committed
assert conn.commit.called is False
# check connection is closed
assert cursor.close.called is True
assert conn.close.called is True
def test_run_mysql_execute_formatted_query_execute_error(config):
conn = CMySQLConnection()
conn.cursor = MagicMock()
conn.commit = MagicMock()
conn.rollback = MagicMock()
conn.close = MagicMock()
cursor = conn.cursor.return_value
cursor.execute = MagicMock(side_effect=Exception("Boom!"))
cursor.close = MagicMock()
with pytest.raises(Exception):
run_mysql_execute_formatted_query(
mysql_conn=conn,
formatted_sql_query=
SQL_MLWH_MULTIPLE_FILTERED_POSITIVE_UPDATE_BATCH,
formatting_args=["1", "2"],
query_args=[True, "v2", "2020-01-01", "2020-01-01"],
)
# check transaction is not committed
assert conn.commit.called is False
# check connection is closed
assert cursor.close.called is True
def test_run_mysql_executemany_query_success(config):
conn = CMySQLConnection()
conn.cursor = MagicMock()
conn.commit = MagicMock()
conn.rollback = MagicMock()
conn.close = MagicMock()
cursor = conn.cursor.return_value
cursor.executemany = MagicMock()
cursor.close = MagicMock()
run_mysql_executemany_query(mysql_conn=conn,
sql_query=SQL_MLWH_MULTIPLE_INSERT,
values=[{}])
# check transaction is committed
assert conn.commit.called is True
# check connection is closed
assert cursor.close.called is True
assert conn.close.called is True
def test_run_mysql_execute_formatted_query_success(config):
conn = CMySQLConnection()
conn.cursor = MagicMock()
conn.commit = MagicMock()
conn.rollback = MagicMock()
conn.close = MagicMock()
cursor = conn.cursor.return_value
cursor.execute = MagicMock()
cursor.close = MagicMock()
run_mysql_execute_formatted_query(
mysql_conn=conn,
formatted_sql_query=SQL_MLWH_MULTIPLE_FILTERED_POSITIVE_UPDATE_BATCH,
formatting_args=["1", "2"],
query_args=[True, "v2", "2020-01-01", "2020-01-01"],
)
# check transaction is committed
assert conn.commit.called is True
# check connection is closed
assert cursor.close.called is True
def run_mysql_execute_formatted_query(
mysql_conn: CMySQLConnection, formatted_sql_query: str, formatting_args: List[str], query_args: List[Any]
) -> None:
"""Executes formatted sql query, unwrapping and batching based on number of input arguments
Arguments:
mysql_conn {CMySQLConnection} -- a client used to interact with the database server
formatted_sql_query {str} -- the formatted SQL query to run (unwrapped using % workflow)
formatting_args {List[str]} -- arguments to batch and unwrap the formatted sql query
query_args {List[Any]} -- additional sql query arguments
"""
# fetch the cursor from the DB connection
cursor = mysql_conn.cursor()
try:
# executing the query with values
num_formatting_args = len(formatting_args)
# BN. If FORMATTING_ARGS_PER_QUERY value is too high, you may get '2006 (HY000): MySQL server has
# gone away' error indicating you've exceeded the max_allowed_packet size for MySQL
FORMATTING_ARGS_PER_QUERY = 15000
formatting_args_index = 0
total_rows_affected = 0
logger.debug(
f"Attempting to execute formatted sql on the MLWH database in batches of {FORMATTING_ARGS_PER_QUERY}"
)
while formatting_args_index < num_formatting_args:
logger.debug(
f"Executing sql for formatting args between {formatting_args_index} and \
{formatting_args_index + FORMATTING_ARGS_PER_QUERY}"
)
formatting_args_batch = formatting_args[
formatting_args_index : (formatting_args_index + FORMATTING_ARGS_PER_QUERY) # noqa: E203
]
sql_unwrap_formatted_args = ", ".join(
list(map(lambda x: "%s", formatting_args_batch))
) # e.g. for 3 ids, this would look like "%s,%s,%s"
if len(formatting_args_batch) > 0:
sql_query = (
formatted_sql_query % sql_unwrap_formatted_args
) # formats the query to have the correct number of formatting arguments for the ids
string_args = (
query_args + formatting_args_batch
) # adds the filtered positive arguments to the id arguments
cursor.execute(sql_query, tuple(string_args))
total_rows_affected += cursor.rowcount
logger.debug(f"{cursor.rowcount} rows affected in MLWH.")
formatting_args_index += FORMATTING_ARGS_PER_QUERY
logger.debug("Committing changes to MLWH database.")
mysql_conn.commit()
logger.debug(f"Successfully affected a total of {total_rows_affected} rows in MLWH.")
except Exception:
logger.error("MLWH database execute transaction failed")
raise
finally:
# close the cursor
logger.debug("Closing the cursor.")
cursor.close()
