MySQL LIMIT in Python

The LIMIT clause in SQL is used to specify the number of records to return from the query. In Python, you can use the mysql-connector-python library to interact with MySQL and apply the LIMIT clause to limit the number of results returned.

1. Install MySQL Connector

If you don’t have it already installed, run the following command to install the MySQL connector:

pip install mysql-connector-python

2. Connect to MySQL Database

First, establish a connection to your MySQL database.

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase"  # Change to your database name)# Create a cursor objectcursor = conn.cursor()print("Connected to MySQL database successfully!")

3. Insert Sample Data

Before applying the LIMIT clause, let’s assume you have a table named employees with some data.

# Create employees tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    age INT,    department VARCHAR(50))""")# Insert some sample data into employees tablecursor.execute("""INSERT INTO employees (name, age, department) VALUES ('Alice', 30, 'HR'), ('Bob', 25, 'IT'), ('Charlie', 28, 'Finance'),('David', 40, 'HR'),('Eve', 35, 'IT')""")conn.commit()

4. Using LIMIT to Restrict Number of Rows

You can use the LIMIT clause to limit the number of rows returned by your query. For example, if you want to retrieve the first 3 records:

# SQL query with LIMITlimit_query = "SELECT * FROM employees LIMIT 3"cursor.execute(limit_query)# Fetch and print the resultsresults = cursor.fetchall()for row in results:    print(row)

This query will return only the first 3 records from the employees table.

5. Using LIMIT with OFFSET

The LIMIT clause can also be used with OFFSET to skip a certain number of rows and retrieve a specific subset of results.

For example, to skip the first 2 rows and retrieve the next 3 rows:

# SQL query with LIMIT and OFFSEToffset_limit_query = "SELECT * FROM employees LIMIT 3 OFFSET 2"cursor.execute(offset_limit_query)# Fetch and print the resultsresults = cursor.fetchall()for row in results:    print(row)

This query will skip the first 2 rows and then return the next 3 rows.

6. Closing the Connection

Once you have completed your operations, always close the connection.

cursor.close()conn.close()print("MySQL connection closed.")

Final Complete Code

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase")cursor = conn.cursor()# Create employees tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    age INT,    department VARCHAR(50))""")# Insert sample data into employees tablecursor.execute("""INSERT INTO employees (name, age, department) VALUES ('Alice', 30, 'HR'), ('Bob', 25, 'IT'), ('Charlie', 28, 'Finance'),('David', 40, 'HR'),('Eve', 35, 'IT')""")conn.commit()# Using LIMIT to get the first 3 recordslimit_query = "SELECT * FROM employees LIMIT 3"cursor.execute(limit_query)print("First 3 records from employees table:")results = cursor.fetchall()for row in results:    print(row)# Using LIMIT with OFFSET to get records starting from the 3rd rowoffset_limit_query = "SELECT * FROM employees LIMIT 3 OFFSET 2"cursor.execute(offset_limit_query)print("\nRecords starting from the 3rd row:")results = cursor.fetchall()for row in results:    print(row)# Close connectioncursor.close()conn.close()

Summary of LIMIT and OFFSET

This is how you can apply the LIMIT clause in SQL using Python! ?