LIBRARY MANAGEMENT SYSTEM
(Class XII CBSE Computer Science Project)
INTRODUCTION:
The Library Management System is a menu-driven Python program. It helps manage books, members, book issue/return in a library. The project uses MySQL as the backend database and Python for frontend concepts.
A library requires systematic management of books and members.
This project computerizes library operations such as:
- Adding and displaying books
- Issuing and returning books
- Calculating late return fines
- Maintaining permanent records using MySQL
The system is easy to understand, error-free, and practical-file ready.
🔹 OBJECTIVES
- To understand MySQL database connectivity in Python
- To implement menu-driven programs
- To use functions and conditional statements
- To calculate and store fines using date handling
- To present data using tabulate module
HARDWARE & SOFTWARE REQUIREMENTS
Hardware
- Computer / Laptop
- Minimum 4 GB RAM
Software
- Python 3.x
- MySQL Server
- mysql-connector-python
- tabulate module
🔹 FLOWCHART (Text Description)
Start
↓
Connect to MySQL
↓
Create Database & Tables
↓
Display Menu
↓
User Choice?
├── Add Book
├── Display Books
├── Search Book
├── Add Member
├── Issue Book
├── Return Book → Calculate Fine → Save Fine
├── Delete Book
└── Exit
↓
StopCOMPLETE PYTHON CODE (Single File)
# LIBRARY MANAGEMENT SYSTEM
# Class XII CBSE Project
import mysql.connector
from datetime import date
from tabulate import tabulate
'''
import pymysql
con = pymysql.connect(
host="localhost",
user="root",
password="root"
)
'''
# ---------------- DATABASE CONNECTION ----------------
con = mysql.connector.connect(
host="localhost",
user="root",
password="root"
)
cur = con.cursor()
# ---------------- CREATE DATABASE ----------------
cur.execute("CREATE DATABASE IF NOT EXISTS Library")
cur.execute("USE Library")
# ---------------- CREATE TABLES ----------------
cur.execute("""
CREATE TABLE IF NOT EXISTS books(
book_id INT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(100),
publisher VARCHAR(100),
price INT,
quantity INT
)
""")
cur.execute("""
CREATE TABLE IF NOT EXISTS members(
member_id INT PRIMARY KEY,
name VARCHAR(100),
class VARCHAR(20),
phone VARCHAR(15)
)
""")
cur.execute("""
CREATE TABLE IF NOT EXISTS issue(
issue_id INT PRIMARY KEY AUTO_INCREMENT,
member_id INT,
book_id INT,
issue_date DATE,
return_date DATE,
fine INT
)
""")
# ---------------- INSERT DUMMY BOOKS ----------------
cur.execute("SELECT COUNT(*) FROM books")
if cur.fetchone()[0] == 0:
dummy_books = [
(101, "Python Basics", "Guido", "Pearson", 350, 5),
(102, "Computer Science", "Sumita Arora", "Dhanpat Rai", 450, 4),
(103, "Data Structures", "Seymour", "McGraw", 500, 3),
(104, "DBMS", "Navathe", "Pearson", 400, 6),
(105, "Java Programming", "Herbert", "Oracle", 550, 2),
(106, "AI Basics", "Russell", "Pearson", 600, 3)
]
cur.executemany("INSERT INTO books VALUES (%s,%s,%s,%s,%s,%s)", dummy_books)
con.commit()
# ---------------- FUNCTIONS ----------------
def add_book():
try:
bid = int(input("Book ID: "))
title = input("Title: ")
author = input("Author: ")
pub = input("Publisher: ")
price = int(input("Price: "))
qty = int(input("Quantity: "))
cur.execute("INSERT INTO books VALUES (%s,%s,%s,%s,%s,%s)",
(bid, title, author, pub, price, qty))
con.commit()
print("✅ Book added successfully")
except:
print("❌ Error adding book")
def display_books():
cur.execute("SELECT book_id,title,author,quantity FROM books")
data = cur.fetchall()
print(tabulate(data, headers=["bid","title","author","qty"], tablefmt="grid"))
def search_book():
key = input("Enter Title or Author: ")
cur.execute("SELECT book_id,title,author,quantity FROM books WHERE title LIKE %s OR author LIKE %s",
('%'+key+'%','%'+key+'%'))
data = cur.fetchall()
print(tabulate(data, headers=["bid","title","author","qty"], tablefmt="grid"))
def add_member():
try:
mid = int(input("Member ID: "))
name = input("Name: ")
cls = input("Class: ")
phone = input("Phone: ")
cur.execute("INSERT INTO members VALUES (%s,%s,%s,%s)",
(mid, name, cls, phone))
con.commit()
print("✅ Member registered")
except:
print("❌ Error registering member")
def issue_book():
mid = int(input("Member ID: "))
cur.execute("SELECT member_id FROM members where member_id={}".format(mid))
mem_id = cur.fetchone()
if mem_id:
pass
else:
print("member id not exist.")
return
bid = int(input("Book ID: "))
cur.execute("SELECT quantity FROM books WHERE book_id=%s", (bid,))
qty = cur.fetchone()
if qty and qty[0] > 0:
cur.execute("INSERT INTO issue(member_id,book_id,issue_date,fine) VALUES (%s,%s,%s,%s)",
(mid, bid, date.today(), 0))
cur.execute("UPDATE books SET quantity=quantity-1 WHERE book_id=%s", (bid,))
con.commit()
print("✅ Book issued successfully")
else:
print("❌ Book not available")
'''
def show_issued_books():
cur.execute("SELECT * FROM issue")
data = cur.fetchall()
if data:
for row in data:
print(row)
'''
def show_issued_books():
cur.execute("SELECT * FROM issue")
data = cur.fetchall()
if not data:
print("No issued books found.")
return
table = []
for row in data:
issue_date = row[3].strftime("%d-%m-%Y") if row[3] else "NULL"
return_date = row[4].strftime("%d-%m-%Y") if row[4] else "NULL"
table.append([
row[0], # issue_id
row[1], # member_id
row[2], # book_id
issue_date,
return_date,
row[5] # fine
])
headers = ["IID", "MID", "BID", "ISS_DT", "RET_DT", "FINE"]
print(tabulate(table, headers=headers, tablefmt="grid"))
def return_book():
iid = int(input("Issue ID: "))
cur.execute("SELECT issue_date, book_id FROM issue WHERE issue_id=%s AND return_date IS NULL", (iid,))
rec = cur.fetchone()
if rec:
issue_dt, bid = rec
days = (date.today() - issue_dt).days
fine = 0
if days > 7:
fine = (days - 7) * 10
print("Issue date:",issue_dt)
print("No of days:",days)
print("Return date:",date.today())
cur.execute("UPDATE issue SET return_date=%s, fine=%s WHERE issue_id=%s",
(date.today(), fine, iid))
cur.execute("UPDATE books SET quantity=quantity+1 WHERE book_id=%s", (bid,))
con.commit()
print(tabulate([[iid, days, fine]], headers=["issue_id","days","fine"], tablefmt="grid"))
print("Book returned successfully.")
else:
print("❌ Invalid Issue ID")
def delete_book():
bid = int(input("Book ID to delete: "))
cur.execute("DELETE FROM books WHERE book_id=%s", (bid,))
con.commit()
print("✅ Book deleted")
# ---------------- MENU ----------------
while True:
print("""
===== LIBRARY MANAGEMENT SYSTEM =====
1. Add New Book
2. Display All Books
3. Search Book
4. Register New Member
5. Issue Book
6. Return Book
7. Delete Book
8. show issued books
9. Exit
""")
ch = int(input("Enter choice: "))
if ch == 1: add_book()
elif ch == 2: display_books()
elif ch == 3: search_book()
elif ch == 4: add_member()
elif ch == 5: issue_book()
elif ch == 6: return_book()
elif ch == 7: delete_book()
elif ch == 8: show_issued_books()
elif ch == 9:
print("Thank You!")
break
else:
print("Invalid choice")