forked from Div25singh/Library_Management_System
-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.py
171 lines (145 loc) · 5.18 KB
/
database.py
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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
__author__ = 'user'
import cx_Oracle
import datetime
from classes import Customer,Book
from connection import con,cur
def make_all_tables():
sql = """create table customers(
customer_id number(5) primary key,
first_name varchar2(10),
last_name varchar2(10),
address varchar2(40),
password varchar2(20))"""
cur.execute(sql)
con.commit()
def make_all_tables1():
sql = """create table book(
title varchar2(15),
author varchar2(30),
publisher varchar2(30),
pub_year number(4),
book_id varchar2(30) primary key,
status varchar2(10) CHECK (status in('Issued','Due','Available')))"""
cur.execute(sql)
con.commit()
def make_all_tables2():
sql = """create table admin(
admin_id number(5),
password varchar2(10))"""
cur.execute(sql)
con.commit()
sql = "insert into admin values(227,'helloadmin')"
cur.execute(sql)
con.commit()
def make_all_tables3():
sql = """create table issuehistory(
book_id varchar2(5),
customer_id number(5),
status varchar2(15) check (status in('Issued','Returned')),
)"""
cur.execute(sql)
con.commit()
def sign_up_customer(customer):
fname = customer.get_first_name()
lname = customer.get_last_name()
address=customer.get_address()
password = customer.get_password()
sql = "select customer_id_sequence.nextval from dual"
cur.execute(sql)
res = cur.fetchall()
c_id = res[0][0]
sql = "insert into customers values(:id,:fname,:lname,:address,:password)"
cur.execute(sql, {"id":c_id, "fname":fname, "lname":lname, "password":password, "address":address})
con.commit()
print("Congratulations ! Your Account was Created Successfully")
print("Your Customer ID : ",c_id)
def login_customer(id,password):
sql = "select count(*) from customers where customer_id = :id and password = :password"
cur.execute(sql, {"id":id, "password":password})
res = cur.fetchall()
count = res[0][0]
if count == 1:
return True
else:
return False
def issuebook(c_id):
b_id=input("ENTER BOOK ID TO BE ISSUED\n")
sql="""update book set status='Issued' where book_id= :id"""
cur.execute(sql,{"id":b_id})
con.commit
sql="Select * from book where book_id= :id"
cur.execute(sql,{"id":b_id})
data=cur.fetchall()
for line in data:
print(line[0],line[1],line[2],line[3],line[4],line[5])
cur.execute("""insert into issuehistory values(:bid,:cid,:stat)""",{'bid':b_id,'cid':c_id,'stat':"Issued"})
con.commit
print("BOOK ISSUED\n")
def returnbook(c_id):
b_id=input("ENTER BOOK ID TO BE RETURNED\n")
sql="""update book set status='Available' where book_id= :id"""
cur.execute(sql,{"id":b_id})
con.commit
sql="Select * from book where book_id= :id"
cur.execute(sql,{"id":b_id})
data=cur.fetchall()
for line in data:
print(line[0],line[1],line[2],line[3],line[4],line[5])
cur.execute("""insert into issuehistory values(:bid,:cid,:stat)""",{'bid':b_id,'cid':c_id,'stat':"Returned"})
con.commit
print("BOOK RETURNED\n")
def issued_books(c_id):
sql="""select a.book_id,a.title,b.status from book a,issuehistory b where a.book_id=b.book_id and b.customer_id= :c_id"""
cur.execute(sql,{"c_id":c_id})
data=cur.fetchall()
for line in data:
print(line[0],line[1],line[2])
con.commit
def avail_book():
sql="select * from book where status='Available'"
cur.execute(sql)
data=cur.fetchall()
for line in data:
print(line[0],line[1],line[2],line[3],line[4],line[5])
con.commit
def all_books():
sql="select * from book"
cur.execute(sql)
data=cur.fetchall()
for line in data:
print(line[0],line[1],line[2],line[3],line[4],line[5])
con.commit
def check_book_exists(id):
sql = "select count(*) from book where book_id = :id"
cur.execute(sql, {"id":id})
res = cur.fetchall()
count = res[0][0]
if count == 1:
return True
else:
return False
def change_password(id,addr):
sql = "update customers set password = :pass where customer_id = :id"
cur.execute(sql, {"pass":addr, "id":id})
con.commit()
print("Password Updated Successfully")
def login_admin(id,password):
sql = "select count(*) from admin where admin_id = :id and password = :password"
cur.execute(sql , {"id":id, "password":password})
res = cur.fetchall()
count = res[0][0]
if count == 1:
return True
else:
return False
def get_all_info_customer(id):
sql = "select * from customers where customer_id = :id"
cur.execute(sql, {"id":id})
res = cur.fetchall()
if len(res) == 0:
return None
customer = Customer()
status = res[0][3]
att = res[0][4]
customer.set_customer_id(id)
return customer