Python MySQL - Limit Clause

In this tutorial, we will learn how to limit the number of rows returned in a resultset with the help of LIMIT clause added to the query in the python code.

The syntax to use this clause is given below:

SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT  N;

The above syntax indicates the following things:

• SELECT {fieldname(s) | *} FROM tableName(s): This part is used to select the records that we would like to return in our query.

• [WHERE condition]: The WHERE clause is optional, but if used then it applies the filter on the result set.

• LIMIT N: It is used to limit the records in the result. Here N starts from 0 but if you will pass LIMIT 0( then it does not return any record). If you will pass 6 then it will return the starting 6 rows in the output. Suppose the records in the specified table are less than N, then all the records from the table are returned in the result set.

Python MySQL LIMIT: Example

Let us select only two rows from the students table(from the Python MySQL create table tutorial). The code for the same is given below:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "studytonight"
)

cursor = db.cursor()
cursor.execute("SELECT * FROM students LIMIT 2")

myresult = cursor.fetchall()

for x in myresult:
  print(x)

The output of the above code will be as follows:

('Ramesh', 'CSE', '149 Indirapuram', 1) ('Peter', 'ME', 'Noida', 2)

Using OFFSET Keyword with LIMIT clause

If you do not want to start from the first position then by using OFFSET keyword in the LIMIT query you can start from any other position. Let us see the code example for the same:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "studytonight"
)

cursor = db.cursor()

cursor.execute("SELECT * FROM students LIMIT 2 OFFSET 1")

myresult = cursor.fetchall()

for x in myresult:
  print(x)

In the above code LIMIT 2 means it will return the 2 rows and OFFSET 1 the resultset will start from the 1st row, means only row 2 is returned. Thus the output is as follows:

('Peter', 'ME', 'Noida', 2) ('Navin', 'CE', 'New Delhi', 3)

Here is the snapshot of the actual output:

The OFFSET keyword isused to specify the starting point. For example if a query returnd 100 rows of data and we have specifed the OFFSET as 50, then data starting from 51st row till the 100th row will be returned.