Project analysis

Our application program is specially

designed for the School Library of
LIONS ENGLISH SCHOOL

They lend books to students who come

in the library.

We have tried to maximise the efficiency and strived for

Students and Librarian ease as well as satisfaction.

We have thoroughly examined the needs of the library

and
after analysis, we have constructed the program.

We have used PYTHON and MYSQL as our platform

to carry out this task.

Page | 1
 Functions and modules

Modules:
import mysql.connector :-
By importing this package, we are able to establish the
connection between MySQL and Python.
It is not default module in Python we have to install it by
writing following code in Microsoft powershell:
pip install mysql-connector-python

import datetime:-
Python datetime module supplies classes to work with
date and time. These classes provide a number of
functions to deal with dates, times, and time intervals.

Page | 2
Functions:
1) connect( ):
This function establishes connection between Python
and MySQL.

2) cursor( ):
It is a special control structure that facilitates the row-
by-row processing of records in the result set.
The Syntax is:
<cursor object>=<connection object>.cursor()

3) execute( ):
This function is used to execute the sql query and
retrieve records using python.
The syntax is:
<cursor object>.execute(<sql query string>)

4) def( ):
A function is a block of code which only runs when it is
called. def() help user to create their own function.
Page | 3
5) fetchall( ):
This function will return all the rows from the results set
in the form of a tuple containing the records.

6) fetchone( ):
This Function will return one row from the result set in
the form of a tuple containing the records.

7) commit( ):
This function provides changes in the database physically
and permanently.

Page | 4
 Detailed DESCRIPTION
Our Project has 3 MySQL tables. These are:-
1)books
2)issue_table
3)return_table

1) The table books contain the following columns:

a)BookID b)BookName c)Author
d)QTY e)Subject

2) The table issue_table contain the following

columns:-
a)BookID b)Student_UID c)StudentName
d)Issuedate

3)The table return_table contain the following

columns:-
a)BookID b)Student_UID c)StudentName
d)Returndate
Page | 5
 Source code
For MySQL:

CREATE DATABASE school_library;

USE school_library;

CREATE TABLE books

(BookID INT AUTO_INCREMENT PRIMARY KEY,
BookName VARCHAR(255), Author VARCHAR(255),
QTY INT, Subject VARCHAR(255));

CREATE TABLE issue_table

(BookID INT, Student_UID INT, StudentName VARCHAR(255),
Issuedate DATE);

CREATE TABLE return_table

(BookID INT, Student_UID INT, StudentName VARCHAR(255),
Returndate DATE);

Page | 6
ADDING BOOKS IN TABLE books IN MY SQL

INSERT INTO books (BookID, BookName, Author, QTY, Subject)

VALUES
(101, 'Concept of Physics Vol1', 'H.C. Verma', 3, 'Physics'),
(102, 'Concept of Physics Vol2', 'H.C. Verma', 4, 'Physics'),
(103, 'Modern ABC Class 12 Chem Part 1', 'S.P. Jauhar', 2, 'Chemistry'),
(104, 'Modern ABC Class 12 Chem Part 2', 'S.P. Jauhar', 3, 'Chemistry'),
(105, 'Atomic Habits', 'James Clear', 5, 'English Litrature'),
(106, 'Mathematics Vol1 Class 12', 'R.D. Sharma', 3, 'Mathematics'),
(107, 'Mathematics Vol2 Class 12', 'R.D. Sharma', 3, 'Mathematics'),
(108, 'Computer Science with Python Class 12', 'Sumita Arora', 3,
'Computer Science'),
(109, 'Computer Science with Python Class 12', 'Sumita Arora', 3,
'Computer Science');

Page | 7
For Python:
import mysql.connector
from datetime import date

def connect():
return mysql.connector.connect(
host="localhost",
user="root",
password="jeet2195",
database="school_library"
)

def create_tables():
db = connect()
cursor = db.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS books (BookID INT
AUTO_INCREMENT PRIMARY KEY, BookName VARCHAR(255), Author
VARCHAR(255), QTY INT, Subject VARCHAR(255))")
cursor.execute("CREATE TABLE IF NOT EXISTS issue_table (BookID
INT, Student_UID INT, StudentName VARCHAR(255), Issuedate
DATE)")
cursor.execute("CREATE TABLE IF NOT EXISTS return_table (BookID
INT, Student_UID INT, StudentName VARCHAR(255), Returndate
DATE)")
db.close()

def add_book(bookname, author, total, subject):

db = connect()
cursor = db.cursor()

Page | 8
 sql = "INSERT INTO books (BookName, Author, QTY, Subject)
VALUES (%s, %s, %s, %s)"
val = (bookname, author, total, subject)
cursor.execute(sql, val)
db.commit()
db.close()

def issue_book(bookid, studentUID, studentname):

