Read Operation in Python with MySQL

Read Operation in Python with MySQL

When working with databases, the SELECT statement is your go-to tool for extracting information. This powerful SQL command allows you to retrieve and format data, apply filters, and sort results efficiently. In this tutorial, we will explore how to use Python and MySQL together to perform read operations. From fetching all rows to applying conditions, you’ll learn how to master database queries with Python scripts.

Download New Real Time Projects :-Click here

Setting Up the Connection

Before we dive into the operations, ensure that the MySQL database is properly configured and connected. Here’s how to create a connection object in Python using the mysql.connector library:

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()

Fetching All Data from a Table

The fetchall() method retrieves all rows from the result set. This is ideal for reading complete data sets from a table.

Example: Fetch All Records

try:  
    cur.execute("SELECT * FROM Employee")  # Query to fetch all rows  
    result = cur.fetchall()  # Fetching rows  

    # Iterating through the result  
    for row in result:  
        print(row)  
except:  
    myconn.rollback()  # Rollback in case of an error  
finally:  
    myconn.close()  # Closing the connection  

Output:

('John', 101, 25000.0, 201, 'New York')
('David', 103, 25000.0, 202, 'Port of Spain')
('Nick', 104, 90000.0, 201, 'New York')

Fetching Specific Columns

To retrieve specific columns, replace the * in the query with the desired column names.

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)

Fetching a Single Row

The fetchone() method is useful for retrieving just one row at a time.

Example: Fetch the First Record

try:  
    cur.execute("SELECT name, id, salary FROM Employee")  
    result = cur.fetchone()  # Fetching the first row  
    print(result)  
except:  
    myconn.rollback()  
finally:  
    myconn.close()  

Output:

('John', 101, 25000.0)

Formatting the Output

The fetched data is returned as a tuple, which might not be user-friendly. Formatting improves readability.

Example: Display Data in a Table-Like Format

try:  
    cur.execute("SELECT name, id, salary FROM Employee")  
    result = cur.fetchall()  

    print(f"{'Name':<10}{'ID':<5}{'Salary':<10}")  # Column headers  
    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.00  

Using the WHERE Clause

Filters can be applied to the query using the WHERE clause.

Example: Fetch 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.00  

Ordering the Results

The ORDER BY clause helps in sorting the data.

Example: Order by Name 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.00  

Example: Order by Name 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)

PHP PROJECT:- CLICK HERE

how to connect python with mysql in command prompt
crud operations in python with source code
crud operations in python w3schools
mysql connector/python
crud operations in python using mysql
python mysql example
crud operations in python with mysql example
Read Operation in Python with MySQL
read operation in python with mysql database
read operation in python with mysql w3schools
read operation in python with mysql example