Read Operation in Python with MySQL
Reading information from a database is one of the most common tasks in application development. In MySQL, the SELECT statement is used to retrieve stored records, while Python can be used to execute these queries and process the returned data.
In this tutorial, we will learn how to perform a Read Operation in Python with MySQL using the mysql.connector library. The examples cover retrieving complete records, selecting particular columns, fetching individual rows, filtering results with WHERE, and arranging records with ORDER BY.
Table of Contents
Setting Up the MySQL Connection
Before executing any read query, Python needs an active connection with the MySQL database. The mysql.connector package provides the required functionality for establishing the connection and creating a cursor.
import mysql.connector
# Establishing the connection
myconn = mysql.connector.connect(
host="localhost",
user="root",
passwd="your_password",
database="PythonDB"
)
# Creating the cursor object
cur = myconn.cursor()The connection object stores the database connection, while the cursor is responsible for executing SQL statements and receiving their results.
Fetching All Records from a Table
When you need the complete contents of a table, the SELECT * query can be used. Python’s fetchall() method then retrieves every row returned by the query.
Example: Fetch All Employee Records
try:
cur.execute("SELECT * FROM Employee")
result = cur.fetchall()
for row in result:
print(row)
except:
myconn.rollback()
finally:
myconn.close()The returned records can be processed one by one using a for loop.
Output
('John', 101, 25000.0, 201, 'New York')
('David', 103, 25000.0, 202, 'Port of Spain')
('Nick', 104, 90000.0, 201, 'New York')Fetching Selected Columns
It is not always necessary to retrieve every column from a table. You can specify only the fields required for your application. For example, the following query reads the employee name, ID, and salary.
Example: Fetch Name, ID, and Salary
try:
cur.execute("SELECT name, id, salary FROM Employee")
result = cur.fetchall()
for row in result:
print(row)
except:
myconn.rollback()
finally:
myconn.close()Output
('John', 101, 25000.0)
('David', 103, 25000.0)
('Nick', 104, 90000.0)Selecting specific columns can make the returned result easier to process when only a few fields are required.
Fetching a Single Row
When the application needs only one record from the result set, the fetchone() method can be used. Instead of returning all available rows, it returns one row at a time.
Example: Fetch the First Record
try:
cur.execute("SELECT name, id, salary FROM Employee")
result = cur.fetchone()
print(result)
except:
myconn.rollback()
finally:
myconn.close()Output
('John', 101, 25000.0)This approach is useful when the application expects to work with an individual record rather than the complete result set.
Displaying Results in a Readable Format
Database results are commonly returned as tuples. Although tuples are useful for programming, displaying them directly may not always be easy to read. Python string formatting can be used to create a table-like console output.
Example: Formatting Employee Details
try:
cur.execute("SELECT name, id, salary FROM Employee")
result = cur.fetchall()
print(f"{'Name':<10}{'ID':<5}{'Salary':<10}")
for row in result:
print(f"{row[0]:<10}{row[1]:<5}{row[2]:<10.2f}")
except:
myconn.rollback()
finally:
myconn.close()Output
Name ID Salary
John 101 25000.00
David 103 25000.00
Nick 104 90000.00Here, formatted strings are used to align the columns, making the result easier to understand.
Using the WHERE Clause
The WHERE clause allows you to limit the records returned by a query. Instead of reading every employee, you can define a condition and retrieve only matching rows.
Example: Find Names Starting with J
try:
cur.execute(
"SELECT name, id, salary FROM Employee WHERE name LIKE 'J%'"
)
result = cur.fetchall()
print(f"{'Name':<10}{'ID':<5}{'Salary':<10}")
for row in result:
print(f"{row[0]:<10}{row[1]:<5}{row[2]:<10.2f}")
except:
myconn.rollback()
finally:
myconn.close()Output
Name ID Salary
John 101 25000.00
John 102 25000.00The LIKE 'J%' condition selects records where the employee name begins with the letter J.
Sorting Retrieved Records
SQL provides the ORDER BY clause for arranging query results. Records can be sorted in ascending or descending order.
Example: Sort Names in Ascending Order
try:
cur.execute(
"SELECT name, id, salary FROM Employee ORDER BY name ASC"
)
result = cur.fetchall()
print(f"{'Name':<10}{'ID':<5}{'Salary':<10}")
for row in result:
print(f"{row[0]:<10}{row[1]:<5}{row[2]:<10.2f}")
except:
myconn.rollback()
finally:
myconn.close()Output
Name ID Salary
David 103 25000.00
John 101 25000.00
Nick 104 90000.00The ASC keyword arranges the names from A to Z.
Example: Sort Names in Descending Order
try:
cur.execute(
"SELECT name, id, salary FROM Employee ORDER BY name DESC"
)
result = cur.fetchall()
for row in result:
print(row)
except:
myconn.rollback()
finally:
myconn.close()Output
('Nick', 104, 90000.0)
('John', 101, 25000.0)
('David', 103, 25000.0)With DESC, the records are arranged from Z to A.
Important Methods Used in the Read Operation
Several Python methods are important when retrieving MySQL data:
execute()runs the SQL query through the cursor.fetchall()returns all rows from the query result.fetchone()returns one row from the result.rollback()reverses pending database changes when an error occurs.close()closes the database connection after the operation is finished.
Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here
YT:- DecodeIT
Conclusion
The Read Operation in Python with MySQL provides a foundation for retrieving and processing database information in Python applications. With the SELECT statement, you can obtain complete records or selected columns, while fetchall() and fetchone() provide different ways to handle returned data.
Using clauses such as WHERE and ORDER BY makes database queries more useful by allowing you to filter and organize the information before displaying it. These concepts are essential when developing Python applications that work with MySQL databases.
Frequently Asked Questions (FAQ)
1. What is a read operation in Python with MySQL?
A read operation retrieves information stored in a MySQL database. Python can execute a SELECT query through the mysql.connector library and process the returned records.
2. Which SQL statement is used for reading MySQL data?
The SELECT statement is used to retrieve records from MySQL tables.
3. What does fetchall() do in Python MySQL?
The fetchall() method retrieves all rows available in the result set returned by the executed query.
4. What is the purpose of fetchone()?
fetchone() retrieves one row from the current result set. It is useful when the application needs to process records individually.
5. How can I filter MySQL records using Python?
You can use the SQL WHERE clause inside the query executed through Python. For example, WHERE name LIKE 'J%' filters names beginning with J.
6. How can MySQL results be sorted?
The ORDER BY clause can be used to arrange retrieved records. ASC sorts in ascending order, while DESC sorts in descending order.
7. Why is mysql.connector used?
mysql.connector provides Python functionality for connecting to MySQL databases, creating cursors, executing SQL statements, and working with returned results.
8. Why should the database connection be closed?
Closing the connection after completing database operations releases the database resources used by the application.
Keywords: Read Operation in Python with MySQL, read operation in python with mysql database, Python MySQL read operation, Python MySQL SELECT example, mysql.connector Python, Python MySQL example, fetchall Python MySQL, fetchone Python MySQL, SELECT query in Python MySQL, Python MySQL database operations, CRUD operations in Python with MySQL, Python MySQL tutorial, MySQL connector Python, WHERE clause Python MySQL, ORDER BY Python MySQL
