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='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