Where clause is used in order to make our search results more specific, using the where clause in SQL/SQLite we can go ahead and specify specific conditions that have to be met when retrieving data from the database.
If we want to retrieve, update or delete a particular set of data we can use the where clause. If we don’t have condition matching values in your database tables we probably didn’t get anything returned.
WHERE Clause in SQL:
Syntax:
SELECT column_1, column_2,…,column_N
FROM table_name
WHERE [search_condition]
Here, in this [search_condition] you can use comparison or logical operators to specify conditions.
For example: = , > , < , != , LIKE, NOT, etc.
WHERE Clause in SQLite using Python:
In Python SQLite Cursor object/class which contains all the methods to execute SQL queries to perform operations, etc. The Cursor is the method of connection class that returns a cursor object.
Therefore, to perform SQLite commands in python we need 3 basic things to be done −
- Establish a connection with the database using the connect() method.
- Create a cursor object by using the cursor() method.
- Now SQLite queries/statements can be executed using the execute() method of the Cursor class.
We’ll create a database to manage data about students. We’ll be storing information about each student, and then we’ll also create a way to retrieve, update and delete data of students using the where clause.
Let’s Creates Database (geekforLazyroar_student.db) and a Table (STUDENT).
Python3
# import the sqlite3 module import sqlite3 # Define connection and cursor connection = sqlite3.connect('geekforLazyroar_student.db') cursor = connection.cursor() # create table cursor.execute("DROP TABLE IF EXISTS STUDENT") createTable = '''CREATE TABLE STUDENT( Student_ID int, First_Name VARCHAR(100), Last_Name VARCHAR(100), Age int, Department VARCHAR(100) )'''cursor.execute(createTable) # check the database creation data if cursor: print("Database Created Successfully !") else: print("Database Creation Failed !") # Commit the changes in database and Close the connection connection.commit() connection.close() |
Output:
Database Created Successfully!
The above code will create “geekforLazyroar_student.db” file locally.
To view the content of the “geekforLazyroar_student.db” we can use this and load our .db file as shown below –
In the above image, we can see the table and table schema has been created in the database.
Now we will insert data into STUDENT table.
Python3
# import the sqlite3 module import sqlite3 # Define connection and cursor connection = sqlite3.connect('geekforLazyroar_student.db') cursor = connection.cursor() # Insert data into the table cursor.execute("INSERT INTO STUDENT VALUES (1,'Rohit', 'Pathak', 21, 'IT')") cursor.execute("INSERT INTO STUDENT VALUES (2,'Nitin', 'Biradar', 21, 'IT')") cursor.execute("INSERT INTO STUDENT VALUES (3,'Virat', 'Kohli', 30, 'CIVIL')") cursor.execute("INSERT INTO STUDENT VALUES (4,'Rohit', 'Sharma', 32, 'COMP')") # printing the cursor data if cursor: print("Data Inserted !") else: print("Data Insertion Failed !") # Commit the changes in database and Close the connection connection.commit() connection.close() |
Output:
Database Inserted!
The above code will insert the data into the STUDENT table
The following codes show the use of Where Clause
Example 1: To retrieve the data of the students whose Department is IT
Python3
import sqlite3 connection = sqlite3.connect('geekforLazyroar_student.db') cursor = connection.cursor() # WHERE CLAUSE TO RETRIEVE DATA cursor.execute("SELECT * FROM STUDENT WHERE Department = 'IT'") # printing the cursor data print(cursor.fetchall()) connection.commit() connection.close() |
Output:
[(1, ‘Rohit’, ‘Pathak’, 21, ‘IT’), (2, ‘Nitin’, ‘Biradar’, 21, ‘IT’)]
We have 2 records in STUDENT table whose Department is IT.
Example 2: To retrieve the data of the students whose First Name starts with ‘R’. We can also use Wildcard characters with where clause as shown below
Python3
import sqlite3 connection = sqlite3.connect('geekforLazyroar_student.db') cursor = connection.cursor() # WHERE CLAUSE TO RETRIEVE DATA cursor.execute("SELECT * from STUDENT WHERE First_name Like'R%'") # printing the cursor data print(cursor.fetchall()) connection.commit() connection.close() |
Output:
[(1, ‘Rohit’, ‘Pathak’, 21, ‘IT’), (4, ‘Rohit’, ‘Sharma’, 32, ‘COMP’)]
We have 2 records in the STUDENT table whose First Name starts with the letter ‘R’.
Example 3: To update the data of student whose Student ID is 4
Python3
import sqlite3 connection = sqlite3.connect('geekforLazyroar_student.db') cursor = connection.cursor() # WHERE CLAUSE TO UPDATE DATA cursor.execute("UPDATE STUDENT SET Department ='E&TC' WHERE Student_ID = 2") # printing the cursor data cursor.execute("SELECT * from STUDENT") print(cursor.fetchall()) connection.commit() connection.close() |
Output:
[(1, ‘Rohit’, ‘Pathak’, 21, ‘IT’), (2, ‘Nitin’, ‘Biradar’, 21, ‘E&TC’),
(3, ‘Virat’, ‘Kohli’, 30, ‘CIVIL’), (4, ‘Rohit’, ‘Sharma’, 32, ‘COMP’)]
Check the database content
Department is updated for Student ID 2.
Example 4: To Delete the data of student whose Age ID is 30
Python3
import sqlite3 connection = sqlite3.connect('geekforLazyroar_student.db') cursor = connection.cursor() # WHERE CLAUSE TO DELETE DATA cursor.execute("DELETE from STUDENT WHERE Age = 32") #printing the cursor data cursor.execute("SELECT * from STUDENT") print(cursor.fetchall()) connection.commit() connection.close() |
Output:
[(1, ‘Rohit’, ‘Pathak’, 21, ‘IT’), (2, ‘Nitin’, ‘Biradar’, 21, ‘E&TC’), (3, ‘Virat’, ‘Kohli’, 30, ‘CIVIL’)]
Check the database content
Data of Student whose Age is 32 has been deleted.
