File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
import psycopg2 # driver 임포트
import time
from datetime import datetime, timedelta
class DB():
def __init__(self):
self.conn=psycopg2.connect(
host='192.168.0.169',
dbname='postgres',
user='postgres',
password='1234',
port='5432'
) # db에 접속
self.conn.autocommit=True
'''
def __init__(self):
self.conn=psycopg2.connect(
host='165.229.169.113',
dbname='traffic_agent',
user='takensoft',
password='ts44301236!@',
port='5432',
options="-c search_path=traffic_agent_v1") # db에 접속
self.conn.autocommit=True
'''
def db_check_id(self,id):
cur = self.conn.cursor() # 커서를 생성한다
cur.execute(f'''
SELECT user_id
FROM "TRAFFICAGENCY".user_id
Where user_id = '{id}';
''')
result=cur.fetchone()
cur.close()
return result
def db_login(self,id,pw):
cur = self.conn.cursor() # 커서를 생성한다
cur.execute(f'''
SELECT user_id, user_pw, user_email, user_sex, user_phone, user_time_stamp
FROM "TRAFFICAGENCY".user_id
Where user_id = '{id}' and user_pw='{pw}';
''')
result=cur.fetchone()
cur.close()
return result
def db_add_id(self,user_id,user_pw,user_email,user_sex,user_phone) :
cur = self.conn.cursor() # 커서를 생성한다
now=time.localtime()
d=time.strftime('%Y-%m-%d %X', now)
cur.execute(f'''
insert into "TRAFFICAGENCY".user_id (user_id,user_pw,user_email,user_sex,user_phone,user_time_stamp)
values ('{user_id}','{user_pw}','{user_email}','{user_sex}','{user_phone}','{d}')
''')
cur.close()
def db_delete_id(self,user_id) :
cur = self.conn.cursor() # 커서를 생성한다
cur.execute(f'''
delete
from "TRAFFICAGENCY".user_id ui
where user_id = '{user_id}'
''')
cur.close()
def db_get_node(self):
cur = self.conn.cursor() # 커서를 생성한다
cur.execute('''
select "index",source_x ,source_y,target_x,target_y," dist "
from "TRAFFICAGENCY".node n
where flcass != 'pedstrian'
''')
result=cur.fetchall()
return result
def db_get_dest(self,dest1):
cur = self.conn.cursor() # 커서를 생성한다
cur.execute(f'''
select j.q,li.q ,li.location_cen_x_4623,li.location_cen_y_4623,li.location_exit_x_4623 ,li.location_exit_y_4623
from "TRAFFICAGENCY".jibun j, "TRAFFICAGENCY".location_info li
where j.build_num =li.build_code and (j.q='{dest1}' or li.q='{dest1}')
''')
result=cur.fetchone()
return (float(result[4]),float(result[5]))
def db_get_near_node(self,dest_x,dest_y,value):
cur = self.conn.cursor() # 커서를 생성한다
cur.execute(f'''
select source_x, source_y
from "TRAFFICAGENCY".node n
where {dest_x} > source_y - {value} and {dest_x} <= source_y + {value}
and {dest_y} > source_x - {value} and {dest_y} <= source_x + {value}
''')
result=cur.fetchall()
return result
def db_get_address(self,dest1):
cur = self.conn.cursor() # 커서를 생성한다
cur.execute(f'''
select j.q,li.q ,li.location_cen_x_4623,li.location_cen_y_4623,li.location_exit_x_4623 ,li.location_exit_y_4623
from "TRAFFICAGENCY".jibun j, "TRAFFICAGENCY".location_info li
where j.build_num =li.build_code and (j.q='{dest1}' or li.q='{dest1}')
''')
result=cur.fetchone()
return (float(result[2]),float(result[3]))
def db_add_report(self,report_id,report_x,report_y) :
cur = self.conn.cursor() # 커서를 생성한다
now=time.localtime()
d=time.strftime('%Y-%m-%d %X', now)
cur.execute(f'''
insert into "TRAFFICAGENCY".report (report_id,report_x,report_y,timestamp)
values ('{report_id}','{report_x}','{report_y}','{d}')
''')
def db_get_near_point(self,dest_x,dest_y):
cur = self.conn.cursor() # 커서를 생성한다
now=datetime.now()
d_plus=now +timedelta(hours=1)
d_plus=str("'"+d_plus.strftime('%Y-%m-%d %X')+"'")
d_minus=now -timedelta(hours=1)
d_minus=str("'"+d_minus.strftime('%Y-%m-%d %X')+"'")
cur.execute(f'''
select report_x, report_y
from "TRAFFICAGENCY".report
where {dest_y} > report_y - 0.000498 and {dest_y} <= report_y + 0.000498
and {dest_x} > report_x - 0.000498 and {dest_x} <= report_x + 0.000498
and timestamp between {d_minus} and {d_plus};
''')
result=cur.fetchall()
return result
def db_add_pothole(self,pothole_id,pothole_location_x,pothole_location_y) :
cur = self.conn.cursor() # 커서를 생성한다
now=datetime.now()
d=now.strftime('%Y-%m-%d %X')
cur.execute(f'''
insert into "TRAFFICAGENCY".pothole (pothole_id,pothole_location_x,pothole_location_y,timestamp)
values ('{pothole_id}','{pothole_location_x}','{pothole_location_y}','{d}')
''')
def db_delete_pothole(self,dest_x,dest_y) :
cur = self.conn.cursor() # 커서를 생성한다
now=datetime.now()
d_plus=now +timedelta(hours=1)
d_plus=str("'"+d_plus.strftime('%Y-%m-%d %X')+"'")
d_minus=now -timedelta(hours=1)
d_minus=str("'"+d_minus.strftime('%Y-%m-%d %X')+"'")
cur.execute(f'''
delete from "TRAFFICAGENCY".pothole
where {dest_y} > pothole_location_y - 0.000498 and {dest_y} <= pothole_location_y + 0.000498
and {dest_x} > pothole_location_x - 0.000498 and {dest_x} <= pothole_location_x + 0.000498
and timestamp between {d_minus} and {d_plus};
''')
def db_display_pothole(self,timestamp) :
cur = self.conn.cursor() # 커서를 생성한다
now=datetime.fromtimestamp(timestamp)
d_plus=now +timedelta(hours=2)
d_plus=str("'"+d_plus.strftime('%Y-%m-%d %H:%M:%S')+"'")
d_minus=now -timedelta(hours=2)
d_minus=str("'"+d_minus.strftime('%Y-%m-%d %H:%M:%S')+"'")
cur.execute(f'''
select pothole_location_x,pothole_location_y from "TRAFFICAGENCY".pothole
where timestamp between {d_minus} and {d_plus};
''')
result=cur.fetchall()
return result