RRB PUBLIC SCHOOL
Class: 12th
Subject: Computer Science (083)
Chapter: Interface of Python with SQL Database
Worksheet with Answers
SECTION A: VERY SHORT QUESTIONS (1 Mark Each)
1. What is the purpose of the connect() function in Python's MySQL connectivity?
Answer: It establishes a connection between Python and the MySQL database.
2. What does cursor() function do?
Answer: It creates a cursor object that allows interaction with the MySQL database.
3. Which method is used to execute SQL commands in Python?
Answer: The execute() method.
4. Which function is used to save changes made to the database?
Answer: The commit() function.
5. Which function is used to close the connection with a database?
Answer: The close() function.
6. What does fetchone() return?
Answer: It returns one record from the result set.
7. What does fetchall() return?
Answer: It returns all records from the result set.
8. What is the use of rowcount attribute?
Answer: It returns the number of rows affected by the last execute() statement.
9. What is the default port number for MySQL?
Answer: 3306.
10. What is the full form of SQL?
Answer: Structured Query Language.
SECTION B: ASSERTION AND REASON QUESTIONS (2 Marks Each)
1. Assertion (A): The execute() function can execute only one SQL statement at a time.
Reason (R): It sends the command to the MySQL server through the cursor object.
Answer: Both A and R are true, and R is the correct explanation of A.
2. Assertion (A): The fetchone() method retrieves all records at once.
Reason (R): The fetchall() method retrieves only one record.
Answer: Both A and R are false.
3. Assertion (A): The commit() method is necessary after executing an INSERT or UPDATE
query.
Reason (R): Because changes must be saved permanently to the database.
Answer: Both A and R are true, and R is the correct explanation of A.
4. Assertion (A): The cursor object is created automatically when connect() is called.
Reason (R): The cursor() method explicitly creates a cursor object.
Answer: A is false, but R is true.
5. Assertion (A): The close() method terminates both the connection and cursor.
Reason (R): Separate close() calls are required for connection and cursor.
Answer: A is false, but R is true.
SECTION C: SHORT ANSWER QUESTIONS (3 Marks Each)
1. Explain the role of the connect(), cursor(), and execute() methods in database
connectivity.
Answer: connect() establishes the connection, cursor() creates a cursor object for executing
SQL statements, and execute() sends SQL commands to the database.
2. What are the steps to insert data into a MySQL table using Python?
Answer:
1. Import [Link]
2. Connect using connect()
3. Create a cursor
4. Execute INSERT query
5. Commit changes
6. Close the connection.
3. Differentiate between fetchone() and fetchall() with an example.
Answer: fetchone() retrieves one record at a time, while fetchall() retrieves all records.
Example:
→ [Link]() → returns one tuple
→ [Link]() → returns list of tuples.
4. Explain the use of format() and %s placeholders in SQL queries.
Answer: Both are used for inserting variable values into SQL queries. %s is commonly used
with MySQL for parameterized queries.
5. What is meant by database connectivity applications?
Answer: Applications that connect front-end programs like Python with a backend database
such as MySQL for data manipulation.
SECTION D: LONG ANSWER QUESTIONS (4 Marks Each)
1. Explain the complete process of connecting Python with MySQL database with code
example.
Answer: Steps:
1. Import [Link]
2. Establish connection using connect()
3. Create cursor
4. Execute SQL query
5. Commit changes
6. Close connection.
Example:
import [Link]
con = [Link](host='localhost', user='root', password='1234',
database='school')
cursor = [Link]()
[Link]('SELECT * FROM students')
for row in [Link]():
print(row)
[Link]()
2. Write the steps to update and delete records in MySQL using Python.
Answer:
1. Create connection and cursor.
2. Execute UPDATE or DELETE query.
3. Use commit() to save changes.
4. Close connection.
Example:
[Link]('UPDATE students SET marks=90 WHERE rollno=5')
[Link]()
SECTION E: WRITE A PROGRAM (5 Marks Each)
1. Write a Python program to insert a record into a table named student.
Answer:
import [Link]
con = [Link](host='localhost', user='root', password='1234',
database='school')
cursor = [Link]()
query = 'INSERT INTO student VALUES(%s, %s, %s)'
data = (1, 'Ravi', 90)
[Link](query, data)
[Link]()
print('Record inserted')
[Link]()
2. Write a program to display all records of a table named employee.
Answer:
import [Link]
con = [Link](host='localhost', user='root', password='1234',
database='office')
cursor = [Link]()
[Link]('SELECT * FROM employee')
for row in [Link]():
print(row)
[Link]()
SECTION F: APPLICATION / CASE BASED QUESTIONS (6 Marks Each)
Case Study 1: A company database 'office' has a table 'employee(empid, name, salary)'.
Write a Python program to increase salary by 10% for employees earning less than 30000.
Answer:
import [Link]
con = [Link](host='localhost', user='root', password='1234',
database='office')
cursor = [Link]()
query = 'UPDATE employee SET salary = salary*1.1 WHERE salary < 30000'
[Link](query)
[Link]()
print([Link], 'records updated')
[Link]()
Case Study 2: A school database contains a table 'marks(rollno, name, marks)'. Write Python
code to display students scoring more than 75.
Answer:
import [Link]
con = [Link](host='localhost', user='root', password='1234',
database='school')
cursor = [Link]()
[Link]('SELECT * FROM marks WHERE marks > 75')
for row in [Link]():
print(row)
[Link]()