Library Management System Python Project for Class 12

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___

Leave a Reply

Your email address will not be published. Required fields are marked *