flask_sqlalchemy

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()