Python MySQL - WHERE Clause
In this tutorial, we will learn how to filter rows from the fetched resultset, or update a specific row, or delete a specific row from a MySQL table using the WHERE clause to specify the condition to find the record/row of data on which the operation is performed.
So, WHERE clause is nothing but a way to provide a condition(or multiple conditions) to the SQL engine, which is applied on the query resultset to filter out the required records of data.
Python MySQL WHERE Clause
We have already used the WHERE clause in our previous tutorials:
If you want to select data from a table based on some condition then you can use WHERE clause in the SELECT statement.
-
The WHERE clause is mainly used for filtering the rows from the result set.
-
It is helpful in fetching, updating, and deleting data from the MySQL Table.
The syntax of using WHERE clause in SELECT statement is as follows:
SELECT column_name
FROM table_name
WHERE condition;
The above syntax is useful in fetching the records on the basis of some conditions.
Using WHERE Clause
Below we have an example where we will fetch the row having rollno = 2 from our students table(from the Python MySQL create table tutorial):
import mysql.connector as mysql
###First create a connection between mysql and python
db = mysql.connect(
host = "localhost",
user = "yourusername",
passwd = "yourpassword",
database = "studytonight"
)
# now create a cursor object on the connection object
# created above by using cursor() method
cursor = db.cursor()
## defining the Query
query = "SELECT * FROM students WHERE rollno= 2"
## getting records from the table
cursor.execute(query)
## fetching all records from the 'cursor' object
records = cursor.fetchall()
## Showing the data
for record in records:
print(record)
The output of the above code will be:
('Peter', 'ME', 'Noida', 2)
Here is the snapshot of the actual output:
python mysql WHERE clause example