Python reads the result and writes it to Excel
Article overview map
Python writes results to Excel
List nested dictionaries. The url, test_method, data, title, etc. in Excel are all a key, and the data under url, test_method, data, title is the value to be taken, that is, the form of Key and value.
1. Get the number of rows according to case_id
log in
First create a requests.py file and call the interface with the request module.
import requests
classHttpRequest:
def http_request(self,url,data,http_method,cookie=None):try:if http_method.upper()=='POST':
res=requests.post(url,data,cookies=cookie)
elif http_method.upper()=='POST':
res=requests.post(url,data,cookies=cookie)else:print("The request method entered is incorrect")
except Exception as e:print("The request reported an error:{0}".format(e))
raise e
return res#Return result
if __name__ =='__main__':
# registered
register_url='http://api.nnzhp.cn/api/user/add_stu'
register_data={"name":"niuhanyang","grade":"Scorpio","phone":'18614711314'}
# log in
login_url='http://api.nnzhp.cn/api/user/login'
login_data={"username":"niuhanyang","passwd":'aA123456'}
# Recharge
recharge_url='http://api.nnzhp.cn/api/user/gold_add'
recharge_data={"stu_id":"2170","gold":'1'}
login_res=HttpRequest().http_request(login_url,login_data,'post')
recharge_res=HttpRequest().http_request(recharge_url,recharge_data,'post',login_res.cookies)print("Recharge result:{}".format(recharge_res.json()))
File test_data_xiejinjieguo_jiacaseid.xlsx
File do_excel_xiejinjieguo_jiacaseid.py
from openpyxl import load_workbook
classDoExcel:
def get_data(self,file_name,sheet_name):
wb=load_workbook(file_name)
sheet=wb[sheet_name]
test_data=[]for i inrange(2,sheet.max_row+1):#
print('sheet.max_row is:',sheet.max_row)
row_data={}
row_data['case_id']= sheet.cell(i,1).value #Column 1 row 2
row_data['url']=sheet.cell(i,2).value
row_data['data']= sheet.cell(i,3).value
row_data['title']= sheet.cell(i,4).value
row_data['http_method']= sheet.cell(i,5).value
test_data.append(row_data)return test_data
# Write the results into Excel
def write_back(self,file_name,sheet_name,i,value):#Write back data specifically
wb=load_workbook(file_name)
sheet=wb[sheet_name]
sheet.cell(i,6).value=value
wb.save(file_name)#Save result
if __name__ =='__main__':
test_data=DoExcel().get_data("../test_data/test_data_xiejinjieguo_jiacaseid.xlsx",'login')print(test_data)
# Error message: No such file or directory:'test_data_xiejinjieguo_jiacaseid.xlsx'Relative path and absolute path
# Involving Excel writing operations, be sure to turn off Excel
File run_xiejinjieguo_jiacaseid.py
# Entrance to execute code
from tools.http_request import HttpRequest
from tools.do_excel_xiejinjieguo_jiacaseid import DoExcel
def run(test_data):#The data format of the list nested dictionary comes in
for item in test_data:print("The use case being tested is{0}".format(item['title']))
res =HttpRequest().http_request(item['url'],eval(item['data']),item['http_method'])#Traverse,Value
print("The result of the request is:{0}".format(res.text))DoExcel().write_back("test_data/test_data_xiejinjieguo_jiacaseid.xlsx",'login', item['case_id']+1,str(res.text))#item['case_id']Indicates the number of rows
# item['case_id']+1 means in the second line
test_data=DoExcel().get_data("test_data/test_data_xiejinjieguo_jiacaseid.xlsx",'login')print('It reads:',test_data)run(test_data)'''
Error:
raise ValueError("Cannot convert {0!r} to Excel".format(value))
ValueError: Cannot convert {'error_code':0,'login_info':{'login_time':'20200211084505','sign':'748fc064f1e33a00fad24b2600381c72','userId':2170}} to Excel
The prompt cannot be written into Excel, only numbers and strings can be written, then force conversion and add str
'''
# i is the row, j is the column
Created requests.py, file test_data_xiejinjieguo_jiacaseid.xlsx, file do_excel_xiejinjieguo_jiacaseid.py, file run_xiejinjieguo_jiacaseid.py, running file run_xiejinjieguo_jiacaseid.py
The output is as follows:
Without case_id, use range
log in
file
test_data_xiejinjieguo_bujiacaseid.xlsx
file
do_excel_xiejinjieguo_bujiacaseid.py
from openpyxl import load_workbook
classDoExcel:
def get_data(self,file_name,sheet_name):
wb=load_workbook(file_name)
sheet=wb[sheet_name]
test_data=[]for i inrange(2,sheet.max_row+1):#max_row+1 means 2345 executes four use cases, if you do not add 1, then one less use case will be executed
row_data={}
row_data['url']=sheet.cell(i,1).value#First column, second row
row_data['data']= sheet.cell(i,2).value
row_data['title']= sheet.cell(i,3).value
row_data['http_method']= sheet.cell(i,4).value
test_data.append(row_data)return test_data
# Write the results into Excel
def write_back(self,file_name,sheet_name,i,value):#Write back data specifically
wb=load_workbook(file_name)
sheet=wb[sheet_name]
sheet.cell(i,5).value=value
wb.save(file_name)#Save result
if __name__ =='__main__':
test_data=DoExcel().get_data("../test_data/test_data_xiejinjieguo_bujiacaseid.xlsx",'login')print(test_data)
# No such file or directory:'test_data_xiejinjieguo_jiacaseid.xlsx'Relative path and absolute path
# Involving Excel writing operations, be sure to turn off Excel
File run_xiejinjieguo_bujiacaseid.py
# Entrance to execute code
from tools.http_request import HttpRequest
from tools.do_excel_xiejinjieguo_bujiacaseid import DoExcel
def run(test_data):#The data format of the list nested dictionary comes in
for e inrange(len(test_data)):#The range is to take the head but not the tail, if there are 4 numbers, it is 0, 1, 2, 3. e represents the number of elements in the list. 4 elements, the length is 4,
# That is the three elements of 0, 1, 2, 3
# Start from 0 and take the value according to the index.
print("The use cases being tested are:{0}".format(test_data[e]['title']))
res =HttpRequest().http_request(test_data[e]['url'],eval(test_data[e]['data']),test_data[e]['http_method'])#
print("The result of the request is:{0}".format(res.json()))DoExcel().write_back("test_data/test_data_xiejinjieguo_bujiacaseid.xlsx",'login', e+2,str(res.json()))#e+2 means the second line
test_data=DoExcel().get_data("test_data/test_data_xiejinjieguo_bujiacaseid.xlsx",'login')run(test_data)
The file requests.py and the file test_data_xiejinjieguo_bujiacaseid.xlsx have been created,
File do_excel_xiejinjieguo_bujiacaseid.py, file run_xiejinjieguo_bujiacaseid.py, run file run_xiejinjieguo_bujiacaseid.py
The output is as follows:
Register
File test_data_xiejinjieguo_zhuce.xlsx
File http_request_xiejinjieguo_zhuce.py
import requests
classHttpRequest:
def http_request(self,url,data,http_method,cookie=None):try:if http_method.upper()=='POST':
res=requests.post(url,json=data,cookies=cookie)#If you don’t specify fromdata, json can only be passed in json format.
# The interface is in json format, and data needs to be specified, so it is written as json=data
elif http_method.upper()=='POST':
res=requests.post(url,json=data,cookies=cookie)else:print("The request method entered is incorrect")
except Exception as e:print("The request reported an error:{0}".format(e))
raise e
return res#Return result
if __name__ =='__main__':
# registered
register_url='http://api.nnzhp.cn/api/user/add_stu'
register_data={"name":"niuhanyang","grade":"Scorpio","phone":'18614711314'}
# log in
login_url='http://api.nnzhp.cn/api/user/login'
login_data={"username":"niuhanyang","passwd":'aA123456'}
# Recharge
recharge_url='http://api.nnzhp.cn/api/user/gold_add'
recharge_data={"stu_id":"2170","gold":'1'}
login_res=HttpRequest().http_request(login_url,login_data,'post')
recharge_res=HttpRequest().http_request(recharge_url,recharge_data,'post',login_res.cookies)print("Recharge result:{}".format(recharge_res.json()))
File do_excel_xiejinjieguo_zhuce.py
from openpyxl import load_workbook
classDoExcel:
def get_data(self,file_name,sheet_name):
wb=load_workbook(file_name)
sheet=wb[sheet_name]
test_data=[]for i inrange(2,sheet.max_row+1):#
row_data={}
row_data['case_id']= sheet.cell(i,1).value #Column 1 row 2
row_data['url']=sheet.cell(i,2).value
row_data['data']= sheet.cell(i,3).value
row_data['title']= sheet.cell(i,4).value
row_data['http_method']= sheet.cell(i,5).value
test_data.append(row_data)return test_data
# Write the results into Excel
def write_back(self,file_name,sheet_name,i,value):#Write back data specifically
wb=load_workbook(file_name)
sheet=wb[sheet_name]
sheet.cell(i,6).value=value
wb.save(file_name)#Save result
if __name__ =='__main__':
test_data=DoExcel().get_data("../test_data/test_data_xiejinjieguo_zhuce.xlsx",'login')print(test_data)
File run_xiejinjieguo_zhuce.py
# Entrance to execute code
from tools.http_request_xiejinjieguo_zhuce import HttpRequest
from tools.do_excel_xiejinjieguo_zhuce import DoExcel
def run(test_data):#The data format of the list nested dictionary comes in
for item in test_data:print("The use case being tested is:{0}".format(item['title']))
res =HttpRequest().http_request(item['url'],eval(item['data']),item['http_method'])#Traverse,Value
print("The result of the request is:{0}".format(res.json()))DoExcel().write_back("test_data/test_data_xiejinjieguo_zhuce.xlsx",'register', item['case_id']+1,str(res.json()))#item['case_id']Number of rows represented
# item['case_id']+1 means in the second line
test_data=DoExcel().get_data("test_data/test_data_xiejinjieguo_zhuce.xlsx",'register')run(test_data)
Created file test_data_xiejinjieguo_zhuce.xlsx, file http_request_xiejinjieguo_zhuce.py, file do_excel_xiejinjieguo_zhuce.py, file run_xiejinjieguo_zhuce.py, execute file run_xiejinjieguo_zhuce.py
The output is as follows:
Recharge-use global variables
file
test_data_xiejinjieguo_chongzhi.xlsx
File http_request_xiejinjieguo_chongzhi.py
import requests
classHttpRequest:
def http_request(self,url,data,http_method,cookie=None):try:if http_method.upper()=='POST':
res=requests.post(url,data,cookies=cookie)#If you don’t specify fromdata, json can only be passed in json format.
# The interface is in json format, and data needs to be specified, so it is written as json=data
elif http_method.upper()=='POST':
res=requests.post(url,data,cookies=cookie)else:print("The request method entered is incorrect")
except Exception as e:print("The request reported an error:{0}".format(e))
raise e
return res#Return result
if __name__ =='__main__':
# registered
# register_url='http://api.nnzhp.cn/api/user/add_stu'
# register_data={"name":"niuhanyang","grade":"Scorpio","phone":'18614711314'}
# log in
login_url='http://api.nnzhp.cn/api/user/login'
login_data={"username":"niuhanyang","passwd":'aA123456'}
# Recharge
recharge_url='http://api.nnzhp.cn/api/user/gold_add'
recharge_data={"stu_id":"2170","gold":'1'}
login_res=HttpRequest().http_request(login_url,login_data,'post')
recharge_res=HttpRequest().http_request(recharge_url,recharge_data,'post',login_res.cookies)print("Recharge result:{}".format(recharge_res.text))
File do_excel_xiejinjieguo_chongzhi.py
from openpyxl import load_workbook
classDoExcel:
def get_data(self,file_name,sheet_name):
wb=load_workbook(file_name)
sheet=wb[sheet_name]
test_data=[]for i inrange(2,sheet.max_row+1):#
row_data={}
row_data['case_id']= sheet.cell(i,1).value #Column 1 row 2
row_data['url']=sheet.cell(i,2).value
row_data['data']= sheet.cell(i,3).value
row_data['title']= sheet.cell(i,4).value
row_data['http_method']= sheet.cell(i,5).value
test_data.append(row_data)return test_data
# Write the results into Excel
def write_back(self,file_name,sheet_name,i,value):#Write back data specifically
wb=load_workbook(file_name)
sheet=wb[sheet_name]
sheet.cell(i,6).value=value
wb.save(file_name)#Save result
if __name__ =='__main__':
test_data=DoExcel().get_data("../test_data/test_data_xiejinjieguo_chongzhi.xlsx",'login')print(test_data)
The file run_xiejinjieguo_chongzhi.py uses global variables here
# Entrance to execute code
from tools.http_request_xiejinjieguo_chongzhi import HttpRequest
from tools.do_excel_xiejinjieguo_chongzhi import DoExcel
COOKIE=None
def run(test_data,sheet_name):#The data format of the list nested dictionary comes in
global COOKIE
for item in test_data:print("The use case being tested is:{0}".format(item['title']))
res =HttpRequest().http_request(item['url'],eval(item['data']),item['http_method'],COOKIE)#Traverse,Value
print(item['data'])if res.cookies:
COOKIE=res.cookies
print("The result of the request is:{0}".format(res.text))DoExcel().write_back("test_data/test_data_xiejinjieguo_chongzhi.xlsx", sheet_name, item['case_id']+1,str(res.text))#item['case_id']Number of rows represented
# item['case_id']+1 means in the second line
test_data=DoExcel().get_data("test_data/test_data_xiejinjieguo_chongzhi.xlsx",'recharge')run(test_data,'recharge')'''
Error:
raise ValueError("Cannot convert {0!r} to Excel".format(value))
ValueError: Cannot convert {'error_code':0,'login_info':{'login_time':'20200211084505','sign':'748fc064f1e33a00fad24b2600381c72','userId':2170}} to Excel
The prompt cannot be written into Excel, only numbers and strings can be written, then force conversion and add str
'''
# i is the row, j is the column
The file test_data_xiejinjieguo_chongzhi.xlsx has been created,
File http_request_xiejinjieguo_chongzhi.py,
File do_excel_xiejinjieguo_chongzhi.py,
File run_xiejinjieguo_chongzhi.py, execute file run_xiejinjieguo_chongzhi.py
The output is as follows:
Recharge-reflection
File http_request_xiejinjieguo_fanshe.py
import requests
classHttpRequest:
def http_request(self,url,data,http_method,cookie=None):try:if http_method.upper()=='POST':
res=requests.post(url,data,cookies=cookie)#If you don’t specify fromdata, json can only be passed in json format.
# The interface is in json format, and data needs to be specified, so it is written as json=data
elif http_method.upper()=='POST':
res=requests.post(url,data,cookies=cookie)else:print("The request method entered is incorrect")
except Exception as e:print("The request reported an error:{0}".format(e))
raise e
return res#Return result
if __name__ =='__main__':
# registered
# register_url='http://api.nnzhp.cn/api/user/add_stu'
# register_data={"name":"niuhanyang","grade":"Scorpio","phone":'18614711314'}
# log in
login_url='http://api.nnzhp.cn/api/user/login'
login_data={"username":"niuhanyang","passwd":'aA123456'}
# Recharge
recharge_url='http://api.nnzhp.cn/api/user/gold_add'
recharge_data={"stu_id":"2170","gold":'1'}
login_res=HttpRequest().http_request(login_url,login_data,'post')
recharge_res=HttpRequest().http_request(recharge_url,recharge_data,'post',login_res.cookies)print("Recharge result:{}".format(recharge_res.text))
File do_excel_xiejinjieguo_fanshe.py
from openpyxl import load_workbook
classDoExcel:
def get_data(self,file_name,sheet_name):
wb=load_workbook(file_name)
sheet=wb[sheet_name]
test_data=[]for i inrange(2,sheet.max_row+1):#
row_data={}
row_data['case_id']= sheet.cell(i,1).value #Column 1 row 2
row_data['url']=sheet.cell(i,2).value
row_data['data']= sheet.cell(i,3).value
row_data['title']= sheet.cell(i,4).value
row_data['http_method']= sheet.cell(i,5).value
test_data.append(row_data)return test_data
# Write the results into Excel
def write_back(self,file_name,sheet_name,i,value):#Write back data specifically
wb=load_workbook(file_name)
sheet=wb[sheet_name]
sheet.cell(i,6).value=value
wb.save(file_name)#Save result
if __name__ =='__main__':
test_data=DoExcel().get_data("../test_data/test_data_xiejinjieguo_chongzhi.xlsx",'login')print(test_data)
File get_cookie.py
# reflection:No matter when and where, just pass in a class name and it can do it for you.
classGetCookie:
Cookie=None
# setattr(GetCookie,'Cookie','123456')#set attribute set attribute value
# print(hasattr(GetCookie,'Cookie'))#has attribute determines whether there is this attribute
# delattr(GetCookie,'Cookie')#delete attribute delete this attribute
# print(getattr(GetCookie,'Cookie'))#get attribute Get attribute value
File run_xiejinjieguo_fanshe.py
# Entrance to execute code
from tools.http_request_xiejinjieguo_fanshe import HttpRequest
from tools.do_excel_xiejinjieguo_fanshe import DoExcel
from tools.get_cookie import GetCookie
def run(test_data,sheet_name):#The data format of the list nested dictionary comes in
for item in test_data:print("The use case being tested is:{0}".format(item['title']))
res =HttpRequest().http_request(item['url'],eval(item['data']),item['http_method'],getattr(GetCookie,'Cookie'))#Traverse,Value
print(item['data'])if res.cookies:setattr(GetCookie,'Cookie',res.cookies)#reflection
# The reflection will take effect when the current file is running, and the attribute value will be regenerated the next time it is run.
print("The result of the request is:{0}".format(res.text))DoExcel().write_back("test_data/test_data_xiejinjieguo_chongzhi.xlsx", sheet_name, item['case_id']+1,str(res.text))#item['case_id']Number of rows represented
# item['case_id']+1 means in the second line
test_data=DoExcel().get_data("test_data/test_data_xiejinjieguo_chongzhi.xlsx",'recharge')run(test_data,'recharge')
Created test_data_xiejinjieguo_chongzhi.xlsx, file http_request_xiejinjieguo_fanshe.py,
File do_excel_xiejinjieguo_fanshe.py, file get_cookie.py,
File run_xiejinjieguo_fanshe.py, execute file run_xiejinjieguo_fanshe.py
The output is as follows:
The registration interface is submitted by dictionary type
Because the registration interface is submitted by dictionary type, the default is form submission, using json.dumps().
import requests
import json
register_url='http://api.nnzhp.cn/api/user/add_stu'
register_data={"name":"niuhanyang","grade":"Scorpio","phone":'18614711314'}
res=requests.post(register_url,data=json.dumps(register_data))print("json parsed result",res.json())'''
Post request method: post(url,data=None,json=None,**kwargs)
data is the formal parameter of the post method
register_data is your actual parameter,data is a formal parameter
It is understood as assigning your dictionary data to the parameters of this post
This is a dictionary type submission. The default is form submission, using json.dumps()
json.dumps()It's not to transfer the form, use json.dumps()Only applicable here.
'''
login_url='http://api.nnzhp.cn/api/user/login'
login_data={"username":"niuhanyang","passwd":'aA123456'}
login_res=requests.post(login_url,login_data)print("json parsed result",login_res.json())
# When to use json()For analysis? Only when the data you return is of this type.
recharge_url='http://api.nnzhp.cn/api/user/gold_add'
recharge_data={"stu_id":"2170","gold":'1'}
# header={"User-Agent":"Mozilla/5.0"}
# recharge_res=requests.post(recharge_url,recharge_data,cookies=login_res.cookies,headers=header)
# print("json parsed result",recharge_res.json())
# print("Request header:",recharge_res.request.headers)'''
common problem:
1. The address is wrong, or the address contains spaces.
2. The result cannot be json()If you can't find the problem if you analyze it in a way, just use the text method to analyze it.
3. Error message: ValueError:Expecting value:line 1 colunm 1(char 0)
When this prompt appears, the address is definitely wrong.
'''
# Expansion point:
# Keep all requests under the session
# s=requests.session()#Created a session
# login_res=s.post(login_url,login_data)#If it is a get request, add params=,For details, see the get source code, there is only one positional parameter, so you have to add a positional parameter yourself
# recharge_res=s.post(recharge_url,recharge_data)
# print("The result of the recharge is:",recharge_res.json())
Expansion points
session: to ensure the validity of the session.
Cookie: Prove to the server that it is a request initiated by the same user, then cookies must be added to all requests.
token: authentication. Prevent illegal access.
Key: Authorization, only the key can be accessed.
The difference between Json and Python Dict
json is a string and dict is a data structure.
Actual effect:
json.loads() converts JSON string to Python dictionary format.
json.dumps() converts python dictionary to JSON string.
Post request
There are generally 4 types of Post requests, namely:
1、 application/x-www-form-urlencoded browser native form
2、 multipart/form-data
3、 application/json
4、 text/xml text format
So you need to add the requested content type Content-Type: XXXXX
**Python processing Excel data is usually written **
def excel_to_list(data_file, sheet):
data_list =[] #Create an empty list to load all the data
wb = xlrd.open_workbook(data_file) #Open excel
sh = wb.sheet_by_name(sheet) #Get workbook
header = sh.row_values(0) #Get header row data
for i inrange(1, sh.nrows): #Skip the header row and start fetching data from the second row
d =dict(zip(header, sh.row_values(i))) #Assemble the title and each row of data into a dictionary
data_list.append(d)return data_list #List nested dictionary format, each element is a dictionary
to sum up
Pit encountered today:
-
As long as the code is wrong, an error will be reported, and there are blank lines in excel, and then remember to delete the blank lines when changing back to the code.
-
The online interface may have bugs, or the interface documents may be written in json format, but in fact there are json and html formats.
Only the return value of json type supports json, html and json are not supported here. html, json() format, you can use text format to get.
The recharge interface of this website (http://doc.nnzhp.cn/index.php?s=/6&page_id=11) is not in json format, but in json and html format, the interface documentation is wrong! Only available in text format!
-
Don't be careless, right-click to copy and copy files in pycharm, the code may change!
-
The values under url, http_method, title, and case_id in excel do not have double quotation marks. Except that the value copied in data has its own double quotation marks, there are no double quotation marks. No need to add quotation marks.
-
It has been found that the values in Excel must be sorted strictly according to the Excel format, with numbers on the right, and values in other formats on the left, without extra spaces.
**Note: Involving Excel writing operations, you must turn off Excel. **
Recommended Posts