CREATING TABLES USING PYTHON AND SQL

1. import psycopg2: This line imports the psycopg2 library, which is used to
interact with PostgreSQL databases from Python.
2. def create_tables():: This line defines a function named create_tables.
This function will be responsible for creating tables in a PostgreSQL
database related to a library system.
3. try:: The start of a try-except block in Python, where the code inside the
try block is executed, and any exceptions that occur are handled in the
except block.
4. conn =
psycopg2.connect(database='library_sys',user='postgres',password='’):
This line establishes a connection to a PostgreSQL database named
library_sys with the username postgres and appropriate password ''. It
assigns the connection object to the variable conn.
5. cursor = conn.cursor(): This line creates a cursor object using the
connection conn. Cursors are used to execute SQL commands in
PostgreSQL.
6. cursor.execute("""CREATE TABLE IF NOT EXISTS books (book_id SERIAL
PRIMARY KEY, title VARCHAR(255), author VARCHAR(255), isbn
VARCHAR(20), available BOOLEAN);"""): This SQL command executed
through cursor.execute() creates a table named books if it does not
already exist. The table has columns for book_id (auto-incrementing
SERIAL primary key), title, author, isbn, and available.
7. Similar CREATE TABLE commands follow for two more tables: borrowers
and transactions, defining their respective columns and relationships.
8. conn.commit(): Commits the transaction to the database, making all
changes made within the transaction permanent.
9. print("Tables created successfully!"): Prints a message indicating that the
tables were created successfully.
10. except psycopg2.Error as e:: This block catches any exceptions that occur
during the execution of the code within the try block and assigns the
exception object to the variable e.
 11. print("Error creating tables:", e): If an error occurs in the try block, this
line prints an error message along with details of the exception.
12. finally:: This block of code ensures that certain actions are performed
regardless of whether an exception occurred or not.
13. if conn: conn.close(): If the connection conn exists (was successfully
opened), it is closed in the finally block to ensure proper cleanup.
14. create_tables(): Finally, this line calls the create_tables() function,
initiating the process of creating the tables in the PostgreSQL database.
This code essentially establishes a connection to a PostgreSQL database and
creates three tables (books, borrowers, and transactions) if they do not exist,
defining their respective columns. It then commits these changes to the
database and prints a success message or handles any errors that may occur
during this process.

PROGRAM CODE:
#pip install psycopg2 in cmd at first

import psycopg2

def create_tables():

try:

conn =
psycopg2.connect(database='library_sys',user='postgres',password='')

cursor = conn.cursor()

# Create books table

cursor.execute("""

CREATE TABLE IF NOT EXISTS books (

book_id SERIAL PRIMARY KEY,

title VARCHAR(255),

author VARCHAR(255),

isbn VARCHAR(20),

available BOOLEAN

);

""")

# Create borrowers table

 cursor.execute("""

CREATE TABLE IF NOT EXISTS borrowers (

borrower_id SERIAL PRIMARY KEY,

name VARCHAR(255),

email VARCHAR(100)

);

""")

# Create transactions table

cursor.execute("""

CREATE TABLE IF NOT EXISTS transactions (

transaction_id SERIAL PRIMARY KEY,

book_id INTEGER REFERENCES books(book_id),

borrower_id INTEGER REFERENCES borrowers(borrower_id),

borrowed_date DATE,

return_date DATE,

returned BOOLEAN

);

""")

conn.commit() # finalize the changes

print("Tables created successfully!")

except psycopg2.Error as e:

print("Error creating tables:", e)

finally:

if conn:

conn.close()

# Call the function to create tables

create_tables()
FUNCTIONS THAT ARE REQUIRED FOR THE LIBRARY
MANAGEMENT SYSTEM
1. Function connect_db()

This function attempts to establish a connection to the PostgreSQL database

named library_sys using the psycopg2 library. If successful, it returns the
connection object; otherwise, it catches any errors (specifically psycopg2.Error)
that occur during the connection attempt and prints an error message before
returning None.
This function encapsulates the process of connecting to the database,
abstracting it for reusability throughout the project.
2. Function add_book()
The add_book() function is responsible for inserting a new book into the books
table within the library_sys database. It takes parameters such as title, author,
isbn, and available (with a default value of True).
It establishes a connection to the database, creates a cursor, executes an SQL
INSERT query to add a new book entry, commits the changes to the database,
and prints a success message. It also handles any potential errors using exception
handling and ensures the connection is closed in the finally block.
3. Function add_borrower()

Similar to add_book(), this function adds a new borrower to the borrowers table
within the library_sys database.
It inserts borrower details (name and email) into the borrowers table, commits
the changes to the database, handles errors, and closes the connection if it
exists, following similar exception handling practices as seen in add_book().
4. Function borrow_book()

This function manages the process of borrowing a book from the library system.
This function updates the availability status of a book to False in the books table
and inserts a new transaction record into the transactions table, indicating that
the book has been borrowed by a particular borrower. It uses the provided book
ID, borrower ID, return date, and manages exceptions as seen in previous
functions.
5. Function delete_transactions_for_book()

The purpose of this function is to delete transactions associated with a specific

book from the transactions table in the database.
It deletes all transactions linked to a particular book based on the provided
book_id by executing a DELETE query on the transactions table. Similar error
handling and connection closure mechanisms are applied.
6. Function search_books()

This function allows users to search for books in the library based on specific
criteria (such as title or author) and a provided keyword.
This function performs a search operation based on the given criteria (either title
or author) and the provided keyword. It constructs an appropriate SQL query
based on the criteria and retrieves books matching the criteria, displaying their
information.
7. Function display_books()

This function retrieves all books from the transactions table in the database and
displays their information.
It retrieves all book records from the transactions table and prints their details
using a loop. Similar to other functions, it handles any potential errors and
ensures proper closing of the connection.
8. Function main()

The main() function is the primary entry point of the program. It orchestrates
the interactions between the user and the database functions through a menu-
driven interface.
This function continuously prompts the user with a menu of options and
performs actions based on the user's input by invoking the respective functions
defined earlier. It continues until the user chooses to exit the program (choice
== '0').
9. if __name__ == "__main__":

This conditional block checks if the current script is the main program being
executed directly (not imported as a module). If so, it calls the main() function
to start the library management system.
This detailed breakdown illustrates how each function in the code contributes to
the library management system, handling database interactions for adding
books and borrowers, managing transactions, searching books, displaying book
information, and providing a user-friendly menu-driven interface to perform
these actions.
PROGRAM CODE:
def connect_db():

try:

conn=psycopg2.connect(database='library_sys',user='postgres',password='')

return conn

except psycopg2.Error as e:

print("Error connecting to the database:", e)

return None

# Function to add a book to the database

def add_book(title, author, isbn, available=True):

try:

conn =
psycopg2.connect(database='library_sys',user='postgres',password='')

cursor = conn.cursor()

cursor.execute("INSERT INTO books (title, author, isbn, available)

VALUES (%s, %s, %s, %s)", (title, author, isbn, available))

conn.commit()

print("Book added successfully!")

except psycopg2.Error as e:

print("Error adding book:", e)

finally:

if conn:

conn.close()

# Function to add a borrower to the database

def add_borrower(name, email):

try:
 conn =
psycopg2.connect(database='library_sys',user='postgres',password='')

cursor = conn.cursor()

cursor.execute("INSERT INTO borrowers (name, email) VALUES (%s,

%s)", (name, email))

conn.commit()

print("Borrower added successfully!")

except psycopg2.Error as e:

print("Error adding borrower:", e)

finally:

if conn:

conn.close()

#borrow book

def borrow_book(book_id, borrower_id, return_date):

try:

conn =
psycopg2.connect(database='library_sys',user='postgres',password='')

cursor = conn.cursor()

cursor.execute("UPDATE books SET available = FALSE WHERE book_id =

%s", (book_id,))

cursor.execute("INSERT INTO transactions (book_id, borrower_id,

borrowed_date, return_date, returned) VALUES (%s, %s, CURRENT_DATE, %s,
FALSE)", (book_id, borrower_id, return_date))

conn.commit()

print("Book borrowed successfully!")

except psycopg2.Error as e:

print("Error borrowing book:", e)

finally:

if conn:

conn.close()

# Function to delete a book or a borrower

def delete_transactions_for_book(book_id):

try:
 conn =
psycopg2.connect(database='library_sys',user='postgres',password='')

cursor = conn.cursor()

# Delete transactions related to the book

cursor.execute("DELETE FROM transactions WHERE book_id = %s",

(book_id,))

conn.commit()

print("Transactions related to the book deleted successfully!")

except psycopg2.Error as e:

print("Error deleting transactions:", e)

finally:

if conn:

conn.close()

# Function to search for a book

def search_books(criteria, keyword):

try:

conn =
psycopg2.connect(database='library_sys',user='postgres',password='')

cursor = conn.cursor()

if criteria.lower() == 'title':

cursor.execute("SELECT * FROM books WHERE LOWER(title) LIKE

LOWER(%s)", ('%' + keyword + '%',))

elif criteria.lower() == 'author':

cursor.execute("SELECT * FROM books WHERE LOWER(author) LIKE

LOWER(%s)", ('%' + keyword + '%',))

else:

print("Invalid search criteria. Please use 'title' or

'author'.")

return

books = cursor.fetchall()

for book in books:

 print(book)

except psycopg2.Error as e:

print("Error searching books:", e)

finally:

if conn:

conn.close()

# Function to display books or borrowers

def display_books():

try:

conn=
psycopg2.connect(database='library_sys',user='postgres',password='')

cursor = conn.cursor()

print('BOOK STATUS IN LIBRARY')

cursor.execute("SELECT * FROM books")

book_status=cursor.fetchall()

for status in book_status:

print(status)

print('TRANSACTION STATUS IN LIBRARY')

cursor.execute("SELECT * FROM transactions")

books = cursor.fetchall()

for book in books:

print(book)

print('BORROW STATUS IN LIBRARY')

cursor.execute("SELECT * FROM borrowers")

borrow_status=cursor.fetchall()

for borrow in borrow_status:

print(borrow)

except psycopg2.Error as e:

print("Error displaying books:", e)

 finally:

if conn:

conn.close()

def main():

connect_db()

while True:

print("Choose an action:")

print("1. Add Book")

print("2. Add Borrower")

print("3. Delete")

print("4. Search")

print("5. Display")

print("0. Exit")

choice = input("Enter your choice: ")

if choice == '1':

n=int(input('How many books do you want to add ?'))

print('Which book do you want to add ? Write the name , author

name and ISBN code respectively ..')

for i in range(n):

b_name=input("Write Book Name -->")

auth_name=input("Write author Name -->")

isbn=input('Write isbn code -->')

add_book(b_name, auth_name, isbn)

# sample format shown below

#add_book("Python Programming", "John Doe", "1234567890")

#add_book("The Great Gatsby", "F. Scott Fitzgerald",

"9780743273565")

elif choice == '2':

n=int(input('How many students wants to borrow book ?'))

print('Write your name and email ID as stated..')

 for i in range(n):

borrower_name=input("Write Your Name -->")

email_id=input("Write your email ID -->")

# Usage of borrower

add_borrower(borrower_name, email_id)

# Usage of borrow book

book_id=int(input('Enter the book id you want to borrow--

>'))

borrower_id=int(input('Enter the borrower id -->'))

return_date=input('Enter the date you would return it-->')

borrow_book(book_id,borrower_id,return_date)

#sample format shown below

#add_borrower("Alice ", "alice@example.com")

#add_borrower("Subhojit", "subhojit@gmail.com")

#borrow_book(1, 1, '2023-12-31')

#borrow_book(3, 2, '2023-12-17')

elif choice == '3':

dlt=input('Which transaction do you want to delete ?')

delete_transactions_for_book(dlt)

delete_transactions_for_book(dlt)

elif choice == '4':

# Usage

search_books('title', input('Enter the title of book ')) #

Search books by title containing

search_books('author', input('Enter the author of book ')) #

Search books by author containing

elif choice == '5':

# Usage

display_books()

elif choice == '0':

 print("Exiting the program. Goodbye!")

break

else:

print("Invalid choice. Please enter a valid option.")

if __name__ == "__main__":

main()
OUTPUT:
1. Adding a book:

2. Borrowing a book:

3. Display status of book, borrow and transactions:

4. Searching based on title or author:

5. Deleting transaction:
Before deletion: (TRANSACTION STATUS)

6. Delete transaction:
After deletion: (TRANSACTION STATUS)