Library Management System
# -*- coding: utf-8 -*-
"""
Created on Tue Aug 20 21:08:40 2024
@author: Arshad
"""
import mysql.connector
from datetime import datetime
# Establishing the connection
conn = mysql.connector.connect(
host="localhost",
user="arshad",
password="arshad123",
database="library_db1")
if conn.is_connected():
print("mysql connected")
cursor = conn.cursor()
def add_book(title, author):
cursor.execute("INSERT INTO books (title, author) VALUES (%s, %s)",(title,
author))
print("Book added successfully.")
conn.commit()
def view_books():
cursor.execute( "SELECT * FROM books")
books = cursor.fetchall()
for book in books:
print(book)
def add_member(name):
cursor.execute("INSERT INTO members (name) VALUES (%s)", (name,))
print("Member added successfully.")
conn.commit()
def borrow_book(book_id, member_id):
# Check if the book is available
cursor.execute("SELECT available FROM books WHERE book_id = %s", (book_id,))
available = cursor.fetchone()[0]
# Update book availability
if available ==1:
cursor.execute("UPDATE books SET available = 0 WHERE book_id = %s",
(book_id,))
# Record the transaction
cursor.execute("UPDATE transactions SET return_date = %s WHERE book_id = %s
AND member_id = %s AND return_date IS NULL",
(datetime.now().date(), book_id, member_id))
print("Book borrowed successfully.")
conn.commit()
else:
print("Book is not available.")
def return_book(book_id, member_id):
# Update book availability
cursor.execute("UPDATE books SET available = 1 WHERE book_id = %s", (book_id,))
# Update the transaction with the return date
cursor.execute("UPDATE transactions SET return_date = %s WHERE book_id = %s AND
member_id = %s",
(datetime.now().date(), book_id, member_id))
conn.commit()
print("Book returned successfully.")
def main():
while True:
print("\nLibrary Management System")
print("1. Add Book")
print("2. View Books")
print("3. Add Member")
print("4. Borrow Book")
print("5. Return Book")
print("6. Exit")
choice = input("Enter your choice: ")
try:
if choice == '1':
title = input("Enter book title: ")
author = input("Enter book author: ")
add_book(title, author)
elif choice == '2':
view_books()
elif choice == '3':
name = input("Enter member name: ")
add_member(name)
elif choice == '4':
book_id = int(input("Enter book ID: "))
member_id = int(input("Enter member ID: "))
borrow_book(book_id, member_id)
elif choice == '5':
book_id = int(input("Enter book ID: "))
member_id = int(input("Enter member ID: "))
return_book(book_id, member_id)
elif choice == '6':
break
else:
print("Invalid choice. Please try again.")
except:
print('Only integer!')
main()
# Close the connection
cursor.close()
conn.close()
SQL
CREATE DATABASE library_db;
USE library_db;
CREATE TABLE books (
book_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,
available INT DEFAULT 1
);
CREATE TABLE members (
member_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE transactions (
transaction_id INT AUTO_INCREMENT PRIMARY KEY,
book_id INT(3),
member_id INT(3),
borrow_date DATE,
return_date DATE,
FOREIGN KEY (book_id) REFERENCES books(book_id),
FOREIGN KEY (member_id) REFERENCES members(member_id)
);
