python http interface automation scripts in detail

# -*- coding:utf-8 -*- 
import requests, xlrd, MySQLdb, time, sys 
#Import the required modules
from xlutils import copy 
# Import the copy function from the xlutils module.
def readExcel(file_path): 
  '''''
  Function to read excel test cases
  :param file_path: pass an excel file, or the absolute path to the file.
  :return:Returns a list of all the test cases in the first excel sheet.
  ''' 
  try: 
    book = xlrd.open_workbook(file_path)#Open excel
  except Exception,e: 
    # If the path is not there or the excel is not correct, return an error message
    print 'Path is not there or excel is not correct',e 
    return e 
  else: 
    sheet = book.sheet_by_index(0)# Fetch the first sheet page
    rows= # Fetch all the rows of the sheet page.
    case_list = []#Save every case
    for i in range(rows): 
      if i !=0: 
        # Add each test case to case_list
        case_list.append(sheet.row_values(i)) 
    # Call the interface test function, pass in the list of all cases and the path to excel, because you need to write the return message and test results to excel later.
    # So you need to pass in the path to the excel test case, the interfaceTest function is defined below
    interfaceTest(case_list,file_path) 
 
def interfaceTest(case_list,file_path): 
  res_flags = [] 
  #List of stored test results
  request_urls = [] 
  List of #stored request messages
  responses = [] 
  #Store a list of returned messages
  for case in case_list: 
    '''''
    Iterate through the values of each case in excel, then fetch the value of each field in the case based on the corresponding indexes
    ''' 
    try: 
      '''''
      Catch the exception here and return the exception if the excel format is incorrect
      ''' 
      #Projects, bugs can be raised based on projects
      product = case[0] 
      #Use case ids, used when raising bugs.
      case_id = case[1] 
      #Interface name, also used when mentioning bugs
      interface_name = case[2] 
      # Use case description
      case_detail = case[3] 
      # Request method
      method = case[4] 
      # request url
      url = case[5] 
      # Into the Senate
      param = case[6] 
      # Expected results
      res_check = case[7] 
      # Testers
      tester = case[10] 
    except Exception,e: 
      return 'The test case is not formatted correctly! %s'%e 
    if param== '': 
      '''''
      If the request parameter is null, the request message is the url, and then the request message is stored in the request message list
      ''' 
      new_url = url# Request message
      request_urls.append(new_url) 
    else: 
      '''''
      If the request parameter is not empty, the request message is url+? + parameters, the format is the same as the following
      http://127.0.0.1:8080/rest/login?operator_no=marry&id=100, then store the request message in the request message list
      ''' 
      new_url = url+'?'+urlParam(param)# Request message
      '''''
      excel inside if there are more than one input parameter, the parameters are separated by ;, a = 1; b = 2 such, the request for more than one parameter to use & connection, to replace ; with &, so call the function urlParam, the parameters in the ; replaced with &, the function is defined below.
      To replace & with &, so call the urlParam function, the parameters in the ; replaced with &, function defined in the following
      ''' 
      request_urls.append(new_url) 
    if () == 'GET': 
      '''''
      If it's a get request, call the get method of the requests module, .text to get the return message, save the return message.
      Save the return message to a list of return messages.
      ''' 
      print new_url 
      results = (new_url).text 
      print results 
      (results) 
      '''''
      After getting the return message, you need to judge whether the test passes or not according to the expected result, call the readRes method.
      Pass in the return message and the expected result to determine whether the test passes or not, the readRes method is defined below.
      The readRes method is defined below.'' 
      res = readRes(results,res_check) 
    else: 
      '''''
      If it is not a get request, that is, a post request, call the post method of the requests module, .text is to get the return message, save the return message, store the return message in the list of return messages, and save the return message in the list of return messages.
      Save the return message to the list of return messages.
      ''' 
      results = (new_url).text 
      (results) 
      '''''
      After getting the return message, you need to judge whether the test passes or not according to the expected result, call the readRes method.
      Pass in the return message and the expected result to determine whether the test passes or not, the readRes method will return the test result, if it returns pass, it
      The readRes method is defined below.
      The readRes method is defined below. ''' 
      res = readRes(results,res_check) 
    if 'pass' in res: 
      '''''
      Determine the result of the test, then insert pass or fail into the list of test results
      ''' 
      res_flags.append('pass') 
    else: 
      res_flags.append('fail') 
      '''''
      If it doesn't pass, call the writeBug method, passing in the case_id, interface name, request message, return message, and expected result.
      The writeBug method is defined below, and is implemented by connecting to the database, then spelling out the sql and inserting it into the bug table.
      ''' 
      writeBug(case_id,interface_name,new_url,results,res_check) 
  '''''
  After all the use cases are executed, the copy_excel method will be called to write the test results to excel.
  The request message, return message and test result of each use case, I have defined a list above to store the result of each use case.
  to store the results of the execution of each use case, the path to the source excel use case and the three lists can be passed in the call, copy_excel method is defined below, also add the
  method is defined below, also added a comment
  The copy_excel method is defined below and commented out.'' 
  copy_excel(file_path,res_flags,request_urls,responses) 
 
