Python MySQL - Limit Clause

The Limit clause is used in SQL to control or limit the number of records in the result set returned from the query generated. By default, SQL gives out the required number of records starting from the top but it allows the use of OFFSET keyword. OFFSET allows you to start from a custom row and get the required number of result rows. 

OFFSET keyword can also be used along with LIMIT to start retrieving records from a custom row number. This is especially useful when working with pagination or when you want to skip a specific number of rows before fetching results.

Syntax

SELECT * FROM tablename LIMIT limit;

• limit: Specifies the maximum number of rows to return.

You can also use the OFFSET keyword:

SELECT * FROM tablename LIMIT limit OFFSET offset;

• offset: Specifies the row to start fetching the records from.

DATABASE IN USE

Example 1: Display Only 2 Records

In this example, we will fetch and display only the first 2 records from the STUDENT table.

import mysql.connector

# Connecting to the database
mydb = mysql.connector.connect(
  host='localhost',
  database='College',
  user='root', 
  password='your_password'  # replace with your MySQL password
)

cs = mydb.cursor()

# Fetch first 2 records from the STUDENT table
statement = "SELECT * FROM STUDENT LIMIT 2"
cs.execute(statement)

# Fetch all the result rows
result_set = cs.fetchall()

# Display the result
for x in result_set:
    print(x)

# Disconnecting from the database
mydb.close()

Output:

Example 2:

Program to start from the second record and display the first two records 

import mysql.connector
 
# Connecting to the database
mydb = mysql.connector.connect(
  host ='localhost',
  database ='College',
  user ='root',
)
 
cs = mydb.cursor()

statement ="SELECT * FROM STUDENT LIMIT 2 OFFSET 1"

cs.execute(statement)
result_set = cs.fetchall()

for x in result_set:
    print(x)

Output:

Explanation:

• LIMIT 2 specifies we want 2 records.
• OFFSET 1 means we want to skip the first record and start from the second one.