Flask-SQLAlchemy精确查询&模糊查询---ORM（1）
程序员文章站
2024-03-03 17:49:34
...
文章目录
0.背景知识
students表格
| 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
{
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: "古天乐"
}
]
}
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
{
status: 200,
msg: "success",
result: [
{
classID: 22,
gender: "1",
st_id: 10007,
remark: "不是所有牛奶都叫特仑苏",
age: 23,
name: "陈道明"
}
]
}
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()
{
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.精确 & 模糊混合查询
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()
{
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: "陈道明"
}
]
}
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()
浏览器输入：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: "陈道明"
}
]
}