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 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 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 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 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 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()