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