1.
Rahim wants to write a program in Python to insert the following record in the table named
Bank_Account in MySQL database, Bank :
Accno – integer
Cname – string
Atype – string
Amount – float
Note the following to establish connectivity between Python and MySQL :
Username – admin
Password – root
Host – localhost
The values of fields Accno, Cname, Atype and Amount have to be accepted from the user. Help Rahim
to write the program in Python.
Ans:
import [Link]
# Step 1: Establish connection
conn = [Link](
host="localhost",
user="admin",
password="root",
database="Bank"
)
# Step 2: Create cursor
cursor = [Link]()
# Step 3: Accept data from user
accno = int(input("Enter Account Number: "))
cname = input("Enter Customer Name: ")
atype = input("Enter Account Type: ")
amount = float(input("Enter Amount: "))
# Step 4: SQL query to insert record
sql = "INSERT INTO Bank_Account (Accno, Cname, Atype, Amount) VALUES (%s, %s, %s, %s)"
values = (accno, cname, atype, amount)
# Step 5: Execute query
[Link](sql, values)
# Step 6: Commit changes
[Link]()
print("Record inserted successfully!")
# Step 7: Close connection
[Link]()
2. Sangeeta wants to write a program in Python to delete the record of a candidate “Raman” from the
table named Placement in MySQL database, Agency: The table Placement in MySQL contains the
following attributes :
CName – String
Dept – String
Place – String
Salary – integer
Note the following to establish connectivity between Python and MySQL :
Username – root
Password – job
Host – localhost
Help Sangeeta to write the program in Python for the above mentioned task.
Ans:
import [Link]
# Step 1: Establish connection
conn = [Link](
host="localhost",
user="root",
password="job",
database="Agency"
)
# Step 2: Create cursor
cursor = [Link]()
# Step 3: SQL query to delete record
sql = "DELETE FROM Placement WHERE CName = %s"
value = ("Raman",)
[Link](sql, value)
[Link]()
# Step 4: Check result
if [Link] > 0:
print("Record of Raman deleted successfully!")
else:
print("Record not found.")
# Step 5: Close connection
[Link]()
With Exception Handling
import [Link]
try:
# Step 1: Establish connection
conn = [Link](
host="localhost",
user="root",
password="job",
database="Agency"
)
cursor = [Link]()
# Step 2: SQL query to delete record
sql = "DELETE FROM Placement WHERE CName = %s"
value = ("Raman",)
[Link](sql, value)
[Link]()
if [Link] > 0:
print("Record of Raman deleted successfully!")
else:
print("Record not found.")
except [Link] as err:
print("Error:", err)
finally:
if conn.is_connected():
[Link]()
print("Connection closed.")
3. Nutan Kumar is using Python connectivity with MySQL for maintaining data for a table named
MEDICINES in a database PHARMACY. The table has the following attributes :
MId (Medicine number) – string
Mname (Medicine Name) – string
Expiry (Expiry Date) – Date
Status (Active/Discard) – string
Consider the following to establish connectivity between Python and MySQL :
Username – root
Password – tiger
Host – localhost
Help Nutan to write the definition of a user-defined function named ChangeStatus() in Python to change
the Status of the Medicines whose Expiry is before '2022-12-31' as 'DISCARD'.
Ans:
import [Link]
def ChangeStatus():
# Establish connection
conn = [Link](
host="localhost",
user="root",
password="tiger",
database="PHARMACY"
)
cursor = [Link]()
# SQL query to update status
sql = "UPDATE MEDICINES SET Status = 'DISCARD' WHERE Expiry < '2022-12-31'"
[Link](sql)
[Link]()
print("Status updated successfully.")
[Link]()
With Exception Handling
import [Link]
def ChangeStatus():
conn = None
try:
conn = [Link](
host="localhost",
user="root",
password="tiger",
database="PHARMACY"
)
cursor = [Link]()
sql = "UPDATE MEDICINES SET Status = 'DISCARD' WHERE Expiry < '2022-12-31'"
[Link](sql)
[Link]()
print("Status updated successfully.")
except [Link] as err:
print("Error occurred:", err)
finally:
if conn is not None and conn.is_connected():
[Link]()
print("Connection closed.")
# Call the function
ChangeStatus()
4. A table named THEATRE, in CINEMA database, has the following structure:
Field Type
Th_ID Char(5)
Name Varchar(15)
City Varchar(15)
Location Varchar(15)
Seats int
Write a function Delete_Theatre(), to input the value of Th_ID from the user and permanently delete
the corresponding record from the table. Assume the following for Python-Database connectivity:
Host:localhost, User: root, Password: Ex2025
Ans:
import [Link]
def Delete_Theatre():
conn = [Link](
host="localhost",
user="root",
password="Ex2025",
database="CINEMA"
)
cursor = [Link]()
# Input Theatre ID from user
tid = input("Enter Theatre ID to delete: ")
# SQL query to delete record
sql = "DELETE FROM THEATRE WHERE Th_ID = %s"
value = (tid,)
[Link](sql, value)
[Link]()
if [Link] > 0:
print("Record deleted successfully.")
else:
print("No such Theatre ID found.")
[Link]()
Delete_Theatre()
Code With Exception Handling
import [Link]
def Delete_Theatre():
conn = None
try:
# Establish connection
conn = [Link](
host="localhost",
user="root",
password="Ex2025",
database="CINEMA"
)
cursor = [Link]()
# Input Theatre ID
tid = input("Enter Theatre ID to delete: ")
# SQL query
sql = "DELETE FROM THEATRE WHERE Th_ID = %s"
value = (tid,)
[Link](sql, value)
[Link]()
if [Link] > 0:
print("Record deleted successfully.")
else:
print("No such Theatre ID found.")
except [Link] as err:
print("Error occurred:", err)
finally:
if conn is not None and conn.is_connected():
[Link]()
print("Connection closed.")
Delete_Theatre()
5. MySQL database named WarehouseDB has a product_inventory table in MySQL which
contains the following attributes:
Item_code: Item code (Integer)
Product_name: Name of product (String)
Quantity: Quantity of product (Integer)
Cost: Cost of product (Integer)
Consider the following details to establish Python -MySQL connectivity:
Username: admin_user
Password: warehouse2024
Host: localhost
Write a Python program to change the Quantity of the product to 91 whose Item_code is 208 in the
product_inventory table.
Ans:
import [Link]
# Step 1: Establish connection
conn = [Link](
host="localhost",
user="admin_user",
password="warehouse2024",
database="WarehouseDB"
)
# Step 2: Create cursor
cursor = [Link]()
# Step 3: SQL query to update quantity
sql = "UPDATE product_inventory SET Quantity = 91 WHERE Item_code = 208"
# Step 4: Execute query
[Link](sql)
# Step 5: Save changes
[Link]()
# Step 6: Display confirmation
if [Link] > 0:
print("Quantity updated successfully.")
else:
print("Item not found.")
# Step 7: Close connection
[Link]()
Code With Exception Handling
import [Link]
conn = None
try:
# Step 1: Establish connection
conn = [Link](
host="localhost",
user="admin_user",
password="warehouse2024",
database="WarehouseDB"
)
# Step 2: Create cursor
cursor = [Link]()
# Step 3: SQL query to update quantity
sql = "UPDATE product_inventory SET Quantity = 91 WHERE Item_code = 208"
# Step 4: Execute query
[Link](sql)
# Step 5: Save changes
[Link]()
# Step 6: Confirmation
if [Link] > 0:
print("Quantity updated successfully.")
else:
print("Item not found.")
except [Link] as err:
print("Error occurred:", err)
finally:
if conn is not None and conn.is_connected():
[Link]()
print("Connection closed.")