def readRes(res,res_check): 
  '''''
  :param res: return message
  :param res_check: expected result
  :return: pass or fail, if not, returns which parameter does not match the expected result
  ''' 
  '''''
  Return message example is this {"id": "J_775682", "p":275.00, "m": "458.00"}
  excel expected results in the format is xx = 11; xx = 22 such, so to return to the message changed to xx = 22 such format!
  so use the string replacement, the return message in the ":" and ":: replaced by =, the return message becomes
  {"id=J_775682", "p=275.00, "m=458.00"}, so the same as the expected results, of course, you can also use python comes with the
  json module to parse the json string, but some of the return is not the standard json format, it is more difficult to deal with, here I will use the string method of
  ''' 
  res = ('":"',"=").replace('":',"=") 
 
  ''''' 
  res_checkbeexcelExpected results in，bexx=11;xx=22this kind of 
  That's why it's a good idea to usesplitSplit String，splitbepythonbuilt-in function，Cutting strings，turn into alist 
  ['xx=1','xx=2']this kind of，And then iterating over thislist，judgementslistcenter的每个元素be否存在这个listcenter， 
  如果每个元素都在返回报文center的话，would indicate consistency with expected results 
  Above we have turned the return message into{"id=J_775682","p=275.00,"m=458.00"} 
  ''' 
  res_check = res_check.split(';') 
  for s in res_check: 
    '''''
    Iterate through the list of expected results, if in the return message, do nothing, pass means do nothing, all exist, then return pass
    If not, return the error message and the inconsistent field, because res_check is read from excel.
    Unicode characters, python strings are str type, so use str method to force type conversion to string type.
    ''' 
    if s in res: 
      pass 
    else: 
      return 'incorrect，Inconsistency between returned parameters and expected results'+str(s) 
  return 'pass' 
def urlParam(param): 
  '''''
  Parameter conversion to convert parameters to 'xx=11&xx=2this kind of' 
  ''' 
  return (';','&') 
 
def copy_excel(file_path,res_flags,request_urls,responses): 
  '''''
  :param file_path: path to the test case
  :param res_flags: list of test results
  :param request_urls: list of request messages
  :param responses: list of return messages
  :return.
  ''' 
  '''''
  This function is used to write excel, to write the request message, return message and test result to the test case excel.
  Because xlrd module can only read excel, not write, so use xlutils module, but there is no module in python can
  directly manipulate the written excel, so you can only use the copy method in the xlutils module to copy a new excel in order to manipulate it.
  ''' 
  # Open the original excel and get this book object
  book = xlrd.open_workbook(file_path) 
  #Copy a new_book
  new_book = (book) 
  # Then get the first sheet page of this copied excel
  sheet = new_book.get_sheet(0) 
  i = 1 
  for request_url,response,flag in zip(request_urls,responses,res_flags): 
    '''''
    At the same time traversing the request message, return message and test results of these three large list
    and then write the results of each case execution to excel, zip function can be traversed together in multiple lists
    because the first line is the head of the table, so from the second line to start writing, that is, the index bit 1 position, i on behalf of line
    So i is assigned to 1, and then write a line, and then i +1, i + = 1 is equal to i = i + 1
    request message, return message, test results were in excel 8, 9, 11 columns, columns are fixed, so it is written to death!
    Followed by the value to be written, because excel uses Unicode character encoding, so the front with a u that with Unicode encoding
    Otherwise, there will be garbled code
    ''' 
    (i,8,u'%s'%request_url) 
    (i,9,u'%s'%response) 
    (i,11,u'%s'%flag) 
    i+=1 
  # Save a test result named after the current time in the current directory (you can specify a directory yourself) after writing it, () is the formatting date
  new_book.save('%s_test_results.xls'%('%Y%m%d%H%M%S')) 
