Scribd
Upload
3 views5 pages

LMS SQL Database

The document is a Python script for a Library Management System using SQLite for database management. It includes functionalities for registering students, issuing and returning books, viewing pending transactions, and managing book records. The script features a menu-driven interface for both librarians and students to interact with the system.

Uploaded by

Pradeepkhanth B
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
3 views5 pages

LMS SQL Database

The document is a Python script for a Library Management System using SQLite for database management. It includes functionalities for registering students, issuing and returning books, viewing pending transactions, and managing book records. The script features a menu-driven interface for both librarians and students to interact with the system.

Uploaded by

Pradeepkhanth B
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 5

import sqlite3

import os

# Database functions
def initialize_database():
conn = sqlite3.connect('Library_Mang_System_Database.db')
cursor = conn.cursor()

cursor.execute('''
CREATE TABLE IF NOT EXISTS books (
book_no TEXT PRIMARY KEY,
book_name TEXT NOT NULL,
author TEXT
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
reg_no TEXT PRIMARY KEY,
name TEXT
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS transactions (
reg_no TEXT,
book_no TEXT,
book_name TEXT,
issue_date TEXT,
return_date TEXT,
FOREIGN KEY (reg_no) REFERENCES students(reg_no),
FOREIGN KEY (book_no) REFERENCES books(book_no)
)
''')

conn.commit()
conn.close()

# Original functions updated for SQL


def newstu():
initialize_database()
conn = sqlite3.connect('Library_Mang_System_Database.db')
cursor = conn.cursor()

reg_no = input("Enter registration number: ")


name = input("Enter student name: ")

try:
cursor.execute("INSERT INTO students (reg_no, name) VALUES (?, ?)",
(reg_no, name))
conn.commit()
print("Student registered successfully!")
except sqlite3.IntegrityError:
print("Student already exists!")
finally:
conn.close()
def Stu():
print("------------------------------------------")
print(" ********************************* ")
print(" 1 Take the Book")
print(" 2 Return the Book")
print(" 3 View the Pending Book")
print(" 5 Exit")
print(" ********************************* ")
print("------------------------------------------")
print(" ")
ch1 = input("Enter The Choice : ")
if ch1 == "1":
add()
elif ch1 == "2":
ret()
elif ch1 == "3":
view()
elif ch1 == "5":
exit()
else:
print("Enter the Right choice")

def add():
initialize_database()
conn = sqlite3.connect('Library_Mang_System_Database.db')
cursor = conn.cursor()

reg_no = input("Enter the Reg No : ")


book_no = input("Enter Book No : ")
book_name = input("Enter Book Name : ")
issue_date = input("Enter Issue Date (YYYY-MM-DD): ")

cursor.execute("SELECT * FROM books WHERE book_no=?", (book_no,))


if not cursor.fetchone():
print("Book does not exist in database!")
conn.close()
return

cursor.execute("SELECT * FROM students WHERE reg_no=?", (reg_no,))


if not cursor.fetchone():
print("Student not registered!")
conn.close()
return

try:
cursor.execute('''
INSERT INTO transactions (reg_no, book_no, book_name, issue_date)
VALUES (?, ?, ?, ?)
''', (reg_no, book_no, book_name, issue_date))
conn.commit()
print("Book issued successfully!")
except sqlite3.Error as e:
print(f"Error issuing book: {e}")
finally:
conn.close()
def ret():
initialize_database()
conn = sqlite3.connect('Library_Mang_System_Database.db')
cursor = conn.cursor()

reg_no = input("Enter the Reg No : ")


book_no = input("Enter The Book Number : ")
return_date = input("Enter Return Date (YYYY-MM-DD): ")

try:
cursor.execute('''
UPDATE transactions
SET return_date = ?
WHERE reg_no = ? AND book_no = ? AND return_date IS NULL
''', (return_date, reg_no, book_no))

if cursor.rowcount == 0:
print("No active transaction found for this book and student!")
else:
conn.commit()
print("Book returned successfully!")
except sqlite3.Error as e:
print(f"Error returning book: {e}")
finally:
conn.close()

def view():
initialize_database()
conn = sqlite3.connect('Library_Mang_System_Database.db')
cursor = conn.cursor()

reg_no = input("Enter the Reg No : ")

cursor.execute('''
SELECT book_no, book_name, issue_date
FROM transactions
WHERE reg_no = ? AND return_date IS NULL
''', (reg_no,))

pending_books = cursor.fetchall()
conn.close()

if not pending_books:
print("No pending books for this student!")
else:
print("\nPending Books:")
print("---------------------------------------------------")
print("Book No \tBook Name \tIssue Date")
print("---------------------------------------------------")
for book in pending_books:
print(f"{book[0]} \t{book[1]} \t{book[2]}")

def view_stu():
initialize_database()
conn = sqlite3.connect('Library_Mang_System_Database.db')
cursor = conn.cursor()

print("\n========= Book Database =========")


cursor.execute("SELECT * FROM students")
books = cursor.fetchall()

if not books:
print("No Student in Library database!")
else:
print("Register No \tStudent Name ")
for book in books:
print(f"{book[0]} \t{book[1]}")

conn.close()

def addbook():
initialize_database()
conn = sqlite3.connect('Library_Mang_System_Database.db')
cursor = conn.cursor()

book_no = input(" Enter Book No : ")


book_name = input(" Enter Book Name : ")
author = input(" Enter Book Author : ")

try:
cursor.execute("INSERT INTO books (book_no, book_name, author) VALUES
(?, ?, ?)",
(book_no, book_name, author))
conn.commit()
print("Book added successfully!")
except sqlite3.IntegrityError:
print("Book already exists!")
finally:
conn.close()

def database():
initialize_database()
conn = sqlite3.connect('Library_Mang_System_Database.db')
cursor = conn.cursor()

print("\n========= Book Database =========")


cursor.execute("SELECT * FROM books")
books = cursor.fetchall()

if not books:
print("No books in database!")
else:
print("Book No \tBook Name \tAuthor")
for book in books:
print(f"{book[0]} \t{book[1]} \t{book[2]}")

conn.close()

# Original menu system


while True:
print(" ----------------**********---------------------")
print(" * Library Management System *")
print(" 1. Librarian Login ")
print(" 2. Student Login ")
print(" -----------------**********-------------------")
ch = input("Enter The Choice : ")

if ch == "1":
print("--------------------------------------------------------------")
print(" ")
print(" Library Management System Your in Librarian Login")
print(" ")
print("---------------------------------------------------------------")
print("--------------------------------------------------------------")
print(" 1. View Book Details ")
print(" 2. Add the Book to Database ")
print(" 3. View the Student List ")
print("--------------------------------------------------------------")
ch5 = input("Enter The Choice : ")
if ch5 == "1":
database()
elif ch5 == "2":
addbook()
elif ch5 == "3":
view_stu()

elif ch == "2":
print(" ---------------------------------------------------------")
print(" ")
print(" Library Management System Your in Student Login ")
print("")
print("- ---------------------------------------------------------")
print(" 1. New Student ")
print(" 2. Existing Student ")
print(" -------------------------------------")
ch1 = input("Enter The Choice : ")
if ch1 == "1":
newstu()
elif ch1 == "2":
Stu()
else:
print("Please Select correct choice")

You might also like