SQLite between Query

SQLite

We are using our student table. Download the between.ipynb file at the end to create your student table and run the sample codes.

Read more on between query here.
SELECT col BETWEEN lower_val AND higher_val
Both lower_val and higher_val are included in the range.
lower_val is less than higher_val

USING between query

We will collect records by using between query by using execute method. We will use between with SELECT command.

We can display error message if there is any error by using try except .

Here we are collecting records where mark column data is between 65 and 70 ( both ends inclusive ) .
q="SELECT * FROM  student WHERE mark between 65 and 70 "
try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4],row[5])
except sqlite3.Error as my_error:
  print("error: ",my_error)
Output
20 Jackly Nine 65 female 2020-10-11
21 Babby John Four 69 female 2020-10-13
22 Reggid Seven 70 female 2020-05-18
34 Gain Toe Seven 69 male 2020-11-14

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=(65,70)
q="SELECT * FROM  student WHERE mark between ? and ? "
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],row[5])
except sqlite3.Error as my_error:
  print("error: ",my_error)
Output ( same as above )
20 Jackly Nine 65 female 2020-10-11
21 Babby John Four 69 female 2020-10-13
22 Reggid Seven 70 female 2020-05-18
34 Gain Toe Seven 69 male 2020-11-14

Using NOT

We can gegate a range by using NOT operator. The query part is here .
my_data=(25,90)
q="SELECT * FROM  student WHERE mark NOT between ? and ?  "
Output (The rows having mark not between 25 and 90 are returned. )
12 Recky Six 94 female 2020-11-18
19 Tinny Nine 18 male 2020-12-18
33 Kenn Rein Six 96 female 2020-10-25

Using DATE

We can use BETWEEN query to get records of a range of dates. Our modified student table has one date column dot. Using this dot column we can display range of records.
my_data=('2020-05-01','2020-07-20')
q="SELECT * FROM  student WHERE dot between ? and ? "

try:
    my_cursor=my_conn.execute(q,my_data)
    
    for row in my_cursor:
        print(row[0],row[1],row[3],row[2],row[4],row[5])
except sqlite3.Error as my_error:
  print("error: ",my_error)
Output
22 Reggid 70 Seven female 2020-05-18
27 Big Nose 81 Three female 2020-07-18
28 Rojj Base 86 Seven female 2020-07-19
Grouping records using CASE with BETWEEN

View and Download between.ipynb file View and Download sqlite-case-between.ipynb file

Sqlite Connection insert Delete Order By


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