今日主要完成了查询功能,可以根据特定日期,一定日期范围,地区,国家,省份,市区共六项任意组合进行模糊查询。
由于遇到多条件的模糊查询,采用了判断各项数据是否为空,不为空则在sql语句末尾追加and (....like %...%)来实现。
并分为了两种情况:有日期与无日期。由于要进行图表联动,所以对于数据如何展示将在之后进行确定。
var btn = document.getElementById("query2"); btn.onclick = function(){ alert(123) var fromyear=document.getElementById("fromyear").value; var fromday=document.getElementById("fromday").value; var frommonth=document.getElementById("frommonth").value; var toyear=document.getElementById("toyear").value; var today=document.getElementById("today").value; var tomonth=document.getElementById("tomonth").value; var continent=document.getElementById("continent").value; var country=document.getElementById("country").value; var province=document.getElementById("province").value; var city=document.getElementById("city").value; $.ajax({ url:"/query2", data: {fromyear: fromyear,fromday:fromday,frommonth:frommonth, toyear:toyear,today:today,tomonth:tomonth, continent:continent,country:country,province:province,city:city}, success: function (data) { if(data.name!=""){ }else{ alert("对不起暂无这一天的数据") } }, error: function (xhr, type, errorThrown) { } }) }
@app.route('/query2') def get_query_data2(): fromyear=request.values.get("fromyear") frommonth=request.values.get("frommonth") fromday=request.values.get("fromday") toyear=request.values.get("toyear") tomonth=request.values.get("tomonth") today=request.values.get("today") continent=request.values.get("continent") country=request.values.get("country") province=request.values.get("province") city=request.values.get("city") if(fromday!=""): for date,country,confirm,dead,heal,nowConfirm in utils.get_query_like1(fromyear,frommonth,fromday,toyear,tomonth,today, continent,country,province,city): pass pass else: for date,country,confirm,dead,heal,nowConfirm in utils.get_query_like2(continent,country,province,city): pass pass return jsonify()
def get_query_like1(fromyear,frommonth,fromday,toyear,tomonth,today, continent,country,province,city): Year=str(fromyear) Month=str(frommonth) Day=str(fromday) ds = Year+"." + Month+"."+Day print(ds) tup = time.strptime(ds, "%Y.%m.%d") print(tup) ds = time.strftime("%Y-%m-%d", tup) Year=str(toyear) Month=str(tomonth) Day=str(today) ds1 = Year+"." + Month+"."+Day print(ds1) tup1 = time.strptime(ds1, "%Y.%m.%d") print(tup1) ds1 = time.strftime("%Y-%m-%d", tup1) sql = "select update_time,country,confirm,dead,heal,nowconfirm from world " "where (update_time between "+"'"+ds+"' and '"+ds1+"'"+") " if (continent != ""): sql = sql + "and (continent like " + '"%' + continent + '%") ' if (country != ""): sql = sql + "and (country like " + '"%' + country + '%") ' if (province != ""): sql = sql + "and (province like " + '"%' + province + '%") ' if (city != ""): sql = sql + "and (city like " + '"%' + city + '%") ' res = query(sql) return res def get_query_like2(continent,country,province,city): sql = "select update_time,country,confirm,dead,heal,nowconfirm from world where 1=1 " if(continent!=""): sql=sql+"and (continent like "+'"%'+continent+'%") ' if(country!=""): sql=sql+"and (country like "+'"%'+country+'%") ' if(province!=""): sql=sql+"and (province like "+'"%'+province+'%") ' if(city!=""): sql=sql+"and (city like "+'"%'+city+'%") ' print(sql) res = query(sql) return res
对查询区块稍微做了美化:
.mainbox .column2 .date { background: #d7f7fa; padding: 1px; width: 40px; font-size: 10px; font-weight: 300; } .mainbox .column2 .area { background: #d7f7fa; padding: 1px; width: 80px; font-size: 10px; font-weight: 300; }
<font id="text">from</font><input type="text" id="fromyear" class="date">年 <input type="text" id="frommonth" class="date">月 <input type="text" id="fromday" class="date">日 <font id="text">to</font> <input type="text" id="toyear" class="date">年 <input type="text" id="tomonth" class="date">月 <input type="text" id="today" class="date">日<br> <input type="text" id="continent" class="area">地区 <input type="text" id="contry" class="area">国家 <input type="text" id="province" class="area">省 <input type="text" id="city" class="area">市 <Button id="query2" >查询</Button> <hr>