Welcome to another tutorial on Python MySQL. Here you learn about the WHERE clause; how to filter rows from the fetched result-set or delete a specific row from a MySQL table as well as updating a specific row using the WHERE clause.
However, the WHERE clause is simply a way to provide a condition or multiple conditions to the SQL engine, that is used on the query result-set to filter out the required records of data.
The WHERE Clause was used previously. Such as the Update Table Data, and Delete Table on Python MySQL.
When there is a need to select data from a table based on a specified condition, the WHERE clause in the SELECT statement can be used. Check out the features below:
Below is the general syntax of using the WHERE clause in the SELECT statement:
SELECT column_name
FROM table_name
WHERE condition;
Check out the example below, where we will fetch the row having rollno=2 from our student's table in our previous tutorial on Create table.
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)
Output:
('Ada Chang', 'Computer Science', 'Hong Kong', 2)