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
輸入的內(nèi)容為：http://127.0.0.1:5000/student?classID=21
結(jié)果：
{
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
結(jié)果:
{
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()
結(jié)果：
{
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()
結(jié)果：
{
status: 200,
msg: "couldn't search any infomation",
result: [ ]
}
結(jié)果：
{
status: 200,
msg: "success",
result: [
{
classID: 22,
gender: "1",
st_id: 10007,
remark: "不是所有牛奶都叫特侖蘇",
age: 23,
name: "陳道明"
}
]
}
-
多條件或查詢(or_)
from sqlalchemy import or_ # 這個是需要額外導(dǎo)入的方法
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=牛奶
多個條件框往，每個條件單獨滿足即可，最終結(jié)果為所有集合的匯總
結(jié)果：
{
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_ # 這個是需要額外導(dǎo)入的方法
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()
這些基本的方法相互組合就可以滿足一般情況，當(dāng)然還有其他更復(fù)雜的功能，請參考文檔软啼，這里就不多做介紹。