def writeBug(bug_id,interface_name,request,response,res_check): 
  '''''
  This function is used to connect to the database, insert the bug into the bugfree data, spell out the sql, and execute the sql.
  :param bug_id: bug number
  :param interface_name: interface name
  :param request: request message
  :param response: return message
  :param res_check: Expected result
  :return.
  ''' 
  bug_id = bug_id.encode('utf-8') 
  interface_name = interface_name.encode('utf-8') 
  res_check = res_check.encode('utf-8') 
  response = ('utf-8') 
  request = ('utf-8') 
  '''''
  Because the above strings read from excel are encoded in Unicode character set, the python strings are encoded in utf-8, so you have to change its character set to utf-8 to put the sql together.
  The python strings are encoded in utf-8, so you have to change the character set to utf-8 to put the sql together.
  The encode method specifies the character set
  ''' 
  # Take the current time and use it as the time to raise the bug
  now = ("%Y-%m-%d %H:%M:%S") 
  #bug title with the bug number plus the interface name and then add _ the results and expectations do not match, you can define what kind of bug title you want to
  bug_title = bug_id + '_' + interface_name + '_Results not as expected' 
  The #replication step is the request message + expected result + return message
  step = '[Request message]<br />'+request+'<br/>'+'[Expected results]<br/>'+res_check+'<br/>'+'<br/>'+'[Response message] <br />'+'<br/>'+response 
  # Spell sql, which the project id, creator, severity, assigned to whom, are written to death in the sql, the use of the time can be based on the project and interface
  # to determine the severity of the bug and who to submit it to
  sql = "INSERT INTO `bf_bug_info` (`created_at`, `created_by`, `updated_at`, `updated_by`, `bug_status`, `assign_to`, `title`, `mail_to`, `repeat_step`, `lock_version`, `resolved_at`, `resolved_by`, `closed_at`, `closed_by`, `related_bug`, `related_case`, `related_result`, " \ 
     "`productmodule_id`, `modified_by`, `solution`, `duplicate_id`, `product_id`, " \ 
     "`reopen_count`, `priority`, `severity`) VALUES ('%s', '1', '%s', '1', 'Active', '1', '%s', 'System Administrator', '%s', '1', NULL , NULL, NULL, NULL, '', '', '', NULL, " \ 
     "'1', NULL, NULL, '1', '0', '1', '1');"%(now,now,bug_title,step) 
  # Establish a connection, use the connect method of the MMySQLdb module to connect to mysql, pass in the account, password, database, port, ip and character set
  coon = (user='root',passwd='123456',db='bugfree',port=3306,host='127.0.0.1',charset='utf8') 
  #Creating a cursor
  cursor = () 
  #Execute sql
  (sql) 
  #Submit
  () 
  # Close the cursor
  () 
  #Close the connection
  () 
if __name__ == '__main__': 
  '''''
  Then make the call, the call needs to pass in an excel, the call is python test_case.xls
  The [1] means take the second parameter passed in, that is, the index is 1, the
  The first is the filename of the python file, if you run it without passing the parameter, it will prompt an error, if it is correct, it
  will call the program to read excel, execute the use case, after the completion of the run, it will print Done
  ''' 
  try: 
    filename = [1] 
  except IndexError,e: 
    print 'Please enter a correct testcase! \n : python  test_case.xls' 
  else: 
    readExcel(filename) 
  print 'Done!'