How can you select data from a table based on some criteria using MySQL in Python?

It is not frequently required to select all the data from the table. Instead , we need to select data or rows from the table based on some condition or criteria.

Suppose, we have a table which includes names and marks of many students. Now,we need to get names of the students whose marks are above 90. This basically requires us to select data based on some criteria ,which is(marks>=90).

Therefore, we are provided with “WHERE” statement in MySQL which allows us to select data from the table based on some specified criteria.

Syntax

SELECT * FROM table_name WHERE condition

Here, table_name specifies the name of the table and condition specifies the basic criteria based on which the selection of data is done. This statement selects all the rows from the table which satisfy the given condition or criteria.

Steps invloved in selecting specific data from a table using MySQL in python

• import MySQL connector

• establish connection with the connector using connect()

• create the cursor object using cursor() method

• create a query using the appropriate mysql statements

• execute the SQL query using execute() method

• close the connection

Suppose we have a table named “Students” as below −

+----------+---------+-----------+------------+
|    Name  | Class   |    City   |    Marks   |
+----------+---------+-----------+------------+
|    Karan |    4    | Amritsar  |    95      |
|    Sahil |    6    | Amritsar  |    93      |
|    Kriti |    3    | Batala    |    88      |
|   Khushi |    9    | Delhi     |    90      |
|    Kirat |    5    | Delhi     |    85      |
+----------+---------+-----------+------------+

Example

We want to select and display the records of the students whose marks are greater than or equal to 90.

import mysql.connector

db=mysql.connector.connect(host="your host", user="your username", password="your
password",database="database_name")

cursor=db.cursor()

query="SELECT * FROM Students WHERE Marks>=90”
cursor.execute(query)
for row in cursor:
   print(row)

The above code when executed successfully displays rows where the marks of the students are above 90. In this case, it returns and displays the 1^st,2^nd and 4^th row.

Output

(‘Karan’, 4 ,’Amritsar’ , 95)
(‘Sahil’ , 6 , ‘Amritsar’ ,93)
(‘Khushi’ , 9 ,’Delhi’ , 90)