Interface of Python with SQL — Board Questions &
Answers (Student-Friendly Format)
Quick Revision Summary
Key Functions:
• [Link]() — Creates connection to MySQL database.
• cursor() — Creates a pointer object to execute SQL commands.
• execute() — Executes SQL statements.
• fetchall() / fetchone() — Retrieve results from SELECT queries.
• commit() — Saves the changes permanently.
• rollback() — Cancels the uncommitted changes.
• close() — Closes connection.
Basic Steps to Connect Python with MySQL:
1■■ Import module — import [Link]
2■■ Connect to database using credentials.
3■■ Create cursor object.
4■■ Execute SQL commands using execute().
5■■ Commit changes if needed.
6■■ Close connection.
SECTION A — 4 MARK QUESTIONS WITH ANSWERS
Q1. Write a Python program to insert a record into a table named Employee with fields (EmpID,
Name, Salary). Explain each step.
Answer:
Step 1: Import connector module
import [Link]
Step 2: Establish connection
con = [Link](host='localhost', user='root', passwd='1234', database='company')
Step 3: Create cursor
cur = [Link]()
Step 4: Execute insert query
[Link]("INSERT INTO Employee VALUES (101, 'Yushit', 50000)")
Step 5: Save changes
[Link]()
Step 6: Close connection
[Link]()
Explanation: Each step ensures data is safely inserted. commit() confirms saving.
Q2. What is the role of cursor object? Explain with an example.
Answer:
Cursor executes queries and fetches records from the database.
Example:
import [Link]
con = [Link](host='localhost', user='root', passwd='1234', database='school')
cur = [Link]()
[Link]("SELECT * FROM student")
for row in [Link]():
print(row)
[Link]()
Explanation: The cursor acts like a pointer that moves through each record.
Q3. Differentiate between commit() and rollback(). Show both with an example.
Answer:
• commit() saves changes permanently.
• rollback() cancels recent changes.
Example:
con = [Link](...) cur = [Link]()
[Link]("DELETE FROM student WHERE marks<40")
choice = input("Save changes? (y/n): ")
if choice=='y':
[Link]()
else:
[Link]()
[Link]()
Q4. Explain exception handling in database connectivity.
Answer:
try-except-finally structure ensures that even if an error occurs, program doesn’t crash.
try:
con = [Link](host='localhost', user='root', passwd='1234', database='school')
cur = [Link]()
[Link]("SELECT * FROM student")
except [Link] as e:
print("Error:", e)
finally:
[Link]()
Explanation: This structure safely handles errors and ensures connection closure.
SECTION B — 5 MARK CASE STUDY QUESTIONS WITH ANSWERS
Q5. A school database has a table student(rollno, name, marks). Write Python code to insert a
record and display all students with marks > 80.
Answer:
import [Link]
con = [Link](host='localhost', user='root', passwd='1234', database='school')
cur = [Link]()
[Link]("INSERT INTO student VALUES (101, 'Riya', 89)")
[Link]()
[Link]("SELECT * FROM student WHERE marks > 80")
records = [Link]()
print("Students with marks > 80:")
for row in records:
print(row)
[Link]()
Explanation: First inserts data, commits, then retrieves all students with marks > 80.
Q6. Write a Python program to increase salary by 10% for all employees in the “Sales” department.
Answer:
import [Link]
con = [Link](host='localhost', user='root', passwd='1234', database='company')
cur = [Link]()
[Link]("UPDATE employee SET salary = salary * 1.1 WHERE department='Sales'")
[Link]()
[Link]("SELECT * FROM employee WHERE department='Sales'")
for row in [Link]():
print(row)
[Link]()
Explanation: Updates the salary of all Sales employees and displays updated records.
Q7. Write Python code to display all books from table books with proper error handling.
Answer:
import [Link]
try:
con = [Link](host='localhost', user='root', passwd='1234', database='library')
cur = [Link]()
[Link]("SELECT * FROM books")
for row in [Link]():
print(row)
except [Link] as e:
print("Database error:", e)
finally:
[Link]()
Explanation: Demonstrates error-safe data retrieval and mandatory connection closure.