Python SQLite - Limit

While fetching records if you want to pmit them by a particular number, you can do so, using the LIMIT clause of SQLite.

Syntax

Following is the syntax of the LIMIT clause in SQLite −

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

Example

Assume we have created a table with name CRICKETERS using the following query −

sqpte> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqpte>

And if we have inserted 5 records in to it using INSERT statements as −

sqpte> insert into CRICKETERS values( Shikhar ,  Dhawan , 33,  Delhi ,  India );
sqpte> insert into CRICKETERS values( Jonathan ,  Trott , 38,  CapeTown ,  SouthAfrica );
sqpte> insert into CRICKETERS values( Kumara ,  Sangakkara , 41,  Matale ,  Srilanka );
sqpte> insert into CRICKETERS values( Virat ,  Kohp , 30,  Delhi ,  India );
sqpte> insert into CRICKETERS values( Rohit ,  Sharma , 32,  Nagpur ,  India );
sqpte>

Following statement retrieves the first 3 records of the Cricketers table using the LIMIT clause −

sqpte> SELECT * FROM CRICKETERS LIMIT 3;
First_Name   Last_Name    Age    Place_Of_B   Country
----------   ----------   ----   ----------   -------------
Shikhar      Dhawan       33     Delhi        India
Jonathan     Trott        38     CapeTown     SouthAfrica
Kumara       Sangakkara   41     Matale       Srilanka
sqpte>

If you need to pmit the records starting from nth record (not 1^st), you can do so, using OFFSET along with LIMIT.

sqpte> SELECT * FROM CRICKETERS LIMIT 3 OFFSET 2;
First_Name   Last_Name    Age    Place_Of_B   Country
----------   ----------   ----   ----------   --------
Kumara       Sangakkara   41     Matale       Srilanka
Virat        Kohp        30     Delhi        India
Rohit        Sharma       32     Nagpur       India
sqpte>

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 retrieves the first two records of the EMPLOYEE table using the LIMIT clause.

import sqpte3
#Connecting to sqpte
conn = sqpte3.connect( example.db )

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving single row
sql =    SELECT * from EMPLOYEE LIMIT 3   

#Executing the query
cursor.execute(sql)

#Fetching the data
result = cursor.fetchall();
print(result)

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Output

[
   ( Ramya ,  Rama priya , 27,  F , 9000.0), 
   ( Vinay ,  Battacharya , 20,  M , 6000.0), 
   ( Sharukh ,  Sheik , 25,  M , 8300.0)
]