我使用的是Python Flask和MySQL。我从应用程序得到输入的名称,价格和数量从MySQL搜索,以便所有的数据。在
输出如下:name | Price | Volume
Screw | 5.0 | 700
iron | null | 67
wood | 23 | null
metal | 76 | 56
plywood| 100 | null
rebar | 75 | 59
steel | null | 87
L steel| 78 | 65
我需要的是,当我选择一个特定的体积范围时,我希望排除体积为零,但价格包含零,反之亦然。在
场景1:
选择60到100之间的数量,选择所有形式的价格和任何名称。
电流输出为:
^{pr2}$
我需要的输出:name | Price | Volume
iron | null | 67
steel | null | 87
L steel| 78 | 65
场景2:
选择价格在50到120之间,选择所有表单卷和任何名称。在
电流输出:name | Price | Volume
iron | null | 67
metal | 76 | 56
plywood| 100 | null
rebar | 75 | 59
steel | null | 87
L steel| 78 | 65
我需要的输出:name | Price | Volume
metal | 76 | 56
plywood| 100 | null
rebar | 75 | 59
L steel| 78 | 65
以下是我的代码:@app.route('/ABC/search1', methods=['GET'])
def ABCsearch1():
name = request.args.get('name',default='',type=str)
priceMin = request.args.get('priceMin',default='',type=str)
priceMax = request.args.get('priceMax',default='',type=str)
volMin = request.args.get('volMin',default='',type=str)
volMax = request.args.get('volMax',default='',type=str)
limit = request.args.get('limit',default=0,type=int)
offSet = request.args.get('offSet',default=0,type=int)
query = """ SELECT * FROM KLSE WHERE (Stock LIKE :s0 or Name LIKE :s1 or Number LIKE :s2)
AND (Price BETWEEN (IF(:s3='_',-5000,:s4)) AND (IF(:s5='_',5000,:s6)) OR Price IS NULL)
AND (Volume BETWEEN (IF(:s7='_',-5000,:s8)) AND (IF(:s9='_',5000,:s10)) OR Volume IS NULL)
LIMIT :s95 OFFSET :s96 """
query = text(query)
input = {'s0':name+"%",'s1':name+"%",'s2':name+"%",'s3':priceMin,'s4':priceMin,'s5':priceMax,'s6':priceMax,'s7':volMin,'s8':volMin,'s9':volMax,'s10':volMax,
's95':limit,'s96':offSet}
try:
call = db.session.execute(query,input)
f = call.fetchall()
col = ['index','Name','Number','Price','id']
f1 = [OrderedDict(zip(col,t)) for t in f]
except Exception:
return 'Error'
return jsonify({'Stock': f1})