企业官网建设流程全解析

实操练习

有如下的表
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 score=78 where sid=(SELECT sid from student where sname = '王五') and subject = '数学' -- (4). 改：所有班级 1 的学生，年龄统一 + 1。 UPDATE student set age = age+1 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.sid=score.sid where subject = '语文' and score.score>90 -- (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 > 85

test_003

import 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",params=register_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,data=None,json=None,**kwargs): try: r = self.session.post(url,data=data,json=json,**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", encoding='utf-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"))

member

def 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,data=data) def login(base_url,br,data): url = base_url + "/futureloan/mvc/api/member/login" return br.post(url,data=data) def recharge(base_url,br,data): url = base_url + "/futureloan/mvc/api/member/recharge" return br.post(url,data=data)

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_register

import pytest from ApiAutoTest.futureloan.baw import member from ApiAutoTest.futureloan.caw.readfile import read_yaml @pytest.fixture(params=read_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,data=register_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']