python requests接口测试系列：连接mysql，获取mysql查询的值作为接口的入参
主要思路：
- 连接mysql数据库，这里数据库需要使用Proxifier来设置代理，然后才能正常连接
- 获取mysql数据库中某一数据，作为接口的参数信息
- 将接口返回结果保存至csv数据表中
# -*- coding:utf-8 -*-
'''
主要功能：
1、连接mysql数据库
2、将返回结果保存至本地文件
'''
import pymysql
import urllib.request
import requests
import json
class TestMysql():
def __int__(self):
self.address = "mysql://127.0.0.1/"
#连接mysql
def connect(self):
flag_init = 0
error_init = 0
error_fail_init = 0
# 初始化接口参数
login_url = 'https://api.ty.com/oauth2/token?grant_type=password&sms_verify=true'
login_header = {"Authorization": "Basic E23werredW3uY243hY5guaXBob76lOrdfREWdfZtrFtfYtfggREWDYTE="}
login_data = {
"username": "15012345678",
"password": "123456",
"appName": "hea-by",
"grant_type": "password",
"sms_verify": "true"
}
# 登录请求接口
r_login = requests.post(url=login_url, data=login_data, headers=login_header)
# api_token = '785f510b-95d1-4bfe-8b4b-faa7fdbc8c23'
#连接mysql
conn = pymysql.connect(host = 'mysql.beta.ty.net',
port = 3306,
user = 'root',
password = '123456',
db = 'test',charset='utf8')
# conn.set_character_set('utf-8')
# 通过获取到的数据库连接conn下的cursor()方法来创建游标
cur = conn.cursor()
# cur.execute("set NAMES gbk") # 一般到这一步就行了
# cur.execute('SET CHARACTER SET gbk;')
# cur.execute('SET character_set_connection=gbk;')
# 打印查询数据库的表中符合的数据数目
test_name_count=cur.execute("select `name` from t_test_nutrition_info;")
print(test_name_count)
# 打印表中的多少数据
test_name_info = cur.fetchmany(test_name_count)
for i in test_name_info:
print(i)
print(type(i))
x = list(i)
print(x)
#接口调用
food_data = {
"msg": x[0]
}
print(x[0])
# list转换为str，下面响应无结果使用
food_string = "".join(x[0])
print(type(food_string))
print("请求食材名称:" + food_string)
print(food_data)
# 获取响应报文
print(r_login.text)
print(type(r_login))
response = json.loads(r_login.text)
access_token = response['access_token']
food_headers = {"Authorization": "Bearer " + access_token}
food_url = 'https://api.ty.com/food_detect'
r_food = requests.post(url = food_url,json= food_data,headers = food_headers) #发送请求接口
# 获取响应报文
# 转换为dict格式
food_response = json.loads(r_food.text)
print(r_food)
print(r_food.json())
print(type(r_food.json()))
print(type(food_response))
# 判断响应结果是否为空，不为空，则获取dict中的第一个
if food_response:
print(food_response[0])
food_response_one = food_response[0]
# 取得AI识别的食物名称
cal_name = food_response_one['properties']['cal_name']
print(cal_name)
# 取得输入的食物名称
food_name = food_response_one['properties']['name']
print(food_name)
# 判断食物和AI是否一致
if cal_name == food_name:
flag_init = flag_init + 1
print("食材匹配成功：%d" %flag_init)
# 首先将字符串转换为list或dict
print(type(cal_name))
food_dict = eval("{'cal_name':cal_name,'name':food_name}")
print(food_dict)
print(type(food_dict))
# 将字典转换为json格式
food_json =json.dumps(food_dict,ensure_ascii= False)
# 识别成功的食材追加保存在文件success_food.json中
file_save = open('success_food.json','a')
file_save.write(food_json)
file_save.close()
else:
print(food_name)
error_init = error_init + 1
print("食材匹配失败：%d" %error_init)
# 写入本地保存匹配失败的食材名称
food_error_dict = eval("{'cal_name':cal_name,'name':food_name}")
#将字典转换为json格式
food_error_json = json.dumps(food_error_dict,ensure_ascii=False)
# 识别识别的食材保存在fail_food.json文件中
file_error_save = open('error_food.json','a')
file_error_save.write(food_error_json)
file_error_save.close()
else:
error_fail_init = error_fail_init + 1
print("食材没有找到%d"%error_fail_init )
# 写入本地保存没有找到的食材名称
food_fail_dict = eval("{'name':food_string}")
# 将字典转换为json格式
food_fail_json = json.dumps(food_fail_dict,ensure_ascii= False)
file_fail_save = open('fail_food.json','a')
file_fail_save.write(food_fail_json)
file_fail_save.close()
# 重新保存文件success_food.json、error_food.json、fail_food.json
# food_response_one = food_response[0]
# cal_name = food_response_one['properties']['cal_name']
# print(cal_name)
#
# # 获取食物名称
# food_name = food_response_one['properties']['name']
# print(food_name)
# # 判断食物和AI是否一致
# if cal_name == food_name :
# flag_init = flag_init + 1
# print("食材匹配成功:%d" %flag_init )
# else:
# print(food_name)
# error_init = error_init + 1
# print("食材匹配失败:%d" %error_init)
# #获取响应状态码
# print(r_food.status_code)
# 关闭游标
cur.close()
# 关闭连接
conn.commit()
conn.close()
if __name__ == '__main__':
tm = TestMysql()
tm.connect()
print("----------测试结束----------")
该代码主要实现了连接数据库，将数据库中的某一数据保存为接口的一个参数，同时保存响应结果到本地文件。
