def call_postgresql(logger): IntInfor = pd.DataFrame({}) try: # conn = psycopg2.connect(database="signal_specialist", user="******", password="******", # host="192.168.20.46", # port="5432") pg = Postgres() # conn = pg.db_conn() # print('postgresql connect succeed.') # print('reaching datas...') try: sql = "select a.sys_code FSTR_INTERSECTID, b.node_name from pe_tobj_node_info a left join pe_tobj_node b " \ "on a.node_id = b.node_id where length(a.sys_code) < 5" # cr = conn.cursor() IntInfor = pg.call_pg_data(sql, fram=True) # rs1 = cr.fetchall() # IntInfor = pd.DataFrame(rs1) # IntInfor.columns = ['FSTR_INTERSECTID', 'node_name'] # print(IntInfor) # input() # conn.close() except Exception as e: logger.error('when reaching interinfo, ' + str(e)) finally: pg.db_close() except Exception as e: logger.error('when calling postgresql, ' + str(e)) return IntInfor
def loss_data_period_check(self, interface_name, stime, etime): """ 缺失数据时段检测 :return: """ pg = Postgres(SqlText.pg_inf_arith) result = pg.call_pg_data(sql_loss_data_period.format( interface_name, stime, etime), fram=True) if result is not None and not result.empty: result_loss = result[result['data_num'] == 0] result = result[result['data_num'] != 0] data_num = result['data_num'].values avg_num = np.mean(data_num) dev = np.std(data_num) print("%s今日各时段平均请求量:%s;标准差:%s" % (interface_name, avg_num, dev)) if avg_num > 0: result_loss.append( result[result['data_num'] < avg_num - 2 * dev]) else: result_loss.append(result) print(result_loss) loss_time = result_loss['record_time'].apply( lambda x: str(x)).values.tolist() return loss_time
def call_postgresql(cur_date_str, scatsid, logger): laneInfor = pd.DataFrame({}) geom_detector = pd.DataFrame({}) try: # conn = psycopg2.connect(database="signal_specialist", user="******", password="******", # host="192.168.20.46", # port="5432") try: pg = Postgres(pg_inf={'database': "inter_info", 'user': "******", 'password': "******", 'host': "192.168.20.46", 'port': "5432"}) sql = "select x.coil_code, x.dir_name, x.function_name from( " \ "select n.node_id, cast(m.coil_code as numeric), n.dir_name, " \ "case when m.function_name = 'icon-func-straight' then '直行' " \ "when m.function_name = 'icon-func-left-straight' then '左直' " \ "when m.function_name = 'icon-func-straight-right' then '直右' " \ "when m.function_name = 'icon-func-left' then '左转' " \ "when m.function_name = 'icon-func-right' then '右转' " \ "when m.function_name = 'icon-func-round' then '掉头' " \ "when m.function_name = 'icon-func-round-left' then '左转掉头' " \ "when m.function_name = 'icon-func-round-straight' then '直行掉头' " \ "when m.function_name = 'icon-func-left-right' then '左右转' " \ "when m.function_name = 'icon-func-three' then '直左右' else '出口' end function_name " \ "from(select a.sys_code, b.trancet_id, b.function_name, b.coil_code " \ "from pe_tobj_node_info a left join pe_tobj_node_lane b on a.node_id = b.node_id " \ " ) m left join pe_tobj_node_round n on m.trancet_id = n.roadsect_id " \ "where m.coil_code is not null and m.coil_code != '-' ) x left join pe_tobj_node_info y on " \ "x.node_id = y.node_id where y.sys_code = '{0}'".format(scatsid) laneInfor = pg.call_pg_data(sql, fram=True) pg.db_close() pg = Postgres(pg_inf={'database': "detector", 'user': "******", 'password': "******", 'host': "192.168.20.56", 'port': "5432"}) sql1 = "select scats_lane, error_name, error_detail from detector_test_results " \ "where datetime = '{0}' and devc_id = '{1}'".format('2018-08-08', scatsid) geom_detector = pg.call_pg_data(sql1, fram=True) pg.db_close() # cr = conn.cursor() # cr.execute(sql) # rs1 = cr.fetchall() # IntInfor = pd.DataFrame(rs1) # IntInfor.columns = ['FSTR_INTERSECTID', 'node_name'] # cr.close() except Exception as e: logger.error('when reaching lanerinfo, ' + str(e)) except Exception as e: logger.error('when calling postgresql, ' + str(e)) return laneInfor, geom_detector
def parse_failed_detector_send(self, delta=15): current_time = dt.datetime.now() # current_date = current_time.date() start_time = (current_time - dt.timedelta(minutes=delta)).strftime('%H:%M:%S') end_time = (current_time).strftime('%H:%M:%S') current_date = str(current_time.date()) if IF_TEST: current_date = TEST_DATE failed_detector = self.parsing_failed_check(current_date, start_time, end_time) if failed_detector is not None: try: pg = Postgres(ConnectInf.pg_inf_inter_info) pg.execute(sql_delete_channel_data) node_inf = pg.call_pg_data(sql_get_node_inf, fram=True) node_inf.columns = ['node_id','FSTR_INTERSECTID'] merge_result = pd.merge(failed_detector,node_inf,how='left', on=['FSTR_INTERSECTID']) return_message = merge_result.values.tolist() pg.send_pg_data(sql=sql_send_parse_failed_detector, data=return_message) except Exception as e: print(e) return
def call_postgresql(scatsid, logger): laneInfor = pd.DataFrame({}) try: # conn = psycopg2.connect(database="signal_specialist", user="******", password="******", # host="192.168.20.46", # port="5432") pg = Postgres() try: sql = "select cast(m.coil_code as numeric), n.dir_name, " \ "case when m.function_name = 'icon-func-straight' then '直行' " \ "when m.function_name = 'icon-func-left-straight' then '左直' " \ "when m.function_name = 'icon-func-straight-right' then '直右' " \ "when m.function_name = 'icon-func-left' then '左转' " \ "when m.function_name = 'icon-func-right' then '右转' " \ "when m.function_name = 'icon-func-round' then '掉头' " \ "when m.function_name = 'icon-func-round-left' then '左转掉头' " \ "when m.function_name = 'icon-func-round-straight' then '直行掉头' " \ "when m.function_name = 'icon-func-left-right' then '左右转' " \ "when m.function_name = 'icon-func-three' then '直左右' else '出口' end function_name " \ "from(select a.sys_code, b.trancet_id, b.function_name, b.coil_code " \ "from pe_tobj_node_info a left join pe_tobj_node_lane b on a.node_id = b.node_id " \ "where a.sys_code = '{0}' ) m left join pe_tobj_node_round n on m.trancet_id = n.roadsect_id " \ "where m.coil_code is not null and m.coil_code != '-' ".format(scatsid) laneInfor = pg.call_pg_data(sql, fram=True) # cr = conn.cursor() # cr.execute(sql) # rs1 = cr.fetchall() # IntInfor = pd.DataFrame(rs1) # IntInfor.columns = ['FSTR_INTERSECTID', 'node_name'] # cr.close() except Exception as e: logger.error('when reaching lanerinfo, ' + str(e)) finally: pg.db_close() except Exception as e: logger.error('when calling postgresql, ' + str(e)) return laneInfor
def call_postgresql(int_list, logger): IntInfor = pd.DataFrame({}) try: # conn = psycopg2.connect(database="signal_specialist", user="******", password="******", # host="192.168.20.46", # port="5432") pg = Postgres( pg_inf={ 'database': "inter_info", 'user': "******", 'password': "******", 'host': "33.83.100.145", 'port': "5432" }) # conn = pg.db_conn() # print('postgresql connect succeed.') # print('reaching datas...') try: sql = "select sys_code from pe_tobj_node_info where node_id in {} ".format( tuple(int_list)) # cr = conn.cursor() IntInfor = pg.call_pg_data(sql, fram=True) # print(IntInfor) # rs1 = cr.fetchall() # IntInfor = pd.DataFrame(rs1) # IntInfor.columns = ['FSTR_INTERSECTID', 'node_name'] # print(IntInfor) # input() # conn.close() except Exception as e: logger.error('when reaching interinfo, ' + str(e)) finally: pg.db_close() except Exception as e: logger.error('when calling postgresql, ' + str(e)) return IntInfor['sys_code'].tolist()
class OperateAutoDis(): # pg_inf = {'database': "research", 'user': "******", 'password': "******", # 'host': "192.168.20.45", 'port': "5432"} # pg_inf = {'database': "signal_specialist", 'user': "******", 'password': "******", # 'host': "192.168.20.46", 'port': "5432"} intid_list = ['14KC7097AL0', '14KOB0981K0'] def __init__(self): self.pg = Postgres() self.int_auto = {} def get_int_type(self, intid_list=None): if IF_TEST: intid_list = OperateAutoDis.intid_list str_intid_list = ['\'' + int + '\'' for int in intid_list] pram = ','.join(str_intid_list) # print(pram) result = self.pg.call_pg_data(SqlContent.sql_get_int_type.format(pram)) print('get_int_type', result) return result def get_alarm_type(self, intid_list=None): if IF_TEST: intid_list = OperateAutoDis.intid_list result = new_kde_cal() print(result) return result def get_alarm_operate_type(self, intid_list=None): if IF_TEST: intid_list = OperateAutoDis.intid_list str_intid_list = ['\'' + int + '\'' for int in intid_list] pram = ','.join(str_intid_list) current_date = dt.datetime.now().date() stime = str(current_date) + ' 00:00:00' # print(pram) result = self.pg.call_pg_data( SqlContent.sql_get_operate_dispose.format(pram, stime), fram=True) print('operate', result) return result def alarm_auto_set(self, alarm_dispose_data): auto = None # TD = 30 # TA = 16 # TW = 16 # TRD = 10 print('alarm_dispose_data', alarm_dispose_data) for i in alarm_dispose_data: (int_id, time_point, auto_dis, alarm_id, dis_type, int_name) = i current_time = dt.datetime.now() time_delta = (current_time - dt.datetime.strptime( str(time_point), '%Y-%m-%d %H:%M:%S')).seconds current_time = dt.datetime.now() # 关注 if dis_type == 1 and time_delta < 60 * TA: end_time = dt.datetime.strptime( str(time_point), '%Y-%m-%d %H:%M:%S') + dt.timedelta(seconds=60 * TA) self.int_auto[int_id] = { 'lastDis': '1', 'auto': 1, 'endTime': end_time } auto = True # 调控,调控完以后,30分钟路口不自动处置 elif dis_type == 2 and time_delta < 60 * TD: end_time = dt.datetime.strptime( str(time_point), '%Y-%m-%d %H:%M:%S') + dt.timedelta(seconds=60 * TD) self.int_auto[int_id] = { 'lastDis': '2', 'auto': 0, 'endTime': end_time } auto = False # 误报 elif dis_type == 3 and time_delta < 60 * TW: end_time = dt.datetime.strptime( str(time_point), '%Y-%m-%d %H:%M:%S') + dt.timedelta(seconds=60 * TW) self.int_auto[int_id] = { 'lastDis': '3', 'auto': 1, 'endTime': end_time } auto = True else: # 无处置记录 end_time = current_time + dt.timedelta(seconds=60 * TRD) self.int_auto[int_id] = { 'lastDis': '5', 'auto': 0, 'endTime': end_time } auto = False return auto def alarm_auto_judge(self, alarm_int_list): """ :param alarm_int_list: 报警路口列表 :return: 报警是否自动处置结果 lastDis:上一次路口处置状态【1:关注;2:调控;3:误报;4:快处;5:推送人工】 """ reponse_all = [] # alarm_type 报警常发偶发判断结果 # self.frame_int_state = self.get_alarm_operate_type(alarm_int_list) # int_type = self.get_int_type(alarm_int_list) # alarm_type = self.get_alarm_type() try: self.frame_int_state = self.get_alarm_operate_type(alarm_int_list) int_type = self.get_int_type(alarm_int_list) alarm_type = self.get_alarm_type() except Exception as e: print("获取路口报警类型失败 或 计算报警强度失败") print(e) else: int_type = dict(int_type) if alarm_type: fre_alarm_list = [ i[0] for i in alarm_type if i[4] == '0' or i[4] == '0.0' ] else: fre_alarm_list = [] for int in alarm_int_list: int_alarm_type = int_type.get(int) # 报警较少路口,检测连续报警 if int_alarm_type == '0': reponse_all = self.continue_alarm_judge(int, reponse_all) # 报警较多路口,常发报警人工处置,偶发报警检测连续报警 elif int_alarm_type == '1': # 常发报警 if int in fre_alarm_list: reponse_all = self.man_disposal_judge(int, reponse_all) # 偶发报警 else: reponse_all = self.continue_alarm_judge( int, reponse_all) return reponse_all def auto_disposal_delta(self, int, reponse_all): int_key = self.int_auto.keys() int_check_state = [] current_time = dt.datetime.now() if int not in int_key: new_end_time = current_time + dt.timedelta(seconds=TRD * 60) self.int_auto[int] = { 'lastDis': '4', 'auto': 0, 'endTime': new_end_time } reponse = {'alarmInt': int, 'autoDis': True} reponse_all.append(reponse) else: int_auto_data = self.int_auto.get(int) last_dis = int_auto_data.get('lastDis') auto = int_auto_data.get('auto') end_time = int_auto_data.get('endTime') if current_time > end_time: # 路口超时后,自动处置第一次报警!同时将路口状态设置为自动处置 new_end_time = current_time + dt.timedelta(seconds=TRD * 60) self.int_auto[int] = { 'lastDis': '4', 'auto': 0, 'endTime': new_end_time } reponse = {'alarmInt': int, 'autoDis': True} reponse_all.append(reponse) elif (last_dis == '4' or last_dis == '5') and auto == 0 and current_time <= end_time: # 规定时间间隔内,上一次处置为自动处置 # 1、判断路口类型;2、判断路口报警类型 int_check_state.append(int) pass elif auto == 1 and current_time <= end_time: # 路口被设置为自动处置,且未超时 reponse = {'alarmInt': int, 'autoDis': True} reponse_all.append(reponse) def man_disposal_judge(self, int, reponse_all): """ :param int: 需要查询的路口 :param reponse_all: 判断结果列表 :return: 判断结果列表 根据人工处置记录判断是否快速处置 """ frame_int_state = self.frame_int_state try: match_dis_type = frame_int_state[frame_int_state['inter_id'] == int].values except Exception as e: print(e) else: # 若路口匹配到了调控记录 auto = self.alarm_auto_set(match_dis_type) if auto is True: reponse = {'alarmInt': int, 'autoDis': True} reponse_all.append(reponse) elif auto is False: reponse = {'alarmInt': int, 'autoDis': False} reponse_all.append(reponse) else: # 匹配不到操作记录,则推送人工 reponse = {'alarmInt': int, 'autoDis': False} reponse_all.append(reponse) return reponse_all def continue_alarm_judge(self, int, reponse_all): int_key = self.int_auto.keys() current_time = dt.datetime.now() if int not in int_key: new_end_time = current_time + dt.timedelta(seconds=TRD * 60) self.int_auto[int] = { 'lastDis': '4', 'auto': 0, 'endTime': new_end_time } reponse = {'alarmInt': int, 'autoDis': True} reponse_all.append(reponse) else: int_auto_data = self.int_auto.get(int) last_dis = int_auto_data.get('lastDis') auto_signal = int_auto_data.get('auto') end_time = int_auto_data.get('endTime') if current_time > end_time: # 路口超时后,自动处置第一次报警!同时将路口状态设置为自动处置 new_end_time = current_time + dt.timedelta(seconds=TRD * 60) self.int_auto[int] = { 'lastDis': '4', 'auto': 0, 'endTime': new_end_time } reponse = {'alarmInt': int, 'autoDis': True} reponse_all.append(reponse) elif (last_dis == '4' or last_dis == '5') and auto_signal == 0 and current_time <= end_time: # 规定时间间隔内,上一次处置为自动处置 # 1、判断路口类型;2、判断路口报警类型 reponse_all = self.man_disposal_judge(int, reponse_all) pass elif auto_signal == 1 and current_time <= end_time: # 路口被设置为自动处置,且未超时 reponse = {'alarmInt': int, 'autoDis': True} reponse_all.append(reponse) return reponse_all
class OperateAutoDis(): # pg_inf = {'database': "research", 'user': "******", 'password': "******", # 'host': "192.168.20.45", 'port': "5432"} # pg_inf = {'database': "signal_specialist", 'user': "******", 'password': "******", # 'host': "192.168.20.46", 'port': "5432"} intid_list = ['14KC7097AL0', '14KOB0981K0'] def __init__(self): self.pg = Postgres() self.int_auto = {} def get_int_type(self, intid_list=None): if TEST: intid_list = OperateAutoDis.intid_list str_intid_list = ['\'' + int + '\'' for int in intid_list] pram = ','.join(str_intid_list) # print(pram) result = self.pg.call_pg_data(SqlContent.sql_get_int_type.format(pram)) print('get_int_type', result) return result def get_alarm_type(self, intid_list=None): if TEST: intid_list = OperateAutoDis.intid_list result = new_kde_cal() print(result) return result def get_alarm_operate_type(self, intid_list=None): if TEST: intid_list = OperateAutoDis.intid_list str_intid_list = ['\'' + int + '\'' for int in intid_list] pram = ','.join(str_intid_list) current_date = dt.datetime.now().date() stime = str(current_date) + ' 00:00:00' print(pram) result = self.pg.call_pg_data( SqlContent.sql_get_operate_dispose.format(pram, stime), fram=True) print('operate', result) return result def alarm_auto_set(self, alarm_dispose_data): auto = None for i in alarm_dispose_data: (int_id, time_point, auto_dis, alarm_id, dis_type, int_name) = i current_time = dt.datetime.now() time_delta = (current_time - dt.datetime.strptime( str(time_point), '%Y-%m-%d %H:%M:%S')).seconds current_time = dt.datetime.now() # 关注 if dis_type == 1 and time_delta < 60 * TIME_DELAY_N: end_time = dt.datetime.strptime( str(time_point), '%Y-%m-%d %H:%M:%S') + dt.timedelta( seconds=60 * TIME_DELAY_N) self.int_auto[int_id] = { 'lastDis': '1', 'auto': 1, 'endTime': end_time } auto = True # 调控 elif dis_type == 2 and time_delta < 60 * TIME_DELAY_N: end_time = dt.datetime.strptime( str(time_point), '%Y-%m-%d %H:%M:%S') + dt.timedelta( seconds=60 * TIME_DELAY_N) self.int_auto[int_id] = { 'lastDis': '2', 'auto': 1, 'endTime': end_time } auto = True # 误报 elif dis_type == 3 and time_delta < 60 * TIME_DELAY_W: end_time = dt.datetime.strptime( str(time_point), '%Y-%m-%d %H:%M:%S') + dt.timedelta( seconds=60 * TIME_DELAY_W) self.int_auto[int_id] = { 'lastDis': '3', 'auto': 1, 'endTime': end_time } auto = True else: # 无处置记录 end_time = current_time + dt.timedelta(seconds=60 * TIME_DELAY_N) self.int_auto[int_id] = { 'lastDis': '5', 'auto': 0, 'endTime': end_time } auto = False return auto def alarm_auto_judge(self, alarm_int_list): """ :param alarm_int_list: 报警路口列表 :return: 报警是否自动处置结果 lastDis:上一次路口处置状态【1:关注;2:调控;3:误报;4:快处;5:推送人工】 """ reponse_all = [] reponse = {'alarmInt': None, 'autoDis': None} int_key = self.int_auto.keys() int_check_state = [] current_time = dt.datetime.now() for int in alarm_int_list: if int not in int_key: new_end_time = current_time + dt.timedelta( seconds=TIME_DELAY_N * 60) self.int_auto[int] = { 'lastDis': '4', 'auto': 0, 'endTime': new_end_time } reponse = {'alarmInt': int, 'autoDis': True} reponse_all.append(reponse) else: int_auto_data = self.int_auto.get(int) last_dis = int_auto_data.get('lastDis') auto = int_auto_data.get('auto') end_time = int_auto_data.get('endTime') if current_time > end_time: # 路口超时后,自动处置第一次报警!同时将路口状态设置为自动处置 new_end_time = current_time + dt.timedelta( seconds=TIME_DELAY_N * 60) self.int_auto[int] = { 'lastDis': '4', 'auto': 0, 'endTime': new_end_time } reponse = {'alarmInt': int, 'autoDis': True} reponse_all.append(reponse) elif (last_dis == '4' or last_dis == '5') and auto == 0 and current_time <= end_time: # 规定时间间隔内,上一次处置为自动处置 # 1、判断路口类型;2、判断路口报警类型 int_check_state.append(int) pass elif auto == 1 and current_time <= end_time: # 路口被设置为自动处置,且未超时 reponse = {'alarmInt': int, 'autoDis': True} reponse_all.append(reponse) if len(int_check_state) > 0: alarm_type = self.get_alarm_type() print('alarm_type', alarm_type) if alarm_type: fre_alarm_list = [ i[0] for i in alarm_type if i[4] == '0' or i[4] == '0.0' ] print("常发报警路口", fre_alarm_list) int_type = self.get_int_type(int_check_state) frame_int_state = self.get_alarm_operate_type(int_check_state) for i in int_type: int_id = i[0] try: match_alarm_type = frame_int_state[ frame_int_state['inter_id'] == int_id].values except Exception as e: print(e) else: # 若路口匹配到了调控记录 auto = self.alarm_auto_set(match_alarm_type) if auto is True: reponse = {'alarmInt': int_id, 'autoDis': True} reponse_all.append(reponse) else: # 报警较少路口 if i[1] == '0': reponse = { 'alarmInt': int_id, 'autoDis': False } reponse_all.append(reponse) # 报警较多路口 elif i[1] == '1': if int_id in fre_alarm_list: reponse = { 'alarmInt': int_id, 'autoDis': True } reponse_all.append(reponse) else: reponse = { 'alarmInt': int_id, 'autoDis': False } reponse_all.append(reponse) else: logger.warning("无法计算报警强度") for int in int_check_state: reponse = {'alarmInt': int, 'autoDis': False} reponse_all.append(reponse) else: pass return reponse_all