Python MySQL – BETWEEN and IN Operator

In this article, we are going to see the database operations BETWEEN and IN operators in MySQL using Python.

Both of these operators are used with the WHERE query to check if an expression is within a range or in a list of values in SQL. We are going to use the below table to perform various operations:

The SQL BETWEEN condition is used to test if an expression is within a range of values (inclusive). The values include text, date, or numbers.

1. It can be used along with the SELECT, INSERT, UPDATE, or DELETE statement.
2. The SQL BETWEEN Condition will return the records where the expression is within the range of value1 and value2.

Syntax:

SELECT column1,column2,….,column n

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

Example 1: Python MySQL program to demonstrate the use of BETWEEN operator.

Python3

# Import mysql.connector module
import mysql.connector

# give connection with xampp
database = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="gfg"
)

# Creating cursor object
cur_object = database.cursor()
print("between operator demo")

#  query
find = "SELECT cus_id,purchase from geeks where\
purchase between 10 and 100 "

# Execute the query
cur_object.execute(find)

# fetching results
data = cur_object.fetchall()
print(" ")
print("Purchase between 10 to 100 ")
print(" ")

for res in data:
    print(res[0], res[1], sep="--")
print(" ")

# Close database connection
database.close()

Output:

The SQL IN operator allows you to easily test if the expression matches any value in the list of values. It is used to remove the need for multiple OR conditions in SELECT, INSERT, UPDATE or DELETE. You can also use NOT IN to exclude the rows in your list.

Syntax:

SELECT column1,column2,…,column n

FROM tablename

WHERE column IN (val1,val2,…,valn);

Example 2: Python MySQL program to demonstrate the use of IN operator.

Python3

# Import mysql.connector module
import mysql.connector

# give connection with xampp
database = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="gfg"
)

# Creating cursor object
cur_object = database.cursor()

#  query
print("in operator demo")
find = "SELECT cus_id,purchase from geeks where \
purchase in (67) "

# Execute the query
cur_object.execute(find)

# fetching all results
data = cur_object.fetchall()
print("customer id with purchase 67: ")
print(" ")

for res in data:
    print(res[0], res[1], sep="--")
print(" ")
print("not in operator demo")
print(" ")

find = "SELECT cus_id,purchase from geeks where purchase\
not in (67) "

# Execute the query
cur_object.execute(find)

# fetching all results
data = cur_object.fetchall()
print(" ")
print("customer id with purchase except 67 purchase ")
print(" ")

for res in data:
    print(res[0], res[1], sep="--")

# Close database connection
database.close()

Output: