Python – MySQL – Limit

Sometimes, we have a large number of records in the database table, and we want to fetch only a certain number of records at a time. We can use the MySQL LIMIT clause to fetch only the required number of records from the table. In this article, we will discuss LIMIT clause with Python and MySQL.

Connect Python with MySQL

To use the MySQL with Python, we need to install the mysql-connector-python library. You can install this library using the following pip command:

pip install mysql-connector-python

After installing the library, we can connect to the MySQL database using the following code snippet:

import mysql.connector

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

print(mydb)

Here, we are connecting to the MySQL database using mysql.connector.connect() method. We need to provide the correct host, username, password and database name to connect to the MySQL database server. If the connection is successful, the above code will print the connection object.

Python MySQL Limit

Once we’re connected to the MySQL database, we can use the following code snippet to get only a certain number of records from the 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)

In the above code, we are selecting all the records from the customers table, but we are fetching only the first 5 records using the LIMIT clause.

The fetchall() method fetches all the rows from the result set. The fetchall() method is used to fetch all the rows in a result set. If you want to fetch only one row, you can use the fetchone() method. Below is an example of how to fetch only one record using the fetchone() method:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers LIMIT 1")

myresult = mycursor.fetchone()

print(myresult)

The above code will fetch only the first row from the customers table.

Python MySQL Offset

The offset is the number of rows to skip before starting to return the rows. We can use the MySQL LIMIT clause with OFFSET to skip a certain number of rows before fetching the required number of rows from the table. Here’s an example that demonstrates how to use the LIMIT clause with OFFSET to fetch only a certain number of records from the 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 OFFSET 2")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

In the above code, we are fetching only 5 records from the customers table, starting from the third record, using the LIMIT clause with OFFSET. The LIMIT is set to 5 and the OFFSET is set to 2. So, the query will skip the first two records and will fetch only the next 5 records from the table.

Conclusion

In this article, we discussed how to use the LIMIT clause with Python and MySQL to fetch only a certain number of records from the table. We also demonstrated how to use the LIMIT clause with OFFSET to fetch a certain number of records starting from a particular record in the table. Using a combination of LIMIT and OFFSET clauses, we can fetch only the required records from the table and improve the performance of our applications.