def delfid(): taskname = request.args.get('fieldname') #reuqest对象:获取客户端get请求的参数值 print(taskname) conn = dbconnect.dbconnect(dbname, dbuser, dbpwd, dbhost, dbport) query = """ SELECT * FROM fieldlist WHERE fieldname = '%s'; """ % (taskname) queryresult = conn.sqlquery(query) print(queryresult) if queryresult is not None: #删除语句之前需先判定待删除的记录是否真实存在 conn = dbconnect.dbconnect(dbname, dbuser, dbpwd, dbhost, dbport) excute = """ delete from "public"."fieldlist" WHERE fieldname= '%s';""" % (taskname) print(excute) deleteresult = conn.sqlchange(excute) print(deleteresult) conn.close() if deleteresult is True: result = { 'msg': 'success', 'status': '200', 'result': 'delete one fielditem!' } else: result = {'status': '-1', 'errmsg': 'Failed delete one item!'} else: result = {'status': '-1', 'errmsg': 'please input right fieldname'} response = make_response(jsonify(result)) return response
def fieldinfo(): fieldname = request.args.get('fieldname') conn = dbconnect.dbconnect(dbname, dbuser, dbpwd, dbhost, dbport) query = """SELECT region,area,croptype,harvestdate,adddate,fielduser,id FROM fieldlist WHERE fieldname = '%s';""" % (fieldname) queryresult = conn.sqlquery(query) print(queryresult) if queryresult is not None: fieldinfo = {} for i in queryresult: ob = {'region': i[0]} ob['area'] = i[1] ob['croptype'] = i[2] ob['harvestdate'] = i[3] ob['adddate'] = i[4] ob['fielduser'] = i[5] fieldinfo[i[6]] = ob else: fieldinfo = {"result": "0"} conn.close() print(fieldinfo) response = make_response(jsonify(fieldinfo)) return response
def fieldupdate(): taskname = request.args.get('fieldname') region = request.args.get('region') print(taskname) conn = dbconnect.dbconnect(dbname, dbuser, dbpwd, dbhost, dbport) query = """ SELECT * FROM fieldlist WHERE fieldname = '%s';""" % (taskname) queryresult = conn.sqlquery(query) if queryresult is not None: excute = """ update "public"."fieldlist" SET "region" = '%s' WHERE fieldname= '%s';""" % (region, taskname) print(excute) changeresult = conn.sqlchange(excute) print(changeresult) if changeresult is True: result = { 'msg': 'success', 'status': '200', 'result': 'update one item!' } else: result = {'status': '-1', 'errmsg': 'Failed update one item!'} else: result = {'status': '-1', 'errmsg': 'please input right fieldname'} response = make_response(jsonify(result)) return response
def addwdata(): dbname, dbuser, dbpwd, dbhost, dbport = dbinfo conn = dbconnect.dbconnect(dbname, dbuser, dbpwd, dbhost, dbport) jsonstr = request.get_data() ##request对象:获取客户端post请求的参数值 jsondata = json.loads(jsonstr.decode('utf-8')) wid = jsondata.get("id_num") datatime = jsondata.get("datatime") temp_max = jsondata.get("temp_max") temp_min = jsondata.get("temp_min") rainfall = jsondata.get("rainfall") temp_acc_avg = jsondata.get("temp_acc_avg") excute = """ INSERT INTO "public"."weather"("id_num","datatime","temp_max","temp_min","rainfall","temp_acc_avg") VALUES('%s','%s','%s','%s','%s','%s')""" % ( wid, datatime, temp_max, temp_min, rainfall, temp_acc_avg) queryresult = conn.sqlchange(excute) print(queryresult) ####True if queryresult is True: response = make_response("Add new weatheritem!") return response else: response = make_response("Failed add weatheritem!") return response
def data(): dbname, dbuser, dbpwd, dbhost, dbport = dbinfo startdate = request.args.get('startdate') enddate = request.args.get('enddate') fieldname = request.args.get('fieldname') conn = dbconnect.dbconnect(dbname, dbuser, dbpwd, dbhost, dbport) fieldweather = {} query = """SELECT datatime,temp_max,temp_min,rainfall,temp_acc_avg FROM "public".weather,"public".fieldlist WHERE fieldname = '%s' AND weather.datatime BETWEEN '%s'AND '%s' AND weather.adc =fieldlist.adc """ % (fieldname, startdate, enddate ) #地块与天气的adc即区域代码需要对应 print(query) ##清楚看出查询语句的实例 queryresult = conn.sqlquery(query) conn.close() print(queryresult) ##查询的结果 print('------------------') if queryresult is not None: for i in queryresult: #ob ={'datatime':i[0]} ob = {'temp_max': i[1]} ob['temp_min'] = i[2] ob['rainfall'] = i[3] ob['temp_acc_avg'] = i[4] #fieldweather[ob['datatime']] = ob ##key值的设定关系着输出结果的呈现 fieldweather[i[0]] = ob else: fieldweather = {"result": "0"} listjson = jsonify(fieldweather) response = make_response(listjson) return response
def flist(): conn = dbconnect.dbconnect(dbname, dbuser, dbpwd, dbhost, dbport) flist = [] query = """ SELECT fieldname FROM fieldlist; """ queryresult = conn.sqlquery(query) print(queryresult) if queryresult is not None: for i in queryresult: flist.append(i[0]) response = make_response(jsonify(flist)) return response
def typelistload(): conn = dbconnect.dbconnect(dbname, dbuser, dbpwd, dbhost, dbport) typelist = [] queryresult = conn.sqlquery("""SELECT distinct croptype FROM fieldlist ORDER BY croptype;""") conn.close() print(queryresult) if queryresult is not None: for i in queryresult: typelist.append(i[0]) listjson = jsonify(typelist) response = make_response(listjson) return response
def fieldtype(): conn = dbconnect.dbconnect(dbname, dbuser, dbpwd, dbhost, dbport) croptype = {} query = """SELECT croptype,array_agg(fieldname) FROM fieldlist GROUP BY croptype;""" queryresult = conn.sqlquery(query) conn.close() print(queryresult) if queryresult is not None: for i in queryresult: croptype[i[0]] = i[1] listjson = jsonify(croptype) print(listjson) response = make_response(listjson) return response
def adddata(): dbname, dbuser, dbpwd, dbhost, dbport = dbinfo conn = dbconnect.dbconnect(dbname, dbuser, dbpwd, dbhost, dbport) jsonstr = request.get_data() print('----------------------------------') print(jsonstr) print(type(jsonstr)) #<class 'bytes'> print('------------------------------') print(jsonstr.decode('utf-8')) #浏览器需要根据文件指定编码如utf-8转换成 字符串 也就是html代码 print(type(jsonstr.decode('utf-8'))) #<class 'str'> jsondata = json.loads(jsonstr.decode('utf-8')) #转化为 字典 。 print('--------------------------------') print(jsondata) print(type(jsondata)) #<class 'dict'> #fid = str(uuid.uuid1()).replace('-','') fid = jsondata.get("id") fieldname = jsondata.get("fieldname") area = jsondata.get("area") croptype = jsondata.get("croptype") harvestdate = jsondata.get("harvestdata") adddate = jsondata.get("adddate") region = jsondata.get("region") fielduser = jsondata.get("fielduser") excute = """ INSERT INTO "public"."fieldlist"("id","fieldname","area","croptype","harvestdate","adddate","region","fielduser") VALUES('%s','%s','%s','%s','%s','%s','%s','%s')""" % ( fid, fieldname, area, croptype, harvestdate, adddate, region, fielduser) print('******') queryresult = conn.sqlchange(excute) print(queryresult) #True if queryresult is True: response = make_response({ 'msg': 'success', 'status': '200', 'result': 'add one fielditem!' }) return response else: response = make_response("Fail add data!") return response
def zbinf(): dbname, dbuser, dbpwd, dbhost, dbport = dbinfo fieldname = request.args.get('fieldname') startdate = request.args.get('startdate') enddate = request.args.get('enddate') conn = dbconnect.dbconnect(dbname, dbuser, dbpwd, dbhost, dbport) zbinfo = [] query = """ SELECT ndvi FROM zb_ndvi WHERE fieldname = '%s' AND datetime BETWEEN '%s' AND '%s' ; """ % ( fieldname, startdate, enddate) print(query) queryresult = conn.sqlquery(query) if queryresult is not None: for i in queryresult: zbinfo.append(i[0]) response = make_response(jsonify(zbinfo)) return response