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

We are using our student table. Download the sqlite_between_practice.ipynb file 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

Interactive Companion Jupyter Notebook

Explore interactive code cells, execute BETWEEN range queries, test NOT BETWEEN negations, and run date range filters directly in Google Colab.

View on GitHub

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 (2.0+ Context Manager)

Variable my_conn is declared as connection string.

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

my_conn = create_engine("sqlite:///my_db.db")
my_data = {"low": 60, "high": 75}
q = text("SELECT * FROM student WHERE mark BETWEEN :low AND :high")

try:
  with my_conn.connect() as connection:
    r_set = connection.execute(q, my_data)  
    r_list = r_set.fetchall()
    for row in r_list:
      print(row)
    print("Total Number of rows : ", len(r_list))
except SQLAlchemyError as e:
  print(e)

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

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: