Flask-SQLAlchemy精确查询&模糊查询—ORM
st_id name gender age classID remark
10001 小明 1 18 21 小明是位可爱的孩子
10002 小红 0 18 22 小红是位聪明的孩子
10003 大牛 1 19 21 大牛是位勇敢的孩子
10004 花花 0 17 22 花花是位懂事的孩子
10005 tony 1 20 23 tony来自美国
10006 古天乐 1 22 21 黑马王子
10007 陈道明 1 23 22 不是所有牛奶都叫特仑苏
1.精确查询
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
2.多条件–精确查询
只有一行有变化,如下方式查询即可
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
2.多条件–模糊查询
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()
3.精确 & 模糊混合查询
1.先精确查询----再模糊查询
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()
4.多条件或查询(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()