Interface Python with MySQL
How is database connectivity useful ?
Answer
When designing real-life applications, it's common to encounter scenarios where data stored in a database needs
to be manipulated, retrieved, or updated through the application's interface. Database connectivity allows the
application to establish a connection with the database, enabling seamless communication and interaction
between the two.
Question 2
What is a connection ?
Answer
A connection (database connection object) controls the connection to the database. It represents a unique
session with a database connected from within a script/program.
Question 3
What is a result set ?
Answer
The result set refers to a logical set of records that are fetched from the database by executing an SQL query
and made available to the application program.
Question 4
What is the package used for creating a Python database connectivity application.
Answer
[Link] is the package used for creating a Python database connectivity application.
Question 5
Which function/method do you use for establishing connection to database ?
Answer
The connect() function of [Link] is used for establishing connection to a MYSQL database.
Question 6
Which function/method do you use for executing an SQL query ?
Answer
The execute() function with cursor object is used for executing an SQL query.
Question 7
Which method do you use to fetch records from the result set ?
Answer
The fetchall() method, fetchmany() method, or fetchone() method can be used to fetch records from the result
set.
Multiple Choice Questions
Question 1
In order to open a connection with MySQL database from within Python using [Link]
package, ............... function is used.
1. open()
2. database()
3. connect()
4. connectdb()
Answer
connect()
Reason — The connect() function of [Link] is used for establishing connection to a MYSQL
database.
Question 2
A database ............... controls the connection to an actual database, established from within a Python program.
1. database object
2. connection object
3. fetch object
4. query object
Answer
connection object
Reason — A database connection object controls the connection to the database. It represents a unique session
with a database connected from within a script/program.
Question 3
The set of records retrieved after executing an SQL query over an established database connection is
called ............... .
1. table
2. sqlresult
3. result
4. resultset
Answer
resultset
Reason — The result set refers to a logical set of records that are fetched from the database by executing an
SQL query and made available to the application program.
Question 4
A database ............... is a special control structure that facilitates the row by row processing of records in the
resultset.
1. fetch
2. table
3. cursor
4. query
Answer
cursor
Reason — A database cursor is a special control structure that facilitates the row by row processing of records
in the resultset, i.e., the set of records retrieved as per query.
Question 5
Which of the following is not a legal method for fetching records from database from within Python?
1. fetchone()
2. fetchtwo()
3. fetchall()
4. fetchmany()
Answer
fetchtwo()
Reason — The fetchall() method, fetchmany() method, or fetchone() method are the legal methods used for
fetching records from the result set.
Question 6
To obtain all the records retrieved, you may use <cursor>. ............... method.
1. fetch()
2. fetchmany()
3. fetchall()
4. fetchmultiple()
Answer
fetchall()
Reason — The <cursor>.fetchall() method will return all the rows from the resultset in the form of a tuple
containing the records.
Question 7
To fetch one record from the resultset, you may use <cursor>. ............... method.
1. fetch()
2. fetchone()
3. fetchtuple()
4. none of these
Answer
fetchone()
Reason — The <cursor>.fetchone() method will return only one row from the resultset in the form of a
tuple containing a record.
Question 8
To fetch multiple records from the resultset, you may use <cursor>. ............... method.
1. fetch()
2. fetchmany()
3. fetchmultiple()
4. fetchmore()
Answer
fetchmany()
Reason — The <cursor>.fetchmany(<n>) method will return only the <n> number of rows from the resultset
in the form of a tuple containing the records.
Question 9
To run an SQL query from within Python, you may use <cursor>. ............... method().
1. query()
2. execute()
3. run()
4. all of these
Answer
execute()
Reason — The <cursor>.execute() method is used to run an SQL query from within Python.
Question 10
To reflect the changes made in the database permanently, you need to run <connection>. ............... method.
1. done()
2. reflect()
3. commit()
4. final()
Answer
commit()
Reason — The <connection>.commit() method is used to permanently reflect the changes made in the
database when working with database connections in Python.
Fill in the Blanks
Question 1
A database connection object controls the connection to the database. It represents a unique session with a
database connected from within a script/program.
Question 2
A database cursor is a special control structure that facilitates the row by row processing of records in the
resultset, i.e., the set of records retrieved as per query.
Question 3
The resultset refers to a logical set of records that are fetched from the database by executing an SQL query and
made available to the application program.
Question 4
After importing [Link], first of all database connection is established using connect().
Question 5
After establishing database connection, database cursor is created so that the sql query may be executed
through it to obtain resultset.
Question 6
The [Link] returns how many rows have been fetched so far using various fetch methods.
Question 7
The running of sql query through database cursor results into all the records returned in the form of resultset.
Question 8
A connectivity package such as [Link] must be imported before writing database connectivity Python
code.
Question 9
connect() method establishes a database connection from within Python.
Question 10
cursor() method creates a cursor from within Python.
Question 11
execute() method executes a database query from within Python.
True/False Questions
Question 1
With creation of a database connection object from within a Python program, a unique session with database
starts.
Answer
True
Reason — A database connection object controls the connection to the database, representing a unique session
initiated from within a script or program.
Question 2
The sql query upon execution via established database connection returns the result in multiple chunks.
Answer
False
Reason — When an SQL query is executed via an established database connection, the result is returned as a
single result set. The result set may contain multiple rows of data, but it is presented as a single unit rather than
in multiple chunks.
Question 3
The [Link] gives the count of records in the resultset.
Answer
False
Reason — The [Link] returns how many rows have been so far retrieved through fetch...() methods
from the cursor.
Question 4
The [Link] returns how many rows have been so far retrieved through fetch..() methods from the
cursor.
Answer
True
Reason — The [Link] returns how many rows have been so far retrieved through fetch...() methods
from the cursor.
Question 5
A DELETE or UPDATE or INSERT query requires commit() to reflect the changes in the database.
Answer
True
Reason — We need to run commit() with the connection object for DELETE, UPDATE, or INSERT queries
that change the data of the database table, so that the changes are reflected in the database.
Assertions and Reasons
Question 1
Assertion. A database connection object controls the connection to a database.
Reason. A connection object represents a unique session with a database, connected from within a
script/program.
Answer
(a)
Both Assertion and Reason are true and Reason is the correct explanation of Assertion.
Explanation
A database connection object controls the connection to the database, ensuring that the script or program can
communicate effectively with the database. This connection object represents a unique session with a database
connected from within a script/program.
Question 2
Assertion. A database cursor receives all the records retrieved as per the query.
Reason. A resultset refers to the records in the database cursor and allows processing of individual records in it.
Answer
(a)
Both Assertion and Reason are true and Reason is the correct explanation of Assertion.
Explanation
A database cursor is a special control structure that facilitates the row-by-row processing of records in the result
set, which is the set of records retrieved as per the query. On the other hand, the result set refers to a logical set
of records fetched from the database by executing an SQL query. The database cursor facilitates the processing
of these records by allowing access to them individually.
Question 3
Assertion. The database cursor and resultset have the same data yet they are different.
Reason. The database cursor is a control structure and the resultset is a logical set of records.
Answer
(a)
Both Assertion and Reason are true and Reason is the correct explanation of Assertion.
Explanation
The database cursor and result set both have data from the database but serve different purposes and are distinct
entities. A database cursor is a special control structure that facilitates the row-by-row processing of records in
the result set, i.e., the set of records retrieved as per the query. On the other hand, the result set refers to a
logical set of records that are fetched from the database by executing an SQL query and made available to the
application program.
Question 4
Assertion. One by one the records can be fetched from the database directly through the database connection.
Reason. The database query results into a set of records known as the resultset.
Answer
(d)
Assertion is false but Reason is true.
Explanation
Records can be fetched from the database using a database connection. To fetch multiple records from the result
set, we use the .fetchmany() method. To fetch one record from the result set, we use the .fetchone() method. To
fetch all the records, we use the .fetchall() method. The result set refers to a logical set of records fetched from
the database by executing an SQL query and made available to the application program.
Question 5
Assertion. The cursor rowcount returns how many rows have been retrieved so far through fetch...() methods.
Reason. The number of rows in a resultset and the rowcount are always equal.
Answer
(c)
Assertion is true but Reason is false.
Explanation
The [Link] returns how many rows have been so far retrieved through fetch...() methods from the
cursor. However, the number of rows in a result set and the rowcount may not always be equal. This is because
the rowcount attribute of the cursor only reflects the number of rows fetched by the fetch...() methods, not
necessarily the total number of rows in the entire result set.
Type A: Short Answer Questions/Conceptual Questions
Question 1
What are the steps to connect to a database from within a Python application ?
Answer
The steps to connect to a database from within a Python application are as follows :
Step 1 : Start Python.
Step 2 : Import the packages required for database programming.
Step 3 : Open a connection.
Step 4 : Create a cursor instance.
Step 5 : Execute a query.
Step 6 : Extract data from result set.
Step 7 : Clean up the environment.
Question 2
Write code to connect to a MySQL database namely School and then fetch all those records from
table Student where grade is ' A' .
Answer
Table Student of MySQL database School
rollno name marks grade section project
101 RUHANII 76.8 A A PENDING
102 GEOGRE 71.2 B A SUBMITTED
103 SIMRAN 81.2 A B EVALUATED
104 ALI 61.2 B C ASSIGNED
105 KUSHAL 51.6 C C EVALUATED
106 ARSIYA 91.6 A+ B SUBMITTED
107 RAUNAK 32.5 F B SUBMITTED
import [Link] as mysql
db_con = [Link](
host = "localhost",
user = "root",
password = "tiger",
database = "School"
)
cursor = db_con.cursor()
[Link]("SELECT * FROM Student WHERE grade = 'A'")
student_records = [Link]()
for student in student_records:
print(student)
db_con.close()
Output
(101, 'RUHANII', 76.8, 'A', 'A', 'PENDING')
(103, 'SIMRAN', 81.2, 'A', 'B', 'EVALUATED')
Question 3
Predict the output of the following code :
import [Link]
db = [Link](....)
cursor = [Link]()
sql1 = "update category set name = '%s' WHERE ID = %s" % ('CSS',2)
[Link](sql1)
[Link]()
print("Rows affected:", [Link])
[Link]()
Answer
Table category
id name
1 abc
2 pqr
3 xyz
Output
Rows affected: 1
SELECT * FROM category ;
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | CSS |
| 3 | xyz |
+----+------+
Explanation
This Python script uses the [Link] module to connect to MySQL database. It updates the 'name'
field in the 'category' table where ID is 2 to 'CSS'. The [Link]() method executes the SQL
query, [Link]() commits the changes, and [Link] gives the number of affected rows.
Finally, [Link]() closes the database connection, ending the Python interface with the MySQL database.
Question 4
Explain what the following query will do ?
import [Link]
db = [Link](....)
cursor = [Link]()
person_id = input("Enter required person id")
lastname = input("Enter required lastname")
[Link]("INSERT INTO staff (person_id, lastname) VALUES ({},
'{}')".format(person_id, lastname))
[Link]()
[Link]()
Answer
This Python script uses the [Link] package to connect to MySQL database. Then it prompts users
for person ID and last name, inserts these values into the 'staff' table, using the INSERT INTO SQL statement.
After that, it executes the SQL query using the [Link] method. The changes made by the query are then
committed to the database using [Link](), ensuring that the changes are saved permanently.
Finally, [Link]() closes the database connection, ending the Python interface with the MySQL database.
Question 5
Explain what the following query will do ?
import [Link]
db = [Link](....)
cursor = [Link]()
[Link]("SELECT * FROM staff WHERE person_id in {}".format((1, 3, 4)))
[Link]()
[Link]()
Answer
This Python script uses the [Link] package to connect to MySQL database. It executes an SQL
SELECT query on the 'staff' table, retrieving all rows where the 'person_id' is 1, 3, 4 (using the IN clause).
The [Link]() is unnecessary for a SELECT query since it doesn't modify the database,
and [Link]() closes the database connection, ending the Python interface with the MySQL database.
Type B: Application Based Questions
Question 1
Design a Python application that fetches all the records from Pet table of menagerie database.
Answer
import [Link]
db_con = [Link](host = "localhost",
user = "root",
passwd = "lion",
database = "menagerie")
cursor = db_con.cursor()
[Link]("SELECT * FROM Pet")
records = [Link]()
for record in records:
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)
Question 2
Design a Python application that fetches only those records from Event table of menagerie database where
type is Kennel.
Answer
import [Link]
db_con = [Link](host = "localhost",
user = "root",
passwd = "lion",
database = "menagerie")
cursor = db_con.cursor()
[Link]("SELECT * FROM event WHERE type = 'kennel'")
records = [Link]()
for record in records:
print(record)
db_con.close()
Output
('Bowser', [Link](1991, 10, 12), 'kennel', None)
('Fang', [Link](1991, 10, 12), 'kennel', None)
Question 3
Schema of table EMPL is shown below :
EMPL (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Design a Python application to obtain a search criteria from user and then fetch records based on that
from empl table. (given in chapter 13, Table 13.5)
Answer
Table Empl
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
8369 SMITH CLERK 8902 1990-12-18 800 NULL 20
8499 ANYA SALESMAN 8698 1991-02-20 1600 300 30
8521 SETH SALESMAN 8698 1991-02-22 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
8566 MAHADEVAN MANAGER 8839 1991-04-02 2985 NULL 20
8654 MOMIN SALESMAN 8698 1991-09-28 1250 1400 30
8698 BINA MANAGER 8839 1991-05-01 2850 NULL 30
8839 AMIR PRESIDENT NULL 1991-11-18 5000 NULL 10
8844 KULDEEP SALESMAN 8698 1991-09-08 1500 0 30
8882 SHIAVNSH MANAGER 8839 1991-06-09 2450 NULL 10
8886 ANOOP CLERK 8888 1993-01-12 1100 NULL 20
8888 SCOTT ANALYST 8566 1992-12-09 3000 NULL 20
8900 JATIN CLERK 8698 1991-12-03 950 NULL 30
8902 FAKIR ANALYST 8566 1991-12-03 3000 NULL 20
8934 MITA CLERK 8882 1992-01-23 1300 NULL 10
import [Link]
db_con = [Link](host = "localhost",
user = "root",
passwd = "fast",
database = "employeedb")
cursor = db_con.cursor()
search_criteria = input("Enter search criteria : ")
sql1 = "SELECT * FROM EMPL WHERE {}".format(search_criteria)
[Link](sql1)
records = [Link]()
print("Fetched records:")
for record in records:
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)