1.精确查询
-
单条件–精确查询
from db_modules import Students
from flask_restful import reqparse, Resource
class StudentsAPI(Resource):
def __init__(self):
self.parser = reqparse.RequestParser()
self.parser.add_argument("st_id", type=str)
self.parser.add_argument("name", type=str)
self.parser.add_argument("classID", type=str)
self.parser.add_argument("remark", type=str)
def get(self):
args = self.parser.parse_args()
key_st_id = args.st_id
key_name = args.name
key_classID = args.classID
key_remark = args.remark
all_results = Students.query.filter_by(classID=key_classID).all()
data_list = list()
if all_results:
for i in all_results:
dict_one = i.to_dict()
print(dict_one, "--------")
data_list.append(dict_one)
value_msg = "success"
else:
value_msg = "couldn't search any infomation"
result = {
"status": 200,
"msg": value_msg,
"result": data_list
}
return result
结果：
{
status: 200,
msg: "success",
result: [
{
classID: 21,
gender: "1",
st_id: 10001,
remark: "小明是位可爱的孩子",
age: 18,
name: "小明"
},
{
classID: 21,
gender: "1",
st_id: 10003,
remark: "大牛是位勇敢的孩子",
age: 19,
name: "大牛"
},
{
classID: 21,
gender: "1",
st_id: 10006,
remark: "黑马王子",
age: 22,
name: "古天乐"
}
]
}
-
多条件–精确查询
# 将单条件中的查询代码改为下面的代码
all_results = Students.query.filter_by(classID=key_classID, name=key_name).all()
2.模糊查询
-
1.单条件–模糊查询
from db_modules import Students
from flask_restful import reqparse, Resource
class StudentsAPI(Resource):
def __init__(self):
self.parser = reqparse.RequestParser()
self.parser.add_argument("st_id", type=str)
self.parser.add_argument("name", type=str)
self.parser.add_argument("classID", type=str)
self.parser.add_argument("remark", type=str)
def get(self):
args = self.parser.parse_args()
key_st_id = args.st_id
key_name = args.name
key_classID = args.classID
key_remark = args.remark
all_results = Students.query.filter(
Students.remark.like("%" + key_remark + "%") if key_remark is not None else ""
).all()
data_list = list()
if all_results:
for i in all_results:
dict_one = i.to_dict()
print(dict_one, "--------")
data_list.append(dict_one)
value_msg = "success"
else:
value_msg = "couldn't search any infomation"
result = {
"status": 200,
"msg": value_msg,
"result": data_list
}
return result
结果:
{
status: 200,
msg: "success",
result: [
{
classID: 22,
gender: "1",
st_id: 10007,
remark: "不是所有牛奶都叫特仑苏",
age: 23,
name: "陈道明"
}
]
}
-
多条件–模糊查询
all_results = Students.query.filter(
Students.st_id.like("%" + key_st_id + "%") if key_st_id is not None else "",
Students.name.like("%" + key_name + "%") if key_name is not None else "",
Students.remark.like("%" + key_remark + "%") if key_remark is not None else "",
Students.classID.like("%" + key_classID + "%") if key_classID is not None else ""
).all()
结果：
{
status: 200,
msg: "success",
result: [
{
classID: 21,
gender: "1",
st_id: 10001,
remark: "小明是位可爱的孩子",
age: 18,
name: "小明"
},
{
classID: 22,
gender: "1",
st_id: 10007,
remark: "不是所有牛奶都叫特仑苏",
age: 23,
name: "陈道明"
}
]
}
3.精确 & 模糊混合查询
-
先精确查询----再模糊查询
all_results = Students.query.filter_by(classID = key_classID).filter(
Students.st_id.like("%" + key_st_id + "%") if key_st_id is not None else "",
Students.name.like("%" + key_name + "%") if key_name is not None else "",
Students.remark.like("%" + key_remark + "%") if key_remark is not None else ""
).all()
结果：
{
status: 200,
msg: "couldn't search any infomation",
result: [ ]
}
结果：
{
status: 200,
msg: "success",
result: [
{
classID: 22,
gender: "1",
st_id: 10007,
remark: "不是所有牛奶都叫特仑苏",
age: 23,
name: "陈道明"
}
]
}
-
多条件或查询(or_)
from sqlalchemy import or_ # 这个是需要额外导入的方法
all_results = Students.query.filter(
or_(Students.st_id.like("%" + key_st_id + "%") if key_st_id is not None else "",
Students.name.like("%" + key_name + "%") if key_name is not None else "",
Students.remark.like("%" + key_remark + "%") if key_remark is not None else "",
Students.classID.like("%" + key_classID + "%") if key_classID is not None else "")
).all()
浏览器输入：http://127.0.0.1:5000/student?name=花&st_id=10005&remark=牛奶
多个条件，每个条件单独满足即可，最终结果为所有集合的汇总
结果：
{
status: 200,
msg: "success",
result: [
{
classID: 22,
gender: "0",
st_id: 10004,
remark: "花花是位懂事的孩子",
age: 17,
name: "花花"
},
{
classID: 23,
gender: "1",
st_id: 10005,
remark: "tony来自美国",
age: 20,
name: "tony"
},
{
classID: 22,
gender: "1",
st_id: 10007,
remark: "不是所有牛奶都叫特仑苏",
age: 23,
name: "陈道明"
}
]
}
-
多条件或查询(and_)
from sqlalchemy import and_ # 这个是需要额外导入的方法
all_results = Students.query.filter(
and_(Students.st_id.like("%" + key_st_id + "%") if key_st_id is not None else "",
Students.name.like("%" + key_name + "%") if key_name is not None else "",
Students.remark.like("%" + key_remark + "%") if key_remark is not None else "",
Students.classID.like("%" + key_classID + "%") if key_classID is not None else "")
).all()
这些基本的方法相互组合就可以满足一般情况，当然还有其他更复杂的功能，请参考文档，这里就不多做介绍。