db = connect()
cursor = db.cursor()
# Check if the book is available
cursor.execute("SELECT QTY FROM books WHERE BookID = %s",
(bookid,))
result = cursor.fetchone()
if result[0] > 0:
# If available, issue the book and decrease the total by 1
cursor.execute("UPDATE books SET QTY = QTY - 1 WHERE BookID
= %s", (bookid,))
sql = "INSERT INTO issue_table (BookID, Student_UID,
StudentName, Issuedate) VALUES (%s, %s, %s, %s)"
val = (bookid, studentUID, studentname, date.today())
cursor.execute(sql,val)
db.commit()
else:
print("Sorry, this book is not available.")
db.close()

def return_book(bookid, studentUID, studentname):

db = connect()
cursor = db.cursor()

Page | 9
 sql = "INSERT INTO return_table (BookID, Student_UID,
StudentName, Returndate) VALUES (%s,%s,%s,%s)"
val = (bookid ,studentUID ,studentname ,date.today())
cursor.execute(sql,val)
db.commit()
db.close()

def delete_book(bookid):
db = connect()
cursor = db.cursor()
sql = "DELETE FROM books WHERE BookID = %s"
val = (bookid,)
cursor.execute(sql,val)
db.commit()
db.close()

def display_books():
db = connect()
cursor = db.cursor()
cursor.execute("SELECT * FROM books")
c=cursor.fetchall()
print("\nAVAILABLE BOOKS")
for x in c:
print("\nBookID:",x[0])
print("BookName:",x[1])
print("Author:",x[2])
print("Total book:",x[3])
print("Subject:",x[4])
print('\n')

def report_menu():
Page | 10
 while True:
print("\n1. Display Books Issue")
print("2. Display Books Return")
print("3. Back to Main Menu")
choice = int(input("Enter your choice: "))
if choice == 1:
display_issue_table()
elif choice == 2:
display_return_table()
elif choice == 3:
break
else:
print("Invalid choice. Please try again.")

def display_issue_table():
db = connect()
cursor = db.cursor()
cursor.execute("SELECT * FROM issue_table")
print()
c=cursor.fetchall()
for x in c:
print("\nBookID:",x[0])
print("Student_UID:",x[1])
print("StudentName:",x[2])
print("Issuedate:",x[3])
print('\n')

def display_return_table():
db = connect()
cursor = db.cursor()
cursor.execute("SELECT * FROM return_table")
Page | 11
 print()
c=cursor.fetchall()
for x in c:
print("\nBookID:",x[0])
print("Student_UID:",x[1])
print("StudentName:",x[2])
print("Returndate:",x[3])
print('\n')

create_tables()

while True:
print("""\n---------------WELCOME TO SCHOOL LIBRARY---------------
\n MENU OF TASKS:
1. Add Book
2. Issue Book
3. Return Book
4. Delete Book
5. Display Books
6. Report Menu
7. Exit """)

choice = int(input("\nEnter the task number: "))

if choice == 1:
bookname= input('\nEnter book name: ')
author= input('Enter author name: ')
total= int(input('Enter total number of books: '))
subject= input('Enter subject: ')
add_book(bookname ,author ,total ,subject)
print("Book added Sucessfully!")
Page | 12
elif choice == 2:
bookid= int(input('\nEnter book id: '))
studentUID= int(input('Enter Student UID: '))
studentname= input('Enter Student Name: ')
issue_book(bookid ,studentUID ,studentname)
print("Book Issued Sucessfully to: ",studentname)

elif choice == 3:
bookid= int(input('\nEnter book id: '))
studentUID= int(input('Enter Student UID: '))
studentname= input('Enter Student Name: ')
return_book(bookid ,studentUID ,studentname)
print("Book Returned by : ",studentname)

elif choice == 4:
bookid= int(input('\nEnter book id to delete: '))
delete_book(bookid)
print("Book Deleted Sucessfully!")
elif choice == 5:
display_books()

elif choice == 6:
report_menu()

elif choice ==7:

print("Thank you! Have a great day")
break

else:
print("Invalid choice. Please try again.")
Page | 13
 output and tables

→OUTPUTS:
1.) Add a Book:

Page | 14
Page | 15
2.) Issue a Book:

Page | 16
3.) Return a Book:

Page | 17
4.) Delete a Book:

Page | 18
5.)Display Books:

Page | 19
Page | 20
Page | 21
Page | 22
6.)Report Menu:

Page | 23
Page | 24
Page | 25
7.)Exit:

Page | 26
→Tables:
1.) SELECT * FROM books;

Page | 27
2.) SELECT * FROM issue_table;

3.) SELECT * FROM return_table;

Page | 28
 Bibliography

To develop this project the following references

were used:

1) https://www.tutorialspoint.com
2) https://github.com

Page | 29