”;
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 in to 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 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.
To drop a table from a MYSQL database using python invoke the execute() method on the cursor object and pass the drop statement as a parameter to it.
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.
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)]
”;