Limit clause - Python MySQL

Welcome to another tutorial on Python MySQL. Here, you will learn about Limit Clause, and how to limit the number of rows returned in a given result-set by using the LIMIT CLAUSE that is added to the query in Python.

Below is the general syntax:

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

The features below are indicated by the syntax above:

The SELECT {fieldname(s) | *} FROM tableName(s) is used to select the records that will be returned in a query.
The [WHERE condition], although, the WHERE CLAUSE is optional, however, If used, it then applies the filter on the result-set.
The LIMIT N is used to limit the records in the result. Where N starts from 0, but if we pass LIMIT 0, then it does not return any record. However, if we pass 6 then it will return the starting 6 rows in the output. now, what if the records in the required table are less than N, then all the records from the table are returned to the given result-set.

Python MySQL LIMIT: Example

In the example, we will select two rows from the students' table (i.e. the table created in the previous tutorial on create table). This is shown below:

import mysql.connector as mysql

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

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

myresult = cursor.fetchall()

for x in myresult:
  print(x)

Output:

('Mike', 'Computer Science', 'London, 1)

Using OFFSET Keyword with LIMIT clause

In a situation where you do not want to start from the first position, you can use the OFFSET keyword in the LIMIT query to start from any other position. Check out the example below:

import mysql.connector as mysql

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

cursor = db.cursor()

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

myresult = cursor.fetchall()

for x in myresult:
  print(x)

Output:

('Michael', 'Computer Science', 'USA', 3)