Limit Number of Rows from Table in Python MySQL


Python MySQL - Limit number of rows selected from table

To select limited number of rows from a MySQL table in Python program,

  1. Create a connection to the MySQL database with user credentials and database name, using connect() function.
  2. Get cursor object to the database using cursor() function.
  3. Call execute() function on the cursor object, and pass the SELECT FROM table query with LIMIT clause.
  4. Call fetchall() function on the cursor object. The function returns an iterator to the records in the table. Each record is a tuple.

Example

Consider that there is a schema named mydatabase in MySQL. The credentials to access this database are, user: root and password: admin1234, and there is a table named fruits in mydatabase.

In the following program, we select the rows from fruits table, but limit the number of records to two.

Python Program

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM fruits LIMIT 2")

myresult = mycursor.fetchall()

for row in myresult:
    print(row)

Output

('Apple', 25, 'Canada')
('Avocado', 37, 'Mexico')

Summary

In this tutorial of Python Examples, we learned how to limit the number of records in the selection from a table in MySQL database, from a Python program.