How to Fetch Data from MySQL Using Python Easily

To fetch data from MySQL using Python, use the mysql-connector-python library to connect to the database, create a cursor, execute a SELECT query, and then fetch the results with fetchall() or fetchone(). Finally, close the connection to free resources.

Syntax

Here is the basic syntax to fetch data from MySQL using Python:

• Connect: Use mysql.connector.connect() with host, user, password, and database.
• Cursor: Create a cursor object to execute SQL commands.
• Execute: Run a SELECT query with cursor.execute().
• Fetch: Retrieve data using cursor.fetchall() or cursor.fetchone().
• Close: Close cursor and connection to clean up.

python

import mysql.connector

# Connect to MySQL database
conn = mysql.connector.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database'
)

# Create a cursor object
cursor = conn.cursor()

# Execute a SELECT query
cursor.execute('SELECT * FROM your_table')

# Fetch all rows
rows = cursor.fetchall()

# Process rows
for row in rows:
    print(row)

# Close cursor and connection
cursor.close()
conn.close()

Example

This example connects to a MySQL database, fetches all rows from a table named employees, and prints each row.

python

import mysql.connector

try:
    # Connect to the database
    conn = mysql.connector.connect(
        host='localhost',
        user='root',
        password='password123',
        database='company'
    )
    cursor = conn.cursor()

    # Execute query
    cursor.execute('SELECT id, name, position FROM employees')

    # Fetch all results
    results = cursor.fetchall()

    # Print each row
    for (id, name, position) in results:
        print(f'ID: {id}, Name: {name}, Position: {position}')

except mysql.connector.Error as err:
    print(f'Error: {err}')

finally:
    if 'cursor' in locals() and cursor:
        cursor.close()
    if 'conn' in locals() and conn:
        conn.close()

Output

ID: 1, Name: Alice, Position: Manager
ID: 2, Name: Bob, Position: Developer
ID: 3, Name: Carol, Position: Designer

Common Pitfalls

• Not installing the mysql-connector-python package before running code.
• Forgetting to close the cursor and connection, which can cause resource leaks.
• Using incorrect database credentials or host information.
• Not handling exceptions, which can crash the program on errors.
• Executing queries without committing changes when modifying data (not needed for SELECT).

python

import mysql.connector

# Wrong: No exception handling and no closing connection
conn = mysql.connector.connect(host='localhost', user='root', password='wrong', database='test')
cursor = conn.cursor()
cursor.execute('SELECT * FROM test_table')
rows = cursor.fetchall()
print(rows)

# Right: With try-except and closing
try:
    conn = mysql.connector.connect(host='localhost', user='root', password='correct', database='test')
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM test_table')
    rows = cursor.fetchall()
    print(rows)
except mysql.connector.Error as e:
    print(f'Error: {e}')
finally:
    if 'cursor' in locals() and cursor:
        cursor.close()
    if 'conn' in locals() and conn:
        conn.close()

Quick Reference

Remember these key points when fetching data from MySQL in Python:

• Install the connector: pip install mysql-connector-python
• Use connect() with correct credentials
• Create a cursor to run queries
• Use fetchall() or fetchone() to get data
• Always close cursor and connection
• Handle exceptions to catch errors

Key Takeaways

Use mysql-connector-python to connect and fetch data from MySQL in Python.

Always create a cursor, execute SELECT queries, and fetch results with fetchall() or fetchone().

Close your cursor and connection to avoid resource leaks.

Handle exceptions to manage errors gracefully.

Ensure your database credentials and host info are correct before connecting.