SQLite limit Query

SQLite

We are using our student table. Check how to create database and student table here.

Read more on limit query here.

USING limit query

LIMIT query takes two inputs, one is the starting position of the record and another is number of records. We will add these two values along with SELECT query using LIMIT.
q="SELECT id,name,class,mark,sex FROM student LIMIT 0,5"
my_cursor=my_conn.execute(q)
#data_rows=my_cursor.fetchall()

for row in my_cursor:
    print(row[0],row[1],row[2],row[3],row[4])
In above code we used my_cursor ( cursor ) as iterator and displayed the records by looping. The output of this code is here.
1 John Deo Four 75 female
2 Max Ruin Three 85 male
3 Arnold Three 55 male
4 Krish Star Four 60 female
5 John Mike Four 60 female
Note that the first record is at 0th position and 5 records are returned staring from 0th position record.

Using error handling

We can display error message if there is any error by using try except . We will also select all class Four.
q="SELECT * FROM  student LIMIT 10,5 "
try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4])
except sqlite3.Error as my_error:
  print("error: ",my_error)
Above code will return 5 records starting from 10th record. Let us change the query to q="SELECT * FROM student1 LIMIT 10,5" , Note that there is no table as student1. Here is the output
error:  no such table: student1

Using Parameters


We have used placeholders ( ? ) in our query and note that my_data is a tuple used for passing value to execute() method for our query.

my_data=(15,5) # tuple to pass values to execute method
q="SELECT * FROM  student LIMIT ?,? "
try:
    my_cursor=my_conn.execute(q,my_data)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4])
except sqlite3.Error as my_error:
  print("error: ",my_error)
Output
16 Gimmy Four 88 male
17 Tumyu Six 54 male
18 Honny Five 75 male
19 Tinny Nine 18 male
20 Jackly Nine 65 female

Paging of records

While displaying large number of records we can restrict the number of records per page and give options to navigate to different pages to view different parts of the total records.

For example, to display 100 records we can display 10 records per page and show navigation menu to show previous or next 9 pages.

LIMIT query is used to show part of the total records and this is used in breaking large number of records into parts.
Paging Script using LIMIT query

Sqlite Connection insert Delete


plus2net.com



Post your comments , suggestion , error , requirements etc here




We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer