ReservationControl.py
3.54 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
#-*- coding: utf-8 -*-
# 한글 인코딩을 위한 주석입니다.
# 사전설정:
# pymysql 패키지가 있어야 합니다.
# pip install pymysql
# 보안상 user_info에서 DB id, password를 가져옵니다.
# user_info.py에서 DB id, password를 설정하고 사용해주세요.
# 외부에서 사용법:
# from ReservationControl import *
import pymysql
import user_info
import datetime
# getReservations - 대여현황반환 함수
# input:
# string user_id
# output:
# 성공시 : tuple형태로 반환 => ( (1 ,"steven123", 7, "전자정보대학", "136호", 10, datetime.datetime(2018,12,5,12,0,0), datetime.datetime(2018,12,5,12,10,0), ), )
# 순서 : reservations_id, user_id, facility_id, location, location_detail, capacity, start_time, end_time
# 실패시 : tuple형태로 반환 => ( ("SQL Error!", ), )
# 사용예:
# result = getReservations("khucse123")
# reservation_start_time = result[0][3]
def getReservations(user_id):
try:
db = pymysql.connect(host='1.201.139.92', port=3306, user=user_info.user_id, password=user_info.user_passwd, db='FRS', charset='utf8')
curs = db.cursor()
#curs.execute("select * from reservations where user_id=%s", user_id)
curs.execute("select r.reservations_id, r.user_id, r.facility_id, f.location, f.location_detail, f.capacity, r.start_time, r.end_time from reservations r, facilities f where r.user_id=%s AND r.facility_id=f.facility_id;", user_id)
result = curs.fetchall()
print("Fetch Success!")
return result
except:
print("SQL Error!")
return (("SQL Error!",),)
finally:
db.close()
# deleteReservations - 대여현황삭제 함수
# input:
# int reservations_id
# output:
# 성공시 : True
# 실패시 : False
# 사용예:
# deleteReservations(5)
# 주의사항:
# 테스트환경에서 없는 reservations_id를 넣으면 에러가 날 수 있음
def deleteReservations(reservations_id):
try:
db = pymysql.connect(host='1.201.139.92', port=3306, user=user_info.user_id, password=user_info.user_passwd, db='FRS', charset='utf8')
curs = db.cursor()
curs.execute("delete from reservations where reservations_id=%s", reservations_id)
db.commit()
print("Delete Success!")
return True
except:
print("SQL Error!")
return False
finally:
db.close()
# addReservations - 대여현황추가 함수
# input:
# string user_id
# int facility_id
# datetime.datetime start_time
# datetime.datetime end_time
# output:
# 성공시 : True
# 실패시 : False
# 사용예:
# tstart = datetime.datetime(2018,12,5,0,11,12)
# tend = datetime.datetime(2018,12,5,0,20,12)
# addReservations("khucse123", 6, tstart, tend)
# 주의사항:
# 테스트환경에서 없는 user_id를 넣으면 에러가 날 수 있음
# 테스트환경에서 없는 facility_id를 넣으면 에러가 날 수 있음
def addReservations(user_id, facility_id, start_time, end_time):
try:
db = pymysql.connect(host='1.201.139.92', port=3306, user=user_info.user_id, password=user_info.user_passwd, db='FRS', charset='utf8')
curs = db.cursor()
curs.execute("insert into reservations (user_id, facility_id, start_time, end_time) values (%s,%s,%s,%s)", (user_id, facility_id, start_time, end_time))
db.commit()
print("Add Reservation Success!")
return True
except:
print("SQL Error!")
return False
finally:
db.close()