When working with large datasets in MySQL, it's often unnecessary or inefficient to retrieve every row from a table. The LIMIT clause helps you control how many rows are returned. In this tutorial, you'll learn how to use the LIMIT clause in MySQL queries using Python.

Table of Contents

1. What is LIMIT in MySQL?

2. Why Use LIMIT?

3. Prerequisites

4. Installing MySQL Connector

5. Connecting to MySQL with Python

6. Using LIMIT in SELECT Queries

7. Using LIMIT with OFFSET

8. Full Working Example

9. Tips and Common Pitfalls

✅ 1. What is LIMIT in MySQL?

The LIMIT clause in SQL is used to restrict the number of rows returned by a query.

Syntax:

SELECT * FROM table_name LIMIT number;

Example:

SELECT * FROM users LIMIT 5;

This query fetches only the first 5 rows from the users table.

2. Why Use LIMIT?

• Improve performance when dealing with large datasets

• Paginate results (e.g., show 10 results per page)

• Debug queries by checking a small sample of data

• Prevent overloading APIs or applications with large result sets

⚙️ 3. Prerequisites

Make sure you have:

• Python installed

• MySQL Server running

• A MySQL database and table (e.g., users)

• The mysql-connector-python library

4. Installing MySQL Connector

Install the required MySQL connector library:

pip install mysql-connector-python

5. Connecting to MySQL with Python

import mysql.connector

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

cursor = db.cursor()

Replace credentials with your actual MySQL login details.

6. Using LIMIT in SELECT Queries

To fetch a limited number of rows:

sql = "SELECT * FROM users LIMIT 5"
cursor.execute(sql)

results = cursor.fetchall()

for row in results:
    print(row)

Explanation:

• LIMIT 5 restricts the result to 5 rows

• cursor.fetchall() retrieves all limited rows

↔️ 7. Using LIMIT with OFFSET (Pagination)

Use OFFSET to skip rows, ideal for pagination:

sql = "SELECT * FROM users LIMIT 5 OFFSET 10"
cursor.execute(sql)

results = cursor.fetchall()

for row in results:
    print(row)

This skips the first 10 rows and returns the next 5.

Dynamic Pagination Example:

def fetch_page(page_number, page_size):
    offset = (page_number - 1) * page_size
    sql = "SELECT * FROM users LIMIT %s OFFSET %s"
    val = (page_size, offset)

    cursor.execute(sql, val)
    return cursor.fetchall()

# Example: page 2 with 5 items per page
rows = fetch_page(2, 5)
for row in rows:
    print(row)

8. Full Working Example

import mysql.connector

def fetch_users(limit, offset=0):
    db = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="mydatabase"
    )

    cursor = db.cursor()
    
    sql = "SELECT * FROM users LIMIT %s OFFSET %s"
    val = (limit, offset)

    cursor.execute(sql, val)
    results = cursor.fetchall()

    db.close()
    return results

# Fetch 5 users starting from the 10th record
users = fetch_users(5, 10)
for user in users:
    print(user)

⚠️ 9. Tips and Common Pitfalls

✅ Best Practices

• Always use ORDER BY with LIMIT for predictable output

• Use pagination with LIMIT and OFFSET to manage large tables

• Use cursor.rowcount to check the number of rows returned if needed

• Avoid SELECT * in production; use specific column names

Summary Table

Final Thoughts

Using the LIMIT clause in Python MySQL queries is essential for optimizing data handling and building scalable applications. Whether you’re paginating a UI, debugging with sample data, or optimizing performance, mastering LIMIT gives you precise control over query results.