How can you retrieve a particular number of records from a table starting from some specified row number in Python MySQL?

Most frequently, we do not require to select all the rows from the table. We may at times need to retrieve a particular number of records from a table, starting from some specific index. Suppose, we have a table of 10 records. We need to select 5 rows from the table starting from 3^rd row.

This is done using the LIMIT and OFFSET clause along with the SELECT statement. The LIMIT is used to specify the number of rows that you want to retrieve. The OFFSET is used to specify the starting position from where the rows are to be fetched. If you want to fetch data starting from the 3^rd row, the OFFSET must be 2.

Syntax

SELECT * FROM table_name LIMIT no_of_rows OFFSET starting index of row

Steps invloved to select specific records from a table using MySQL in python

• import MySQL connector

• establish connection with the connector using connect()

• create the cursor object using cursor() method

• create a query using the appropriate mysql statements

• execute the SQL query using execute() method

• close the connection

Suppose we have the following table named “Students” −

+----------+---------+-----------+------------+
|    Name  |   Class |    City   |    Marks   |
+----------+---------+-----------+------------+
|    Karan |    4    | Amritsar  |    95      |
|    Sahil |    6    | Amritsar  |    93      |
|    Kriti |    3    | Batala    |    88      |
|   Khushi |    9    | Delhi     |    90      |
|    Kirat |    5    | Delhi     |    85      |
+----------+---------+-----------+------------+

Example

Suppose we want to select 3 rows starting from the 2^ndrow.

import mysql.connector

db=mysql.connector.connect(host="your host", user="your username", password="your
password",database="database_name")

cursor=db.cursor()

query="SELECT * FROM Students LIMIT 3 OFFSET 1"
cursor.execute(query)

for row in cursor:
   print(row)
db.close()

The above code fetches and prints 3 records starting from 2^nd row.

Output

(‘Sahil’ , 6 , ‘Amritsar’ ,93)
(‘Kriti’ , 3 , ‘Batala’ ,88)
(‘Amit’ , 9 , ‘Delhi’ , 90)