Of course! Using a while loop with a MySQL database in Python is a common task, especially when you don't know exactly how many rows a query will return. It's the fundamental way to fetch data row by row from a cursor.

Let's break it down, from the basic concept to a complete, practical example.

The Core Concept: The Cursor

When you execute a query in Python using a library like mysql-connector-python, you don't get all the results at once. Instead, you get a cursor object. Think of the cursor as a pointer to a single row in the result set.

A while loop is used to:

1. Tell the cursor to fetch the next row.
2. Check if a row was actually returned (i.e., we haven't reached the end of the result set).
3. If a row was returned, process it.
4. Repeat until the cursor returns nothing, signaling the end of the data.

The Basic Pattern

This is the standard and most common way to use a while loop with a MySQL cursor.

import mysql.connector
# --- 1. Establish Database Connection ---
# Replace with your actual database credentials
config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': '127.0.0.1',  # or 'localhost'
    'database': 'your_database'
}
try:
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()
    # --- 2. Execute Your SQL Query ---
    query = "SELECT id, name, email FROM users;"
    cursor.execute(query)
    # --- 3. The WHILE LOOP to fetch rows ---
    # The loop continues as long as cursor.fetchone() returns a row.
    # When it reaches the end, fetchone() returns None, and the loop stops.
    while row := cursor.fetchone():
        # 'row' is a tuple, e.g., (1, 'Alice', 'alice@example.com')
        user_id, name, email = row  # Unpack the tuple for clarity
        print(f"ID: {user_id}, Name: {name}, Email: {email}")
except mysql.connector.Error as err:
    print(f"Error: {err}")
finally:
    # --- 4. Close the Cursor and Connection ---
    # This is crucial to free up resources.
    if 'conn' in locals() and conn.is_connected():
        if 'cursor' in locals():
            cursor.close()
        conn.close()
        print("\nMySQL connection is closed.")

Key Parts of the Pattern:

• cursor.execute(query): Sends the SQL command to the database.

• while row := cursor.fetchone():: This is the magic.

  • cursor.fetchone(): This method fetches the next single row from the result set. If there are no more rows, it returns None.
  • This is the walrus operator (introduced in Python 3.8). It assigns the result of cursor.fetchone() to the variable row and then evaluates the expression. The loop continues as long as row is not None.

• Alternative (for older Python versions): If you're using Python < 3.8, you can do this:

  row = cursor.fetchone()
  while row is not None:
      # ... process row ...
      user_id, name, email = row
      print(f"ID: {user_id}, Name: {name}, Email: {email}")
      # IMPORTANT: You must fetch the next row inside the loop
      row = cursor.fetchone()

• row: Each row is a tuple containing the values for that specific row in the order they were selected in your SELECT statement.

Complete Practical Example

Let's create a full, runnable example.

Setup

First, make sure you have the library installed:

pip install mysql-connector-python

Create a database and a table. You can do this from a MySQL client or with Python.

-- In your MySQL client (e.g., MySQL Workbench, DBeaver, or command line)
CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);
-- Insert some sample data
INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Alice', 'Williams', 'Engineering', 95000.00),
('Bob', 'Johnson', 'Marketing', 65000.00),
('Charlie', 'Brown', 'Engineering', 110000.00),
('Diana', 'Miller', 'Sales', 72000.00),
('Eve', 'Davis', 'HR', 58000.00);

Python Script (fetch_employees.py)

This script connects to the database, fetches all employees from the 'Engineering' department, and prints their details using a while loop.

import mysql.connector
from mysql.connector import Error
def get_employees_by_department(dept_name):
    """
    Connects to a MySQL database and fetches all employees from a specific department
    using a while loop to process the results row by row.
    """
    connection = None
    cursor = None
    try:
        # Establish the connection
        connection = mysql.connector.connect(
            host='localhost',
            database='company_db',
            user='your_username',  # <-- IMPORTANT: Replace with your MySQL username
            password='your_password'  # <-- IMPORTANT: Replace with your MySQL password
        )
        if connection.is_connected():
            cursor = connection.cursor()
            # The SQL query with a placeholder for the department name
            sql_query = "SELECT id, first_name, last_name, salary FROM employees WHERE department = %s"
            # Execute the query, passing the department as a parameter
            # This is the secure way to prevent SQL injection!
            cursor.execute(sql_query, (dept_name,))
            print(f"Fetching employees from the '{dept_name}' department...\n")
            # The WHILE LOOP to process each row
            while row := cursor.fetchone():
                # Unpack the tuple into individual variables
                emp_id, first, last, salary = row
                # You can do anything with the data here, like calculations or formatting
                print(f"  - ID: {emp_id}, Name: {first} {last}, Salary: ${salary:,.2f}")
            if cursor.rowcount == 0:
                print(f"No employees found in the '{dept_name}' department.")
    except Error as e:
        print(f"Error while connecting to MySQL or fetching data: {e}")
    finally:
        # Closing the cursor and connection in the 'finally' block
        # ensures they are always closed, even if an error occurs.
        if connection and connection.is_connected():
            if cursor:
                cursor.close()
            connection.close()
            print("\nMySQL connection is closed.")
# --- Run the function ---
if __name__ == "__main__":
    get_employees_by_department('Engineering')

Expected Output

When you run the script, you will see:

Fetching employees from the 'Engineering' department...
  - ID: 1, Name: Alice Williams, Salary: $95,000.00
  - ID: 3, Name: Charlie Brown, Salary: $110,000.00
MySQL connection is closed.

Alternative: fetchall() vs. while loop with fetchone()

It's useful to know the alternatives to understand when to use a while loop.

Example of fetchall():

# ... (connection and cursor setup) ...
cursor.execute("SELECT * FROM employees;")
all_employees = cursor.fetchall() # all_employees is now a list: [(1, 'Alice', ...), (2, 'Bob', ...)]
for employee in all_employees:
    print(employee) # Prints each tuple

Summary

• Use a while loop with cursor.fetchone() when you need to process a potentially large result set to avoid high memory consumption.
• The pattern while row := cursor.fetchone(): is modern, concise, and Pythonic (for Python 3.8+).
• Always unpack the row tuple for cleaner code (id, name, email = row).
• Always close your cursor and connection, preferably in a finally block to ensure resources are released.