Python MySQL Tutorial – How to SELECT Data from a Table Using Python

Last updated 5 months, 1 week ago | 473 views 75     5

Tags:- MySQL Python

Retrieving data from a MySQL table is one of the most essential tasks when working with databases. In this step-by-step tutorial, you’ll learn how to fetch records from a MySQL table using Python, with practical examples, explanations, and a complete working script.


Table of Contents

  1. Prerequisites

  2. Install MySQL Connector

  3. Connect to a MySQL Database

  4. SELECT All Rows

  5. SELECT Specific Columns

  6. Use WHERE Clause

  7. Fetch One or All Results

  8. Loop Through Results

  9. Full Working Example

  10. Common Pitfalls and Tips


✅ 1. Prerequisites

Make sure you have the following:

  • Python installed

  • MySQL server running

  • A MySQL database and table with data

  • The mysql-connector-python library installed


2. Install MySQL Connector

Install the official MySQL connector package with pip:

pip install mysql-connector-python

3. Connect to a MySQL Database

Start by connecting to your MySQL database:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

cursor = db.cursor()

Replace your_username, your_password, and mydatabase with your own credentials.


 4. SELECT All Rows

Use SELECT * FROM table_name to retrieve all rows from a table.

cursor.execute("SELECT * FROM users")
results = cursor.fetchall()

for row in results:
    print(row)

5. SELECT Specific Columns

Select only certain fields (columns) for better performance:

cursor.execute("SELECT name, email FROM users")
results = cursor.fetchall()

for name, email in results:
    print(f"Name: {name}, Email: {email}")

6. Use WHERE Clause to Filter

Use the WHERE clause to filter your data:

sql = "SELECT * FROM users WHERE age > %s"
val = (25,)

cursor.execute(sql, val)
results = cursor.fetchall()

for row in results:
    print(row)

Use %s placeholders to prevent SQL injection.


7. Fetch One or All Results

  • fetchone() returns the first row of the result.

  • fetchall() returns all rows as a list.

cursor.execute("SELECT * FROM users")
first_row = cursor.fetchone()
print("First Row:", first_row)

8. Loop Through Results

You can loop through the results easily using a for loop:

cursor.execute("SELECT * FROM users")

for (id, name, email, age) in cursor:
    print(f"{id}: {name} ({email}), Age: {age}")

9. Full Working Example

import mysql.connector
from mysql.connector import Error

def select_users():
    try:
        db = mysql.connector.connect(
            host="localhost",
            user="root",
            password="your_password",
            database="mydatabase"
        )

        cursor = db.cursor()

        # Select users older than 25
        sql = "SELECT id, name, email FROM users WHERE age > %s"
        val = (25,)
        cursor.execute(sql, val)

        results = cursor.fetchall()
        print("Users older than 25:")
        for row in results:
            print(row)

    except Error as e:
        print("Error:", e)

    finally:
        if db.is_connected():
            db.close()
            print("Database connection closed.")

# Run the function
select_users()

⚠️ 10. Common Pitfalls and Tips

Issue Solution
fetchone() returns None No results matched the query
SQL error Check for typos in SQL syntax
Incorrect number of placeholders Make sure the tuple length matches placeholders
Data not retrieved Ensure the table has data before selecting

✅ Tips

  • Always use parameterized queries (with %s) to protect against SQL injection.

  • Use LIMIT in your queries to restrict large result sets.

  • Don’t forget to close the database connection in a finally block.

  • Fetch only what you need to optimize performance.


Summary Table

Task Method
Select all rows SELECT * FROM users
Select specific columns SELECT name, email FROM users
Filter results SELECT * FROM users WHERE age > %s
Fetch all rows cursor.fetchall()
Fetch one row cursor.fetchone()

Final Thoughts

Using Python to select data from a MySQL database is efficient and scalable with the help of the mysql-connector-python library. Whether you're fetching all rows, filtering with conditions, or selecting specific columns, Python makes it easy to interact with your data securely and efficiently.