35.10 Limit

By | October 6, 2021

Limit the Result

You can limit the number of records returned from the query, by using the “LIMIT” statement:

Example

Select the 5 first records in the “customers” table:
import mysql.connector
mydb = mysql.connector.connect(
  host=”localhost”,
  user=”yourusername“,
  password=”yourpassword“,
  database=”mydatabase”
)
mycursor = mydb.cursor()
mycursor.execute(“SELECT * FROM customers LIMIT 5”)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Output:
C:\Users\My Name>python demo_mysql_limit.py
(1, ‘John’, ‘Highway 21’)
(2, ‘Peter’, ‘Lowstreet 27’)
(3, ‘Amy’, ‘Apple st 652’)
(4, ‘Hannah’, ‘Mountain 21’)
(5, ‘Michael’, ‘Valley 345’)

Start From Another Position

If you want to return five records, starting from the third record, you can use the “OFFSET” keyword:

Example

Start from position 3, and return 5 records:
import mysql.connector
mydb = mysql.connector.connect(
  host=”localhost”,
  user=”yourusername“,
  password=”yourpassword“,
  database=”mydatabase”
)
mycursor = mydb.cursor()
mycursor.execute(“SELECT * FROM customers LIMIT 5 OFFSET 2”)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Output:
C:\Users\My Name>python demo_mysql_limit_offset.py
(3, ‘Amy’, ‘Apple st 652’)
(4, ‘Hannah’, ‘Mountain 21’)
(5, ‘Michael’, ‘Valley 345’)
(6, ‘Sandy’, ‘Ocean blvd 2’)
(7, ‘Betty’, ‘Green Grass 1’)

Leave a Reply

Your email address will not be published. Required fields are marked *