INTERFACE PYTHON WITH SQL
SR EXERCISE QUESTIONS
NO.
1. Question: Design a Python application that fetches all the records
from Pet table of menagerie database.
Program: import [Link]
db_con = [Link](host = "localhost", user = "root", passwd =
"root", database = "menagerie")
cursor = db_con.cursor()
[Link]("Select * From Pet")
rec = [Link]()
for record in rec:
print(record)
db_con.close()
Output: ('Fluffy', 'Harold', 'cat', 'f', [Link](1993, 2, 4), None)
('Claws', 'Gwen', 'cat', 'm', [Link](1994, 3, 17), None)
('Buffy', 'Harold', 'dog', 'f', [Link](1989, 5, 13), None)
('Fang', 'Benny', 'dog', 'm', [Link](1990, 8, 27), None)
('Bowser', 'Diane', 'dog', 'm', [Link](1979, 8, 31), [Link](1995,
7, 29))
('Chirpy', 'Gwen', 'bird', 'f', [Link](1998, 9, 11), None)
('Whistler', 'Gwen', 'bird', None, [Link](1997, 12, 9), None)
('Slim', 'Benny', 'snake', 'm', [Link](1996, 4, 29), None)
2. Question: Design a Python application that fetches only those records
from Event table of menagerie database where type is Kennel.
Program: import [Link]
db_con = [Link](host = "localhost", user = "root", passwd =
"root", database = "menagerie")
cursor = db_con.cursor()
[Link]("Select * From event Where type = 'kennel'")
rec = [Link]()
for record in rec:
print(record)
db_con.close()
Output: ('Bowser', [Link](1991, 10, 12), 'kennel', None)
('Fang', [Link](1991, 10, 12), 'kennel', None)
3. Question: Design a Python application to obtain a search criteria from user
and then fetch records based on that from empl table.
Empl (EmpNo, EName, Job, MGR, HireDate, Sal, Comm, DeptNo)
Program: import [Link]
db_con = [Link](host = "localhost", user = "root", passwd =
"root", database = "employee")
cursor = db_con.cursor()
s = input("Enter search criteria : ")
sql1 = "Select * From Empl Where {}".format(s)
[Link](sql1)
rec = [Link]()
print("Fetched records:")
for record in rec:
print(record)
db_con.close()
Output: Enter search criteria : job = 'clerk'
Fetched records:
(8369, 'Smith', 'Clerk', 8902, [Link](1990, 12, 18), 800.0, None, 20)
(8886, 'Anoop', 'Clerk', 8888, [Link](1993, 1, 12), 1100.0, None, 20)
(8900, 'Jatin', 'Clerk', 8698, [Link](1991, 12, 3), 950.0, None, 30)
(8934, 'Mita', 'Clerk', 8882, [Link](1992, 1, 23), 1300.0, None, 10)
4. Question: Create 2 tables, (Trainer-TID, TName, City, HireDate, Salary) and
(Course-CID, CName, Fees, StartDate, PID) and build a menu that does the
following: (i)add trainer (ii)add course (iii)update trainer (iv)update course
(v)delete trainer (vi)delete course (vii)display trainer (viii)display course
(ix)exit
Program: import [Link]
con = [Link](host="localhost", user="root", passwd=”root”,
database=”trainer”
cursor = [Link]()
def add_trainer():
tid = int(input("Enter Trainer ID: "))
name = input("Enter Trainer Name: ")
city = input("Enter City: ")
hire_date = input("Enter Hire Date (YYYY-MM-DD): ")
salary = float(input("Enter Salary: "))
query = "Insert into Trainer Values (%s,%s,%s,%s,%s)"
[Link](query, (tid, name, city, hire_date, salary))
[Link]()
print("Trainer added successfully!")
def add_course():
cid = int(input("Enter Course ID: "))
cname = input("Enter Course Name: ")
fees = float(input("Enter Fees: "))
start_date = input("Enter Start Date (YYYY-MM-DD): ")
pid = int(input("Enter Trainer ID (PID): "))
query = "Insert into Course Values (%s,%s,%s,%s,%s)"
[Link](query, (cid, cname, fees, start_date, pid))
[Link]()
print("Course added successfully!")
def update_trainer():
tid = int(input("Enter Trainer ID to update: "))
salary = float(input("Enter new Salary: "))
query = "Update Trainer Set Salary=%s Where TID=%s"
[Link](query, (salary, tid))
[Link]()
print("Trainer updated successfully!")
def update_course():
cid = int(input("Enter Course ID to update: "))
fees = float(input("Enter new Fees: "))
query = "Update Course Set Fees=%s Where CID=%s"
[Link](query, (fees, cid))
[Link]()
print("Course updated successfully!")
def delete_trainer():
tid = int(input("Enter Trainer ID to delete: "))
query = "Delete From Trainer Where TID=%s"
[Link](query, (tid,))
[Link]()
print("Trainer deleted successfully!")
def delete_course():
cid = int(input("Enter Course ID to delete: "))
query = "Delete From Course Where CID=%s"
[Link](query, (cid,))
[Link]()
print("Course deleted successfully!\n")
def display_trainer():
[Link]("Select * From Trainer")
data = [Link]()
for row in data:
print(row)
print()
def display_course():
[Link]("Select * From Course")
data = [Link]()
for row in data:
print(row)
print()
def menu():
while True:
choice = int(input("Enter your choice (1-9): "))
if choice == 1:
add_trainer()
elif choice == 2:
add_course()
elif choice == 3:
update_trainer()
elif choice == 4:
update_course()
elif choice == 5:
delete_trainer()
elif choice == 6:
delete_course()
elif choice == 7:
display_trainer()
elif choice == 8:
display_course()
elif choice == 9:
print("Exiting program")
break
else:
print("Invalid choice!")
menu()
[Link]()
Output: Enter Trainer ID: 101
Enter Trainer Name: Rahul Sharma
Enter City: Delhi
Enter Hire Date (YYYY-MM-DD): 2024-05-10
Enter Salary: 45000
Trainer added successfully!
Enter Course ID: 201
Enter Course Name: Python Basics
Enter Fees: 2500
Enter Start Date (YYYY-MM-DD): 2024-06-15
Enter Trainer ID (PID): 101
Course added successfully!
Enter Trainer ID to update: 101
Enter new Salary: 48000
Trainer updated successfully!
Enter Course ID to update: 201
Enter new Fees: 2800
Course updated successfully!
Enter Trainer ID to delete: 102
Trainer deleted successfully!
Enter Course ID to delete: 202
Course deleted successfully!
(101, 'Rahul Sharma', 'Delhi', [Link](2024, 5, 10), 48000.0)
(201, 'Python Basics', 2800.0, [Link](2024, 6, 15), 101)
Exiting program