def post(self): json_from_request = json.loads( Converter('zh-hans').convert( request.stream.read().decode('utf-8'))) user_execute_work = json_from_request['task_info']['bot_execute'] log.info(json.dumps(json_from_request)) app_id = json_from_request['app_id'] log.info('app_id: ' + app_id) work_stats = 0 update_kv_map = {} try: conn = pymysql.Connect(host=const.DB_HOST, user=const.DB_ACCOUNT, passwd=const.DB_PASSWORD, charset='utf8') data = dao.Database(conn).query_bot_work_list(app_id) log.debug(data) if len(data) == 1: work_stats = data[0][1] except Exception as e: log.info("query_bot_work_list occured some error: " + utils.except_raise(e)) finally: conn.close() if work_stats == 0: try: data = {} data['bot_id'] = json_from_request['app_id'] data['work'] = user_execute_work conn = pymysql.Connect(host=const.DB_HOST, user=const.DB_ACCOUNT, passwd=const.DB_PASSWORD, charset='utf8') update_row = dao.Database(conn).insert_work_to_bot_work_list( data) except Exception as e: log.info("insert_work_to_bot_work_list occured some error: " + utils.except_raise(e)) finally: conn.close() log.info(user_execute_work) update_kv_map = { "bot_response": work_num_to_str(int(user_execute_work)) + '派車,成功' } else: update_kv_map = { "bot_response": '正在執行' + work_num_to_str(work_stats) + '任務,請稍後在下命令' } ret = encapsule_rtn_format(update_kv_map, None) return Response(json.dumps(ret), status=200)
def post(self): json_from_request = json.loads( Converter('zh-hans').convert( request.stream.read().decode('utf-8'))) app_id = json_from_request['app_id'] log.info('app_id: ' + app_id) try: conn = pymysql.Connect(host=const.DB_HOST, user=const.DB_ACCOUNT, passwd=const.DB_PASSWORD, charset='utf8') data = dao.Database(conn).query_bot_work_list(app_id) log.info(data) if len(data) == 1: result = { 'bot_id': data[0][0], 'work': data[0][1], 'return_flag': data[0][2], 'return_finish': data[0][3] } log.info(result) update_kv_map = { "bot_response": work_num_to_str(data[0][1]) + return_flag_to_str(data[0][2]) } ret = encapsule_rtn_format(update_kv_map, None) return Response(json.dumps(ret), status=200) except Exception as e: log.info("query_bot_work_list occured some error: " + utils.except_raise(e)) finally: conn.close()
def get_velocities(test_number): database = dao.Database('fydp') num = settings.test_number database.execute( "SELECT velocity FROM detailed_results WHERE test_id=(?)", (test_number, )) return [item[0] for item in database.fetchall()]
def diffInfo(self, before, after): db = dao.Database() cols = db.getCols() #获取不需要保存的字段 unchange = [] for col in cols: #editable if col[3] == 0: unchange.append(col[0]) result = [] for attr in before: #不可修改的属性跳过 if attr in unchange: continue #比较是否有变化 if not self.testEqual(before[attr], after[attr]): # print attr # print before[attr] # print after[attr] result.append(attr) #保持result与label的对应 label = [] for r in result: for c in cols: if c[0] == r: label.append(c[1]) break return result, label
def get_first_id_by_test_id(test_number): database = dao.Database('fydp') database.execute( "SELECT id FROM detailed_results WHERE test_id=(?) AND time=(?)", ( test_number, 0, )) return [item[0] for item in database.fetchall()]
def setNewRow(velocity, time, timestamp, sensor_id, pressure, test_section, overheat): database = dao.Database('fydp') database.execute( "INSERT INTO detailed_results(id, test_id, velocity, time, timestamp, sensor_id, pressure, test_section, overheat) VALUES (? , ? , ?, ?, ?, ?, ?, ?, ?)", (None, settings.test_number, velocity, time, timestamp, sensor_id, pressure, test_section, overheat)) database.commit()
def setTestStatus(testStatus): database = dao.Database('fydp') database.execute( "UPDATE result_by_id SET test_status=(?) WHERE id=(?)", ( testStatus, settings.test_number, )) database.commit()
def setSensorID(sensorID): database = dao.Database('fydp') database.execute("UPDATE result_by_id SET sensor_id=(?) WHERE id=(?)", ( sensorID, settings.test_number, )) database.commit()
def get_first_id_by_test_section(test_number, test_section): database = dao.Database('fydp') database.execute( "SELECT id FROM detailed_results WHERE test_id=(?) AND test_section=(?) LIMIT 1", ( test_number, test_section, )) return [item[0] for item in database.fetchall()]
def get_times_overheated(test_number): database = dao.Database('fydp') database.execute( "SELECT time FROM detailed_results WHERE test_id=(?) AND overheat=(?)", ( test_number, 1, )) return [item[0] for item in database.fetchall()]
def query_bot_work_list_test(): try: conn = pymysql.Connect(host='0.0.0.0',user='******',passwd='password',charset='utf8') data = dao.Database(conn).query_bot_work_list(None) log.info(len(data)) log.info(data) except Exception as e: log.info("query_bot_work_list occured some error: "+utils.except_raise(e)) finally: conn.close()
def migrate_db_test(): try: conn = pymysql.Connect(host='0.0.0.0',user='******',passwd='password',charset='utf8') dao.Database(conn).executeScriptsFromFile('./migrate/010200001_init.db') except Exception as e: log.info("SQL occured some error: "+utils.except_raise(e)) finally: try: conn.close() except Exception as e: log.info("SQL occured some error: "+utils.except_raise(e))
def getsysdetail(self, ename): db = dao.Database() cols = db.getCols() attr_str = ','.join([r[1] for r in cols]) sql = 'select ' + attr_str + u' from systable where 英文标识 = ?' result = db.select(sql, ename) if len(result) > 0: return self.wrap_cvalue(result[0], cols) else: return None
def insert_bot_work_list_test(): try: data = {} data['bot_id']='bot_id___' data['work']=1 data['return_flag']=1 data['return_finish']=1 conn = pymysql.Connect(host='0.0.0.0',user='******',passwd='password',charset='utf8') dao.Database(conn).insert_bot_work_list( data ) except Exception as e: log.info("insert_bot_work_list occured some error: "+utils.except_raise(e)) finally: conn.close()
def get(self): bot_id = request.args.get('bot_id') if bot_id == None: return { 'status': 422, 'message': "Missing required parameters [bot_id]", 'result': {}, }, 422 try: conn = pymysql.Connect(host=const.DB_HOST, user=const.DB_ACCOUNT, passwd=const.DB_PASSWORD, charset='utf8') data = dao.Database(conn).query_bot_work_list(bot_id) log.info(data) if len(data) == 1: return { 'status': 200, 'message': 'success', 'result': { 'bot_id': data[0][0], 'work': data[0][1], 'return_flag': data[0][2], 'return_finish': data[0][3] }, }, 200 else: return { 'status': 204, 'message': "bot_id: " + bot_id + " doesn't exist", 'result': {}, }, 204 except Exception as e: log.info("query_bot_work_list occured some error: " + utils.except_raise(e)) return { 'status': 500, 'message': "[{}] {}".format(e.__class__.__name__, e.args[0]) }, 500 finally: try: conn.close() except Exception as e: log.info("close connection error: " + utils.except_raise(e)) return { 'status': 500, 'message': "[{}] {}".format(e.__class__.__name__, e.args[0]) }, 500
def migrate_db(sql_path): try: conn = pymysql.Connect(host=const.DB_HOST, user=const.DB_ACCOUNT, passwd=const.DB_PASSWORD, charset='utf8') dao.Database(conn).executeScriptsFromFile(sql_path) except Exception as e: log.info("SQL occured some error: " + except_raise(e)) finally: try: conn.close() except Exception as e: log.info("SQL occured some error: " + except_raise(e))
def GET(self): data = web.input(keyword=None, page='1') keyword = data['keyword'] if data['page'].isdigit(): page = int(data['page']) else: page = 1 result, pagesize = self.search_sys(keyword, page) #获得版本信息 db = dao.Database() version = db.getGlobalVersion() return self.render().search(result, page, pagesize, keyword, version)
def POST(self): data = web.input(ename='', action='') action = data.action ename = data.ename #处理预提交 if action == 'presubmit': result = {'code': 0, 'msg': '', 'field': [], 'label': []} #file:///D:/Program/Reference/twitter-bootstrap/docs/components.html if not self.isEditable(): result['code'] = 1 result['msg'] = '数据库无法修改(可能由于离线、匿名登录或数据库不可写)' elif ename == '': result['code'] = 1 result['msg'] = '格式错误' else: result['code'] = 0 after = data before = self.getsysdetail(ename) result['field'], result['label'] = self.diffInfo(before, after) if len(result['field']) == 0: result['msg'] = '系统信息未修改,无需保存' else: result['msg'] = '确认修改以下内容:' web.header('Content-Type', 'application/json') return json.dumps(result) elif action == 'save': if ename == '': return self.render().error('操作失败失败') elif not self.isEditable(): #检查是否可写 return self.render().error('数据库无法修改(可能由于离线、匿名登录或数据库不可写)') after = data before = self.getsysdetail(ename) fields, labels = self.diffInfo(before, after) if len(fields) == 0: return self.render().error('没有需要修改的内容') curVersion = int(data['sys_version']) result, msg = dao.Database().updateSystemInfo( after, before, self.loginUser(), fields, labels, curVersion) if result: web.redirect('/sys/edit?ename=' + ename + '&tip=savesuccess') return else: return self.render().error(msg)
def checkDatabase(self, readOnly): db = dao.Database() try: version = db.getGlobalVersion() self.dbReadable = True if not readOnly: logId = db.writeLoginLog(self.logonUser) self.dbEditable = logId > 0 else: self.dbEditable = False return self.dbReadable, self.dbEditable, version except Exception, ex: db.error(ex) return False, False, {}
def search_sys(self, keyword, pageindex): db = dao.Database() if keyword is not None: keyword = keyword.strip() if keyword is None or keyword == '': len = db.select(self.SQL_LEN) result = db.select(self.SQL_GETRESULT) else: wrap = '%' + keyword + '%' len = db.select(self.SQL_LEN + self.WHERE_CLAUSE, wrap, wrap, wrap) result = db.select(self.SQL_GETRESULT + self.WHERE_CLAUSE, wrap, wrap, wrap) pagesize = int(math.ceil(len[0][0] / (self.PAGE_SIZE * 1.0))) result = result[(pageindex - 1) * self.PAGE_SIZE:pageindex * self.PAGE_SIZE] return (result, pagesize)
def export2excel(self, filename): # localpath = self.config.param['localpath'] # db = dao.Database(localpath) db = dao.Database() cols = db.getCols() #构造查询字符串 col_str = ','.join([u'{0} as {1}'.format(r[1], r[0]) for r in cols]) sql = 'select ' + col_str + ' from systable' # print sql rows = db.select(sql) font0 = xlwt.Font() font0.name = 'Times New Roman' font0.colour_index = 2 font0.bold = True style0 = xlwt.XFStyle() style0.font = font0 # style1 = xlwt.XFStyle() # style1.num_format_str = 'D-MMM-YY' wb = xlwt.Workbook() ws = wb.add_sheet(u'系统信息') # model = sysmodel.model() #写表头 for i in range(len(cols)): ws.write(0, i, cols[i][1]) #写内容 for j in range(len(rows)): for i in range(len(cols)): ws.write(j + 1, i, rows[j][i]) wb.save(filename) self.msg(u'已导出文件到: ' + filename)
def GET(self): data = web.input(ename='', tip='') ename = data['ename'] if ename == '': web.redirect('/sys/') return #是否显示提示 tip = data['tip'] if tip == '': showtip = 'none' backurl = 'javascript:history.back()' else: showtip = 'block' backurl = '/' #获得系统信息 sysinfo = self.getsysdetail(ename) #获得系统最新版本信息 version = dao.Database().getSystemVersion(ename) return self.render().edit(sysinfo, version, showtip, backurl)
def get_strings(): database = dao.Database('fydp') database.execute("SELECT * FROM language") settings.languageList = database.fetchall()
def setNewRow(): database = dao.Database('fydp') database.execute( "INSERT INTO result_by_id(id, sensor_id, test_status, failure_mode) VALUES (?, ? , ?, ?)", (settings.test_number + 1, None, 0, 0)) database.commit()
def clearDatabase(): database = dao.Database('fydp') database.execute("DELETE FROM result_by_id") database.commit()
def clearDatabase(): database = dao.Database('fydp') database.execute("DELETE FROM detailed_results") database.commit()
def get_test_section_by_id(detailed_id): database = dao.Database('fydp') database.execute( "SELECT test_section FROM detailed_results WHERE id=(?)", (detailed_id, )) return [item[0] for item in database.fetchall()]
def get_velocity_by_id(detailed_id): database = dao.Database('fydp') database.execute("SELECT velocity FROM detailed_results WHERE id=(?)", (detailed_id, )) return [item[0] for item in database.fetchall()]
def post(self): content_type = request.headers.get('content-type') content_type = ''.join(i.lower() for i in content_type.split()) log.info(content_type) if content_type != 'application/json': return { 'status': 415, 'message': 'content type should be [application/json]' }, 415 receive_json = request.get_json() log.info(receive_json) if receive_json == None or receive_json.get( 'bot_id') == None or receive_json.get( 'work') == None or receive_json.get( 'return_flag') == None or receive_json.get( 'return_finish') == None: return { 'status': 422, 'message': 'Error, missing necessary parameter [bot_id] or [work] or [return_flag] or [return_finish]' }, 422 try: index = [0, 1, 2, 3, 4, 5].index(receive_json.get('work')) log.info(index) except Exception as e: log.info("work parameter error: " + utils.except_raise(e)) return { 'status': 422, 'message': 'work should be int [0, 1, 2, 3, 4, 5]' }, 422 try: index = [0, 1].index(receive_json.get('return_flag')) log.info(index) except Exception as e: log.info("work parameter error: " + utils.except_raise(e)) return { 'status': 422, 'message': 'return_flag should be int [0, 1]' }, 422 try: regResult = re.search(r"^\d{2}$", str(receive_json.get('return_finish'))) if regResult == None: return { 'status': 422, 'message': 'return_finish should be regex [^\d{2}$]' }, 422 except Exception as e: log.info("work parameter error: " + utils.except_raise(e)) try: data = {} data['bot_id'] = receive_json['bot_id'] data['work'] = receive_json['work'] data['return_flag'] = receive_json['return_flag'] data['return_finish'] = receive_json['return_finish'] conn = pymysql.Connect(host=const.DB_HOST, user=const.DB_ACCOUNT, passwd=const.DB_PASSWORD, charset='utf8') update_row = dao.Database(conn).insert_bot_work_list(data) log.info(update_row) # # if update_row != 0: return { 'status': 200, 'message': 'success', 'result': { 'description': "Success, update work with param:" + json.dumps(data) }, }, 200 except Exception as e: log.info("query_bot_work_list occured some error: " + utils.except_raise(e)) return { 'status': 500, 'message': "[{}] {}".format(e.__class__.__name__, e.args[0]) }, 500 finally: try: conn.close() except Exception as e: log.info("close connection error: " + utils.except_raise(e)) return { 'status': 500, 'message': "[{}] {}".format(e.__class__.__name__, e.args[0]) }, 500
def get_table(): database = dao.Database('fydp') database.execute("SELECT * FROM detailed_results") return database.fetchall()