Python MySQL LIMIT Clause with Examples

The LIMIT clause controls how many rows a SELECT query returns. When you are working with large tables — thousands or millions of rows — fetching every record at once wastes memory and slows your application. LIMIT lets you retrieve only the records you actually need.

This chapter shows how to use LIMIT in Python with the mysql-connector-python package, including pagination with OFFSET, combining LIMIT with ORDER BY, and best practices for error handling.

Prerequisites

Make sure the MySQL connector is installed before running any of the examples:

pip install mysql-connector-python

All examples assume a customers table exists in a database called mydatabase. You can create one by following the Python MySQL Create Table chapter.

How the LIMIT clause works

The basic syntax restricts the result set to a fixed number of rows:

SELECT * FROM table_name LIMIT n;

To skip a number of rows before starting the result set, add OFFSET:

SELECT * FROM table_name LIMIT n OFFSET m;

OFFSET m means "skip the first m rows". This is the foundation of page-by-page navigation through large datasets.

Fetching a fixed number of rows

The example below connects to a MySQL database and retrieves only the first five customers:

Retrieve the first 5 rows with LIMIT

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )

    mycursor = mydb.cursor()
    mycursor.execute("SELECT * FROM customers LIMIT 5")

    results = mycursor.fetchall()

    for row in results:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

The fetchall() method returns a list of tuples — one tuple per row. Because the query already limits results to five rows, the list will contain at most five items regardless of the total number of rows in the table.

Using LIMIT with OFFSET for pagination

OFFSET shifts the starting row of the result. Paired with LIMIT, it lets you implement pagination: page 1 shows rows 1–10, page 2 shows rows 11–20, and so on.

The formula is: OFFSET = (page_number - 1) * page_size.

Retrieve page 2 of results (rows 11–20)

import mysql.connector
from mysql.connector import Error

page_number = 2
page_size = 10
offset = (page_number - 1) * page_size  # evaluates to 10

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )

    mycursor = mydb.cursor()

    sql = "SELECT * FROM customers LIMIT %s OFFSET %s"
    mycursor.execute(sql, (page_size, offset))

    results = mycursor.fetchall()

    for row in results:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

The %s placeholders are filled in by the connector driver, which safely handles the values and prevents SQL injection. Never build the LIMIT or OFFSET values by concatenating strings directly into the query.

Combining LIMIT with ORDER BY

Without ORDER BY, the database can return rows in any order. When you use LIMIT you almost always want a predictable order — otherwise page 1 and page 2 might contain overlapping or arbitrary rows.

Retrieve the 5 most recently added customers

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )

    mycursor = mydb.cursor()

    # Sort by id descending so the newest rows come first, then take 5
    mycursor.execute("SELECT * FROM customers ORDER BY id DESC LIMIT 5")

    results = mycursor.fetchall()

    for row in results:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

In MySQL the evaluation order is FROM → WHERE → ORDER BY → LIMIT, so LIMIT always operates on the sorted result set.

Fetching a single row with fetchone()

When you know you only need one row — such as looking up a record by primary key — you can limit the query to one row and use fetchone() instead of fetchall(). This avoids allocating a list for a result you will never use:

Retrieve a single row

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )

    mycursor = mydb.cursor()
    mycursor.execute("SELECT * FROM customers LIMIT 1")

    row = mycursor.fetchone()

    if row:
        print(row)
    else:
        print("No records found.")

except Error as e:
    print(f"Error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

fetchone() returns a single tuple or None if the result set is empty.

Building a reusable pagination function

For real applications it is convenient to wrap the pagination logic in a function so the calling code stays clean:

Reusable paginate() helper

import mysql.connector
from mysql.connector import Error

def paginate(cursor, table, page, page_size=10):
    """Return one page of rows from *table*.

    Args:
        cursor:    An active mysql.connector cursor.
        table:     Name of the table to query (string).
        page:      1-based page number.
        page_size: Number of rows per page (default 10).

    Returns:
        A list of row tuples, or an empty list when no rows remain.
    """
    offset = (page - 1) * page_size
    # Table names cannot be parameterised, so validate the name first.
    if not table.isidentifier():
        raise ValueError(f"Invalid table name: {table!r}")
    sql = f"SELECT * FROM `{table}` LIMIT %s OFFSET %s"
    cursor.execute(sql, (page_size, offset))
    return cursor.fetchall()


try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    # Print the first three pages
    for page in range(1, 4):
        rows = paginate(mycursor, "customers", page=page, page_size=5)
        if not rows:
            print(f"Page {page}: no more rows.")
            break
        print(f"--- Page {page} ---")
        for row in rows:
            print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

Note that table names cannot be passed as %s parameters — the connector does not support parameterising identifiers. The isidentifier() check guards against injection through the table name argument.

Common gotchas

LIMIT without ORDER BY gives unpredictable results. MySQL does not guarantee a stable row order unless you specify ORDER BY. Two identical queries might return different rows if the table is modified between calls.

Large OFFSET values are slow. LIMIT 10 OFFSET 1000000 still makes MySQL scan and discard one million rows before returning ten. For deep pagination on very large tables, consider keyset pagination (also called cursor-based pagination): record the last id seen on the previous page and use WHERE id > last_id LIMIT 10.

Do not build LIMIT values by string concatenation. Use %s placeholders or, if the value is computed in Python, ensure it is a plain integer before embedding it in the SQL string.

Summary

Goal	SQL pattern
First N rows	`SELECT ... LIMIT N`
Skip M rows, take N	`SELECT ... LIMIT N OFFSET M`
Page P of size N	`LIMIT N OFFSET (P-1)*N`
One row only	`SELECT ... LIMIT 1` + `fetchone()`
Ordered first N	`SELECT ... ORDER BY col LIMIT N`

Python MySQL Select — querying rows from a table
Python MySQL Where — filtering results with conditions
Python MySQL Order By — sorting result sets
Python MySQL Join — combining multiple tables