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(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 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 clear_database():
pg = Postgres()
save_date = (dt.datetime.now() -
dt.timedelta(days=3)).strftime("%Y-%m-%d %H:%M:%S")
pg.execute(SqlText.sql_delete_real_phase.format(save_date))
pg.execute(SqlText.sql_delete_kde_vaue.format(save_date))
pg.db_close()
print("数据库清理完成")
def call_pg_data(sql,pg_inf=None):
if pg_inf:
db = Postgres(pg_inf)
else:
db = Postgres()
pg_conn, cr = db.db_conn()
if pg_conn:
cr.execute(sql)
index = cr.description
result = cr.fetchall()
db.db_close()
if result:
fresult = tuple2frame(result, index)
return fresult
else:
return pd.DataFrame({})
def alarm_operate_match(self, s_datetime, e_datetime):
pg_inf = pg_inf_46
self.pg = Postgres(pg_inf)
host = pg_inf.get('host')
conn,cr = self.pg.db_conn()
if cr:
cr.execute(sql_alarm_operate_match.format(host,s_datetime,e_datetime,MATCH_LIMITTIME))
conn.commit()
def parse_failed_detector_send(self):
current_time = dt.datetime.now()
# current_date = current_time.date()
current_date = str(current_time.date())
if IF_TEST:
current_date = '2018-11-09'
failed_detector = self.parsing_failed_check(current_date)
pg = Postgres(ConnectInf.pg_inf_inter_info)
self.pg.send_pg_data(sql=sql_send_parse_failed_detector, data=failed_detector)
return
def operate_data_send(operate_data):
send_data = []
for key in operate_data.keys():
describe = operate_data.get(key)
if describe is not None:
datetime = dt.datetime.strftime(dt.datetime.now(), '%Y-%m-%d %H:%M:%S')
oper_num = describe.get('OperNum')
json_describe = json.dumps(describe, ensure_ascii=False)
send_data.append([datetime,json_describe,key,oper_num])
pg = Postgres()
conn, cr = pg.db_conn()
for data in send_data:
# print(data)
cr.execute(sql_insert, data)
conn.commit()
pg.db_close()
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 MainProcess():
global IntersectIDlist # 路口列表
global IntStrInput # 配置表
# global enjoy_strategicInfo
global last_date
global no_data_node
global resolve_fail_node
no_data_node = {}
resolve_fail_node = []
IntersectInfo = CallOracle() # 从数据库读取路口列表
currenttime = dt.datetime.now()
last_date = currenttime.date()
if len(IntersectInfo) > 0:
IntersectIDlist = IntersectInfo['SITEID']
pg_inf_inter_info = {'database': "inter_info", 'user': "******", 'password': "******",
'host': "192.168.20.46", 'port': "5432"}
try:
pg = Postgres(pg_inf_inter_info)
except Exception as e:
print('MainProcess:连接数据库失败', e)
else:
IntStrInput = pg.call_pg_data(sql_get_scats_input)
print(IntStrInput)
if IntStrInput is not None:
int_id = np.array(IntersectIDlist).tolist()
int_num = len(int_id)
print("请求总路口数：", int_num)
group = round(int_num / CONSTANT.group_interval, 0)+1
int_grouped_data = int_grouped(int_id, group)
thread_creat(int(group), int_grouped_data,IntStrInput)
return
else:
print('获取节点列表失败')
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()
def __init__(self):
self.pg = Postgres()
self.int_auto = {}
def __init__(self):
self.status = None
self.pg = Postgres(SqlText.pg_inf_arith)
self.ora = Oracle(SqlText.OracleUser)
self.conn, self.cr = self.ora.db_conn()
def __init__(self):
self.status = None
self.pg = Postgres(InterfaceStatus.pg_inf)
self.ora = Oracle(InterfaceStatus.OracleUser)
self.conn, self.cr = self.ora.db_conn()