1 from colorama import Fore, Style, init

2 import subprocess
3 import sys
4 import pymysql
5
6 init(autoreset=True)
7
8 conn = [Link](
9 host="localhost",
10 user="root",
11 password="sujal1"
12 ) cursor = [Link]() # EXECUTE
13 EXTERNAL PYTHON SCRIPT def
14 execute(a):
15
16
17
18
[Link]([[Link], a])
19
20
# CREATE DATABASE AND TABLES
21
def create_database():
22
a = "CREATE DATABASE IF NOT EXISTS library"
23
[Link](a)
24
25 ✅
print([Link] + [Link]+ " Database 'library' created successfully!")
26
27 execute("[Link]")
28 execute("[Link]")
29 execute("[Link]")
30
31 # DROP DATABASE
32 def drop_database():
33 [Link]("DROP DATABASE IF EXISTS library")
34 print([Link] + [Link] + )
35
36 # ADMIN MENU
37 def admin_menu():
38 while True:
39 print([Link] + [Link] ╔══════════════════════════════════════════
+' )
40 print([Link] + [Link] + ) )
41 print([Link] + [Link] ╠══════════════════════════════════════════
+' )
42 print([Link] + [Link] ║
+' '║
1. CREATE DATABSAE ║' ║')
43 print([Link] + [Link] ╚═══════════════════════════════
+' 2. DROP DATABASE ║ ')
44 print([Link] + [Link] ══════════════════════╝
+ 3. RETURN TO MAIN MENU ' ║ ')
45 print([Link] + [Link] +
46 try:
47
48 choice = int(input([Link] + [Link]+ ))
49 except ValueError:
50
51
print([Link] + [Link] + " ❌
Please enter a valid number!")
continue
52
53 if choice == 1:
54 create_database()
55
elif choice == 2:
56
drop_database()
57
elif choice == 3:
58
59
print([Link] + [Link] + " ↩ Returning to Main Menu‫ێێ‬
΀")
break
60
else:
61
62
print([Link] + [Link] + " ❌ Invalid choice. Please choose between 1-4.")
63
64 if __name__ == "__main__":
65 admin_menu()
[Link]()
[Link]()

File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 1 of 1
 1 from [Link] import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer
2 from [Link] import getSampleStyleSheet, ParagraphStyle
3 from [Link] import A4, landscape
4 from [Link] import inch
5 from [Link] import colors
6 from colorama import Fore, Style, init
7 from datetime import datetime
8 import pymysql
9
10 init(autoreset=True) 11
12 13 14 15 16 17 18 19 20 21
22 23 24 25
conn 26 27 28 29 30
= [Link](
31 32 33 host="localhost",
34 35 36 37 38 39
40 41 42 43 44 45 46 47
user="root",
48 49 50 51 52 53 54 55 56
password="sujal1",
57 58 59database="library"
60 61 62 63 64 65
66 67 68 69=70
) cursor 71 72 73 74
[Link]()
75 76 77 78 79 80 81

# PDF GENERATOR
def generate_pdf_report(title, headers, data, filename_prefix):
timestamp = [Link]().strftime("%Y%m%d_%H%M%S")
filename = f"{filename_prefix}_{timestamp}.pdf"
pdf = SimpleDocTemplate(

filename,
pagesize=landscape(A4),
leftMargin=0.5 * inch,
rightMargin=0.5 * inch,
topMargin=0.75 * inch,
bottomMargin=0.75 * inch
)
styles = getSampleStyleSheet()
elements = []
# School Heading
school_style = ParagraphStyle(

'SchoolHeading',
parent=styles['Heading1'],
fontSize=18,
textColor=[Link]('#1a237e'),
alignment=1, # Center alignment
spaceAfter=10,
fontName='Helvetica-Bold'
)
[Link](Paragraph("<b>Ananya Vidyalaya CBSE</b>", school_style))
[Link](Spacer(1, 12))
# Title
[Link](Paragraph(f"<b>{title}</b>", styles['Title']))
[Link](Spacer(1, 20))
# Handle empty data
if not data:

[Link](Paragraph("No records found.", styles['Normal']))

else:
# Create a custom paragraph style for table cells
cell_style = ParagraphStyle(
'CellStyle',
parent=styles['Normal'],
fontSize=9, leading=11,
alignment=1,
wordWrap='CJK'# Center alignment

)
# Convert data to Paragraphs for text wrapping
table_data = []
# Add headers (as regular text, not Paragraphs, for bold styling)
table_data.append(headers)
# Add data rows with Paragraph objects for wrapping
for row in data:

wrapped_row = []
for cell in row:
# Convert None to empty string
cell_text = str(cell) if cell is not None else ""
# Wrap each cell in a Paragraph for automatic text wrapping
File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 1 of 5
 82 wrapped_row.append(Paragraph(cell_text, cell_style))
83 table_data.append(wrapped_row)
84
85 # Calculate available width
86 page_width = landscape(A4)[0] - ([Link] + [Link])
87 num_columns = len(headers)
88
89 # Dynamic column width calculation
90 # Distribute width evenly,but you can customize percolumn if needed
91 col_widths = [page_width / num_columns] * num_columns
92
93 # Create table with dynamic column widths
94 table = Table(table_data, colWidths=col_widths, repeatRows=1)
95
96
# Enhanced table styling
97
[Link](TableStyle([
98
# Header styling
99
('BACKGROUND', (0, 0), (-1, 0), [Link]),
100
('TEXTCOLOR', (0, 0), (-1, 0), [Link]),
101
('ALIGN', (0, 0), (-1, 0), 'CENTER'),
102
('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
103
('FONTSIZE', (0, 0), (-1, 0), 10),
104
('BOTTOMPADDING', (0, 0), (-1, 0), 12),
105
106 ('TOPPADDING', (0, 0), (-1, 0), 12),
107
108 # Data rows styling
109 ( 'BACKGROUND' , (0, 1), (-1, -1), [Link]),
110 ( 'ALIGN' , (0, 1), (-1, -1), 'CENTER'),
111 ( 'VALIGN' , (0, 0), (-1, -1), 'MIDDLE'),
112 ( 'FONTNAME' , (0, 1), (-1, -1), 'Helvetica'),
113 ( 'FONTSIZE', (0, 1), (-1, -1), 9),
114 ( 'TOPPADDING' , (0, 1), (-1, -1), 8),
115 ( 'BOTTOMPADDI , (0, 1), (-1, -1), 8),
( NG'
'LEFTPADDING' , (0, 0), (-1, -1), 6),
116
( 'RIGHTPADDING' , (0, 0), (-1, -1), 6),
117
118
119 # Grid
120 ( 'GRID' , (0, 0), (-1, -1), 1, [Link]),
121 # Alternating rowcolors for betterreadability
122
123 ('ROWBACKGROUNDS', (0, 1), (-1, -1), [[Link], [Link]]),
124 ]))
125 [Link](table)
126
127
128 [Link](elements)
129 print(Fore.LIGHTGREEN_EX + f"\n ✅ PDF generated: {filename}\n")
130 # Auto-open PDF
131
132 try :
133 importplatform
134 importos
135 [Link]() == "Windows":
136
[Link](filename)
137
elif [Link]() == "Darwin":
138
[Link](f"open '{filename}'")
139
else:
140
[Link](f"xdg-open '{filename}'")
141
exceptException as e:
142
143
⚠
print(f" Could not open PDF: {e}")
144
145
146 # BOOK REPORTS
147 # BOOK REPORT: ALL BOOKS
148 def report_all_books():
149 [Link]( "SELECT * FROM book")
150 data = [Link]()
151 headers = [ "Book No", "Book Name", "Author", "Publisher" , "Page", "Price", "Total Copies", "Avaliable Copies
152 "] generate_pdf_report(
153 " Library Report: All Books" , headers, data, "All_Books_Report")
154
155
156 # BOOK REPORT: BY ID
157 def report_book_by_id():
158 book_no = input([Link]+ )
159 [Link]( "SELECT * FROM book WHERE book_no = , %s" (book_no,))
160 data = [Link]()
headers = [ "Book No", "Book Name", "Author", "Publisher" , "Page", "Price", "Total Copies", "Avaliable Copies
"] generate_pdf_report( f"
Book Report (ID: {book_no})" , headers, data, f"Book_{book_no}_Report")

File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 2 of 5
161
162 # BOOK REPORT: BY KEYWORD
163 def report_book_by_keyword():
164 keyword = input([Link] + )
165 [Link]( "SELECT * FROM book WHERE book_name LIKE %s OR author_name LIKE %s OR publisher_name LIKE %s
166 , (f"%{keyword } % ", f " % { keyword} % ", f " % { keyword} % "))
167 data = [Link]()
168 headers = [ "Book No", "Book Name", "Author" "Publisher"
, "Page"
, "Price"
, "Total, Copies" "Avaliable
, Copies
169 "] generate_pdf_report( f" Books Matching'{
170 keyword}'" , headers, data, f"Book_Search_{keyword}" )
171
172
173 # MEMBER REPORTS
174 # MEMBER REPORT: ALL MEMBERS
175 def report_all_members():
176
[Link]("SELECT * FROM member")
177
data = [Link]()
178
headers = ["Member No", "Name", "Type", "Mobile", "RollNo", "Standard"]
179
generate_pdf_report(" Library Report: All Members", headers, data, "All_Members_Report")
180
181
182
# MEMBER REPORT: BY ID / TYPE
183
def report_member_by_id():
184
185 print([Link] + "Report by: \[Link] No \[Link] Type")
186 choice = input([Link] + "Enter your choice: ")
if choice == "1":
187
188 ─
print([Link] + [Link] + "\nREPORT MEMBER BY No\n" + Fore.LIGHTBLACK_EX + " " * 30)
189 member_no = int(input([Link] + ))
190 [Link]("SELECT * FROM member WHERE member_no = %s", (member_no,))
191 data = [Link]()
192 headers = ["Member No", "Name", "Type", "Mobile", "RollNo", "Standard"]
193 generate_pdf_report(f" Member Report (ID: {member_no})", headers, data, f"Member_{member_no}_Report")
194
195 elif choice == "2":
196 print([Link] + [Link] + "\nReport MEMBER BY TYPE\n" + Fore.LIGHTBLACK_EX + " ─" * 30)
197 member_type = input([Link] + ).strip().upper()
198 [Link]("SELECT * FROM member WHERE member_type = %s", (member_type,))
199 data = [Link]()
if not data:

❌
print([Link] + " No members found for this type.")
20 else:
0 headers = ["Member No", "Name", "Type", "Mobile", "RollNo", "Standard"]
201 generate_pdf_report(f"Member Report (TYPE: {member_type})", headers, data, f"Member_{member_type}
20 _Report")
2 else:
20 ⚠
print([Link] + [Link] + " Invalid choice. Please choose between 1–2.")
3
20
4 # MEMBER REPORT: BY KEYWORD
20 def report_member_by_keyword():
5 keyword = input([Link] + )
20 [Link]("SELECT * FROM member WHERE member_name LIKE %s OR member_type LIKE %s OR mobile_no LIKE %
6 OR roll_no LIKE %s OR standard LIKE %s",
20 (f"%{keyword}%", f"%{keyword}%", f"%{keyword}%", f"%{keyword}%", f"%{keyword}%"))
7 data = [Link]()
20 headers = ["Member No", "Name", "Type", "Mobile", "RollNo", "Standard"]
8 generate_pdf_report(f" Members Matching '{keyword}'", headers, data, f"Member_Search_{keyword}")
20
9
210
# TRANSACTION REPORTS
211
# TRANSACTION REPORT: ALL TRANSACTIONS
212
def report_all_transactions():
213
[Link]("SELECT * FROM transaction")
214
data = [Link]()
215
headers = ["Trans No", "Book No", "Member No", "Issue Date", "Return Date", "Actual Return Date", "Fine"]
216
generate_pdf_report(" Library Report: All Transactions", headers, data, "All_Transactions_Report")
217
218
219
22 # TRANSACTION REPORT: ALL UNRETURNED BOOKS
0 def report_all_unreturned_books():
221 [Link]("""SELECT transaction_no, book_no, member_no, issue_date, return_date, actual_return_date
222 FROM transaction WHERE actual_return_date IS NULL""")
223 data = [Link]()
22 headers = ["Trans No", "Book No", "Member No", "Issue Date", "Return Date", "Actual Return Date"]
4 generate_pdf_report(" Library Report: All Unreturned Books", headers, data, "All_Unreturned_Books_Report")
225
22
6 # TRANSACTION REPORT: UNRETURNED BY MEMBER / TYPE
22 def report_unreturned_books_by_member():
7 print([Link] + "\nReport by:\n1. Member ID\n2. Member Type")
22
8
File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 3 of 5
22
9
23
0
23 choice = input([Link] + "Enter your choice: ")
8
23 if choice == '1':
9 print([Link] + [Link] + "\nREPORT MEMBER BY No\n" + Fore.LIGHTBLACK_EX + " " * 30)─
24 member_no = input([Link]+ )
0 query = """ t . t r a n s a c t i o n _ n o ,
241 SELECT t.book_no, b.book_name,
24 m.member_no, m.member_name, m.member_type, t.issue_date, t.return_date, t.actual_return_date
2 FRO `transaction` t
24 M book b ON t.book_no = b.book_no
3 JOIN member m ON t.member_no = m.member_no
24 JOIN t.actual_return_date IS NULL
4 WHE t.member_no = %s
24 """ RE
5 AND
[Link](query, (member_no,))
24 data = [Link]()
6
title = f" UnreturnedBooks forMember ID { member_no }"
24
filename_prefix = f"Unreturned_Books_Member_{ member_no }"
7
24
elif choice == '2':
8
24
print([Link] + [Link] + "\nREPORT MEMBER BY TYPE\n" + Fore.LIGHTBLACK_EX + " " * 30) ─
member_type = input([Link] + ).upper()
9
query = """
25
0 SELECTt.transaction_no, b.book_no, b.book_name,
251 m.member_no, m.member_name, m.member_type, t.issue_date, t.return_date, t.actual_return_date
252 FRO `transaction` t
253 M book b ON t.book_no = b.book_no
25 JOIN member m ON t.member_no = m.member_no
4 JOIN t.actual_return_date IS NULL
255 WHE m.member_type = %s
25 """ RE
6 AND
[Link](query, (member_type,))
25 data = [Link]()
7 title = f" UnreturnedBooks forMember Type {member_type}"
25 filename_prefix = f" Unr etu rne d_B oo ks_ Typ e_ {me mbe r_t ype}"
8 :
25 else print([Link] + ❌
9 return " Invalid choice." )
26
0
261 headers = ["Trans No", "Book No", "Book Name", "Member No", "Member Name","Member Type", "Issue Date", "
26 Return Date", "Actual Return Date"]
2
26 #no data is found
3 ifnot data:
26 ⚠
print([Link] + "\n No unreturned books found for this selection.")
4
26 return
5 generate_pdf_report(title, headers, data, filename_prefix)
26
6
26 # REPORT MENU
7 d e report_menu():
f
26
while True :
8
26
print([Link] + [Link] +" ╔═════════════════════════════════════════ )
print([Link] + [Link] + ) )
9
print([Link] + [Link] +" ╠═════════════════════════════════════════ )
27
print([Link] + [Link] +" 1. All║ ║ ║ ║ ║ ║ ║ ║ ║ ║║
" Books
" Book
" "by " " " " " ")
0
╚═══════════════════════════════
print([Link] + [Link] +" 2. Book No Book by ║ ")
271
══════════════════════╝
print([Link] + [Link] + 3. Keyword All Members ║ " ")
27
print([Link] + [Link] + 4. Member by No / Type ║ ")
2
print([Link] + [Link] + 5. Member by Keyword ║ ")
27
print([Link] + [Link] + 6. AllTransactions ║ ")
3
print([Link] + [Link] + 7. AllUnreturned Books ║ ")
27
4
print([Link] + [Link] + 8. ║ ")
27
print([Link] + [Link] + 9. UnreturnedBooks by Member No / Type║ ")
5
print([Link] + [Link] + 10. Return to Main Menu
print([Link] + [Link] +
║ ")
27
6
27 choice = input([Link] + [Link] + )
7
27 if choice =='1': report_all_books()
8 elif choice =='2': report_book_by_id()
27 elif choice =='3': report_book_by_keyword()
9 elif choice =='4': report_all_members()
28 elif choice =='5': report_member_by_id()
0 elif choice =='6': report_member_by_keyword()
281 elif choice =='7': report_all_transactions()
28 elif choice =='8': report_all_unreturned_books()
2 elif choice =='9': report_unreturned_books_by_member()
28 elif
choice =='10':
3
28 File - C:\Users\Sujal\PycharmProjects\Print\[Link]
4
Page 4 of 5
28
5
28
318 print([Link] + [Link] + " ↩ Returning to Main Menu‫)"΀ێێ‬
319 break
32 else :
0 ⚠
print([Link] + [Link] + " Invalid choice. Please choose between 1–10.")
321
322
323 if __name__ == "__main__":
32 report_menu()
4
325

File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 5 of 5
 1 from colorama import Fore, Style, init
2 import pymysql
3 init(autoreset=True)
4 conn
5 = [Link](
6 7 host="localhost",
8 9 user="root",
10 11 password="sujal1",
12 13 database="library"
14 15
) cursor = [Link]()
16 17
# ADD NEW BOOK def
18 19
add_new_book():
20
21
22 try:
23
24
print([Link] + [Link] + "\n \n" + Fore.LIGHTBLACK_EX + " ─" * 40)
book_no = input([Link] + ).lower()
25 [Link]("SELECT * FROM book WHERE book_no = %s", (book_no,))
26 existing = [Link]()
27 if existing:
28
29
⚠
print([Link] + [Link] + " Book number already exists!")
return
30
elif book_no == "exit":
31
print([Link] + [Link] + )
32
return
33
book_name = input([Link] + )
34
author_name = input([Link] + )
35
publisher = input([Link] + )
36
no_of_pages = int(input([Link] + ))
37
no_of_copies = int(input([Link] + ))
available_copies = int(input([Link] + ))
price = float(input([Link] + ))
[Link]("""
INSERT INTO book (book_no, book_name, author_name, publisher_name, no_of_pages, price,
number_of_copies, available_copies)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""", (book_no, book_name, author_name, publisher, no_of_pages, price, no_of_copies, available_copies))
[Link]()
✅
print([Link] + [Link] + " Book added successfully!")
38 except Exception as e:
39 ❌
print([Link] + f" Error: {e}")
40
41 # MODIFY BOOK
42 def modify_book():
43 print([Link] + [Link] + "\n ─
\n" + Fore.LIGHTBLACK_EX + " " * 40)
44 book_no = input([Link] + )
45 [Link]("SELECT * FROM book WHERE book_no = %s", (book_no,))
46 book = [Link]()
47 if not book:
48 ❌
print([Link] + [Link] + " Book not found.")
49 return
50 print([Link] + )
51 book_name = input([Link] + + Fore.LIGHTWHITE_EX + f"[{book[1]}]: ") or book[1]
52 author_name = input([Link] + + Fore.LIGHTWHITE_EX + f"[{book[2]}]: ") or book[2]
53 publisher_name = input([Link] + + Fore.LIGHTWHITE_EX + f"[{book[3]}]: ") or book[3]
54 no_of_pages = int(input([Link] + + Fore.LIGHTWHITE_EX + f"[{book[4]}]: ") or book[4])
55 price = float(input([Link] + + Fore.LIGHTWHITE_EX + f"[{book[5]}]: ") or book[5])
56 number_of_copies = int(input([Link] + + Fore.LIGHTWHITE_EX + f"[{book[6]}]: ") or
57 book[6])
58 available_copies = int(input([Link] + + Fore.LIGHTWHITE_EX + f"[{book[7]}]: ") or
59 book[7])
60 [Link]("""
61 UPDATE book SET
62 book_name = %s,
63 author_name = %s,
64 publisher_name = %s,
65 no_of_pages = %s,
66 price = %s,
67 number_of_copies = %s,
68 available_copies = %s
69
WHERE book_no = %s
70
""", (book_name, author_name, publisher_name, no_of_pages, price, number_of_copies, available_copies, book_no
71 ))
[Link]()
72
73
✅
print([Link] + [Link] + " Book updated successfully!")
74
75
76 # DELETE BOOK
77 def delete_book():
print([Link] + [Link] + "\n ─
\n" + Fore.LIGHTBLACK_EX + " " * 30)
book_no = input([Link] + )
[Link]("SELECT * FROM book WHERE book_no = %s", (book_no,))
book = [Link]()

File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 1 of 3
 78 ifnot book:
79 print([Link] + [Link] + " ❌ Book not found.")
80 return
81 print(Fore.LIGHTCYAN_EX + "\n )
82 ─
print(Fore.LIGHTBLACK_EX + " " * 25)
83 print([Link] + print([Link]
, book[0]) +
84 print([Link] + print([Link]
, book[1]) +
85 print([Link] + print([Link]
, book[2]) +
86 print([Link] + print([Link], book[3])
+ choice =
87 input([Link] + [Link] +
, book[4]) ,
88 book[5])
89 , book[6])
90 , book[7])
91
92
⚠
"\n Confirm delete? (Y/N): " ).lower()
if choice != "y":
93
94
print([Link] + [Link] + " ❎ Deletion cancelled.")
return
95
try :
96
[Link]( "DELETE FROM book WHERE book_no = %s", (book_no,))
97
[Link]()
98
99
✅
print([Link] + [Link] "+ Book deleted successfully.")
except [Link]:
100
101
print([Link] + [Link] ⚠
+ " Cannot delete — active transactions exist.")
except Exception as e: +
102
103 ❌
print([Link] + [Link] f" Error: {}") e
104
105 # LIST ALL BOOKS
def list_all_books():
106
107 print([Link] + [Link] + "\n ─
\n" + Fore.LIGHTBLACK_EX + " " * 125)
108 [Link]( "SELECT * FROM book" )
109 books = [Link]()
110 ifnot books:
111 print([Link] + [Link] + ⚠
" No books found.")
112 return
113 header = "{:<8} {:<30} {:<20} {:<20} {:<8} {:<8} {:<12} {:<10}" .format(
114 "BookNo", "Book Name" , "Author" , "Publisher", "Pages", "Price" , "Total", "Available"
115 )
116 print(Fore.LIGHTCYAN_EX + header)
117 ─
print(Fore.LIGHTBLACK_EX + " " * 125)
118 for b in books:
119 print([Link] + "{:<8} {:<30} {:<20} {:<20} {:<8} {:<8} {:<12} {:<10}" .format(*b))
120 ✅
print([Link] + [Link]+ "\n All books displayed successfully!\n" )
121
122 # SEARCH BY BOOK NO
123 def find_by_book_no():
124 print([Link] + [Link] + "\n \n" + Fore.LIGHTBLACK_EX + " ─" * 30)
125 book_no = input([Link] + )
126 [Link]("SELECT * FROM book WHERE book_no = %s", (book_no,))
127 book = [Link]()
128 if book:
129 print(Fore.LIGHTCYAN_EX + [Link] + "\n )
130
131
─
print(Fore.LIGHTBLACK_EX + " " * 25)
print([Link] + print([Link] , book[0])+
132 print([Link] + print([Link]
, book[1]) +
133 print([Link] + print([Link], book[2])+
134 print([Link] + print([Link] + :
, book[3])
135
136
❌
print([Link] + " Book not found.")
, book[4]) ,
book[5])
137
, book[6])
138
, book[7])
139
140 else
141 # SEARCH BY KEYWORD
142
143
144 def find_by_keywords():
145 print([Link] + [Link] + "\n ─
\n" + Fore.LIGHTBLACK_EX + " " * 30)
146 keywords = input([Link] + )
147 like_keywords = f"%{ keywords}%"
148 [Link]( """
149 SELECT * FROM book
150 WHERE book_name LIKE %s OR author_name LIKE %s OR publisher_name LIKE %s
151 """ , (like_keywords,like_keywords,like_keywords))
152 books = [Link]()
153 if books:
154 print(Fore.LIGHTCYAN_EX + [Link] + "\n )
155 ─
print(Fore.LIGHTBLACK_EX + " " * 125)
156 print(Fore.LIGHTCYAN_EX + "{:<8} {:<30} {:<20} {:<20} {:<8} {:<8} {:<12} {:<10}" .format(
157 , "BookNo" "Book Name" , "Author" , "Publisher" "Pages",
, "Price", "Total", "Available"
158 ))
─
print(Fore.LIGHTBLACK_EX + " " * 125)
for b in books:
File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 2 of 3
159 print([Link] + "{:<8} {:<30} {:<20} {:<20} {:<8} {:<8} {:<12} {:<10}" .format(*b))
160 else :
161 # BOOK MENU ⚠
print([Link] + [Link] + " No matching books found.")
162
163
164 def book_menu():
165 while True :
166 print([Link] + [Link] +" ╔═════════════════════════════════════════
)
167 print([Link] + [Link] + ) )
168 print([Link] + [Link] +" ╠═════════════════════════════════════════
)
169 ║
print([Link] + [Link] +" 1. AddNew ║ "║ ║
Book" ║ ║ ║" ║")
" " "
170 ╚═══════════════════════════════
print([Link] + [Link] +" 2. Modify Book Details ║ ")
171 ══════════════════════╝
print([Link] + [Link] + 3. Delete Book ║ ") "
172 print([Link] + [Link] + 4. List All Books ║ ")
173 print([Link] + [Link] + 5. Find Book by Number ║ ")
174 print([Link] + [Link] + 6. Find Book by Keyword ║ ")
175
176
print([Link] + [Link] + 7. Return to Main Menu
print([Link] + [Link] +
║ ")
177 try:
178
choice = int(input([Link] + [Link] + ))
179
exceptValueError:
180
181
❌
print([Link] + [Link] + " Pleaseenter a valid number.")
continue
182
183 ifchoice == 1:
184 add_new_book()
185 elif choice == 2:
186 modify_book()
187 elif choice == 3:
188 delete_book()
189 elif choice == 4:
190 list_all_books()
191 elif choice == 5:
192 find_by_book_no()
193 elif choice == 6:
194 fi nd _by _ke ywo rds ()
195 elif choice == 7:
196 ↩
print([Link] + [Link] + " Returning to Main Menu ‫)"΀ ێێ‬
197 break
198 else:
199 ⚠
print([Link] + [Link] + " Invalid choice. Please choose between 1–7.")
20
0 if __name__== "__main__":
201 book_menu()
20 [Link]()
2 [Link]()
20
3

File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 3 of 3
1 from colorama import Fore, Style, init
2 import subprocess
3 import sys
4 import pymysql
5
6 init(autoreset=True)
7
8 conn = [Link](
9 host="localhost",
10 user="root",
11 password="sujal1"
12 ) cursor = [Link]() # EXECUTE
13 EXTERNAL PYTHON SCRIPT def
14 execute(a):
15
16
17
[Link]([[Link], a])
18
19
# MAIN MENU
20
def main_menu():
21
while True:
22
23
print(Fore.LIGHTBLUE_EX + [Link]+ ' ╔═══════════════════════════════════════
)
print(Fore.LIGHTBLUE_EX + [Link]+ ) )
24
print(Fore.LIGHTBLUE_EX + [Link]+ ' ╠═══════════════════════════════════════
)
25
print(Fore.LIGHTBLUE_EX + [Link]+ ' ║ ║ ║ ║ '║
' 1. BOOK MENU
' ║' ║')'
26
print(Fore.LIGHTBLUE_EX + [Link]+ ' ╚═══════════════════════════════
║ ')
2. MEMBER MENU
27
28
print(Fore.LIGHTBLUE_EX + [Link]+ ══════════════════════╝ ' ║ ')
3. TRANSACTION MENU
29
print(Fore.LIGHTBLUE_EX + [Link]+ 4. REPORT ║ ')
30
print(Fore.LIGHTBLUE_EX + [Link]+ 5. ADMIN ║ ')
31
print(Fore.LIGHTBLUE_EX + [Link]+ 6. EXIT ║ ')
print(Fore.LIGHTBLUE_EX + [Link]+
32
choice = input([Link] + [Link]
33
34 + )
35 if choice == '1' :
36 execute( "[Link]" )
37 elif choice == '2' :
38 execute( "[Link]" )
39 elif choice == '3' :
40 execute( "[Link]" )
41 elif choice == '4' :
42 execute( "[Link]" )
43 elif choice == '5' :
44 pw = input( "Enter your password: " )
45 if pw == "1234" :
46 ✅
print(Fore.LIGHTGREEN_EX "+ Access Granted")
47 execute( "[Link]")
48 else:
49
50
⚠
print(Fore.LIGHTRED_EX + " Invalid password" )
elif choice == '6':
51 print([Link] + [Link] + )
52 break
53 else:
54
55
❌
print([Link] + [Link] + " Invalid choice! Try again.")

56
if __name__ == "__main__":
57
main_menu()
58
[Link]()
[Link]()

File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 1 of 1
1 from colorama import Fore, Style, init
2 import pymysql
3
4 init(autoreset=True)
5
6 # Database connection
7 conn = [Link](
8 host="localhost",
9 user="root",
10 password="sujal1"
11 ) cursor = [Link]()
12 [Link]("USE library") #
13 CREATE BOOK TABLE IF NOT EXISTS
14 create_table_query = """ CREATE
15 TABLE IF NOT EXISTS book (
16
17
18
19
book_no INT PRIMARY KEY,
20
book_name VARCHAR(255),
21
author_name VARCHAR(255),
22
publisher_name VARCHAR(255),
23
no_of_pages INT,
24
price DECIMAL(10,2),
25
number_of_copies INT,
26
available_copies INT
27
)
28 """
29 [Link](create_table_query)
30 31
print([Link]
32 33 34 35 36 37+ 38
[Link]
39 +" ✅ Table 'book' created successfully .")
40 #41DEFAULT
# INSERTBOOKDEFAULT
DATA TO INSERT
BOOKS
books =[

(1, "Wings of Fire" , "A.P.J. Abdul Kalam", "Universities Press" ,180, 250, 10, 10),
(2, "The Discovery of India" , "Jawaharlal Nehru", "Penguin Books" ,650, 499, 15, 7),
(3, "India 2020" ,"A.P.J. Abdul Kalam" "Penguin
, Books" ,300,350,12, 5),
(4, "Ignited Minds" , "A.P.J. Abdul Kalam", "Penguin Books" ,220,280, 8, 3),
(5, "My Experiments with Truth", "Mahatma Gandhi", "Navajivan Publishing" , 450, 400, 20, 12),
]

42 insert_query = """
INSERT INTO book (book_no, book_name, author_name, publisher_name, no_of_pages, price, number_of_copies,
4
available_copies)
3
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
4
"""
4
try
4
5 :
4 [Link](insert_query, books)
50
6 [Link]()
4 print([Link] + [Link] + " ✅ Sample books inserted successfully.")
7
51 except [Link]:
4
52
8
print([Link] + [Link] + " ✅ Some books may already exist. Skipping duplicates.")
53
4
54 [Link]()
9
55 [Link]()

File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 1 of 1
1 from colorama import Fore, Style, init
2 import pymysql
3
4 init(autoreset=True)
5
6 conn = [Link](
7 host="localhost",
8 user="root",
9 password="sujal1",
10 database="library"
11 ) cursor = [Link]() #
12 ADD NEW MEMBER def
13 add_new_member():
14
15
16
try:
17
18
print([Link] + [Link] + "\n \n" + Fore.LIGHTBLACK_EX + " ─" * 40)
member_no = int(input([Link] + ))
19
20
[Link]("SELECT * FROM member WHERE member_no = %s", (member_no,))
21
existing = [Link]()
22
if existing:
23
24
⚠
print([Link] + [Link] + " Member already exists!")
25 return
26
27 member_name = input([Link] + )
28 mobile_no = input([Link] + )
29 member_type = input([Link] + ).upper()
30 roll_no = None
31 standard = None
32 if member_type == "S":
33 roll_no = input([Link] + )
34 standard = input([Link] + )
35
36 [Link]("""
37 INSERT INTO member(member_no, member_name, mobile_no, member_type, roll_no, standard)
38 VALUES(%s, %s, %s, %s, %s, %s)
39 """, (member_no, member_name, mobile_no, member_type, roll_no, standard))
40 [Link]()
41 ✅
print([Link] + [Link] + " Member added successfully!")

42 except Exception as e:
43 ❌
print([Link] + [Link] + f" Error: {e}")
44
45 # MODIFY MEMBER
46 def modify_member():
47 print([Link] + [Link] + "\n \n" + Fore.LIGHTBLACK_EX + " ─" * 40)
48 member_no = int(input([Link] + ))
49 [Link]("SELECT * FROM member WHERE member_no = %s", (member_no,))
50 member = [Link]()
51 if not member:
52 ❌
print([Link] + [Link] + " Member not found.")
53 return
54
55 print([Link] + [Link] + )
56 member_name = input([Link] + + Fore.LIGHTWHITE_EX + f"[{member[1]}]: ") or member[1] +
57 mobile_no = input([Link] + Fore.LIGHTWHITE_EX + f"[{member[3]}]: ") or member[3]
58 member_type = input(
59 ] [Link] + + Fore.LIGHTWHITE_EX + f"[{member[2]}]: ").upper() or member[2
60
61 roll_no = None
62 standard = None
63 if member_type == "S":
64 roll_no = input([Link] + + Fore.LIGHTWHITE_EX + f"[{member[4]}]: ") or member[4]
65 standard = input([Link] + + Fore.LIGHTWHITE_EX + f"[{member[5]}]: ") or member[5]
66
67 [Link]("""
68
UPDATE member SET
69
member_name =
70
%s, mobile_no =
71
%s, member_type
72
= %s, roll_no = %s,
73
standard = %s
74
WHERE member_no = %s
75
""", (member_name, mobile_no, member_type, roll_no, standard, member_no))
76
[Link]()
77
78
✅
print([Link] + [Link] + " Member updated successfully!")
79
80
# DELETE MEMBER
def delete_member():

File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 1 of 4
 81 print([Link] + [Link] + "\n \n" + Fore.LIGHTBLACK_EX + " ─" * 30)
82 member_no = input([Link] + )
83 [Link]( "SELECT * FROMmember WHERE member_no ,=(member_no,))
%s"
84 member = [Link]()
85
86 ifnot member:
87 print([Link] + [Link] + " ❌ Member not found.")
88
89 return
90 print(Fore.LIGHTCYAN_EX + "\n )
91
92
─
print(Fore.LIGHTBLACK_EX + " " * 25)
print([Link] + , member[0])
93 print([Link] + , member[1]) ,
94 print([Link] + member[2])
95 print([Link] + , member[3])
96
if member[2] == "S":
97
print([Link] + , member[4])
98
print([Link] + , member[5])
99
100
101
choice = input([Link] + "\n ⚠ Confirm delete? (Y/N): " ).lower()
if choice != "y":
102
103
print([Link] + [Link] + ❎
" Deletion cancelled." )
104 return
105
106 try :
107 [Link]( "DELETEFROMmember WHERE member_no = %s", (member_no,))
108 [Link]()
109 ✅
print([Link] + [Link] "+ Member deleted successfully.")
110 except [Link]:
print([Link] + [Link] +
⚠
"
Cannot delete — active transactions exist.")
111
112 except Exception as e: +
113 ❌
print([Link] + [Link] f" Error: {e}")
114
115 # LIST ALL MEMBERS
116 def list_all_members():
117 print([Link] + [Link] + "\n \n" + Fore.LIGHTBLACK_EX + " ─" * 80)
118 [Link]( "SELECT * FROM member")
119 members = [Link]()
120 ifnot members:
121 print([Link] + [Link] + " ⚠ No members found.")
122 return
123
124 header = "{:<8} {:<25} {:<10}{:<15} {:<10} {:<10}".format(
125 "MemberNo""Name"
, , "Type" , "Mobile", "RollNo", "Class"
126 )
127 print(Fore.LIGHTCYAN_EX + header)
128 ─
print(Fore.LIGHTBLACK_EX + " " * 80)
129
130 for m in members:
131 print([Link] + "{:<8} {:<25} {:<10} {:<15} {:<10} {:<10}" .format(
132 m[0], m[1], m[2], m[3], m[4] if m[4] else "-", m[5] if m[5] else "-"
133 ))
134
135
136
print([Link] + [Link] + "\n ✅ All members displayed successfully!\n")
137
# SEARCHBY MEMBERNO / TYPE
138
def find_by_member_no():
139
print( "Search by: \[Link] No \[Link] Type:")
140
choice =input( "Enteryour choice: " )
141
if choice == "1" :
142
143
print([Link]+ [Link]+ "\n \n" + Fore.LIGHTBLACK_EX + " ─" * 30)
member_no= int(input([Link] + ))
144
145 [Link]( "SELECT *FROM member WHERE m ember_no = %s"
, (member_no,))
146 member = [Link]()
147 if member:
148 print(Fore.LIGHTCYAN_EX+ [Link] + "\n )
149 print(Fore.LIGHTBLACK_EX ─
"+ " * 25)
150 print([Link] + , member[0])
151 print([Link] ,+ member[1])
152 print([Link] ,+member[2])
153 print([Link] + , member[3])
154 if member[2] == "S" :
155 print([Link] + , member[4])
156 print([Link] + , member[5])
157 else :
158 ❌
print([Link] + [Link]+ " Membernot found.")
159
160 elif choice == "2" :
161 print([Link]+ [Link]+ "\n \n" + Fore.LIGHTBLACK_EX + " ─" * 30)
member_type = input([Link]+ )
[Link]( "SELECT *FROM member WHERE , (member_type,))
m ember_type = %s"
File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 2 of 4
162 member = [Link]()
163 if member:
164 ─
print(Fore.LIGHTBLACK_EX + " " * 80)
165 header = "{:<8}{:<25} {:<10} {:<15} {:<10} {:<10}".format(
166 "MemberNo""Name"
, , "Type" , "Mobile", "RollNo", "Class"
167 )
168 print(Fore.LIGHTCYAN_EX + header)
169 ─
print(Fore.LIGHTBLACK_EX + " " * 80)
170
171 for m in member:
172 print([Link] + "{:<8}{:<25} {:<10} {:<15} {:<10} {:<10}" .format(
173 m[0], m[1], m[2], m[3], m[4] if m[4] else "-", m[5] if m[5] else "-"
174 ))
175
else :
176
177
print([Link] + [Link] + "
# SEARCH BY KEYWORD
❌ Member not found.")

178
179
def find_by_keywords():
180
181
print([Link] + [Link] + "\n \n" + Fore.LIGHTBLACK_EX + " " * 30)─
keyword = input([Link] + )
182
like_keywords = f"%{ keyword}%"
183
[Link]( """
184
SELECT * FROMmember
185
186 WHERE member_name LIKE %s OR member_type LIKE %s OR mobile_no LIKE %s OR roll_no LIKE %s OR standard
LIKE %s
187
188 """ , (like_keywords, like_keywords, like_keywords, like_keywords, like_keywords))
189 members = [Link]()
190 if members:
191 print(Fore.LIGHTCYAN_EX + [Link] + "\n )
192 ─
print(Fore.LIGHTBLACK_EX + " " * 80)
193 print(Fore.LIGHTCYAN_EX + "{:<8}{:<25} {:<10}{:<15} {:<10} {:<10}" .format(
194 "MemberNo", "Name","Type" , "Mobile" "RollNo"
, "Class"
,
195 ))
196 ─
print(Fore.LIGHTBLACK_EX + " " * 80)
197 for m in members:
198 print([Link] + "{:<8}{:<25} {:<10}{:<15} {:<10} {:<10}" .format(
199 m[0], m[1], m[2], m[3], m[4] i f m[4] e l s e " - " , m[5] i f m[5] e l s e " - "
20 ))
0 else :
201 ⚠
print([Link] + [Link] + " No matching members found.")
# MEMBER MENU
20
2
20 def member_menu():
3 while True :
20 print([Link] + [Link]+ " ╔═════════════════════════════════════════
)
4 print([Link] + [Link]+ ) )
20 print([Link] + [Link]+ " ╠═════════════════════════════════════════
)
5 print([Link] + [Link]+ " ║ 1. Add New Member ║ " )
20 print([Link] + [Link]+ " ║ 2. Modify Member Details ║ " )
6 print([Link] + [Link]+ " ║ 3. Delete Member ║ " )
20 print([Link] + [Link]+ " ║ 4. List All Members ║ " )
7 print([Link] + [Link]+ " ║ 5. Find Member by No or Type ║ " )
20 print([Link] + [Link]+ " ║ 6. Find Member by Keyword ║ " )
8 print([Link] + [Link]+ " ║ 7. Return to Main Menu ║ " )
20
9
print([Link] + [Link]+ " ╚═════════════════════════════════════════
210
try :
211
choice = int(input([Link] + ))
212
except ValueError:
213
214
print([Link] + [Link]+ " ❌ Please enter a valid number.")
continue
215
216
217 if choice == 1:
218 add_new_member()
219 elif choice == 2:
22 modify_member()
0 elif choice == 3:
221 delete_member()
222 elif choice == 4:
223 li st _al l_m emb ers ()
22 elif choice == 5:
4 fi nd _by _me mbe r_n o()
225 elif choice == 6:
22 fi nd _by _ke ywo rds ()
6 elif choice == 7:
22 print([Link] + [Link] + " ↩ Returning to Main Menu‫)"΀ێێ‬
7 break
22 else :
8 print([Link] + [Link] + "⚠ Invalid choice. Please choose between 1–7.")
22
9 if __name__ == "__main__":
23
0 File - C:\Users\Sujal\PycharmProjects\Print\[Link]
231
Page 3 of 4
232
233
23
24 member_menu()
2 [Link]()
24 [Link]()
3
24
4

File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 4 of 4
1 from colorama import Fore, Style, init
2 import pymysql
3
4 init(autoreset=True)
5
6 # Database connection
7 conn = [Link](
8 host="localhost",
9 user="root",
10 password="sujal1"
11 ) cursor = [Link]()
12 [Link]("USE library")
13
14
15
16 # CREATE MEMBER TABLE IF NOT EXISTS
17 create_table_query = """
18 CREATE TABLE IF NOT EXISTS member (
19 member_no INT PRIMARY KEY,
2 member_name VARCHAR(100) NOT NULL,
0 member_type ENUM('S', 'T', 'F') NOT NULL,
21 mobile_no VARCHAR(15), roll_no
22 VARCHAR(50), standard VARCHAR(50)
23
2 )
4 """
25
27 [Link](create_table_query)
2 ✅
28 print([Link] + [Link] + 29 " Table 'member' created sucessfully .")
6
30 31 32 33 34 35 36 37 38 39 # INSERT
MEMBER DATA
# DEFAULT MEMBER DATA TO INSERT
members = [
(1, 'Riya Sharma' , 'S' , '9876543210' ,'ST101' '10th, A' ),
(2, 'Amit Patel' 'S'
, , '9988776655' ,'ST102' '12th
, B' ),
(3, 'Sunita Joshi' , 'T' , '9123456788' ,None , None),
(4, 'Rajiv Menon', 'F' , '9001122334' None
, , None),
(5, 'Kunal Mehra' , 'T' , '9876501234' ,None , None)
]

40 insert_query = """
INSERT INTO member (member_no, member_name, member_type, mobile_no, roll_no, standard)
41
VALUES (%s, %s, %s, %s, %s, %s)
4
"""
2
try
4 :
3 [Link](insert_query, members)
4 [Link]()
47
4 ✅
print([Link] + [Link] + " Sample members inserted successfully.")
4
48 except [Link]:
5
49 ✅
print([Link] + [Link] + " Some records may already exist. Skipping duplicates.")
4
50
6
51 [Link]()
52 [Link]()

File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 1 of 1
1 from datetime import date, timedelta
2 import pymysql
3 from colorama import Fore, Style, init
4
5 init(autoreset=True)
6
7 conn = [Link](
8 host="localhost",
9 user="root",
10 password="sujal1",
11 database="library"
12 ) cursor = [Link]()
13
14
15
16
# ISSUE BOOK
17
def issue_book():
18
19
print([Link] + [Link] + "\n ─
\n" + Fore.LIGHTBLACK_EX + " " * 40)
try:
20
member_no = input([Link] + )
21
book_no = input([Link] + )
22
# Check member
23
[Link]("SELECT member_type FROM member WHERE member_no=%s", (member_no,))
24
member = [Link]()
25
if not member:
26
27
❌
print([Link] + [Link] + " Member not found!")
28 return
29 member_type = member[0]
30 # Check book
31 [Link]("SELECT available_copies FROM book WHERE book_no=%s", (book_no,))
32 book = [Link]()
33 if not book:
34
35 ❌
print([Link] + [Link] + " Book not found!")
36 return
37 available_copies = book[0]
38 if available_copies <= 0:
39 print([Link] + [Link] + " ⚠ No available copies!")
40 return
41
42 # Issue limit
43 [Link](
44 "SELECT COUNT(*) FROM transaction WHERE member_no=%s AND actual_return_date IS NULL",
45 (member_no,)
46 )
47 current_issues = [Link]()[0]
48 max_limit = 3 if member_type == 'S' else 5
49 if current_issues >= max_limit:
50
51
print([Link] + [Link] + f" ⚠
Issue limit reached ({max_limit } books allowed).")
return
52
53
# Dates
54
issue_date = [Link]()
55
return_date = issue_date + timedelta(days=7 if member_type == 'S' else 10)
56
# Insert transaction
57
[Link]("""
58
59
60 INSERT INTO transaction (book_no, member_no, issue_date, return_date)
61 VALUES (%s, %s, %s, %s)
62 """, (book_no, member_no, issue_date, return_date))
63 [Link]()
64 # Update book copies
65
66 [Link]( "UPDATE book SET available_copies = available_copies - 1 WHERE book_no = %s", (book_no,))
67 [Link]()
68 ✅
print([Link] + [Link] + f" Book issued successfully! Return by {return_date
}.")
69 except Exception as e:
70 ❌
print([Link] + [Link] + f" Error: {e}")
71 [Link]()
7
2
7 # RETURN BOOK
3 def return_book():
7 print([Link] + [Link] + "\n \n" + Fore.LIGHTBLACK_EX + " ─" * 40)
4 try:
7 transaction_no = int(input([Link] + ))
5 [Link]("""
7
6 SELECT book_no, return_date
7
7 File - C:\Users\Sujal\PycharmProjects\Print\[Link]
7 Page 1 of 4
8
7
9
8
 82 FROM transaction
83 WHERE transaction_no=%s AND actual_return_date IS NULL
84 """, (transaction_no,))
85 record = [Link]()
86 ifnot record:
87 ❌
print([Link] + [Link] + " Transaction not found or already returned!")
88 return
89
90 book_no, return_date_expected = record
91 actual_return_date = [Link]()
92
93 # Fine
94 fine_per_day = 5
95 days_late = (actual_return_date - return_date_expected).days
96 fine = fine_per_day *days_late if days_late > 0 else 0
97 [Link](
98
"""
99
UPDATE transaction
100
SET actual_return_date=%s, fine=%s
101
WHERE transaction_no=%s
102
""" , (actual_return_date, fine, transaction_no))
103
[Link]()
104
105
# Update book copies
106
107 [Link]( "UPDATE book SET available_copies = available_copies + 1 WHERE book_no = %s", (book_no,))
108 [Link]()
print([Link] + [Link]
109
110 + f" ✅
Book returned successfully! Fine: {fine
₹ }")
111 except Exception as e:
112 print([Link] + [Link] ❌
+ f" Error: {e}")
113 [Link]()
114
115
116 # LIST ALL TRANSACTIONS
117 def list_all_transaction():
118 print([Link] + [Link] + "\n \n" + Fore.LIGHTBLACK_EX + " ─" * 90)
119 try :
120 [Link]( "SELECT * FROM transaction")
121 rows = [Link]()
122 ifnot rows:
123 print([Link] + [Link] + " ⚠
No transactions.")
124 return
125
126 # Header
127 print(Fore.LIGHTCYAN_EX + "{:<10} {:<8} {:<10} {:<12} {:<12} {:<15} {:<5}" .format(
128 "Trans No", "Book No" , "Member No", "Issue Date", "Return Date", "Actual Return" , "Fine"
129 ))
130
131
─
print(Fore.LIGHTBLACK_EX + " " * 90)

132 for row in rows:

133 # ConvertNone values to '—' andeverything to string
134 safe_row = tuple( "—" if val is None else str(val) for val in row)
135 print([Link] + "{:<10} {:<8} {:<10} {:<12} {:<12} {:<15} {:<5}" .format(*safe_row))
136
137
except Exception as e:
138
139
print([Link] + [Link] + f" ❌ Error: {e}")
140
141
# ALL UNRETURNED BOOKS
142
def all_unreturned_books():
143
print([Link] + [Link] + "\n )
144
145 ─
print(Fore.LIGHTBLACK_EX + " " * 120)
146 try:
147 [Link]( """
148 SELECT t.transaction_no,
149 b.book_name,
150 m.member_no,
151 m.member_na
152 me,
153 m.mobile_no,
154 t.issue_date,
155 t.return_date
FRO transaction t
156 M book b ON t.book_no = b.book_no
157 JOIN member m ON t.member_no = m.member_no
158 JOIN t.actual_return_date IS NULL
159 """) WHE
160 rowsRE= [Link]()
161 ifnot rows:
162 print([Link] + [Link] + )
return

File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 2 of 4
163 print(Fore.LIGHTCYAN_EX + "{:<10} {:<30} {:<10} {:<20} {:<15} {:<12} {:<12}" .format(
164 "Trans No", "Book Name", "MemberNo", "Member Name", "Mobile No", "Issue Date", "Return Date"
165 ))
166 ─
print(Fore.LIGHTBLACK_EX + " " * 120)
167
168 for row in rows:
169 safe_row = tuple( "—" if val isNone else str(val) for val in row)
170 print([Link] + "{:<10} {:<30} {:<10} {:<20} {:<15} {:<12} {:<12}" .format(*safe_row))
171
172 except Exception as e:
173
174
print([Link] + [Link] + f" ⚠ Error: {e}")
175
176 # UNRETURNED BOOKS BY MEMBER
177 def unreturned_books_by_member():
178 print([Link] + [Link] + "\n )
179
180
─
print(Fore.LIGHTBLACK_EX + " " * 45)
try :
181
choice = input([Link] + "Search by:\n1. Member No\n2. Member Type\nEnter choice: ").strip()
182
183
if choice == '1':
184
member_no = input([Link] + ).strip()
185
[Link]( """
186
187 SELECTt.transaction_no, b.book_name, m.member_no, m.member_name,
188 m.mobile_no, t.issue_date, t.return_date
189 FRO transaction t
190 M book b ON t.book_no = b.book_no
191 JOIN member m ON t.member_no = m.member_no
192 JOIN t.member_no = %s AND t.actual_return_date IS NULL
193 WHE
""", (member_no,))
194 RE
195 elif choice == '2':
196 member_type = input([Link] + ).strip().upper()
197 [Link]("""
198 SELECTt.transaction_no, b.book_name, m.member_no, m.member_name,
199 m.mobile_no, t.issue_date, t.return_date
20 FRO transaction t
0 M book b ON t.book_no = b.book_no
201 JOIN member m ON t.member_no = m.member_no
20 JOIN m.member_type = %s AND t.actual_return_date IS NULL
2 WHE
""" , (member_type,))
20 RE
3 else :
20 p r i n t ( F o r e . R E D "+ Invalid
S t y choice.")
l e . B R I G H T + ❌
4 r e t u r n
20
5 rows = [Link]()
20 if not rows:
6
20
print([Link] + [Link] + " ⚠ No unreturned books found.")
return
7
20 print(Fore.LIGHTCYAN_EX + "{:<10} {:<30} {:<10} {:<20} {:<15} {:<12} {:<12}" .format(
8 , "MemberNo", "Member Name", "Mobile No", "Issue Date", "Return Date"
"Trans No", "Book Name"
20
))
9
210
─
print(Fore.LIGHTBLACK_EX + " " * 120)
for row in rows:
211
212
safe_row = tuple( "—" if val isNone else str(val) for val in row)
213
print([Link] + "{:<10} {:<30} {:<10} {:<20} {:<15} {:<12} {:<12}" .format(*safe_row))
214
215
print(Fore.LIGHTBLACK_EX + " " ─
* 120)
216
217
except Exception as e:
218
219 print([Link] + [Link] + f" ❌ Error: {e}")
22
0
221 # BOOK DETAILS
222 d e book_details():
f
223 try :
22 book_no = int(input([Link] + ))
4 ─
print(Fore.LIGHTBLACK_EX + " " * 40)
225 [Link]( """
22 SELECT book_no, book_name, author_name, publisher_name, no_of_pages, price, number_of_copies,
6 available_copies
22 FROM book W HERE book_no = %s
7 """ , (book_no,))
22 book= [Link]()
8 ifnot book:
22 print([Link] + [Link] + ❌
" Book not found." )
9 return
23 book_no, book_name, author_name, publisher_name, no_of_pages, price, total_copies, available_copies =
0
231 File - C:\Users\Sujal\PycharmProjects\Print\[Link]
232 Page 3 of 4
233
23
4
242 book
243 print(Fore.LIGHTCYAN_EX + + Fore.LIGHTWHITE_EX + f"{book_no}")
244 print(Fore.LIGHTCYAN_EX + + Fore.LIGHTWHITE_EX + f"{book_name}")
245 print(Fore.LIGHTCYAN_EX + + Fore.LIGHTWHITE_EX + f"{author_name}")
246 print(Fore.LIGHTCYAN_EX + + Fore.LIGHTWHITE_EX + f"{publisher_name}")
247 print(Fore.LIGHTCYAN_EX + + Fore.LIGHTWHITE_EX + f"{no_of_pages}")
248 print(Fore.LIGHTCYAN_EX + + Fore.LIGHTWHITE_EX + f"{price}")
249 print(Fore.LIGHTCYAN_EX + + Fore.LIGHTWHITE_EX + f"{total_copies}")
250 print(Fore.LIGHTCYAN_EX + + Fore.LIGHTWHITE_EX + f"{available_copies}")
251 except Exception as e:
252
253
❌
print([Link] + [Link] + f" Error finding book: {e}")

254 # CHECK BOOK AVAILABILITY

255 def check_book_available():
256 try:
257 book_no = int(input([Link] + ))
258
259
─
print(Fore.LIGHTBLACK_EX + " " * 40)
[Link]("SELECT book_name, available_copies FROM book WHERE book_no = %s", (book_no,))
260 result = [Link]()
261 if not result:
262
❌
print([Link] + [Link] + " Book not found.")
263 return
264 book_name, available_copies = result
265 print(Fore.LIGHTCYAN_EX + + Fore.LIGHTWHITE_EX + f"{book_name}")
266 print(Fore.LIGHTCYAN_EX + + Fore.LIGHTWHITE_EX + f"{available_copies}")
267 except Exception as e:
268 ❌
print([Link] + [Link] + f" Error checking book availability: {e}")
269
270
271 # TRANSACTION MENU
272 def transaction_menu():
273 while True:
274
275
print([Link] + [Link] + " ╔═════════════════════════════════════════
print([Link] + [Link] + )
276 print([Link] + [Link] + " ╠═════════════════════════════════════════
277 ║
print([Link] + [Link] + " 1. Issue Book ║ ")
278 ║
print([Link] + [Link] + " 2. Return Book ║ ")
279 ║
print([Link] + [Link] + " 3. List All Transactions ║ ")
280 ║
print([Link] + [Link] + " 4. List All Unreturned Books ║ ")
281 ║
print([Link] + [Link] + " 5. Unreturned Books by Member / Type║ ")
282 ║
print([Link] + [Link] + " 6. Book Details ║ ")
283 ║
print([Link] + [Link] + " 7. Check Book Availability ║ ")
284 ║
print([Link] + [Link] + " 8. Return to Main Menu ║ ")
285
286
print([Link] + [Link] + " ╚═════════════════════════════════════════
287
288 choice = input([Link] + [Link] + ).strip()
289 if choice == '1':
290 issue_book()
291 elif choice == '2':
292 return_book()
293 elif choice == '3':
294 list_all_transaction()
295 elif choice == '4':
296 all_unreturned_books()
297 elif choice == '5':
298 unreturned_books_by_member()
299 elif choice == '6':
300 book_details()
301 elif choice == '7':
302 check_book_available()
303 elif choice == '8':
304 ↩
print([Link] + [Link] + " Returning to Main Menu ‫)"΀ێێ‬
305 break
306 else:
307 ❌
print([Link] + [Link] + " Invalid choice! Try again.")
308
309
310 if __name__ == "__main__":
311 transaction_menu()
312 [Link]()
[Link]()

File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 4 of 4
1 from colorama import Fore, Style, init
2 import pymysql
3 from datetime import date, timedelta
4
5 init(autoreset=True)
6
7 # Database connection
8 conn = [Link](
9 host="localhost",
10 user="root",
11 password="sujal1",
12 database="library"
13 ) cursor = [Link]() # CREATE
14 TRANSACTION TABLE IF NOT EXISTS
15 create_table_query = """ CREATE TABLE IF
16 NOT EXISTS transaction (
17
18
19
20
transaction_no INT PRIMARY KEY AUTO_INCREMENT,
21
book_no INT NOT NULL,
22
member_no INT NOT NULL,
23
issue_date DATE NOT NULL,
24
return_date DATE NOT NULL,
25
actual_return_date DATE DEFAULT NULL,
26
fine INT DEFAULT 0,
27
FOREIGN KEY(book_no) REFERENCES book(book_no),
28
FOREIGN KEY(member_no) REFERENCES member(member_no)
29
)
30 """ 31 [Link](create_table_query) 32print([Link] + [Link] + " ✅
Table
'transaction' created successfully .") 33 34 35 36 37 38 39 40 41 42 43 44 # INSERT DEFAULT
TRANSACTIONS

# DEFAULT TRANSACTION DATA TO INSERT

today = [Link]()
transactions = [
(1, 1, today - timedelta(days=2), (2, 2, today
today- +timedelta(days=15),
timedelta(days=5), today
None, - 0),
timedelta(days=5), (3, 3, today - timedelta(days=20), today
today -- timedelta(days=4), 0),
timedelta(days=10), today (4, 1, today - timedelta(days=30), today -- timedelta(days=3), 10),
timedelta(days=15), today - timedelta(days=12), 35),
(5, 4, today - timedelta(days=5), today + timedelta(days=10), None, 0),
]

45 insert_query = """
INSERT INTO transaction (book_no, member_no, issue_date, return_date, actual_return_date, fine)
4
VALUES (%s, %s, %s, %s, %s, %s)
6
"""
4
try
7
4 :
8 [Link](insert_query, transactions)
53
4 [Link]()
9 print([Link] + [Link] + " ✅ Sample transactions inserted successfully." )
5 except [Link]:
54
0
55
51
print([Link] + [Link] + " ✅ Some transactions may already exist. Skipping duplicates.")
56
52 [Link]()
57
58 [Link]()

File - C:\Users\Sujal\PycharmProjects\Print\[Link]
Page 1 of 1