实操练习有如下的表CREATE TABLE student(sid INT PRIMARY KEY AUTO_INCREMENT COMMENT 学生编号,sname VARCHAR(20) NOT NULL COMMENT 学生姓名,sex CHAR(1) COMMENT 性别男/女,age TINYINT COMMENT 年龄,class_id INT COMMENT 班级编号,birthday DATE COMMENT 生日);INSERT INTO student(sname,sex,age,class_id,birthday) VALUES(张三,男,18,1,2008-02-10),(李四,女,17,1,2009-05-15),(王五,男,18,2,2008-08-22),(赵六,女,19,2,2007-12-03),(孙七,男,17,1,2009-01-20),(周八,女,18,3,2008-06-18),(吴九,男,19,3,2007-09-09),(郑十,女,17,2,2009-11-11);CREATE TABLE score(scid INT PRIMARY KEY AUTO_INCREMENT COMMENT 成绩ID,sid INT COMMENT 学生编号关联student.sid,subject VARCHAR(20) NOT NULL COMMENT 科目,score TINYINT COMMENT 分数0-100);-- 测试数据INSERT INTO score(sid,subject,score) VALUES(1,语文,88),(1,数学,92),(1,英语,76),(2,语文,95),(2,数学,80),(2,英语,90),(3,语文,72),(3,数学,65),(3,英语,83),(4,语文,81),(4,数学,96),(4,英语,89),(5,语文,66),(5,数学,59),(5,英语,70),(6,语文,91),(6,数学,87),(6,英语,93),(7,语文,55),(7,数学,62),(7,英语,68),(8,语文,79),(8,数学,85),(8,英语,77);-- (1). 新增往学生表插入一名新学生姓名「钱十一」女18 岁班级 3生日 2008-03-25。 INSERT INTO student(sname,sex,age,class_id,birthday) VALUES (钱十一,女,18,3,2008-03-25) -- (2). 新增给学生钱十一添加 3 科成绩语文 82、数学 75、英语 94。 INSERT INTO score(sid,subject,score) VALUES ((SELECT sid s from student where sname 钱十一),语文,82),((SELECT sid s from student where sname 钱十一),数学,75),((SELECT sid s from student where sname 钱十一),英语,94) -- (3). 改将王五的数学成绩改为 78 分。 UPDATE score set score78 where sid(SELECT sid from student where sname 王五) and subject 数学 -- (4). 改所有班级 1 的学生年龄统一 1。 UPDATE student set age age1 where class_id 1 -- (5). 删删除数学成绩低于 60 分的成绩记录。 DELETE from score where subject 数学 and score 60 -- (6). 单表查询查询所有女生显示姓名、年龄、班级按年龄降序。 SELECT sname,age,class_id from student where sex 女 ORDER BY age desc -- (7). 统计每个科目的平均分、最高分、最低分、总人数。 SELECT AVG(score),max(score),min(score),count(sid),subject from score GROUP BY subject -- (8). 统计每个学生一共有几门科目成绩只显示有 3 科成绩的学生 ID。 select sid from score GROUP BY sid HAVING count(subject) 3 -- (9). 统计各个班级的学生人数过滤人数≥3 人的班级。 select class_id,count(sid) c from student GROUP BY class_id HAVING c 3 -- (10). 查询每门科目不及格60的学生数量。 SELECT subject,count(*) from score where score 60 GROUP BY subject -- (11). 内连接两表查询所有学生姓名、科目、分数只看语文成绩。 select st.sname,sc.subject,sc.score from student st join score sc on st.sid sc.sid where sc.subject 语文 -- (12). 左连接查询所有学生信息 对应成绩没有成绩的学生科目显示「无成绩」。 SELECT student.*,IFNULL(score.score,无成绩) score from student left join score on student.sid score.sid -- (13). 查询姓名、班级、各科总分按总分从高到低排序。 SELECT student.sname,student.class_id,sum(score.score) sum_score from student join score on student.sid score.sid GROUP BY student.sid -- (14). 查询班级 2 所有学生的数学成绩展示姓名、数学分数。 SELECT student.sname,score.score math_score from student join score on student.sid score.sid where subject 数学 and student.class_id 2 -- (15). 查询语文分数大于 90 分的学生姓名、年龄、班级、语文成绩。 SELECT student.sname,student.age,student.class_id,score.score yuwen_score from student join score on student.sidscore.sid where subject 语文 and score.score90 -- (16). 查询平均分高于 85 分的学生展示姓名、平均分、总分。 SELECT student.sname,avg(score.score) a,sum(score.score) from student join score on student.sid score.sid GROUP BY student.sid HAVING a 85test_003import pytest import requests pytest.fixture(params[ {casename:5位密码报错, casedata:{mobilephone:17731548395,pwd:12345}, expect:{status:0,code:20108,msg:密码长度必须为6~18}}, {casename:19位密码报错, casedata:{mobilephone:17731548395,pwd:123456789abcedf1234}, expect:{status:0,code:20108,msg:密码长度必须为6~18}}, {casename:密码为空报错, casedata:{mobilephone:17731548395,pwd:}, expect:{status:0,code:20103,msg:密码不能为空}}, ]) def register_data(request): return request.param def test_register(register_data): print(f测试用例标题{register_data[casename]}) r requests.get(http://127.0.0.1:8088/futureloan/mvc/api/member/register,paramsregister_data[casedata]) assert r.json()[msg] register_data[expect][msg] assert r.json()[code] register_data[expect][code] assert r.json()[status] register_data[expect][status]baserequests import requests class BaseRequests: def __init__(self): self.session requests.session() def post(self,url,dataNone,jsonNone,**kwargs): try: r self.session.post(url,datadata,jsonjson,**kwargs) # 参数的透传 print(f发送post请求成功url{url}参数{data}、{json}、{kwargs}) print(f发送post请求成功响应体{r.text}) return r except Exception as e: print(f发送post请求失败url{url}参数{data}、{json}、{kwargs}) print(f发送post请求失败异常信息{e}) def get(self,url,params,**kwargs): try: r self.session.get(url,params,**kwargs) #参数的透传 print(f发送get请求成功url{url}参数{params}、{kwargs}) #log. print(f发送get请求成功响应体{r.text}) return r except Exception as e: print(f发送get请求失败url{url}参数{params}、{kwargs}) print(f发送get请求失败异常信息{e}) def put(self,url,data,**kwargs): try: r self.session.put(url,data,**kwargs) print(f发送put请求成功url{url}参数{data}、{kwargs}) # log. print(f发送put请求成功响应体{r.text}) return r except Exception as e: print(f发送put请求失败url{url}参数{data}、{kwargs}) print(f发送put请求失败异常信息{e}) def delete(self,url,**kwargs): try: r self.session.delete(url,**kwargs) print(f发送delete请求成功url{url}参数{kwargs}) # log. print(f发送delete请求成功响应体{r.text}) return r except Exception as e: print(f发送delete请求失败url{url}参数{kwargs}) print(f发送delete请求失败异常信息{e}) if __name__ __main__: br BaseRequests() br.get(http://127.0.0.1:8088/futureloan/mvc/api/member/register,params{mobilephone:17731548395}) br.post(http://127.0.0.1:8088/futureloan/mvc/api/member/register, data{mobilephone: 17731548395})readfile 获取项目路径 解析csv 解析ini 解析excel import configparser import os.path import openpyxl import yaml def get_project_path(): # 获取父路径 cf os.path.realpath(__file__) # readfile.py cd os.path.dirname(cf) #caw cd os.path.dirname(cd) #futureloan return cd / # 解析ini文件 def read_ini(path,key): # 获取完整的文件路径 path get_project_path() path print(path) cp configparser.ConfigParser() cp.read(path) value cp.get(env,key) return value def read_yaml(path): path get_project_path() path with open(path, r, encodingutf-8) as f: content f.read() # 字符串 # pyYaml return yaml.safe_load(content) # 列表[字典] # 解析excel文件 def read_excel(filename,sheet Sheet1): # 获取完整的文件路径 filepath os.path.join(get_project_path(), data, filename) # 加载整个表格文件 wb openpyxl.load_workbook(str(filepath)) # 获取对应工作表 wsh wb[sheet] rl [] for row in wsh: cells [] for cell in row: # 如果单元格是None存储空字符串 if not cell.value: cells.append() else: cells.append(cell.value) # 将子列表一行信息存储到大的列表中 rl.append(cells) return rl[1::] if __name__ __main__: print(read_ini(data/env.ini, base_url)) print(read_ini(data/env.ini, db_info)) print(read_yaml(data/register.yaml))memberdef register(base_url,br,data): :param base_url: 从env.ini读取出来 :param br: BaseRequests的实例 :param data: 注册的数据 :return: url base_url /futureloan/mvc/api/member/register return br.post(url,datadata) def login(base_url,br,data): url base_url /futureloan/mvc/api/member/login return br.post(url,datadata) def recharge(base_url,br,data): url base_url /futureloan/mvc/api/member/recharge return br.post(url,datadata)register.yaml- casename: 密码为空报错密码不能为空 casedata: {mobilephone: 18091234567, pwd: } expect: {status: 0, code: 20103, data: None, msg: 密码不能为空} - casename: 手机号码为空报错手机号不能为空 casedata: {mobilephone: , pwd: 123456} expect: {status: 0, code: 20103, data: None, msg: 手机号不能为空} - casename: 密码大于18位报错密码长度必须为6~18 casedata: {mobilephone: 18091234567, pwd: 1234567890123456789} expect: {status: 0, code: 20108, data: None, msg: 密码长度必须为6~18} - casename: 密码小于6位报错密码长度必须为6~18 casedata: {mobilephone: 18091234567, pwd: 12345} expect: {status: 0, code: 20108, data: None, msg: 密码长度必须为6~18}test_registerimport pytest from ApiAutoTest.futureloan.baw import member from ApiAutoTest.futureloan.caw.readfile import read_yaml pytest.fixture(paramsread_yaml(data/register.yaml)) def register_data(request): return request.param def test_register(register_data,base_url,br): print(f测试用例标题{register_data[casename]}) r member.register(base_url, br,dataregister_data[casedata]) assert r.json()[msg] register_data[expect][msg] assert r.json()[code] register_data[expect][code] assert r.json()[status] register_data[expect][status]
)
