Python MySQL LIMIT Clause Explained with Examples

When working with databases in Python using MySQL, you often deal with large datasets. But what if you only need a few records instead of loading thousands at once? That's where the LIMIT clause comes into play.

In this article, we’ll understand how to use the LIMIT clause in MySQL through Python to fetch a specific number of records efficiently.

What is the LIMIT Clause?

The LIMIT clause in SQL is used to specify the number of records to return. It’s especially useful when:

• You want to show a fixed number of rows on a webpage (e.g., blog posts, products).
• You’re implementing pagination.
• You want to test queries on a small sample of data.

Syntax of LIMIT Clause

SELECT column_name FROM table_name LIMIT number;

You can also use it with OFFSET:

SELECT column_name FROM table_name LIMIT offset, count;

Using LIMIT with Python and MySQL

Here’s how you can use the LIMIT clause in Python using mysql-connector-python.

Example Code:

import mysql.connector

# Connect to MySQL Database
conn = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

cursor = conn.cursor()

# Fetch only 5 records
query = "SELECT name, email FROM users LIMIT 5"
cursor.execute(query)

results = cursor.fetchall()

for row in results:
    print(row)

cursor.close()
conn.close()

LIMIT with OFFSET (Pagination)

Let’s say you want to implement pagination on your website where each page shows 10 records:

page = 2
per_page = 10
offset = (page - 1) * per_page

query = f"SELECT name, email FROM users LIMIT {offset}, {per_page}"
cursor.execute(query)

This will fetch records 11 to 20 from the users table.

Things to Remember

• LIMIT helps reduce memory usage by loading only necessary data.
• Always validate user input if you're using dynamic LIMIT values to prevent SQL injection.
• Combine LIMIT with ORDER BY for consistent results.

Final Thoughts

The LIMIT clause is a powerful tool for managing data loads in your application. Whether you're creating a blog, an e-commerce site, or a dashboard, understanding how to use LIMIT with Python and MySQL will keep your app fast and efficient.