Library Management System

Objective :

The objective of this project is to build a library management system using Python and MySQL Connector. The system should be able to perform various tasks related to library management such as:

1. Add new books to the library

2. Search for books in the library

3. Check out books from the library

4. Check in books to the library

5. Sign up as a member of the library

6. Log in as a member of the library

7. Display all books in the library

8. Display available books in the library

The system should store the information about books and members in a database and should use SQL to perform operations on the database. The system should use the hashlib library to securely store the members' passwords. The goal of the project is to develop a working library management system that can be used to manage a small library.

Prerequisites :

To build a library management system using Python and MySQL Connector, you need the following prerequisites:

1. Basic knowledge of Python programming language

2. Knowledge of relational databases and SQL

3. A MySQL database setup and configured on your system or access to a MySQL database server

4. A text editor or Integrated Development Environment (IDE) to write the Python code

5. Knowledge of the Python Standard Library and external libraries such as hashlib, mysql-connector-python

6. Familiarity with basic SQL commands such as SELECT, INSERT, UPDATE, and DELETE.

Additionally, you should have the following software installed on your system:

1. Python 3.x

2. MySQL Connector for Python

3. MySQL Server

Once you have all the prerequisites in place, you can start writing the code for the library management system.

Overview:

The program has the following features:

1. Database Connectivity: It uses the mysql.connector.connect() method to connect to a MySQL database with the specified host, username, and password.

2. Database Management: The program implements several functions for creating, adding, searching, checking out, and checking in books in the library and for signing up and logging in members. It uses SQL queries to interact with the library and members tables in the database.

3. Hashing Passwords: The program implements the hash_password function to hash passwords before storing them in the members table. It uses the sha256 algorithm provided by the hashlib module.

4. Error Handling: The program uses the try-except statement to handle exceptions when accessing the library and members tables. If the tables do not exist, the program creates them using the create_library_table and create_members_table functions.

Here is a brief explanation of each function in the program:

1. create_database: This function creates a database named 'Library' if it does not exist.

2. create_library_table: This function creates a library table in the 'Library' database.

3. create_members_table: This function creates a members table in the 'Library' database.

4. hash_password: This function hashes the password using the sha256 algorithm.

5. add_book: This function adds a book to the library table in the 'Library' database.

6. search_book: This function searches for a book in the library table based on the title.

7. check_out_book: This function updates the availability of a book in the library table to "unavailable".

8. check_in_book: This function updates the availability of a book in the library table to "available".

9. sign_up: This function adds a member to the members table in the 'Library' database.

10. log_in: This function validates the username and password of a member in the members table.

Importing the required Modules:

mysql.connector is a Python library that allows connecting to a MySQL database and performing operations on it such as creating tables, inserting data, and executing SQL statements.

hashlib is a library that provides secure hash and message digest algorithms such as SHA-256. It can be used to secure data by computing a fixed-size digital fingerprint of the input data, which can then be used to verify that the data has not been altered in transit.

import mysql.connector
import hashlib

Establish Connection To Database:

This code connects to a MySQL database using the mysql-connector-python library. The parameters used are:

• host: specifies the host name where the database is located, which is "localhost" in this case.

• user: specifies the username to connect to the database, which is "root".

• password: specifies the password to connect to the database, which is "root".

Once the connection is established, a cursor object is created using conn.cursor(). The cursor allows you to execute SQL statements and retrieve results.

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
)
cursor = conn.cursor()

Function to Create a Database in MySQL:

The following code defines a Python function called create_database that creates a database in a MySQL database server. The function performs the following steps:

1. Create a Cursor Object: The first step is to create a cursor object using cursor.execute(). The cursor is used to execute SQL statements in the database.

2. Create a Database: The function uses the cursor to execute the SQL statement "create database if not exists Library". This statement creates a database named "Library" if it doesn't exist already.

3. Use the Database: The function then uses the cursor to execute the SQL statement "use Library". This statement sets the "Library" database as the current database to use.

cursor.execute("create database if not exists Bookshop")
cursor.execute("use Library")

Function to Create a Library Table in MySQL:

The following code defines a Python function called create_library_table that creates a table in a MySQL database server. The function performs the following steps:

def create_library_table():
    create_table = """
    CREATE TABLE library (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    publication_date DATE NOT NULL,
    ISBN VARCHAR(13) NOT NULL,
    available TINYINT(1) NOT NULL DEFAULT 1
    );
    """
    cursor.execute(create_table)
    print("Library table created successfully!")

