Python sqlite between to collect matching records using a range of values from table

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

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=(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

More on SQLite Date query
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

Numpy Pandas Python & MySQL Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

SQLite between Query

Video Tutorial on SQLite BETWEEN query