Python SQLite – WHERE Clause
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.
Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.
To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course
WHERE Clause in SQL:
SELECT column_1, column_2,…,column_N
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 (geekforgeeks_student.db) and a Table (STUDENT).
Database Created Successfully!
The above code will create “geekforgeeks_student.db” file locally.
To view the content of the “geekforgeeks_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.
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
[(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
[(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
[(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
[(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.