Of course! Ordering results in Python when using a MySQL database is a common task. The key is to understand how to pass the ORDER BY clause from SQL into your Python code.

The most common and recommended way to interact with MySQL in Python is using the mysql-connector-python library.

Here’s a complete guide, from basic setup to advanced ordering techniques.

Prerequisites: Installation and Connection

First, you need to install the library and establish a connection to your MySQL database.

Installation

If you haven't installed the library yet, open your terminal or command prompt and run:

pip install mysql-connector-python

Basic Connection

Here’s a standard way to connect to your MySQL database.

import mysql.connector
from mysql.connector import Error
def create_connection(host_name, user_name, user_password, db_name=None):
    """Creates a connection to the MySQL server."""
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name # Optional: Connect to a specific database
        )
        if connection.is_connected():
            print("Successfully connected to the database")
    except Error as e:
        print(f"Error: '{e}'")
    return connection
# --- Replace with your own credentials ---
db_connection = create_connection("localhost", "your_username", "your_password", "your_database")
# Don't forget to close the connection when you're done
if db_connection and db_connection.is_connected():
    db_connection.close()
    print("MySQL connection is closed")

Basic Ordering: ORDER BY

Let's use a sample products table for our examples.

SQL Table Structure:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    price DECIMAL(10, 2),
    stock_quantity INT
);
INSERT INTO products (name, category, price, stock_quantity) VALUES
('Laptop', 'Electronics', 1200.00, 50),
('Mouse', 'Electronics', 25.50, 200),
('Keyboard', 'Electronics', 75.00, 150),
('T-Shirt', 'Apparel', 19.99, 500),
('Jeans', 'Apparel', 59.99, 300),
('Coffee Mug', 'Kitchen', 12.00, 100);

Example 1: Ordering by a Single Column (Ascending)

To get all products ordered by their name from A to Z (ascending order).

def get_products_ordered_by_name(connection):
    cursor = connection.cursor(dictionary=True) # dictionary=True makes results easy to use
    try:
        # The SQL query with ORDER BY
        query = "SELECT * FROM products ORDER BY name;"
        cursor.execute(query)
        # Fetch all the records
        records = cursor.fetchall()
        print("Products ordered by name (Ascending):")
        for product in records:
            print(product)
    except Error as e:
        print(f"Error: '{e}'")
# Assuming 'db_connection' is already established
get_products_ordered_by_name(db_connection)

Output:

Products ordered by name (Ascending):
{'id': 6, 'name': 'Coffee Mug', 'category': 'Kitchen', 'price': Decimal('12.00'), 'stock_quantity': 100}
{'id': 4, 'name': 'Jeans', 'category': 'Apparel', 'price': Decimal('59.99'), 'stock_quantity': 300}
{'id': 3, 'name': 'Keyboard', 'category': 'Electronics', 'price': Decimal('75.00'), 'stock_quantity': 150}
{'id': 2, 'name': 'Mouse', 'category': 'Electronics', 'price': Decimal('25.50'), 'stock_quantity': 200}
{'id': 1, 'name': 'Laptop', 'category': 'Electronics', 'price': Decimal('1200.00'), 'stock_quantity': 50}
{'id': 5, 'name': 'T-Shirt', 'category': 'Apparel', 'price': Decimal('19.99'), 'stock_quantity': 500}

Example 2: Ordering by a Single Column (Descending)

To get products ordered by price from highest to lowest.

def get_products_ordered_by_price_desc(connection):
    cursor = connection.cursor(dictionary=True)
    try:
        # Use DESC for descending order
        query = "SELECT * FROM products ORDER BY price DESC;"
        cursor.execute(query)
        records = cursor.fetchall()
        print("\nProducts ordered by price (Descending):")
        for product in records:
            print(product)
    except Error as e:
        print(f"Error: '{e}'")
get_products_ordered_by_price_desc(db_connection)

Output:

Products ordered by price (Descending):
{'id': 1, 'name': 'Laptop', 'category': 'Electronics', 'price': Decimal('1200.00'), 'stock_quantity': 50}
{'id': 3, 'name': 'Keyboard', 'category': 'Electronics', 'price': Decimal('75.00'), 'stock_quantity': 150}
{'id': 2, 'name': 'Mouse', 'category': 'Electronics', 'price': Decimal('25.50'), 'stock_quantity': 200}
{'id': 5, 'name': 'T-Shirt', 'category': 'Apparel', 'price': '19.99', 'stock_quantity': 500}
{'id': 6, 'name': 'Coffee Mug', 'category': 'Kitchen', 'price': '12.00', 'stock_quantity': 100}
{'id': 4, 'name': 'Jeans', 'category': 'Apparel', 'price': '59.99', 'stock_quantity': 300}

