Python MySQL Tutorial – How to Use LIMIT to Control Query Results

Last updated 2 weeks, 1 day ago | 32 views 75 5

Python MySQL Tutorial – How to Use LIMIT to Control Query Results

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.

What is LIMIT in MySQL?
Why Use LIMIT?
Prerequisites
Installing MySQL Connector
Connecting to MySQL with Python
Using LIMIT in SELECT Queries
Using LIMIT with OFFSET
Full Working Example
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

Pitfall	Solution
Forgetting `OFFSET` when paginating	Use `OFFSET` to control where to start
Not using placeholders (`%s`)	Use parameterized queries to prevent SQL injection
Using `LIMIT` without sorting (`ORDER BY`)	Add `ORDER BY` for consistent results
Fetching too many rows	Start small (e.g., `LIMIT 10`) and increase only when necessary

✅ 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

SQL Task	Example
Limit rows	`SELECT * FROM users LIMIT 5`
Skip rows	`SELECT * FROM users LIMIT 5 OFFSET 10`
Order results	`SELECT * FROM users ORDER BY id DESC LIMIT 5`
Use placeholders	`"SELECT * FROM users LIMIT %s OFFSET %s"`

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.

Tips and Tricks

What is pass in Python?

Python | Pass Statement

The pass statement is used as a placeholder for future code. It represents a null operation in Python. It is generally used for the purpose of filling up empty blocks of code which may execute during runtime but has yet to be written.

def myfunction():
    pass

How can you generate random numbers?

Python | Generate random numbers

Python provides a module called random using which we can generate random numbers. e.g: print(random.random())

We have to import a random module and call the random() method as shown below:

 import random

 print(random.random())

The random() method generates float values lying between 0 and 1 randomly.

To generate customized random numbers between specified ranges, we can use the randrange() method
Syntax: randrange(beginning, end, step)

import random

print(random.randrange(5,100,2))

What is lambda in Python?

Python | Lambda function

A lambda function is a small anonymous function. This function can have any number of parameters but, can have just one statement.

Syntex:
lambda arguments : expression

a = lambda x,y : x+y

print(a(5, 6))

It also provides a nice way to write closures. With that power, you can do things like this.

def adder(x):
    return lambda y: x + y

add5 = adder(5)

add5(1)    #6

As you can see from the snippet of Python, the function adder takes in an argument x and returns an anonymous function, or lambda, that takes another argument y. That anonymous function allows you to create functions from functions. This is a simple example, but it should convey the power lambdas and closures have.

What is swapcase() function in the Python?

Python | swapcase() Function

It is a string's function that converts all uppercase characters into lowercase and vice versa. It automatically ignores all the non-alphabetic characters.

string = "IT IS IN LOWERCASE."  

print(string.swapcase())

How to remove whitespaces from a string in Python?

Python | strip() Function | Remove whitespaces from a string

To remove the whitespaces and trailing spaces from the string, Python provides a strip([str]) built-in function. This function returns a copy of the string after removing whitespaces if present. Otherwise returns the original string.

string = "  Python " 
 
print(string.strip())

What is the usage of enumerate() function in Python?

Python | enumerate() Function

The enumerate() function is used to iterate through the sequence and retrieve the index position and its corresponding value at the same time.

lst = ["A","B","C"] 
 
print (list(enumerate(lst)))

#[(0, 'A'), (1, 'B'), (2, 'C')]

Can you explain the filter(), map(), and reduce() functions?

Python | filter(), map(), and reduce() Functions

filter() function accepts two arguments, a function and an iterable, where each element of the iterable is filtered through the function to test if the item is accepted or not.
```
>>> set(filter(lambda x:x>4, range(7)))

# {5, 6}
```
map() function calls the specified function for each item of an iterable and returns a list of result
```
>>> set(map(lambda x:x**3, range(7)))

# {0, 1, 64, 8, 216, 27, 125}
```
reduce() function reduces a sequence pair-wise, repeatedly until we arrive at a single value..
```
>>> reduce(lambda x,y:y-x, [1,2,3,4,5])

# 3
```
Let’s understand this:

2-1=1
3-1=2
4-2=2
5-2=3

Hence, 3.

What is a namedtuple?

Python | namedtuple

A namedtuple will let us access a tuple’s elements using a name/label. We use the function namedtuple() for this, and import it from collections.

>>> from collections import namedtuple

#format
>>> result=namedtuple('result','Physics Chemistry Maths') 

#declaring the tuple
>>> Chris=result(Physics=86,Chemistry=92,Maths=80) 

>>> Chris.Chemistry
# 92

Write a code to add the values of same keys in two different dictionaries and return a new dictionary.

We can use the Counter method from the collections module

from collections import Counter

dict1 = {'a': 5, 'b': 3, 'c': 2}
dict2 = {'a': 2, 'b': 4, 'c': 3}

new_dict = Counter(dict1) + Counter(dict2)


print(new_dict)
# Print: Counter({'a': 7, 'b': 7, 'c': 5})

Python In-place swapping of two numbers

Python | In-place swapping of two numbers

>>> a, b = 10, 20
>>> print(a, b)
10 20

>>> a, b = b, a
>>> print(a, b)
20 10

Reversing a String in Python

Python | Reversing a String

>>> x = 'PythonWorld'
>>> print(x[: : -1])
dlroWnohtyP

Python join all items of a list to convert into a single string

Python | Join all items of a list to convert into a single string

>>> x = ["Python", "Online", "Training"]
>>> print(" ".join(x))
Python Online Training

python return multiple values from functions

Python | Return multiple values from functions

>>> def A():
	return 2, 3, 4

>>> a, b, c = A()

>>> print(a, b, c)
2 3 4

Python Print String N times

Python | Print String N times

>>> s = 'Python'
>>> n = 5

>>> print(s * n)
PythonPythonPythonPythonPython

Python check the memory usage of an object

Python | Check the memory usage of an object

>>> import sys
>>> x = 100

>>> print(sys.getsizeof(x))
28

From The Article

Explain the difference between @staticmethod and @classmethod?

What is tag in HTML?

How can you generate random numbers?

What will be the output of the following statements and why?

What is the MRO in Python?

What do you mean by foreign key?

Python MySQL Tutorial – How to Use LIMIT to Control Query Results

Python MySQL Tutorial – How to Use LIMIT to Control Query Results

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

Explanation:

↔️ 7. Using LIMIT with OFFSET (Pagination)

Dynamic Pagination Example:

8. Full Working Example

⚠️ 9. Tips and Common Pitfalls

✅ Best Practices

Summary Table

Final Thoughts

Tips and Tricks

Python | Pass Statement

Python | Generate random numbers

Python | Lambda function

Python | swapcase() Function

Python | strip() Function | Remove whitespaces from a string

Python | enumerate() Function

Python | filter(), map(), and reduce() Functions

Python | namedtuple

From The Article

Trending View All

How to show data values on top of each bar …

A non-numeric value encountered in PHP

The view account.views.register did not return an HttpResponse object. It …

Input type number maxlength not working

Uncaught TypeError: e.indexOf is not a function in JQuery

How to start array index from 1 in PHP

Interview Questions

PHP Interview Question

PayPal Interview Question

MySQL Interview Question

PHP-MySQL Interview Question

SQL Interview Question

CodeIgniter Interview Question

JQuery Interview Question

htaccess Interview Question

JavaScript Interview Question

HTML Interview Question

Python Interview Question

Django Interview Question

✅ 1. What is `LIMIT` in MySQL?

2. Why Use `LIMIT`?

6. Using `LIMIT` in SELECT Queries

↔️ 7. Using `LIMIT` with `OFFSET` (Pagination)