"""
Library Book Borrowing System (Tkinter + SQLite)
Features:
- User Login / Register
- Admin Login
- Borrow / Return Books
- Due Date Tracking
- Overdue Alerts
"""
import sqlite3
import tkinter as tk
from tkinter import ttk, messagebox
from datetime import datetime, timedelta
DB = "library.db"
# ----------------- DATABASE SETUP -----------------
def init_db():
conn = sqlite3.connect(DB)
c = conn.cursor()
# Users Table
c.execute("""
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE,
password TEXT,
role TEXT
)
""")
# Books Table
c.execute("""
CREATE TABLE IF NOT EXISTS books (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT,
author TEXT,
available INTEGER DEFAULT 1
)
""")
# Borrow Table
c.execute("""
CREATE TABLE IF NOT EXISTS borrowed (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
book_id INTEGER,
borrowed_date TEXT,
due_date TEXT,
FOREIGN KEY(user_id) REFERENCES users(id),
FOREIGN KEY(book_id) REFERENCES books(id)
)
""")
# Default admin
c.execute("SELECT * FROM users WHERE role='admin'")
if not c.fetchone():
c.execute("INSERT INTO users(username, password, role) VALUES('admin','admin','admin')")
print("Default admin created: admin / admin")
conn.commit()
conn.close()
# ----------------- LOGIN WINDOW -----------------
class LoginWindow:
def __init__(self, root):
self.root = root
root.title("Library System - Login")
root.geometry("350x250")
tk.Label(root, text="Username:", font=("Arial", 12)).pack(pady=5)
self.username_entry = tk.Entry(root)
self.username_entry.pack()
tk.Label(root, text="Password:", font=("Arial", 12)).pack(pady=5)
self.password_entry = tk.Entry(root, show="*")
self.password_entry.pack()
tk.Button(root, text="Login", command=self.login).pack(pady=10)
tk.Button(root, text="Register", command=self.register).pack()
def login(self):
username = self.username_entry.get()
password = self.password_entry.get()
conn = sqlite3.connect(DB)
c = conn.cursor()
c.execute("SELECT * FROM users WHERE username=? AND password=?", (username, password))
user = c.fetchone()
conn.close()
if user:
role = user[3]
if role == "admin":
AdminDashboard(tk.Toplevel(), user)
else:
UserDashboard(tk.Toplevel(), user)
else:
messagebox.showerror("Error", "Invalid credentials!")
def register(self):
RegisterWindow(tk.Toplevel())
# ----------------- REGISTER WINDOW -----------------
class RegisterWindow:
def __init__(self, root):
self.root = root
root.title("Register")
root.geometry("300x250")
tk.Label(root, text="Create Username").pack(pady=5)
self.user_entry = tk.Entry(root)
self.user_entry.pack()
tk.Label(root, text="Create Password").pack(pady=5)
self.pass_entry = tk.Entry(root, show="*")
self.pass_entry.pack()
tk.Button(root, text="Register", command=self.register_user).pack(pady=10)
def register_user(self):
username = self.user_entry.get()
password = self.pass_entry.get()
conn = sqlite3.connect(DB)
c = conn.cursor()
try:
c.execute("INSERT INTO users(username,password,role) VALUES(?,?,?)",
(username, password, "user"))
conn.commit()
messagebox.showinfo("Success", "Registration complete!")
self.root.destroy()
except:
messagebox.showerror("Error", "Username already exists.")
conn.close()
# ----------------- ADMIN DASHBOARD -----------------
class AdminDashboard:
def __init__(self, root, user):
self.root = root
root.title("Admin Dashboard")
root.geometry("600x500")
tk.Label(root, text="Admin Dashboard", font=("Arial", 16)).pack(pady=10)
tk.Button(root, text="Add Book", width=20, command=self.add_book_window).pack(pady=5)
tk.Button(root, text="Remove Book", width=20, command=self.remove_book_window).pack(pady=5)
tk.Button(root, text="View All Books", width=20, command=self.view_books).pack(pady=5)
tk.Button(root, text="View Users", width=20, command=self.view_users).pack(pady=5)
def add_book_window(self):
win = tk.Toplevel()
win.title("Add Book")
win.geometry("300x200")
tk.Label(win, text="Title").pack()
title = tk.Entry(win)
title.pack()
tk.Label(win, text="Author").pack()
author = tk.Entry(win)
author.pack()
def save():
conn = sqlite3.connect(DB)
c = conn.cursor()
c.execute("INSERT INTO books(title, author) VALUES(?,?)", (title.get(), author.get()))
conn.commit()
conn.close()
messagebox.showinfo("Success", "Book Added!")
win.destroy()
tk.Button(win, text="Save", command=save).pack(pady=10)
def remove_book_window(self):
win = tk.Toplevel()
win.title("Remove Book")
win.geometry("300x200")
tk.Label(win, text="Book ID").pack()
book_id = tk.Entry(win)
book_id.pack()
def delete():
conn = sqlite3.connect(DB)
c = conn.cursor()
c.execute("DELETE FROM books WHERE id=?", (book_id.get(),))
conn.commit()
conn.close()
messagebox.showinfo("Removed", "Book deleted!")
win.destroy()
tk.Button(win, text="Delete", command=delete).pack(pady=10)
def view_books(self):
BookListWindow(tk.Toplevel(), admin=True)
def view_users(self):
UsersListWindow(tk.Toplevel())
# ----------------- USER DASHBOARD -----------------
class UserDashboard:
def __init__(self, root, user):
self.root = root
self.user = user
root.title("User Dashboard")
root.geometry("600x500")
tk.Label(root, text=f"Welcome {user[1]}", font=("Arial", 16)).pack(pady=10)
tk.Button(root, text="Borrow Book", width=20, command=self.borrow_window).pack(pady=10)
tk.Button(root, text="Return Book", width=20, command=self.return_window).pack(pady=10)
tk.Button(root, text="My Borrowed Books", width=20, command=self.my_books).pack(pady=10)
self.check_due_alerts()
def borrow_window(self):
BookListWindow(tk.Toplevel(), user=self.user)
def return_window(self):
ReturnBookWindow(tk.Toplevel(), self.user)
def my_books(self):
UserBorrowedBooks(tk.Toplevel(), self.user)
def check_due_alerts(self):
conn = sqlite3.connect(DB)
c = conn.cursor()
today = datetime.now()
c.execute("""SELECT books.title, borrowed.due_date
FROM borrowed
JOIN books ON books.id = borrowed.book_id
WHERE borrowed.user_id=?""",
(self.user[0],))
rows = c.fetchall()
conn.close()
alerts = []
for title, due_date in rows:
due = datetime.strptime(due_date, "%Y-%m-%d")
days_left = (due - today).days
if days_left < 0:
alerts.append(f"OVERDUE: {title} (Due {due_date})")
elif days_left <= 2:
alerts.append(f"Due Soon: {title} (Due {due_date})")
if alerts:
messagebox.showwarning("Due Date Alerts", "\n".join(alerts))
# ----------------- BOOK LIST WINDOW -----------------
class BookListWindow:
def __init__(self, root, admin=False, user=None):
self.root = root
self.user = user
root.title("Books List")
root.geometry("600x400")
columns = ("ID","Title","Author","Available")
tree = ttk.Treeview(root, columns=columns, show="headings")
for col in columns:
tree.heading(col, text=col)
tree.pack(fill="both", expand=True)
conn = sqlite3.connect(DB)
c = conn.cursor()
c.execute("SELECT * FROM books")
rows = c.fetchall()
conn.close()
for r in rows:
tree.insert("", tk.END, values=r)
if user:
tk.Button(root, text="Borrow Selected", command=lambda: self.borrow(tree)).pack(pady=10)
def borrow(self, tree):
selected = tree.focus()
if not selected:
messagebox.showwarning("Select", "Select a book first!")
return
values = tree.item(selected)["values"]
book_id, title, author, available = values
if available == 0:
messagebox.showerror("Unavailable", "Book already borrowed!")
return
due = (datetime.now() + timedelta(days=7)).strftime("%Y-%m-%d")
conn = sqlite3.connect(DB)
c = conn.cursor()
c.execute("UPDATE books SET available=0 WHERE id=?", (book_id,))
c.execute("INSERT INTO borrowed(user_id, book_id, borrowed_date, due_date) VALUES(?,?,?,?)",
(self.user[0], book_id, datetime.now().strftime("%Y-%m-%d"), due))
conn.commit()
conn.close()
messagebox.showinfo("Success", f"Book borrowed! Due on {due}")
self.root.destroy()
# ----------------- RETURN BOOK WINDOW -----------------
class ReturnBookWindow:
def __init__(self, root, user):
self.root = root
self.user = user
root.title("Return Book")
root.geometry("500x350")
columns = ("Borrow ID","Book Title","Due Date")
self.tree = ttk.Treeview(root, columns=columns, show="headings")
for col in columns:
self.tree.heading(col, text=col)
self.tree.pack(fill="both", expand=True)
conn = sqlite3.connect(DB)
c = conn.cursor()
c.execute("""SELECT borrowed.id, books.title, borrowed.due_date
FROM borrowed
JOIN books ON books.id = borrowed.book_id
WHERE borrowed.user_id=?""",
(user[0],))
rows = c.fetchall()
conn.close()
for r in rows:
self.tree.insert("", tk.END, values=r)
tk.Button(root, text="Return Selected", command=self.return_book).pack(pady=10)
def return_book(self):
selected = self.tree.focus()
if not selected:
messagebox.showwarning("Select", "Select a book!")
return
borrow_id, title, due = self.tree.item(selected)["values"]
conn = sqlite3.connect(DB)
c = conn.cursor()
c.execute("SELECT book_id FROM borrowed WHERE id=?", (borrow_id,))
book_id = c.fetchone()[0]
c.execute("DELETE FROM borrowed WHERE id=?", (borrow_id,))
c.execute("UPDATE books SET available=1 WHERE id=?", (book_id,))
conn.commit()
conn.close()
messagebox.showinfo("Returned", f"{title} returned successfully!")
self.root.destroy()
# ----------------- USER BORROWED LIST -----------------
class UserBorrowedBooks:
def __init__(self, root, user):
self.root = root
root.title("My Borrowed Books")
root.geometry("600x350")
columns = ("Book Title","Borrowed Date","Due Date")
tree = ttk.Treeview(root, columns=columns, show="headings")
for col in columns:
tree.heading(col, text=col)
tree.pack(fill="both", expand=True)
conn = sqlite3.connect(DB)
c = conn.cursor()
c.execute("""SELECT books.title, borrowed.borrowed_date, borrowed.due_date
FROM borrowed
JOIN books ON books.id = borrowed.book_id
WHERE borrowed.user_id=?""",
(user[0],))
rows = c.fetchall()
conn.close()
for r in rows:
tree.insert("", tk.END, values=r)
# ----------------- USERS LIST WINDOW (ADMIN) -----------------
class UsersListWindow:
def __init__(self, root):
root.title("All Users")
root.geometry("600x300")
columns = ("ID","Username","Role")
tree = ttk.Treeview(root, columns=columns, show="headings")
for col in columns:
tree.heading(col, text=col)
tree.pack(fill="both", expand=True)
conn = sqlite3.connect(DB)
c = conn.cursor()
c.execute("SELECT id, username, role FROM users")
rows = c.fetchall()
conn.close()
for r in rows:
tree.insert("", tk.END, values=r)
# ----------------- MAIN APP -----------------
if __name__ == "__main__":
init_db()
root = tk.Tk()
LoginWindow(root)
root.mainloop()