Fetching data from the table

☞ We will perform the fetch query on the table given below.
We will use the following in this section.

1. rowcount
   • It returns the number of rows that were affected by an execute() and retrieved from the cursor.
2. fetchone( )
   • It fetches one row from the result set.
   • It fetches a row in the form of a tuple or list.
   • It returns the first record for the first time, next time it returns the second record and so on.
   • If no record is left in the table, it will return None.

   ☞ Example :
   

   import mysql.connector as m
   mydb = m.connect(host='localhost', user='root', password='in$pireweb$oft', database='school')
   cur = mydb.cursor()
   
   a = float(input("Enter fees : "))
   b = input("Enter gender[M/F] : ")
   s="SELECT * FROM student WHERE fees <= '{}' or gender = '{}' ".format(a,b)
   cur.execute(s)
                   OR
   s="SELECT * FROM student WHERE fees <= %s or gender = %s "
   rec=(a,b)
   cur.execute(s,rec)
   
   record = cur.fetchone()         #returns one record at a time
   no_rec = cur.rowcount                 #returns no. of record
   print(record)
   print("No. of records = ",no_rec)
   

   ☞ Output :
   

   Enter fees : 1550
   Enter gender[M/F] : M
   (1, 'aruna', datetime.date(2010, 5, 21), 'M', Decimal('1500.52'))
   No. of records = 1
   
3. fetchall()
   • It fetches all the rows in a result set.
   • It returns a list of tuples.
   • If some rows have already been fetched from the result set, then it retrieves the remaining rows.
   • If no row is left, it returns an empty list.

   ☞ Example :
   

   import mysql.connector as m
   mydb = m.connect(host='localhost', user='root', password='in$pireweb$oft', database='school')
   cur = mydb.cursor()
   
   a = float(input("Enter fees : "))
   b = input("Enter gender[M/F] : ")
   
   s="SELECT * FROM student WHERE fees <= '{}' or gender = '{}' ".format(a,b)
   cur.execute(s)
                    OR
   s="SELECT * FROM student WHERE fees <= %s or gender = %s "
   rec=(a,b)
   cur.execute(s,rec)
   
   record = cur.fetchall()                     #list of records
   no_rec = cur.rowcount
   for i in record:
       print(i)
   print("No. of records = ",no_rec)
   

   ☞ Output :
   

   Enter fees : 1550
   Enter gender[M/F] : M
   (1, 'aruna', datetime.date(2010, 5, 21), 'M', Decimal('1500.52'))
   (4, 'Raman', datetime.date(2008, 6, 22), 'M', Decimal('1400.50'))
   (5, 'Mohit', datetime.date(2009, 9, 18), 'M', Decimal('1400.50'))
   (6, 'Ananya', datetime.date(2010, 10, 20), 'F', Decimal('1500.25'))
   (7, 'Aman', None, 'M', None)
   No. of records = 5
   
4. fetchmany(size)
   • It fetches the specified number of rows (as a list of tuples) from the result set.
   • Default size is 1
   • If no rows in the result set,then returns an empty list.

   ☞ Example :
   

   import mysql.connector as m
   mydb = m.connect(host='localhost', user='root', password='in$pireweb$oft', database='school')
   cur = mydb.cursor()
   
   a = float(input("Enter fees : "))
   b = input("Enter gender[M/F] : ")
   
   s="SELECT * FROM student WHERE fees <= '{}' or gender = '{}' ".format(a,b)
   cur.execute(s)
                     OR
   s="SELECT * FROM student WHERE fees <= %s or gender = %s "
   rec=(a,b)
   cur.execute(s,rec)
   
   record = cur.fetchmany(2)           #list of records
   no_rec = cur.rowcount
   for i in record:
       print(i)
   print("No. of records = ",no_rec)
   

   ☞ Output :
   

   Enter fees : 1550
   Enter gender[M/F] : M
   (1, 'aruna', datetime.date(2010, 5, 21), 'M', Decimal('1500.52'))
   (4, 'Raman', datetime.date(2008, 6, 22), 'M', Decimal('1400.50'))
   No. of records = 2