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.
- It can be used along with the SELECT, INSERT, UPDATE, or DELETE statement.
- 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 moduleimport mysql.connector# give connection with xamppdatabase = mysql.connector.connect( host="localhost", user="root", password="", database="gfg")# Creating cursor objectcur_object = database.cursor()print("between operator demo")# queryfind = "SELECT cus_id,purchase from Lazyroar where\purchase between 10 and 100 "# Execute the querycur_object.execute(find)# fetching resultsdata = cur_object.fetchall()print(" ")print("Purchase between 10 to 100 ")print(" ")for res in data: print(res[0], res[1], sep="--")print(" ")# Close database connectiondatabase.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 moduleimport mysql.connector# give connection with xamppdatabase = mysql.connector.connect( host="localhost", user="root", password="", database="gfg")# Creating cursor objectcur_object = database.cursor()# queryprint("in operator demo")find = "SELECT cus_id,purchase from Lazyroar where \purchase in (67) "# Execute the querycur_object.execute(find)# fetching all resultsdata = 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 Lazyroar where purchase\not in (67) "# Execute the querycur_object.execute(find)# fetching all resultsdata = 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 connectiondatabase.close() |
Output:
