Python sqlite limit to manage number of recors from different positions of a table

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 0^th position and 5 records are returned staring from 0^th 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 10^th 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

Always use parameterized query when the data is coming from unknown sources. Use ? as placeholder and a provide a tuple to pass the value to query or execute() method. This is required to prevent injection attack.

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 UPDATE SELECT ORDER By IIF

Numpy Pandas Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

SQLite limit Query

USING limit query

Using error handling

Using Parameters

Paging of records

Subscribe