1. Explain the following 'results' retrieval methods with examples.

   (a) fetchone()
   (b) rowcount
   (c) fetchall()

(a) fetchone() — It fetches one row from the result set in the form of a tuple or a list. This function will return one record from the result set, i.e., first time it will return the first record, next time it will return the second record and so on. If no more record is left in the table, it will return None.

Example:

Let us consider the table student:

import mysql.connector
mydb = mysql.connector.connect(host = "localhost", user = "root", passwd = "tiger", database = "School")
mycursor = mydb.cursor()
mycursor.execute("select * from student")
myrecords = mycursor.fetchone()
print(myrecords)

Output

(10, 7, 'Sameer', 'Cricket', 'B', 'Photography', 'A')

(b) rowcount — This is read-only attribute and returns the number of rows that were affected by an execute() method and retrieved from the cursor.

Example:

import mysql.connector
mydb = mysql.connector.connect(host = "localhost", user = "root", passwd = "tiger", database = "School")
mycursor = mydb.cursor()
mycursor.execute("select * from student")
myrecords = mycursor.fetchone()
affected_rows = mycursor.rowcount
print("Number of affected rows: ", affected_rows)

Output

Number of affected rows:  1

(c) fetchall() — It fetches all the rows in a result set and returns a list of tuples. If some rows have already been extracted from the result set, then it retrieves the remaining rows from the result set. If no more rows are available, it returns an empty list.

Example:

import mysql.connector
mydb = mysql.connector.connect(host = "localhost", user = "root", passwd = "tiger", database = "School")
mycursor = mydb.cursor()
mycursor.execute("select * from student")
myrecords = mycursor.fetchall()
for x in myrecords:
    print(x)

Output

(11, 8, 'Sujit', 'Tennis', 'A', 'Gardening', 'C')
(12, 7, 'Kamal', 'Swimming', 'B', 'Photography', 'B')
(13, 7, 'Veena', 'Tennis', 'C', 'Cooking', 'A')
(14, 9, 'Archana', 'Basket Ball', 'A', 'Literature', 'A')
(15, 10, 'Arpit', 'Cricket', 'A', 'Gardening', 'C')