Python MySQL Limit

In MySQL, the LIMIT clause is used to restrict the number of rows returned by a query. In Python, you can use this clause in your SQL queries to limit the result set when fetching data from the database.

1. Install MySQL Connector

If you haven’t installed the MySQL connector yet, use this command:

pip install mysql-connector-python

2. Connect to the MySQL Database

You need to connect to the database and use the LIMIT clause to control how many rows you fetch.

Example Python Script Using LIMIT

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",         # Change this to your MySQL host if needed
  user="yourusername",       # Change this to your MySQL username
  password="yourpassword",   # Change this to your MySQL password
  database="mynewdatabase"   # Change this to your database name
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query to select data from a table with a LIMIT of 3 rows
mycursor.execute("SELECT * FROM customers LIMIT 3")

# Fetch all the rows
result = mycursor.fetchall()

# Print the result
for row in result:
    print(row)

# Close the connection
mydb.close()

Explanation:

• LIMIT 3: This restricts the result set to the first 3 rows.
• mycursor.fetchall(): Fetches all rows from the query result, but since we have a LIMIT, it will only return 3 rows.

3. Using LIMIT with OFFSET

You can use LIMIT with OFFSET to skip a specific number of rows before returning the results.

For example, if you want to skip the first 5 rows and then return the next 3 rows:

mycursor.execute("SELECT * FROM customers LIMIT 3 OFFSET 5")

result = mycursor.fetchall()

for row in result:
    print(row)

This query will skip the first 5 rows and then return the next 3 rows from the table.

4. Full Example: Using LIMIT in Python MySQL Queries

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mynewdatabase"
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query to fetch the first 5 rows from the customers table
sql = "SELECT * FROM customers LIMIT 5"

# Execute the query
mycursor.execute(sql)

# Fetch all the rows within the limit
result = mycursor.fetchall()

# Print the result
for row in result:
    print(row)

# Close the connection
mydb.close()

5. Using LIMIT with ORDER BY

You can combine LIMIT with ORDER BY to get a sorted subset of rows. For example, to get the first 5 customers ordered by their name in ascending order:

sql = "SELECT * FROM customers ORDER BY name ASC LIMIT 5"

mycursor.execute(sql)

result = mycursor.fetchall()

for row in result:
    print(row)

6. Closing the Connection

Always close the connection after you’re done:

mydb.close()

Summary:

• LIMIT restricts the number of rows returned by a query.
• OFFSET skips a specific number of rows before returning the results.
• LIMIT with ORDER BY is useful when you want a sorted subset of data.