Select and where clause in MySQL with Python

To learn this lesson well, you need to know how to insert data into a table in MySQL with Python. You can read the article Insert and Delete Data in MySQL with Python to learn how to insert data into a table in MySQL.

Note: We are using MySQL integrated into XAMPP. You need to read the article Install PHP Web Development Environment with XAMPP to learn how to install XAMPP and start MySQL.

The select and where examples will be performed on the gochocit_posts table in the gochocit database. This database and table were created in the article Create Database and Table in MySQL with Python. You need to read it to see the structure of the database and the data of the table.

1. Select MySQL with Python

The "SELECT" statement retrieves the rows of data in the MySQL table.

import mysql.connector
# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database = "gochocit"
)
mycursor = mydb.cursor()

sql = "SELECT * FROM gochocit_posts"
mycursor.execute(sql)
myresult = mycursor.fetchall()

print("type of myresult:", type(myresult))
for x in myresult:
  print(x)
# disconnect from MySQL
mydb.close()

Result

type of myresult: <class 'list'>
(1, 'Vinh Le', 'This is post number 1', 'Content of post number 1', datetime.datetime(2022, 3, 27, 19, 46, 5))
(2, 'Vinh Le', 'This is post number 2', 'Content of post number 2', datetime.datetime(2022, 3, 27, 19, 47, 6))
(3, 'Vinh Le', 'This is post number 3', 'Content of post number 3', datetime.datetime(2022, 3, 27, 19, 48, 7))
(4, 'Vinh Le', 'This is post number 4', 'Content of post number 4', datetime.datetime(2022, 3, 27, 19, 49, 8))
(5, 'Vinh Le', 'This is post number 5', 'Content of post number 5', datetime.datetime(2022, 3, 27, 19, 50, 9))

The fetchall() function returns a list containing all the queried data rows.

In the above example, we use "SELECT *" to retrieve all the data columns in the table. However, we can specify which columns to select.

import mysql.connector
# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database = "gochocit"
)
mycursor = mydb.cursor()

sql = "SELECT post_author, post_title, post_content FROM gochocit_posts"
mycursor.execute(sql)
myresult = mycursor.fetchall()

print("type of myresult:", type(myresult))
for x in myresult:
  print(x)
# disconnect from MySQL
mydb.close()

Result

type of myresult: <class 'list'>
('Vinh Le', 'This is post number 1', 'Content of post number 1')
('Vinh Le', 'This is post number 2', 'Content of post number 2')
('Vinh Le', 'This is post number 3', 'Content of post number 3')
('Vinh Le', 'This is post number 4', 'Content of post number 4')
('Vinh Le', 'This is post number 5', 'Content of post number 5')

When select data in a table, we can retrieve many rows of data. But if we only want to retrieve the first row of data that has been selected, we can use the fetchone() function of the MySQL Connector module in Python.

import mysql.connector
# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database = "gochocit"
)
mycursor = mydb.cursor()

sql = "SELECT * FROM gochocit_posts"
mycursor.execute(sql)
myresult = mycursor.fetchone()

print(myresult)
# disconnect from MySQL
mydb.close()

Result

(1, 'Vinh Le', 'This is post number 1', 'Content of post number 1', datetime.datetime(2022, 3, 27, 19, 46, 5))

2. Using the WHERE clause in MySQL with Python

The WHERE clause helps to retrieve specific data rows that satisfy a certain condition.

import mysql.connector
# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database = "gochocit"
)
mycursor = mydb.cursor()

sql = "SELECT * FROM gochocit_posts WHERE ID='5'"
mycursor.execute(sql)
myresult = mycursor.fetchall()

for x in myresult:
  print(x)
# disconnect from MySQL
mydb.close()

Result

(5, 'Vinh Le', 'This is post number 5', 'Content of post number 5', datetime.datetime(2022, 3, 27, 19, 50, 9))

In the above example, we only retrieve the data row with ID=5.

We can use %s to place the value to filter the data in the WHERE clause.

import mysql.connector
# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database = "gochocit"
)
mycursor = mydb.cursor()

sql = "SELECT * FROM gochocit_posts WHERE ID = %s"
adr = ("5", )

mycursor.execute(sql, adr)
myresult = mycursor.fetchall()

for x in myresult:
  print(x)
# disconnect from MySQL
mydb.close()

Result

(5, 'Vinh Le', 'This is post number 5', 'Content of post number 5', datetime.datetime(2022, 3, 27, 19, 50, 9))

With the WHERE clause, we can also select data rows that start with, contain, or end with a certain letter or phrase using LIKE and % to represent characters.

import mysql.connector
# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database = "gochocit"
)
mycursor = mydb.cursor()

sql = "SELECT * FROM gochocit_posts WHERE post_content LIKE '%2%'"
mycursor.execute(sql)
myresult = mycursor.fetchall()

for x in myresult:
  print(x)
# disconnect from MySQL
mydb.close()

Result

(2, 'Vinh Le', 'This is post number 2', 'Content of post number 2', datetime.datetime(2022, 3, 27, 19, 47, 6))

In the example above, we want to extract the data rows where the post_content includes the number ‘2‘.