1. Define the Table Structure: The function uses a multi-line string to define the structure of the table. The table is named "library" and has the following columns:

   • id: an auto-incrementing integer that serves as the primary key for the table.

   • title: a string with a maximum length of 255 characters, which cannot be null.

   • author: a string with a maximum length of 255 characters, which cannot be null.

   • publication_date: a date field, which cannot be null.

   • ISBN: a string with a maximum length of 13 characters, which cannot be null.

   • available: a boolean field, which cannot be null and has a default value of 1.

2. Execute the Table Creation Statement: The function uses the cursor to execute the SQL statement defined in the create_table variable. This statement creates the "library" table with the structure defined in the previous step.

3. Print a Success Message: Finally, the function prints a success message to confirm that the table was created successfully.

Function To Create Members Table:

def create_members_table():
    create_table = """
    CREATE TABLE members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
    );
    """
    cursor.execute(create_table)
    print("Members table created successfully!")

1. "id" column: This column is of integer data type and is set as the primary key of the table. The "AUTO_INCREMENT" keyword specifies that this column's value will be automatically incremented for each new row inserted into the table.

2. "username" column: This column is of VARCHAR (variable character) data type with a maximum length of 255 characters. The "NOT NULL" keyword specifies that this column cannot contain a null value.

3. "password" column: This column is of VARCHAR data type with a maximum length of 255 characters and also cannot contain a null value.

The SQL statement to create the table is stored in the "create_table" variable. The statement is executed using the "execute()" method of a cursor object, and a message "Members table created successfully!" is printed if the table creation was successful.

Define the hash_password Function:

def hash_password(password):
    return hashlib.sha256(password.encode()).hexdigest()

Here's what happens step-by-step:

1. The function takes a plain-text password as input.

2. The plain-text password is encoded to a byte-string using the encode() method.

3. The encoded password is then hashed using the SHA-256 algorithm by calling hashlib.sha256().

4. The resulting hash value is returned in hexadecimal form using the hexdigest() method.

Function To Add a Book:

def add_book(title, author, publication_date, ISBN):
    add_book = f"INSERT INTO library (title, author, publication_date, ISBN) VALUES ('{title}', '{author}', '{publication_date}', '{ISBN}');"
    cursor.execute(add_book)
    conn.commit()
    print("Book added successfully!")

This code defines a function called add_book that takes 4 parameters as input: title, author, publication_date, and ISBN.

1. It creates a SQL statement as a string and assigns it to the variable add_book. The string uses string interpolation to insert the values of the parameters into the SQL statement.

2. The statement cursor.execute(add_book) uses the cursor object to execute the SQL statement stored in the add_book variable.

3. The statement conn.commit() is used to commit the transaction and save the changes made to the database.

4. Finally, the code prints "Book added successfully!" to the console.

Search Books In Library:

def search_book(title):
    search_book = f"SELECT * FROM library WHERE title = '{title}';"
    cursor.execute(search_book)
    result = cursor.fetchone()
    if result:
        print("Search results:")
        print("Title:", result[1])
        print("Author:", result[2])
        print("Publication Date:", result[3])
        print("ISBN:", result[4])
        print("Available:", result[5])
    else:
        print("Book not found.")

This code defines a function called search_book that searches for a book in a library database. The function takes in a title argument and creates a SQL statement to search for a book with that title in the "library" table. The SQL statement is executed using the cursor.execute method. If a result is returned, the function prints the details of the book such as title, author, publication date, ISBN, and availability. If no result is returned, the function prints "Book not found."

Check Out the Book:

def check_out_book(title):
    check_out_book = f"UPDATE library SET available = 0 WHERE title = '{title}' AND available = 1;"
    cursor.execute(check_out_book)
    conn.commit()
    if cursor.rowcount == 1:
        print("Book checked out successfully!")
    else:
        print("Book not available.")

• Define the function check_out_book

• Create a SQL statement check_out_book to update the library table

  • Set available to 0 where the title is equal to the provided title argument and available is 1

• Execute the SQL statement using the cursor object

• Commit the changes to the database using the conn object

• If the cursor.rowcount is equal to 1, print "Book checked out successfully!"

• If the cursor.rowcount is not equal to 1, print "Book not available."