flask中mysql动态多条件查询,实现不确定数量参数的搜索
一、实现思路:
1、定义一个变量数组condition = [],存储查询参数
2、condition.append(),动态增加参数至该变量数组
3、利用filter(*condition)过滤查询
二、完整代码:
1 def get(self): 2 try: 3 # per_page = current_app.config['PER_PAGE'] # current_app获取 4 per_page = request.args.get('limit') 5 cp_no = request.args.get('cp_no') 6 user_tel = request.args.get('user_tel') 7 city = request.args.get('city') 8 town = request.args.get('town') 9 page = request.args.get('page') 10 is_solved = request.args.get('is_solved') 11 is_overtime = request.args.get('is_overtime') 12 condition = [] # 动态接收查询参数 13 print(user_tel) 14 if cp_no: 15 cp_no = str(cp_no) 16 condition.append(Complain.cp_no == cp_no) 17 if user_tel: 18 user_tel = str(user_tel) 19 condition.append(Complain.user_tel == user_tel) 20 if city: 21 city = str(city) 22 condition.append(Complain.city == city) 23 if town: 24 town = str(town) 25 condition.append(Complain.town == town) 26 if is_solved: 27 condition.append(Complain.is_solved == is_solved) 28 if is_overtime: 29 condition.append(Complain.is_overtime == is_overtime) 30 if per_page: 31 per_page = int(per_page) 32 if page: 33 page = int(page) 34 res = Complain.query.filter(*condition).order_by(Complain.cp_id).paginate(page,per_page) 35 data = paginateToDict(res.items)# 分页后转换为dic 36 total = res.total 37 else: 38 res = Complain.query.filter(*condition).order_by(Complain.cp_id).all() 39 data = queryToDict(res) 40 total = len(res) #总记录数 41 return jsonify({"data":data,"total":total,"code":20000}) 42 except: 43 db.session.rollback() 44 return jsonify({"data":"wrong","code":500}) 45 finally: 46 db.session.close()另一种方法
使用filter() 查询功能更灵活,所以大部分会使用filter() 结合 _or
students_filter = Students.query.filter( or_(Students.name == name, name == None), or_(Students.tel == tel, tel == None), or_(Students.email == email, email == None), ).all()