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
  • Video Tutorial on SQLite BETWEEN query


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 negate 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 SQLAlchemy with error handling

Variable my_conn is declared as connection string.
from sqlalchemy.exc import SQLAlchemyError
my_data=(60,75)
q="SELECT * FROM student WHERE mark between ? AND ? "
try:
  r_set=my_conn.execute(q,my_data)  
  r_list=r_set.fetchall()
  for row in r_list:
    print(row)
except SQLAlchemyError as e:
  error=str(e.__dict__['orig'])
  print(error)
else:
  print("Total Number of rows : ",len(r_list))

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
Subscribe to our YouTube Channel here

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





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