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()