Menu Close

Python MySQL Where Clause

In this Python MySQL Where tutorial, we are going to learn all about how to select records from the database table according to the conditions specified using Python MySQL Connector.
In the previous tutorial, we have seen a complete process to select records from the database table.

Make sure you have already installed Python MySQL connector in your machine using pip as well as create database and table.

Python MySQL Where Clause

MySQL provides a WHERE clause to filter the record of the database table according to your need. Where clause in MySQL is used to filter the result set returned by the SELECT statement.

Example:

Here we will select all the records from the table named “students”. We use fetchall() method, that is select all the rows from the last executed query.

import mysql.connector
mydb = mysql.connector.connect(

	host="localhost",
	user="root",
	password="root21",
	database = "demodb",
    port = 3308
	
)
cursor = mydb.cursor()
cursor.execute('SELECT * FROM students')
all_students = cursor.fetchall()
for record in all_students:
	print(record)

Output

(1, 'Vishvajit', 12)
(2, 'John', 22)
(3, 'Alex', 32)
(4, 'Peter', 12)
(5, 'Alex Peter', 12)
(6, 'Alsena', 45)
(7, 'Jaini', 56)

SELECT Columns:

Here we will select only those students whose id is greater than 2 and less than 5. To filter the id, here we will use the Python MySQL WHERE clause.

Example:

import mysql.connector
mydb = mysql.connector.connect(

	host="localhost",
	user="root",
	password="root21",
	database = "demodb",
    port = 3308
	
)
cursor = mydb.cursor()
cursor.execute('SELECT * FROM students WHERE id < 5 and id > 2')
all_students = cursor.fetchall()
for record in all_students:
	print(record)

Output

(1, 'Vishvajit')
(2, 'John')
(3, 'Alex')
(4, 'Peter')
(5, 'Alex Peter')
(6, 'Alsena')
(7, 'Jaini')

Note:- Above credentials may be different according to your MySQL configuration.
I am showing all the credentials for only testing purposes, It will not working in your case.

Wildcard Characters:

You can select records from the database table that starts, ends and include a given characters or phrase.

Example:

import mysql.connector
mydb = mysql.connector.connect(

	host="localhost",
	user="root",
	password="root21",
	database = "demodb",
        port = 3308
	
)
cursor = mydb.cursor()
cursor.execute('SELECT * FROM students WHERE name LIKE "A%"')
students = cursor.fetchall()
for i in students:
    print(i)

Output:

(3, 'Alex', 32)
(5, 'Alex Peter', 12)
(6, 'Alsena', 45)

Select specific record:

You can select record from the database table, by using unique column.

Example:

import mysql.connector
mydb = mysql.connector.connect(

	host="localhost",
	user="root",
	password="root21",
	database = "demodb",
    port = 3308
	
)
cursor = mydb.cursor()
cursor.execute('SELECT * FROM students WHERE id = 2')
student = cursor.fetchone()
print(student)

Output

(2, 'John', 22)

Conlcusion

In this Python MySQL WHERE tutorial, you have learned all about how to filter the result set using where clause returned by MySQL SELECT statement.
Python MySQL connector is the best for connecting Python applications to the MySQL database and executes SQL queries. After created of database successfully, you can perform a query on that particular database.

If this article for you, please share and keep visiting for further Python MySQL database tutorials.

More Python MySQL Tutorials


More about Python MySQL Where:- Click Here

Previous Article
Next Article

Related Posts