After the data has been added, the subsequent phase involves reading or retrieving that information. You can obtain any entry from a MySQL table by utilizing the "SELECT" statement. The SELECT SQL query provides the records of the table displayed in a tabular format.
Python offers the methods fetchall and fetchone to retrieve the information contained within a table, presenting it as rows. We can loop through the results to access each individual row.
The fetchall function retrieves all the information contained within the table.
Syntax for MySQL SELECT Query
It has the following syntax:
SELECT col1, col2, colN FROM table_name;
Python Example for MySQL SELECT Query
Let us examine an illustration that showcases the MySQL SELECT Query within a Python environment.
Example
import mysql.connector
# Connecting to the MySQL database and creating the connection object
db_connection = mysql.connector.connect(
host="localhost",
user="root",
password="Root",
database="mydatabase"
)
# Creating a cursor object to interact with the database
db_cursor = db_connection.cursor()
#writing the SQL query to select pr read all records from the customers table
db_cursor.execute("SELECT * FROM customers")
# the fetchall() method will gather all the data
# returned by the SQL query
results = db_cursor.fetchall()
# using the for loop to print each record
for record in results:
print(record)
Output:
(1, 'Ananya', 'Mumbai')
(2, 'Raj', 'Delhi')
(3, 'Yshakan', 'Bangalore')
(4, 'Aman', 'Hyderabad')
(5, 'Sneha', 'Chennai')
(6, 'Karan', 'Pune')
(7, 'Divya', 'Kolkata')
(8, 'Ishaan', 'Jaipur')
(9, 'Megha', 'Ahmedabad')
(10, 'Neha', 'Surat')
(11, 'Rohit', 'Lucknow')
(12, 'Tina', 'Chandigarh')
(13, 'Aditya', 'Bhopal')
Explanation:
In the preceding code, we established a connection between Python and the MySQL database by utilizing the mysql.connector package.
The connection is initiated by utilizing the host information (localhost), user credentials (root), password (Root), and the target database (mydatabase). A cursor object, referred to as db_cursor, has been created to carry out SQL commands.
We have constructed a SQL query that employs a SELECT statement to retrieve every record from the customers table. In SQL, the asterisk (*) serves as a wildcard when we intend to obtain all the information available within the table.
Subsequently, we have employed the fetchall method to retrieve all the data obtained from the SQL query, which has been stored in the variable named results.
Finally, we have utilized the for loop to traverse each entry and displayed the values contained within the table.
Selecting Columns
We have the ability to retrieve specific columns from a table by specifying their names rather than employing the asterisk (*).
Syntax for Selecting Columns
The following syntax demonstrates how to select specific columns from a table:
"SELECT" column_name(s):
Python Example for Selecting Columns
Let us consider an example to demonstrate the process of selecting specific columns from a table in Python.
Example
import mysql.connector
# Connecting to the MySQL database
db_connection = mysql.connector.connect(
host="localhost",
user="root",
password="Root",
database="mydatabase"
)
# Creating a cursor object to interact with the database
db_cursor = db_connection.cursor()
#writing the SQL query to select name and address from the table
db_cursor.execute("SELECT name, address FROM customers")
# Fetching all results from the executed query
records = db_cursor.fetchall()
# Looping through the results and printing each record
for data in records:
print(data)
Output:
(1, 'Ananya', 'Mumbai')
(2, 'Raj', 'Delhi')
(3, 'Yshakan', 'Bangalore')
(4, 'Aman', 'Hyderabad')
(5, 'Reema', 'Kolkata')
(6, 'Karan', 'Pune')
(7, 'Divya', 'Kolkata')
(8, 'Ishaan', 'Jaipur')
(9, 'Megha', 'Ahmedabad')
(10, 'Neha', 'Surat')
(11, 'Rohit', 'Lucknow')
(12, 'Tina', 'Chandigarh')
(13, 'Aditya', 'Bhopal')
Explanation:
In this example, we utilize an SQL query that employs the SELECT statement to retrieve solely the name and address information from the specified table. The command 'SELECT name, address FROM customers' will effectively accomplish this task and provide the relevant records.
Using the fetchone Method
To retrieve a single row from the result set, you can utilize the fetchone method. This method will provide you with the first row available in the result.
Python fetchone Method Example
Let’s consider an example to demonstrate the functionality of the fetchone method in Python.
Example
import mysql.connector
# Connecting to the MySQL database
db_connection = mysql.connector.connect(
host="localhost",
user="root",
password="Root",
database="mydatabase"
)
# Creating a cursor object to interact with the database
db_cursor = db_connection.cursor()
#writing the SQL query to fetch the records from the tbale
db_cursor.execute("SELECT * FROM customers")
#using the fetchone() method to fetch only one row
myresult = db_cursor.fetchone()
#printing the output
print(myresult)
Output:
(1, 'Ananya', 'Mumbai')
Explanation:
In the program outlined above, we initially crafted an SQL query to retrieve all records from the table. Subsequently, from the retrieved dataset, we utilized the fetchone method to display exclusively the first row.
Conclusion
The SELECT FROM statement is a robust command that enables users to access data from a table. Through various SQL queries, you have the ability to choose specific data or retrieve the entire dataset from the table. After establishing a connection between Python and a MySQL database, you can employ methods such as fetchall and fetchone to obtain either several rows or just one row, depending on your needs. Gaining proficiency in the Select operation will not only empower you to extract data from your database but will also significantly enhance your capabilities in data analysis and reporting.
Frequently Asked Questions/ FAQs
1. What's the difference between fetchone, fetchall, and fetchmany methods in Python?
All three methods are used to fetch data from tables, but three of them are used for different purposes:
- The fetchone method is used when the user wants to retrieve a single row from the table.
- The fetchall method is used to return the data stored inside the table.
- The fetchmany method is used to fetch the specified number of rows.
- SELECT * FROM ABC
- SELECT name address FROM ABC
- SELECT name, address FROM ABC
- SELECT FROM ABC (name, address)
2. Which of the following syntax is correct to get only the "name" and "address" columns from the database?
c. SELECT name, address FROM ABC
3. Is it always required to close the connection and cursor?
Indeed, it is considered a best practice to utilize the close method to terminate the connection and cursor in Python database applications. This action promptly frees up all the resources that have been utilized.