Python MySQL Limit Results

🎯 Get Just What You Need!

LIMIT controls how many records you get - like asking for "just the first 5 students"!


import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()
cursor.execute("SELECT name, age FROM students LIMIT 5")
results = cursor.fetchall()

for student in results:
  print(f"{student[0]} - {student[1]} years old")

LIMIT

Control

Fast

Results

Precise

Amount

What is LIMIT?

LIMIT controls how many records you get:

🎯 Control Amount - Get exactly the number you want
⚡ Faster Results - Less data means faster loading
📱 Perfect for Apps - Show 10 items per page
💾 Save Memory - Don't load thousands of records at once

Get First 5 Records

Show Only 5 Students


import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()

# Get only the first 5 students
cursor.execute("SELECT name, age FROM students LIMIT 5")

results = cursor.fetchall()
print("👥 First 5 students:")
for student in results:
    print(f"  {student[0]} ({student[1]} years old)")

mydb.close()

Get Top 3 Oldest Students

Combine LIMIT with ORDER BY


import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()

# Get the 3 oldest students
cursor.execute("SELECT name, age FROM students ORDER BY age DESC LIMIT 3")

results = cursor.fetchall()
print("👴 Top 3 oldest students:")
for i, student in enumerate(results, 1):
    print(f"  {i}. {student[0]} ({student[1]} years old)")

mydb.close()

Skip Some Records (OFFSET)

Skip First 3, Get Next 5


import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()

# Skip first 3 students, get next 5
cursor.execute("SELECT name, age FROM students LIMIT 5 OFFSET 3")

results = cursor.fetchall()
print("📋 Students 4-8:")
for student in results:
    print(f"  {student[0]} ({student[1]} years old)")

mydb.close()

Create Pages (Pagination)

Show Students Page by Page


import mysql.connector

def show_students_page(page_number, students_per_page=3):
    """Show students page by page"""
    try:
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            password="yourpassword",
            database="school"
        )
        
        cursor = mydb.cursor()
        
        # Calculate offset
        offset = (page_number - 1) * students_per_page
        
        # Get students for this page
        cursor.execute("SELECT name, age FROM students LIMIT %s OFFSET %s", 
                      (students_per_page, offset))
        
        results = cursor.fetchall()
        
        if results:
            print(f"📄 Page {page_number} ({students_per_page} students per page):")
            for student in results:
                print(f"  • {student[0]} ({student[1]} years old)")
        else:
            print(f"❌ No students found on page {page_number}")
        
        mydb.close()
        
    except mysql.connector.Error as error:
        print(f"❌ Error: {error}")

# Show different pages
show_students_page(1)  # First page
print()
show_students_page(2)  # Second page

LIMIT with Filtering

Get First 3 Students Over 16


import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()

# Get first 3 students older than 16
cursor.execute("SELECT name, age FROM students WHERE age > 16 ORDER BY age LIMIT 3")

results = cursor.fetchall()
print("🎓 First 3 students over 16:")
for student in results:
    print(f"  {student[0]} ({student[1]} years old)")

mydb.close()

Get Random Records

Pick 3 Random Students


import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()

# Get 3 random students
cursor.execute("SELECT name, age FROM students ORDER BY RAND() LIMIT 3")

results = cursor.fetchall()
print("🎲 3 randomly selected students:")
for student in results:
    print(f"  {student[0]} ({student[1]} years old)")

mydb.close()

When to Use LIMIT

📱 App Lists

Show 10 items at a time
LIMIT 10

🏆 Top Results

Best 5 students
ORDER BY grade DESC LIMIT 5

📄 Pagination

Page 2 of results
LIMIT 10 OFFSET 10

🎲 Random Sample

Random selection
ORDER BY RAND() LIMIT 3