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 a 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 to Select Specific Records

• 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

Sample Data

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: Selecting Records with LIMIT and OFFSET

Let's select 3 rows starting from the 2^nd row (OFFSET 1) ?

import mysql.connector

# Establish connection
db = mysql.connector.connect(
    host="localhost",
    user="your_username", 
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# Query to select 3 rows starting from 2nd row
query = "SELECT * FROM Students LIMIT 3 OFFSET 1"
cursor.execute(query)

# Fetch and display results
for row in cursor:
    print(row)

# Close connection
db.close()

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

Output

('Sahil', 6, 'Amritsar', 93)
('Kriti', 3, 'Batala', 88)
('Khushi', 9, 'Delhi', 90)

Alternative: Using fetchmany()

You can also use fetchmany() to limit results without OFFSET ?

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username", 
    password="your_password",
    database="your_database"
)

cursor = db.cursor()
cursor.execute("SELECT * FROM Students")

# Skip first row and fetch next 3
cursor.fetchone()  # Skip first row
results = cursor.fetchmany(3)  # Fetch next 3 rows

for row in results:
    print(row)

db.close()

Key Points

• OFFSET is zero-indexed (OFFSET 0 = first row, OFFSET 1 = second row)

• LIMIT specifies the maximum number of rows to return

• Always close database connections to free resources

• Use proper error handling in production code

Conclusion

Use LIMIT and OFFSET clauses to retrieve specific records from MySQL tables in Python. OFFSET defines the starting position while LIMIT controls the number of rows returned. This approach is essential for pagination and efficient data retrieval.