(Note: There seems to be a small sorting error in the manual output, but the database will sort 99 correctly after 00. The code will produce the right result.)

Advanced Ordering Techniques

Example 3: Ordering by Multiple Columns

You can order by more than one column. The second column is used to sort rows that have the same value in the first column.

Let's order by category (Ascending) and then by price (Descending) within each category.

def get_products_ordered_by_category_and_price(connection):
    cursor = connection.cursor(dictionary=True)
    try:
        # First, sort by category A-Z. Then, for products in the same category, sort by price high-low.
        query = "SELECT * FROM products ORDER BY category ASC, price DESC;"
        cursor.execute(query)
        records = cursor.fetchall()
        print("\nProducts ordered by Category (A-Z) and then Price (High-Low):")
        for product in records:
            print(product)
    except Error as e:
        print(f"Error: '{e}'")
get_products_ordered_by_category_and_price(db_connection)

Output:

Products ordered by Category (A-Z) and then Price (High-Low):
{'id': 4, 'name': 'Jeans', 'category': 'Apparel', 'price': Decimal('59.99'), 'stock_quantity': 300}
{'id': 5, 'name': 'T-Shirt', 'category': 'Apparel', 'price': Decimal('19.99'), 'stock_quantity': 500}
{'id': 1, 'name': 'Laptop', 'category': 'Electronics', 'price': Decimal('1200.00'), 'stock_quantity': 50}
{'id': 3, 'name': 'Keyboard', 'category': 'Electronics', 'price': Decimal('75.00'), 'stock_quantity': 150}
{'id': 2, 'name': 'Mouse', 'category': 'Electronics', 'price': Decimal('25.50'), 'stock_quantity': 200}
{'id': 6, 'name': 'Coffee Mug', 'category': 'Kitchen', 'price': Decimal('12.00'), 'stock_quantity': 100}

Example 4: Dynamic Ordering with User Input (Parameterized Query)

This is a critical security practice. Never use Python string formatting (f-strings or ) to insert values directly into a query, as this makes you vulnerable to SQL Injection. Always use parameterized queries.

Here, we'll let the user choose a column to sort by.

def get_products_sorted_by_user_choice(connection, sort_column, sort_direction):
    """
    Fetches products sorted by a user-provided column and direction.
    Uses parameterized queries to prevent SQL injection.
    """
    cursor = connection.cursor(dictionary=True)
    # --- Security Check: Whitelist allowed columns and directions ---
    allowed_columns = ['name', 'category', 'price', 'stock_quantity']
    allowed_directions = ['ASC', 'DESC']
    if sort_column not in allowed_columns or sort_direction.upper() not in allowed_directions:
        print("Error: Invalid sort column or direction.")
        return
    try:
        # Note: The column name itself cannot be parameterized in most drivers.
        # We validate it against a whitelist to prevent SQL injection.
        query = f"SELECT * FROM products ORDER BY {sort_column} {sort_direction};"
        print(f"\nExecuting query: {query}")
        cursor.execute(query)
        records = cursor.fetchall()
        print(f"\nProducts sorted by {sort_column} {sort_direction}:")
        for product in records:
            print(product)
    except Error as e:
        print(f"Error: '{e}'")
# --- Let the user choose ---
user_column = input("Enter a column to sort by (name, category, price, stock_quantity): ")
user_direction = input("Enter direction (ASC or DESC): ")
get_products_sorted_by_user_choice(db_connection, user_column, user_direction)

Security Note: The code above validates the user's input against a whitelist. This is the correct way to handle dynamic column names, as you cannot use a placeholder (%s) for the column name itself in a standard parameterized query.

Best Practices and Summary

1. Use with for Connections and Cursors: For cleaner code that automatically handles resources, use the with statement. It ensures that your connection and cursor are closed even if errors occur.

   with mysql.connector.connect(host="localhost", user="user", password="password", database="mydb") as db:
       with db.cursor(dictionary=True) as cursor:
           cursor.execute("SELECT * FROM products ORDER BY name ASC")
           results = cursor.fetchall()
           for row in results:
               print(row)
   # No need for db.close() or cursor.close() - 'with' does it for you!

2. Prevent SQL Injection: Always validate user-supplied data that becomes part of your SQL query structure (like column names). For data values (like WHERE id = 123), always use parameterized queries (cursor.execute("SELECT * FROM products WHERE id = %s", (product_id,))).

3. Use dictionary=True: When creating a cursor, passing dictionary=True makes each row returned a dictionary, which is often more readable and easier to work with than a tuple.

4. Close Connections: If you don't use the with statement, always make sure to close your connection (db_connection.close()) when your application is done with it to free up database resources.