Environment Setup
This section covers the database layer implementation for the library management system. The application uses SQLite as the underlying database engine, which provides a lightweight, file-based solution suitable for desktop applications.
System Configuration:
- Operating System: Windows 10
- Editor: Sublime Text 3
- Python Version: 3.x
- GUI Framework: PyQT5
Database Architecture
The database layer consists of four primary manager classes, each handling specific aspects of the system data:
Database Connection Manager
The base class provides core database connectivity and transaction management:
import sqlite3
import hashlib
import os
from pathlib import Path
class DatabaseConnector:
"""Handles database connection lifecycle and basic operations."""
def __init__(self, db_path: str):
self.connection = sqlite3.connect(db_path)
self.cursor = self.connection.cursor()
def __enter__(self):
return self.cursor
def __exit__(self, exc_type, exc_val, exc_tb):
if exc_type is None:
self.connection.commit()
return False
def __del__(self):
self.connection.close()
def switch_database(self, new_path: str):
self.connection.close()
self.connection = sqlite3.connect(new_path)
self.cursor = self.connection.cursor()
def execute_table_creation(self, create_statement: str):
self.cursor.execute(create_statement)
self.connection.commit()
def finalize(self):
self.connection.commit()
self.connection.close()
User Management
The user manager handles authentication, user profiles, and borrowing statistics:
class UserManager(DatabaseConnector):
"""Manages user accounts, credentials, and borrowing history."""
def __init__(self, db_path: str):
super().__init__(db_path)
self._initialize_user_table()
def _initialize_user_table(self):
table_definition = """
CREATE TABLE IF NOT EXISTS members (
member_id CHAR(10) PRIMARY KEY,
full_name VARCHAR(20),
password_hash CHAR(32),
is_admin BOOLEAN,
total_borrows INTEGER,
current_borrows INTEGER
)"""
self.execute_table_creation(table_definition)
def seed_initial_data(self):
admin_password = self._hash_password('admin123')
self.register_member('admin', 'Administrator', admin_password, admin_flag=True)
standard_password = self._hash_password('user123')
self.register_member('user000000', 'StandardUser', standard_password, admin_flag=False)
def _hash_password(self, plaintext: str) -> str:
hasher = hashlib.md5()
hasher.update(plaintext.encode('utf-8'))
return hasher.hexdigest()
def register_member(self, member_id: str, name: str, password: str, admin_flag: bool = False):
insert_statement = """
INSERT INTO members
(member_id, full_name, password_hash, is_admin, total_borrows, current_borrows)
VALUES (?, ?, ?, ?, 0, 0)
"""
self.cursor.execute(insert_statement, (member_id, name, password, admin_flag))
self.connection.commit()
def retrieve_member(self, member_id: str):
query = "SELECT * FROM members WHERE member_id = ?"
self.cursor.execute(query, (member_id,))
return self.cursor.fetchall()
def fetch_administrators(self):
query = "SELECT member_id, full_name FROM members WHERE is_admin = 1"
self.cursor.execute(query)
return self.cursor.fetchall()
def fetch_standard_members(self):
query = "SELECT member_id, full_name FROM members WHERE is_admin = 0"
self.cursor.execute(query)
return self.cursor.fetchall()
def change_password(self, new_password: str, member_id: str):
query = "UPDATE members SET password_hash = ? WHERE member_id = ?"
self.cursor.execute(query, (new_password, member_id))
self.connection.commit()
def update_borrowing_status(self, member_id: str, borrow_operation: bool = True):
if borrow_operation:
query = """
UPDATE members
SET total_borrows = total_borrows + 1, current_borrows = current_borrows + 1
WHERE member_id = ?
"""
else:
query = """
UPDATE members
SET total_borrows = total_borrows - 1, current_borrows = current_borrows - 1
WHERE member_id = ?
"""
self.cursor.execute(query, (member_id,))
self.connection.commit()
Book Inventory Management
The book manager handles catalog operations, inventory tracking, and availability status:
class BookCatalogManager(DatabaseConnector):
"""Manages book catalog, inventory levels, and availability."""
def __init__(self, db_path: str):
super().__init__(db_path)
self._initialize_book_table()
def _initialize_book_table(self):
table_definition = """
CREATE TABLE IF NOT EXISTS books (
title VARCHAR(30),
book_id CHAR(6),
author VARCHAR(20),
category VARCHAR(10),
publisher VARCHAR(20),
publish_date DATE,
total_copies INTEGER,
available_copies INTEGER,
borrowed_copies INTEGER
)"""
self.execute_table_creation(table_definition)
def seed_catalog(self):
sample_books = [
('Mechanics', 'BK1000', 'Liu Bin', 'Science', 'USTC', '1999-01-01', 100, 100, 0),
('Calculus', 'BK1001', 'Newton', 'Science', 'USTC', '1998-01-01', 14, 14, 0),
('Electromagnetism', 'BK1002', 'Ye Bangjiao', 'Science', 'USTC', '1997-01-01', 24, 24, 0),
('Thermodynamics', 'BK1003', 'Zhang Pengfei', 'Science', 'USTC', '2002-01-01', 45, 45, 0),
('Electrodynamics', 'BK1004', 'Ye Bangjiao', 'Science', 'USTC', '2003-01-01', 100, 100, 0),
('Database Systems', 'BK1006', 'Yuan Pingbo', 'Science', 'USTC', '2010-01-01', 10, 10, 0),
('Electromagnetics', 'BK1005', 'Ye Bangjiao', 'Science', 'USTC', '2012-01-01', 43, 43, 0),
('Mathematical Analysis', 'BK1007', 'Chen Qing', 'Science', 'USTC', '2013-01-01', 23, 23, 0),
('Problem Solver I', 'BK1008', 'Djokovic', 'Mathematics', 'Russian Press', '2010-01-01', 50, 50, 0),
('Problem Solver II', 'BK1009', 'Djokovic', 'Mathematics', 'Russian Press', '2010-01-01', 50, 50, 0),
('Problem Solver III', 'BK1010', 'Djokovic', 'Mathematics', 'Russian Press', '2010-01-01', 50, 50, 0),
('Problem Solver IV', 'BK1011', 'Djokovic', 'Mathematics', 'Russian Press', '2010-01-01', 50, 50, 0),
('Problem Solver V', 'BK1012', 'Djokovic', 'Mathematics', 'Russian Press', '2010-01-01', 50, 50, 0),
('Problem Solver VI', 'BK1013', 'Djokovic', 'Mathematics', 'Russian Press', '2010-01-01', 50, 50, 0),
]
for book_data in sample_books:
self.add_book_entry(*book_data)
def add_book_entry(self, title: str, book_id: str, author: str, category: str,
publisher: str, publish_date: str, total: int, available: int, borrowed: int):
insert_statement = """
INSERT INTO books
(title, book_id, author, category, publisher, publish_date,
total_copies, available_copies, borrowed_copies)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""
self.cursor.execute(insert_statement,
(title, book_id, author, category, publisher, publish_date,
total, available, borrowed))
self.connection.commit()
def remove_book(self, book_id: str):
delete_statement = "DELETE FROM books WHERE book_id = ?"
self.cursor.execute(delete_statement, (book_id,))
self.connection.commit()
def modify_inventory(self, quantity: int, book_id: str, increase: bool = True):
if increase:
query = """
UPDATE books
SET total_copies = total_copies + ?, available_copies = available_copies + ?
WHERE book_id = ?
"""
else:
query = """
UPDATE books
SET total_copies = total_copies - ?, available_copies = available_copies - ?
WHERE book_id = ?
"""
self.cursor.execute(query, (quantity, quantity, book_id))
self.connection.commit()
def retrieve_all_books(self):
query = "SELECT * FROM books"
self.cursor.execute(query)
return self.cursor.fetchall()
def find_book_by_id(self, book_id: str):
query = "SELECT * FROM books WHERE book_id = ?"
self.cursor.execute(query, (book_id,))
return self.cursor.fetchall()
def search_by_criteria(self, criteria: str, limit: int = 5):
query = f"SELECT * FROM books ORDER BY {criteria} LIMIT ?"
self.cursor.execute(query, (limit,))
return self.cursor.fetchall()
def process_book_transaction(self, book_id: str, is_borrowing: bool = True):
if is_borrowing:
query = """
UPDATE books
SET available_copies = available_copies - 1, borrowed_copies = borrowed_copies + 1
WHERE book_id = ?
"""
else:
query = """
UPDATE books
SET available_copies = available_copies + 1, borrowed_copies = borrowed_copies - 1
WHERE book_id = ?
"""
self.cursor.execute(query, (book_id,))
self.connection.commit()
Transaction Logging
The transaction manager records all book acquisition and disposal events:
class TransactionLogger(DatabaseConnector):
"""Records book acquisition and disposal events."""
def __init__(self, db_path: str):
super().__init__(db_path)
self._initialize_transaction_table()
def _initialize_transaction_table(self):
table_definition = """
CREATE TABLE IF NOT EXISTS transactions (
book_id CHAR(6),
transaction_date DATE,
transaction_type INTEGER,
quantity INTEGER
)"""
self.execute_table_creation(table_definition)
def seed_transactions(self):
initial_records = [
('BK1000', '2018-04-22', 1, 100),
('BK1001', '2018-04-22', 1, 14),
('BK1002', '2018-04-22', 1, 24),
('BK1003', '2018-04-22', 1, 45),
('BK1004', '2018-04-22', 1, 100),
('BK1004', '2018-04-27', 1, 45),
('BK1005', '2018-04-27', 1, 45),
('BK1006', '2018-04-28', 1, 10),
('BK1007', '2018-04-28', 1, 23),
('BK1008', '2018-04-28', 1, 50),
('BK1009', '2018-04-28', 1, 50),
('BK1010', '2018-04-28', 1, 50),
]
for record in initial_records:
self.log_transaction(*record)
def log_transaction(self, book_id: str, transaction_date: str, transaction_type: int, quantity: int):
insert_statement = "INSERT INTO transactions VALUES (?, ?, ?, ?)"
self.cursor.execute(insert_statement, (book_id, transaction_date, transaction_type, quantity))
self.connection.commit()
def record_addition(self, book_id: str, date: str, quantity: int):
self.log_transaction(book_id, date, 1, quantity)
def record_removal(self, book_id: str, date: str, quantity: int):
self.log_transaction(book_id, date, 0, quantity)
def retrieve_all_transactions(self):
query = "SELECT * FROM transactions"
self.cursor.execute(query)
return self.cursor.fetchall()
Database Initialization Routine
The following module handles automatic database setup and path configuration:
# -*- coding: utf-8 -*-
import os
import sqlite3
from pathlib import Path
BASE_DIR = Path.home()
APP_DIR = BASE_DIR / '.LibrarySystem'
if not APP_DIR.exists():
APP_DIR.mkdir()
DATABASE_PATH = str(APP_DIR / 'library.db')
USER_TABLE_SCHEMA = """
CREATE TABLE IF NOT EXISTS members (
member_id CHAR(10) PRIMARY KEY,
full_name VARCHAR(20),
password_hash CHAR(32),
is_admin BOOLEAN,
total_borrows INTEGER,
current_borrows INTEGER
)"""
BORROW_RECORD_SCHEMA = """
CREATE TABLE IF NOT EXISTS borrow_records (
member_id CHAR(10),
book_id CHAR(6),
borrow_date DATE,
return_date DATE,
status BIT
)"""
BOOK_TABLE_SCHEMA = """
CREATE TABLE IF NOT EXISTS books (
title VARCHAR(30),
book_id CHAR(6),
author VARCHAR(20),
category VARCHAR(10),
publisher VARCHAR(20),
publish_date DATE,
total_copies INTEGER,
available_copies INTEGER,
borrowed_copies INTEGER
)"""
TRANSACTION_TABLE_SCHEMA = """
CREATE TABLE IF NOT EXISTS transactions (
book_id CHAR(6),
transaction_date DATE,
transaction_type INTEGER,
quantity INTEGER
)"""
Testing Procedures
Verification functions ensure database operatinos function correctly:
def verify_user_operations():
manager = UserManager(DATABASE_PATH)
manager.register_member('admin', 'Admin', '123456')
manager.register_member('manager', 'Manager1', '123456')
manager.register_member('test', 'TestA', '123456')
manager.register_member('test1', 'TestB', '123456')
manager.register_member('test2', 'TestC', '123456')
admins = manager.fetch_administrators()
users = manager.fetch_standard_members()
records = manager.retrieve_member('admin')
return admins, users, records
def verify_transaction_logging():
logger = TransactionLogger(DATABASE_PATH)
records = logger.retrieve_all_transactions()
for entry in records:
print(entry)
return records
def verify_book_operations():
catalog = BookCatalogManager(DATABASE_PATH)
if len(catalog.find_book_by_id('BK1006')):
catalog.modify_inventory(10, 'BK1005')
else:
catalog.add_book_entry('Advanced Mechanics', 'BK1006', 'Liu',
'Science', 'USTC', '1999-01-01', 34, 34, 1)
all_books = catalog.retrieve_all_books()
print(f'Total books: {len(all_books)}')
for book in all_books:
print(book)
specific = catalog.find_book_by_id('BK1006')
if len(specific):
print(specific)
filtered = catalog.search_by_criteria('author')
print(filtered)
return all_books
if __name__ == '__main__':
verify_user_operations()
verify_transaction_logging()
verify_book_operations()
Implementation Notes
The database initialization establishes essential table structures before the GUI layer loads. This approach ensures data integrity and provides a consistent foundation for subsequent operations.
Key design considerations include:
- Using context managers for automatic transaction handling
- Implementing parameterized queries to prevent SQL injection
- Maintaining separate managers for distinct data domains
- Providing seed data methods for development and testing