LIBRARYMANAGEMENTSYSTEM
DATABASEDESIGN-
BOOK
BOOKID INT(PRIMARYKEY)
BNAME VARCHAR
AUTHOR VARCHAR
PRICE FLOAT
COPIES INT
REM_COPIES INT
MEMBER
MEMBERID INT(PRIMARYKEY)
MNAME VARCHAR
MADD VARCHAR
PHONE CHAR(10)
ISSUE
ISSUEID INT(PRIMARYKEY)
ISSUEDATE DATE
MEMBERID INT(FOREIGNKEY)
BOOKID INT(FOREIGNKEY)
COPIES INT
RETURN
RETURNID INT(PRIMARYKEY)
RETURNDATE DATE
MEMBERID INT(FOREIGNKEY)
BOOKID INT(FOREIGNKEY)
COPIES INT
1
CODE-
‘MAIN’MODULE-
import book
import member
import transaction
import report
while(True):print("
="*80)
print("\t\t\t------Library Management System-----\n")
print("="*80)
print("\t\t\t\tEnter Your Choice\n\t\t\t\[Link] Details\n\t\t\t\
[Link]\n\t\t\t\[Link]\n\t\t\t\[Link]\n\t\t\t\[Link]")
choice=int(input())i
fchoice==1:
while(True):
print("\t\t\t\tEnterYourChoice\n\t\t\t\[Link]\n\t\t\t\
[Link]\
\n\t\t\t\[Link] A Book\n\t\t\t\t4. Search A Book\n\t\t\t\t5. Update A
Book\
\n\t\t\t\
[Link]")ch=int(input())
ifch==1:book.book_i
nput()
elifch==2:book.book_
edit()
elifch==3:book.book_delete()
elifch==4:book.book_search(
)
elifch==5:book.book_update(
)
elif
ch==6:b
reak
elifchoice==2:
while(True):
print("\t\t\t\tEnterYourChoice\n\t\t\t\[Link]\n\t\t\t\
[Link]\
\n\t\t\t\t3. Delete A Member\n\t\t\t\t4. Search A
2
Member\n\t\t\t\
[Link]")ch=int(input())
ifch==1:member.member_in
put()
elifch==2:member.member_
edit()
elifch==3:member.member_
delete()
elifch==4:member.member_search()
elif
ch==5:b
reak
elifchoice==3:w
hile(True):
print("\t\t\t\tEnter Your Choice\n\t\t\t\[Link] Book\n\t\t\t\t2.
ReturnBook\n\t\t\t\[Link]")
ch =
int(input())ifch
==1:
transaction.book_issue()e
lifch==2:
transaction.book_return()
elifch==3:
break
elifchoice==4:w
hile(True):
print("\t\t\t\tEnter Your Choice\n\t\t\t\[Link] Details\n\t\t\t\t2.
MemberDetails\
\n\t\t\t\[Link] Details\n\t\t\t\[Link] Details\n\t\t\t\
[Link](Chart)\
\n\t\t\t\
[Link]")ch=int(input())
ifch==1:report.book_ou
tput()
elifch==2:report.member_
output()
elifch==3:report.issue_outp
ut()
elifch==4:report.return_out
put()
elifch==5:[Link]
_chart()
elifch==6:
3
breakeli
fchoice==5:
break
‘BOOK’MODULE-
[Link]
mportpandasaspd
fromtabulateimporttabulate
con=[Link](host="localhost",user="root",passwd="sanjay",database="library")cursor=con.c
ursor()
def
book_input():tr
y:
bookid=input("Enter Book
Id")bname=input("EnterBookName")a
uthor=input("EnterAuthorName")pric
e=float(input("EnterPrice"))
copies=int(input("EnterNoofCopies"))
qry="insertintobookvalues({},'{}','{}',{},{},
{});".format(bookid,bname,author,price,copies,copies)
[Link](qry)
[Link]()
print("addedsuccessfully..")exce
pt:
print("Error..WorngEntry")
defbook_edit():x=int(input("Ente
rBookID"))
qry="select * from book where bookid =
{};".format(x)[Link](qry)
r=[Link]()
ifr:
y=float(input("EnterNewPrice"))
qry="updatebooksetprice={}wherebookid={};".format(y,x)[Link]
ute(qry)
[Link]()
print("EditedSuccessfully.")
else:
print("WrongBookID")
defbook_update():
4
x=int(input("EnterBookID"))
qry="select * from book where bookid =
{};".format(x)[Link](qry)
r=[Link]()print("Presen
tCopies-",r[4])
print("PresentRemainingCopies-",r[5])ifr:
y=float(input("EnterNoofNewCopies"))
qry = "update book set copies = {}, rem_copies = {} where bookid =
{};".format(r[4]+5,r[5]+5,x)[Link]
ecute(qry)[Link]()
print("UpdatedSuccessfully.")
qry="select *frombook where
bookid={};".format(x)df=pd.read_sql(qry,con)
print("NewUpdatedBookDetails")
print(tabulate(df,headers='keys',tablefmt='psql',showindex=False))else
:
print("WrongBookID")
defbook_delete():x=int(input("Ente
rBookID"))
qry="select * from book where bookid =
{};".format(x)[Link](qry)
r=[Link]()
ifr:
qry = "delete from book where bookid =
{};".format(x)[Link](qry)
[Link]()
print("deletedSuccessfully.")
else:
print("WrongBookID")
defbook_search():x=int(input("Ente
rBookID"))
qry="select * from book where bookid =
{};".format(x)[Link](qry)
r=[Link]()
ifr:
df=pd.read_sql(qry,con)
print(tabulate(df,headers='keys',tablefmt='psql',showindex=False))
5
else:
print("WrongBookID")
‘MEMBER’MODULE-
importbook
[Link]
mportpandasaspd
fromtabulateimporttabulate
con=[Link](host="localhost",user="root",passwd="sanjay",database="library")cursor=con.c
ursor()
defmember_input():tr
y:
memberid=int(input("EnterMemberId"))mn
ame=input("EnterMemberName")madd=inp
ut("EntermemberAddress")phone=input("E
nterPhoneNo")
qry = "insert into member values({},'{}','{}','{}');".format(memberid, mname, madd,
phone)[Link](qry)
[Link]()
print("addedsuccessfully..")exc
ept:
print("Error...")
defmember_edit():x=int(input("Enter
MemberID"))
qry="select*frommemberwherememberid={};".format(x)[Link](
qry)
r=[Link]()
ifr:
y=input("EnterNewAddress")
qry="updatemembersetmadd='{}'wherememberid={};".format(y,x)[Link](qry)
[Link]()
print("EditedSuccessfully.")
else:
print("WrongMemberID")
defmember_delete():
6
x=int(input("EnterMemberID"))
qry="select*frommemberwherememberid={};".format(x)[Link](
qry)
r=[Link]()
ifr:
qry="deletefrommemberwherememberid={};".format(x)[Link]
e(qry)
[Link]()
print("deletedSuccessfully.")
else:
print("WrongmemberID")
defmember_search():x=int(input("En
terMemberID"))
qry="select*frommemberwherememberid={};".format(x)[Link](
qry)
r=[Link]()
ifr:
df=pd.read_sql(qry,con)
print(tabulate(df,headers='keys',tablefmt='psql',showindex=False))
else:
print("WrongMemberID")
‘TRANSACTIONMODULE-
importbook
[Link]
mportpandasaspd
fromtabulateimporttabulate
con=[Link](host="localhost",user="root",passwd="sanjay",database="library")cursor=con.c
ursor()
defbook_issue():
q="selectmax(issueid)fromissue;"cu
[Link](q)
r=[Link]()
[0]ifr:
issueid
=r+1else:
issueid=1
x=int(input("EnterMemberID"))
7
q1="select*frommemberwherememberid={};".format(x)[Link](q)
r=[Link]()
ifr:
y=int(input("EnterBookID"))
q2
="selectbookid,rem_copiesfrombookwherebookid={};".format(y)[Link]
ute(q2)
r=[Link]()
ifr:
ifr[1]>0:
issuedate=input("EnterIssueDate")copies
=int(input("EnterNoofCopies"))remcopies
=r[1]-copies
q3="insertintoissuevalues({},'{}',{},{},
{});".format(issueid,issuedate,x,y,copies)[Link](q3)
q4="updatebooksetrem_copies={}wherebookid={};".format(remcopies,y)[Link](q
4)
[Link]()print("Bo
okIssued...")
else:
print("BookisNotAvailable")els
e:
print("WrongBookID")
else:
print("WrongMemeberId")
defbook_return():
q="selectmax(returnid)fromreturns;"cur
[Link](q)
r=[Link]()
[0]ifr:
reutrnid
=r+1else:
returnid =
1x=int(input("EnterMemberID"))
q1="select*frommemberwherememberid={};".format(x)[Link](q
1)
r=[Link]()
ifr:
y=int(input("EnterBookID"))
q2
="selectbookid,rem_copiesfrombookwherebookid={};".format(y)[Link]
ute(q2)
8
r=[Link]()
ifr:
returndate= input("Enter return
Date")copies = int(input("Enter No of
Copies"))remcopies=r[1]+copies
q3="insertintoreturnsvalues({},'{}',{},{},
{});".format(returnid,returndate,x,y,copies)[Link](q3)
q4="updatebooksetrem_copies={}wherebookid={};".format(remcopies,y)[Link](q4)
[Link]()print("Book
Returned...")
else:
print("WrongBookID")
else:
print("WrongMemeberId")
‘REPORT’MODULE-
[Link]
mportpandasaspd
from tabulate import
[Link]
otasplt
con=[Link](host="localhost",user="root",passwd="sanjay",database="library")cursor=con.c
ursor()
defbook_output():
df=pd.read_sql("select*frombook",con)
print(tabulate(df,headers='keys',tablefmt='psql',showindex=False))
defmember_output():
df=pd.read_sql("select*frommember",con)
print(tabulate(df,headers='keys',tablefmt='psql',showindex=False))
defreturn_output():
df=pd.read_sql("select*fromreturns",con)
print(tabulate(df,headers='keys',tablefmt='psql',showindex=False))
defissue_output():
df=pd.read_sql("select*fromissue",con)
print(tabulate(df,headers='keys',tablefmt='psql',showindex=False))
9
defcol_chart():
q="selectbookid,count(copies)astotalcopiesfromissuegroupbybookid;"df=pd.r
ead_sql(q,con)
print(df)
[Link]([Link],[Link])pl
[Link]("BookID")[Link]("Copi
esIssued")[Link]("BestReadingB
ook")[Link]([Link])
[Link]()
10
