World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Internship Partner
In Association with
In collaboration with
SIGN IN to your Account
New to InsideAIML? Create an account
Employer? Create an account
World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Internship Partner
In Association with
In collaboration with
Forgot Your Password?
Enter your email below and we will send a message to reset your password
World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Internship Partner
In Association with
In collaboration with
Sign Up and Start Learning!
By providing your contact details, you agree to our Terms of Use & Privacy Policy.
Already have an account? Sign In
World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Internship Partner
In Association with
In collaboration with
Sign Up and Start Learning!
By providing your contact details, you agree to our Terms of Use & Privacy Policy.
Already have an account? Sign In
Download our e-book of Introduction To Python
Related Blog
Matplotlib - Object-oriented InterfaceMatplotlib - MultiplotsCNTK - Creating First Neural NetworkMatplotlib - Working with ImagesMicrosoft Cognitive Toolkit (CNTK) - CPU and GPUPython Forensics - Memory and ForensicsPython Blockchain - Scope and ConclusionDiscuss Microsoft Cognitive ToolkitMatplotlib - Twin AxesMatplotlib - Subplot2grid() Function View More
Top Discussion
Exception Type: JSONDecodeError at /update/ Exception Value: Expecting value: line 1 column 1 (char 0) How can I write Python code to change a date string from "mm/dd/yy hh: mm" format to "YYYY-MM-DD HH: mm" format? How to choosing the right estimator for the machine learning problem? How to Write Python ZIP File? How to extracting text from PDF file using python How can a web interface execute a .py file from a PHP file? What methods can we use to differentiate between correlated and uncorrelated variables in a regression analysis? How to leave/exit/deactivate a Python virtualenvironment Join Discussion
Top Courses
Master's In Artificial Intelligence Program in Collaboration With IBM
4.5 (1,292 Ratings)
589 Learners
Webinars
Why You Should Learn Data Science in 2023
Sep 30th (7:00 PM) 1117 Registered
Python MySQL - Limit
Neha Kumawat
2 years ago
Table of Content
- Limit Clause Using Python
- Example
- Output
- LIMIT with OFFSET
- Example
- Output
While fetching records if you want to limit them by a particular number, you can do so, using the LIMIT clause of MYSQL.
Example
Assume we have created a table in MySQL with name EMPLOYEES as −
mysql> CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
);
Query OK, 0 rows affected (0.36 sec)
And if we have inserted 4 records into it using INSERT statements as −
mysql> INSERT INTO EMPLOYEE VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000),
('Mac', 'Mohan', 26, 'M', 2000);
Following SQL statement retrieves the first two records of the Employee table using the LIMIT clause.
SELECT * FROM EMPLOYEE LIMIT 2;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE | SEX | INCOME |
+------------+-----------+------+------+--------+
| Krishna | Sharma | 19| M | 2000 |
| Raj | Kandukuri | 20| M | 7000 |
+------------+-----------+------+------+--------+
2 rows in set (0.00 sec)
Limit Clause Using Python
If you invoke the execute() method on the cursor object by passing the SELECT query along with the LIMIT clause, you can retrieve required number of records.
Example
Following python example creates and populates a table with name EMPLOYEE and, using the LIMIT clause it fetches the first two records of it.
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='password', host='127.0.0.1', database='mydb'
)
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving single row
sql = '''SELECT * from EMPLOYEE LIMIT 2'''
#Executing the query
cursor.execute(sql)
#Fetching the data
result = cursor.fetchall();
print(result)
#Closing the connection
conn.close()
Output
[('Krishna', 'Sharma', 26, 'M', 2000.0), ('Raj', 'Kandukuri', 20, 'M', 7000.0)]LIMIT with OFFSET
If you need to limit the records starting from nth record (not 1st), you can do so, using OFFSET along with LIMIT.
Example
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='password', host='127.0.0.1', database='mydb'
)
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving single row
sql = '''SELECT * from EMPLOYEE LIMIT 2 OFFSET 2'''
#Executing the query
cursor.execute(sql)
#Fetching the data
result = cursor.fetchall();
print(result)
#Closing the connection
conn.close()
Output
[('Ramya', 'Ramapriya', 29, 'F', 5000.0), ('Mac', 'Mohan', 26, 'M', 2000.0)]I hope you enjoyed reading this article and finally, you came
to know about Python MySQL - Limit.
For more such blogs/courses on data science, machine
learning, artificial intelligence and emerging new technologies do visit us at InsideAIML.
Thanks for reading…
Happy Learning…