Library Management System Python Project for Class 12
# Python Project Library Management System us MySql Connectivity.
import mysql.connector as sqlctr
import sys
from datetime import datetime
mycon = sqlctr.connect(host='localhost', user='root', password='admin')
if mycon.is_connected():
print('\n')
print('Successfully connected to localhost')
else:
print('Error while connecting to localhost')
cursor = mycon.cursor()
#creating database
cursor.execute("create database if not exists pathsala")
cursor.execute("use pathsala")
#creating the tables we need
cursor.execute("create table if not exists books(SN int(5) primary key,Book_Name varchar(30), Quantity_Available int(10),Price_Per_Day int(10))")
cursor.execute("create table if not exists BORROWER(SN int(5),borrowers_name varchar(40),book_lent varchar(20),contact_no int(10))")
def command(st):
cursor.execute(st)
def fetch():
data = cursor.fetchall()
for i in data:
print(i)
def all_data(tname):
li = []
st = 'desc '+tname
command(st)
data = cursor.fetchall()
for i in data:
li.append(i[0])
st = 'select * from '+tname
command(st)
print('\n')
print('-------ALL_DATA_FROM_TABLE_'+tname+'_ARE-------\n')
print(tuple(li))
fetch()
def detail_burrower(name,contact):
tup=('SN','borrowers_name','book_lent','date','contact_no')
print('\n---Details for borrower '+name+'---\n')
print(tup)
st='select * from borrower where borrowers_name like "{}" and contact_no={}'.format(name,contact)
command(st)
fetch()
def days_between(d1, d2):
d1 = datetime.strptime(d1, "%Y-%m-%d")
d2 = datetime.strptime(d2, "%Y-%m-%d")
global days
days=abs((d2 - d1).days)
def price_book(days,book_name):
st1 = 'select Price_Per_Day from books where Book_Name="{}"'.format(book_name)
command(st1)
data = cursor.fetchall()
for i in data:
global t_price
t_price=int(i[0])*days
print('No. of days {} book is kept : {}'.format(book_name,days))
print('Price per day for book {} is Rs.{}'.format(book_name,i[0]))
print('Total fare for book '+book_name +'-',t_price)
def lend():
flag='True'
while flag=='True':
print('\n___AVAILABLE BOOKS___\n')
st0 = 'select Book_Name from books where Quantity_Available>=1'
command(st0)
fetch()
st1='select max(SN) from borrower'
command(st1)
data_sn=cursor.fetchall()
for i in data_sn:
SN=i[0]+1
book_selected=str(input('Enter name of book from above list : '))
borrowers_name=str(input('Enter Borrower Name : '))
date=str(input('Enter date (YYYY-MM-DD) : '))
contact=int(input('Enter contact no. : '))
st_insert='insert into borrower values({},"{}","{}","{}",{})'.format(SN,borrowers_name,book_selected,date,contact)
command(st_insert)
st_quantity='select quantity_available from books where book_name="{}"'.format(book_selected)
command(st_quantity)
data_quantity=cursor.fetchall()
for quantity in data_quantity:
qty=quantity[0]-1
st_dec='update books set quantity_available={} where book_name="{}"'.format(qty,book_selected)
command(st_dec)
dec=str(input('Do you want to add more records (Y/N) : '))
if dec.upper=="Y":
flag= 'True'
else:
flag='False'
def borrowers():
print('\n\n___OPTIONS AVAILABLE___\n\nEnter 1 : To Show detail of all borrowers \nEnter 2 : To check detail of a particular borrower \nEnter 3 : To calculate total fine of a borrower \nEnter 4 : To go Back \nEnter 5 : To commit all the changes and exit')
dec = input('enter your choice-')
if dec=='1':
all_data('borrower')
elif dec=='2':
name = str(input('\nenter borrower name-'))
contact = str(input('enter borrower contact no.-'))
detail_burrower(name,contact)
elif dec=='3':
tfine()
elif dec=='4':
action_list()
elif dec=='5':
close()
borrowers()
def tfine():
name=str(input('\nEnter borrower name : '))
contact=input('Enter borrower contact_no : ')
detail_burrower(name, contact)
st1 = 'select book_lent from borrower where borrowers_name ="{}" and contact_no={}'.format(name,contact)
command(st1)
data=cursor.fetchall()
for i in data:
book_name=i[0]
st2 = 'select date from borrower where borrowers_name="{}" and book_lent="{}"'.format(name,book_name)
command(st2)
data1=cursor.fetchall()
for date in data1:
date_taken=date[0]
date_return = str(input('\nEnter returning date for book "{}" (YYYY-MM-DD) , Press ENTER to skip-'.format(book_name)))
while date_return!='':
days_between(str(date_return),str(date_taken))
price_book(days,i[0])
print('\nEnter Y : If Rs.{} is paid and book is returned.\nEnter N : If fare is not paid and book is not returned.'.format(t_price))
dec=str(input('Enter (Y?N) : '))
if dec.upper()=="Y":
st= 'select SN , Quantity_Available from books where Book_Name ="{}"'.format(i[0])
command(st)
data2=cursor.fetchall()
for price in data2:
update('books', 'Quantity_Available',price[1]+1,price[0])
st_del = 'delete from borrower where borrowers_name="{}" and book_lent="{}"'.format(name,book_name)
command(st_del)
break
else:
print("\n\nPLEASE PAY THE FARE AND RETURN BOOK AFTER READING.\n\n")
break
def insert():
flag = 'true'
while flag=='true':
licol=[]
li1=[]
li_val=[]
command('desc books')
data=cursor.fetchall()
for i in data:
licol.append(i[0])
command('select max(SN) from books')
dta=cursor.fetchall()
for j in dta:
li_val.append(j[0]+1)
for k in range(1,4):
val = str(input('Enter '+licol[k]+'-'))
li_val.append(val)
li1.append(tuple(li_val))
values = ', '.join(map(str, li1))
st1 = "INSERT INTO books VALUES {}".format(values)
command(st1)
all_data('books')
print('\n')
print("\nDATA INSERTED SUCCESSFULLY\n")
dec = str(input('Do u want to insert more data?(Y/N)-'))
if dec.upper() == "Y":
flag='true'
else:
flag='false'
action_list()
def update(tname,col1,post_value,pre_value):
st = str('update %s set %s=%s where SN=%s') % (tname, col1, "'%s'", "'%s'") % (post_value, pre_value)
command(st)
all_data(tname)
print('\nVALUE UPDATED SUCCESSFULLY')
def close():
mycon.commit()
mycon.close()
if mycon.is_connected():
print('still connected to localhost')
else:
print('\n\nconnection closed successfully.')
sys.exit()
def action_list():
print('\n')
print('#### WELCOME TO LIBRARY MANAGEMENT SYSTEM ####\n\nEnter 1 : To View details of all available Books\nEnter 2 : To check detail of a particular book\nEnter 3 : To lend a book \nEnter 4 : To add new books in list \nEnter 5 : To update data \nEnter 6 : To view details of borrowers \nEnter 7 : To commit all changes and exit')
dec = input('\nenter your choice-')
if dec == '1':
all_data('books')
elif dec=='2':
tup=('SN','Book_Name','Quantity_Available','Price_Per_Day')
tup1 = ('SN', 'borrowers_name', 'book_lent', 'contact_no')
in1=str(input('enter first name , last name or middle name of a book-'))
print('\n___ALL DATA OF BOOKS HAVING "{}" IN THEIR NAME FROM BOTH TABLE____'.format(in1))
st =str('select * from books where book_name like "{}"'.format('%'+in1+'%'))
st1=str('select * from borrower where book_lent like "{}"'.format('%'+in1+'%'))
print('\n__DATA FROM TABLE BOOKS__\n')
command(st)
print(tup)
fetch()
print('\n__DATA FROM TABLE BORROWER__\n')
command(st1)
print(tup1)
fetch()
print()
elif dec == '3':
lend()
elif dec=='4':
insert()
elif dec=='5':
flag='true'
while flag=='true':
tname = 'books'
li = []
st1 = 'desc '+tname
command(st1)
data = cursor.fetchall()
for i in data:
li.append(i[0])
all_data(tname)
print('\n columns in table '+tname+' are')
print(li)
col1 = str(input('enter column name for modification from above list-'))
lipo = ['SN']
lipo.append(col1)
print(tuple(lipo))
st0 = 'select SN , %s from books' % (col1)
command(st0)
fetch()
pre_value = str(input('enter corresponding SN for the data to be changed-'))
post_value = str(input('enter new value for column %s having SN %s-' % (col1, pre_value)))
update(tname, col1, post_value, pre_value)
dec = str(input('Do you want to change more data?(Y/N)-'))
if dec == 'y' or dec == 'Y':
flag='true'
else:
flag='false'
elif dec=='6':
borrowers()
elif dec=='7':
close()
action_list()
action_list()
Output:
Successfully connected to localhost
#### WELCOME TO LIBRARY MANAGEMENT SYSTEM ####
Enter 1 : To View details of all available Books
Enter 2 : To check detail of a particular book
Enter 3 : To lend a book
Enter 4 : To add new books in list
Enter 5 : To update data
Enter 6 : To view details of borrowers
Enter 7 : To commit all changes and exit
enter your choice-1
-------ALL_DATA_FROM_TABLE_books_ARE-------
('SN', 'Book_Name', 'Quantity_Available', 'Price_Per_Day')
#### WELCOME TO LIBRARY MANAGEMENT SYSTEM ####
Enter 1 : To View details of all available Books
Enter 2 : To check detail of a particular book
Enter 3 : To lend a book
Enter 4 : To add new books in list
Enter 5 : To update data
Enter 6 : To view details of borrowers
Enter 7 : To commit all changes and exit
enter your choice-2
enter first name , last name or middle name of a book-python
___ALL DATA OF BOOKS HAVING "python" IN THEIR NAME FROM BOTH TABLE____
__DATA FROM TABLE BOOKS__
('SN', 'Book_Name', 'Quantity_Available', 'Price_Per_Day')
__DATA FROM TABLE BORROWER__
('SN', 'borrowers_name', 'book_lent', 'contact_no')
#### WELCOME TO LIBRARY MANAGEMENT SYSTEM ####
Enter 1 : To View details of all available Books
Enter 2 : To check detail of a particular book
Enter 3 : To lend a book
Enter 4 : To add new books in list
Enter 5 : To update data
Enter 6 : To view details of borrowers
Enter 7 : To commit all changes and exit
enter your choice-3
___AVAILABLE BOOKS___
Explanation:
This code is a Python program for a library management system that uses MySQL as a database. The program allows the librarian to add books to the library, lend books to borrowers, and keep track of borrowers’ details, including the book they borrowed, the date it was borrowed, and the borrower’s contact details.
Firstly, the code imports the necessary modules and establishes a connection to the MySQL server. It then checks whether the connection is established successfully or not.
The program then creates a database called “pathsala” and sets it as the default database. Two tables are created, namely “books” and “borrower,” to store the data related to books and borrowers, respectively.
The program defines several functions to perform various tasks, such as executing SQL commands, fetching data from the database, and calculating the total fare for a book.
The function “all_data(tname)” is used to display all the data stored in a particular table in a formatted manner. The function first retrieves the column names of the specified table and stores them in a list. It then retrieves all the data from the specified table and prints it along with the column names in a formatted manner.
The function “detail_burrower(name, contact)” is used to display the details of a particular borrower. It takes the borrower’s name and contact number as input and displays the details of the borrower in a formatted manner.
The function “days_between(d1, d2)” is used to calculate the number of days between two dates. It takes two dates as input and calculates the number of days between them.
The function “price_book(days, book_name)” is used to calculate the total fare for a book borrowed by a borrower. It takes the number of days the book is borrowed and the name of the book as input and calculates the total fare by multiplying the price per day with the number of days.
The function “lend()” is used to lend a book to a borrower. It displays the list of available books, takes the borrower’s name, contact number, and the date of borrowing as input and updates the “borrower” and “books” tables accordingly. It also asks the user whether to lend more books or not.
The function “borrowers()” is used to display the options available to the librarian. It allows the user to view the details of all borrowers, check the details of a particular borrower, calculate the total fine of a borrower, go back to the previous menu, and exit the program.
The function “tfine()” is used to calculate the total fine for a borrower. It takes the borrower’s name and contact number as input, retrieves the book the borrower borrowed, and the date on which the book was borrowed. It then asks the user to enter the date on which the book was returned and calculates the total fine based on the number of days the book was borrowed and the price per day.
In summary, the program provides a simple library management system using Python and MySQL. It allows the librarian to manage books and borrowers and perform various tasks such as lending books, calculating fines, and displaying borrower details.