File name
Commit message
Commit date
File name
Commit message
Commit date
import psycopg2 # driver 임포트
import time
from io import StringIO
from datetime import datetime, timedelta
class DB():
def __init__(self):
self.conn=psycopg2.connect(
host='210.180.118.83',
dbname='TRAFFICAGNET',
user='takensoft',
password='',
port='5432',
options=f'-c search_path=trafficagent'
) # db에 접속
self.conn.autocommit=True
self.schema = 'trafficagent'
'''
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 "{self.schema}".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 "{self.schema}".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() # 커서를 생성한다
recievingtime=datetime.now()
d = recievingtime.isoformat(sep=' ', timespec='milliseconds')
# d=recievingtime.strftime("%Y-%m-%d %H:%M:%S.%f")
cur.execute(f'''
insert into "{self.schema}".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}')
''')
def db_add_action(self,user_id,user_pw,user_email,user_sex,user_phone) :
cur = self.conn.cursor() # 커서를 생성한다
recievingtime=datetime.now()
d = recievingtime.isoformat(sep=' ', timespec='milliseconds')
# d=recievingtime.strftime("%Y-%m-%d %H:%M:%S.%f")
cur.execute(f'''
insert into "{self.schema}".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 "{self.schema}".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 "{self.schema}".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 "{self.schema}".jibun j, "{self.schema}".location_info li
where j.build_num =li.build_code and (j.q='{dest1}' or li.q='{dest1}' or li.build_name_city like '{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 "{self.schema}".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 "{self.schema}".jibun j, "{self.schema}".location_info li
where j.build_num =li.build_code and (j.q='{dest1}' or li.q='{dest1}' or li.build_name_city like '{dest1}')
''')
result=cur.fetchone()
print( (float(result[2]),float(result[3])))
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 "{self.schema}".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 "{self.schema}".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 "{self.schema}".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 "{self.schema}".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) :
cur = self.conn.cursor() # 커서를 생성한다
cur.execute(f'''
select report_x,report_y from "{self.schema}".report
''')
result=cur.fetchall()
return result
def insert_gps_data(self, csv_block, columns):
cur = self.conn.cursor()
data = StringIO(csv_block)
# using COPY instead of INSERT to do even less operation per data.
cur.copy_from(data, 'gps_data', sep=',', columns = columns)
self.conn.commit()
cur.close()
return True
def close_connection(self):
cur = self.conn.cursor()
cur.close()
return True