In this tutorial, we will learn how to select data from MySQL table/data using MySQL SELECT statement in Python programming language.
Prerequisites
- Python 3.x installed on your system
- MySQL server installed on your system
- MySQL Connector/Python module installed
MySQL SELECT Statement
The SELECT statement is used to retrieve data from one or more MySQL tables. The following is the general syntax of MySQL SELECT statement:
SELECT column_name(s) FROM table_name WHERE condition(s)
Here, column_name(s) is the column name(s) of the table that we want to retrieve records from, table_name is the name of the table that we want to retrieve records from, and condition(s) is the conditions that must be fulfilled for a record to be returned.
Select All Rows
To retrieve all rows from a MySQL table, we can use the following SELECT statement:
import mysql.connector
#connect to database
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
#create cursor object
mycursor = mydb.cursor()
#execute the SQL query
mycursor.execute("SELECT * FROM customers")
#fetch all records
records = mycursor.fetchall()
#print the records
for record in records:
print(record)
The above code snippet establishes a connection to the MySQL database and retrieves all records from the customers table. We then iterate over the records and print them to the console.
Select Specific Columns
To retrieve specific columns from a MySQL table, we can specify the column names separated by commas in the SELECT statement. The following is an example:
import mysql.connector
#connect to database
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
#create cursor object
mycursor = mydb.cursor()
#execute the SQL query
mycursor.execute("SELECT name, email FROM customers")
#fetch all records
records = mycursor.fetchall()
#print the records
for record in records:
print(record)
The above code snippet retrieves only the name and email columns from the customers table.
Select Records with a Condition
We can retrieve records from a MySQL table that satisfy a particular condition by including the condition(s) in the WHERE clause of our SELECT statement. The following is an example:
import mysql.connector
#connect to database
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
#create cursor object
mycursor = mydb.cursor()
#execute the SQL query
mycursor.execute("SELECT * FROM customers WHERE name = 'John Doe'")
#fetch all records
records = mycursor.fetchall()
#print the records
for record in records:
print(record)
The above code snippet retrieves all records from the customers table where the name column is equal to ‘John Doe’.
Select Records with Multiple Conditions
We can include multiple conditions in our SELECT statement by using the AND and OR operators. The following is an example:
import mysql.connector
#connect to database
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
#create cursor object
mycursor = mydb.cursor()
#execute the SQL query
mycursor.execute("SELECT * FROM customers WHERE age > 25 AND country = 'USA'")
#fetch all records
records = mycursor.fetchall()
#print the records
for record in records:
print(record)
The above code snippet retrieves all records from the customers table where the age column is greater than 25 and the country column is equal to ‘USA’.
Select Records with LIKE Operator
The LIKE operator is used to retrieve records that match a particular pattern. The following is an example:
import mysql.connector
#connect to database
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
#create cursor object
mycursor = mydb.cursor()
#execute the SQL query
mycursor.execute("SELECT * FROM customers WHERE name LIKE '%J%'")
#fetch all records
records = mycursor.fetchall()
#print the records
for record in records:
print(record)
The above code snippet retrieves all records from the customers table where the name column contains the letter ‘J’.
Select Records with ORDER BY Clause
We can order the records returned by our SELECT statement by including the ORDER BY clause. The following is an example:
import mysql.connector
#connect to database
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
#create cursor object
mycursor = mydb.cursor()
#execute the SQL query
mycursor.execute("SELECT * FROM customers ORDER BY name")
#fetch all records
records = mycursor.fetchall()
#print the records
for record in records:
print(record)
The above code snippet retrieves all records from the customers table and orders them by the name column in ascending order. We can also order by multiple columns by including the column names separated by commas in the ORDER BY clause.
Select Records with LIMIT Clause
We can limit the number of records returned by our SELECT statement by including the LIMIT clause. The following is an example:
import mysql.connector
#connect to database
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
#create cursor object
mycursor = mydb.cursor()
#execute the SQL query
mycursor.execute("SELECT * FROM customers LIMIT 5")
#fetch all records
records = mycursor.fetchall()
#print the records
for record in records:
print(record)
The above code snippet retrieves the first 5 records from the customers table.
Conclusion
In this tutorial, we learned how to retrieve data from a MySQL table using the SELECT statement in Python. We covered basic and advanced techniques, including selecting all rows, selecting specific columns, selecting records with a condition, selecting records with multiple conditions, selecting records with the LIKE operator, ordering records with the ORDER BY clause, and limiting the number of records returned with the LIMIT clause.
