Python-MySQL Connectivity
Q1. Update Query
MySQL database named CompanyDB has an Employee table in MySQL
which contains the following attributes:
• EmpID: Employee ID (Integer)
• EmpName: Employee Name (String)
• Department: Department of employee (String)
• Salary: Salary of employee (Integer)
Consider the following details to establish Python-MySQL connectivity:
• Username: admin_user
• Password: warehouse2024
• Host: localhost
Write a Python program to update the Salary of the employee to 55000
whose EmpID is 105.
Answer :
import mysql.connector
con = mysql.connector.connect(host="localhost", user="admin_user",
password="warehouse2024", database="CompanyDB")
cur = con.cursor()
cur.execute("UPDATE Employee SET Salary=55000 WHERE
EmpID=105")
con.commit()
print("Salary updated successfully.")
cur.close()
con.close()
Q2. Update Query
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
Answer :
import mysql.connector
conn = mysql.connector.connect( host='localhost', user='admin_user',
password='warehouse2024', database='WarehouseDB' )
cur = conn.cursor()
cur.execute("UPDATE product_inventory SET Quantity = 91 WHERE
Item_code = 208")
conn.commit()
print(cur.rowcount, 'row(s) updated')
cur.close()
conn.close()
Q3. Insert Query
MySQL database named SchoolDB has a Library table in MySQL which
contains the following attributes:
• BookID: Book ID (Integer)
• Title: Title of book (String)
• Author: Author of book (String)
• Price: Price of book (Integer)
Connection details:
• Username: admin_user
• Password: warehouse2024
• Host: localhost
Write a Python program to insert a new book record: BookID=501,
Title='Python Basics', Author='A. Sharma', Price=450.
Answer :
import mysql.connector
con = mysql.connector.connect(
host="localhost",
user="admin_user",
password="warehouse2024",
database="SchoolDB"
)
cur = con.cursor()
cur.execute("INSERT INTO Library VALUES (501, 'Python Basics', 'A.
Sharma', 450)")
con.commit()
print("Book inserted successfully.")
cur.close()
con.close()
Q4. Delete Query
MySQL database named ShopDB has an Orders table with attributes:
• OrderID: Order ID (Integer)
• CustomerName: Name of customer (String)
• Product: Product ordered (String)
• Amount: Amount of order (Integer)
Connection details:
• Username: admin_user
• Password: warehouse2024
• Host: localhost
Write a Python program to delete the order whose OrderID = 302.
Answer :
import mysql.connector
con = mysql.connector.connect(
host="localhost",
user="admin_user",
password="warehouse2024",
database="ShopDB"
)
cur = con.cursor()
cur.execute("DELETE FROM Orders WHERE OrderID=302")
con.commit()
print("Order deleted successfully.")
cur.close()
con.close()
Q5. Select All Records
MySQL database named HospitalDB has a Patient table in MySQL with the
following fields:
• PatientID: Patient ID (Integer)
• PatientName: Name of patient (String)
• Age: Age of patient (Integer)
• Disease: Disease name (String)
Connection details:
• Username: admin_user
• Password: warehouse2024
• Host: localhost
Write a Python program to display all the records of the Patient table.
Answer :
import mysql.connector
con = mysql.connector.connect(
host="localhost",
user="admin_user",
password="warehouse2024",
database="HospitalDB"
)
cur = con.cursor()
cur.execute("SELECT * FROM Patient")
records = cur.fetchall()
for row in records:
print(row)
cur.close()
con.close()
Q6. Select with Condition
MySQL database named ExamDB has a Students table in MySQL which
contains:
• RollNo: Roll number (Integer)
• StudentName: Name of student (String)
• Class: Class (String)
• Marks: Marks obtained (Integer)
Connection details:
• Username: admin_user
• Password: warehouse2024
• Host: localhost
Write a Python program to display the StudentName and Marks of students
who scored more than 80.
Answer :
import mysql.connector
con = mysql.connector.connect(
host="localhost",
user="admin_user",
password="warehouse2024",
database="ExamDB"
)
cur = con.cursor()
cur.execute("SELECT StudentName, Marks FROM Students WHERE
Marks > 80")
records = cur.fetchall()
for row in records:
print(row)
cur.close()
con.close()
