import psycopg2 # driver 임포트 import time from datetime import datetime, timedelta class DB(): def __init__(self): self.conn=psycopg2.connect( host='localhost', dbname='postgres', user='postgres', password='ts4430!@', port='5432' ) # db에 접속 self.conn.autocommit=True def db_check_id(self,id): cur = self.conn.cursor() # 커서를 생성한다 cur.execute(f''' SELECT user_id FROM rds.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 rds.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 rds.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 rds.user_id ui where user_id = '{user_id}' ''') cur.close() def db_add_action(self,action_id,lat,lon,user_id,action_success) : cur = self.conn.cursor() # 커서를 생성한다 now=datetime.now() d=now.strftime('%Y-%m-%d %X') cur.execute(f''' insert into rds.action (action_id,lat,lon,action_time_stamp,user_id,action_success) values ('{action_id}','{lat}','{lon}','{d}','{user_id}','{action_success}') ''') def db_display_action(self,timestamp) : cur = self.conn.cursor() # 커서를 생성한다 now=timestamp d_plus=now +timedelta(hours=2) d_plus=str("'"+d_plus.strftime('%Y-%m-%d %X')+"'") d_minus=now -timedelta(hours=2) d_minus=str("'"+d_minus.strftime('%Y-%m-%d %X')+"'") cur.execute(f''' select * from rds.pothole where timestamp between {d_minus} and {d_plus}; ''') result=cur.fetchall() return result