Flask-SQLAlchemy查询方法汇总 - 单一表内查询

1. 精确查询

• a) 单条件–精确查询

  all_results = Students.query.filter_by(classID=key_classID).all()
  

• b) 多条件–精确查询

  all_results = Students.query.filter_by(classID=key_classID, name=key_name).all()
  

2. 模糊查询

• a) 单条件–模糊查询

  all_results = Students.query.filter(
          Students.remark.like("%" + key_remark + "%") if key_remark is not None else ""
      ).all()
  

• b) 多条件–模糊查询

      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. 精确 & 模糊混合查询

• a) 先精确查询----再模糊查询

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