← Back to Python Course | Chapter 15: Python & MySQL | Lesson 11 of 12

Python MySQL Limit

A LIMIT clause caps how many rows a SELECT statement returns -- essential for pagination, previewing a large table, or simply fetching just the top few results (often combined with ORDER BY to get, say, the 5 most recent or highest-value rows).

In this page:

  1. Basic LIMIT Usage
  2. Getting the "Top N" Results
  3. Skipping Rows with OFFSET
  4. Building a Reusable Pagination Function
  5. LIMIT Without OFFSET vs a Full Fetch
Syntax
python 
cursor.execute("SELECT * FROM table_name LIMIT number")
cursor.execute("SELECT * FROM table_name LIMIT number OFFSET start")

Basic LIMIT Usage

cursor.execute("SELECT * FROM customers LIMIT 5") returns at most the first 5 rows the query would otherwise return, regardless of how many total rows actually match -- useful for quickly previewing a large table without pulling every row.

Note: Add LIMIT whenever previewing or sampling a large table during development, to avoid accidentally pulling thousands of rows into memory or the terminal.
Warning: Without an accompanying ORDER BY, which specific rows LIMIT returns is not guaranteed to be meaningful or consistent between runs.

Example: Basic LIMIT Usage

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers LIMIT 5")  # caps the result at 5 rows
print("Returns at most 5 rows")

Getting the "Top N" Results

Combining ORDER BY ... DESC with LIMIT n is the standard pattern for retrieving the top N results by some measure -- for example, the 5 most expensive products, or the 10 most recent orders.

Note: Always pair LIMIT with an explicit ORDER BY when the goal is genuinely "the top N by some criteria" -- LIMIT alone does not guarantee any particular ordering.
Warning: Ties at the boundary of the LIMIT (multiple rows with the identical sort value at the cutoff point) can make which specific row appears last somewhat arbitrary unless a tie-breaking column is added to ORDER BY.

Example: Getting the "Top N" Results

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM products ORDER BY price DESC LIMIT 5")  # highest prices first, capped at 5
print("The 5 most expensive products")

Skipping Rows with OFFSET

LIMIT n OFFSET m skips the first m matching rows entirely, then returns up to n rows starting after that point -- the fundamental mechanism behind paginated results, like "page 2" of a search results list.

Note: Calculate OFFSET as (page_number - 1) * page_size when implementing pagination, a common formula worth memorizing.
Warning: A very large OFFSET value on a huge table can be noticeably slow, since MySQL still internally processes and discards every skipped row before reaching the ones actually returned.

Example: Skipping Rows with OFFSET

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers ORDER BY id LIMIT 10 OFFSET 10")  # skips the first 10, then takes 10 more
print("Page 2 of results, 10 per page")

Building a Reusable Pagination Function

Wrapping the LIMIT/OFFSET pattern in a small Python function that accepts a page number and page size makes pagination logic reusable and consistent across every place in an application that needs to page through results.

Note: Centralize pagination logic in one reusable function rather than recalculating LIMIT and OFFSET by hand at every call site, reducing the chance of an off-by-one page-size bug.
Warning: Always validate that page_number is at least 1 before computing OFFSET -- a page_number of 0 or negative produces a nonsensical or invalid negative OFFSET.

Example: Building a Reusable Pagination Function

python 
def paginate(page_number, page_size=10):
    offset = (page_number - 1) * page_size  # rows to skip for this page
    return f"LIMIT {page_size} OFFSET {offset}"

print(paginate(2))

LIMIT Without OFFSET vs a Full Fetch

Applying LIMIT at the SQL level (letting the database itself return only the needed rows) is far more efficient than fetching an entire table with fetchall() and then slicing the resulting Python list down to size, since the unwanted rows never even cross the network in the first place.

Note: Always push row-limiting logic into the SQL query itself with LIMIT, rather than fetching everything and slicing the result in Python afterward.
Warning: result = cursor.fetchall()[:5] still transfers and holds every single row in memory before discarding all but 5 -- a real, avoidable performance cost on a large table.

Example: LIMIT Without OFFSET vs a Full Fetch

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers LIMIT 5")  # efficient: filtered in SQL
print("Only 5 rows ever cross the network")
Related Topics

Python MySQL Update Table · Python MySQL Join · Python MySQL Drop Table · Python MySQL Delete Record

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. Assuming LIMIT alone gives a meaningful 'top N' result without an accompanying ORDER BY -- without sorting first, LIMIT simply returns an arbitrary N rows, not necessarily the ones intended.
  2. Miscalculating the OFFSET value when implementing pagination, causing rows to be skipped entirely or shown twice across pages.
  3. Using LIMIT with a very large OFFSET on a huge table, which can be surprisingly slow since MySQL still has to scan past all the skipped rows internally.
Chapter Summary
  • LIMIT n restricts a SELECT statement to returning at most n rows.
  • LIMIT n OFFSET m skips the first m rows, then returns up to n rows after that, enabling pagination.
  • LIMIT is almost always combined with ORDER BY to get a meaningful "top N" or "most recent N" result.
Browser Support

LIMIT (and OFFSET) syntax is standard MySQL, supported identically across all mysql-connector-python versions.

🔒

Chapter Quiz — Complete all 12 topics to unlock

0/12 topics done

Complete these topics first:

← Previous Lesson Python MySQL Update Table Next Lesson → Python MySQL Join

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.