When retrieving data from a MySQL database, you often want it sorted—by name, date, price, or any other column. The ORDER BY clause in SQL allows you to do just that. In this tutorial, you’ll learn how to use ORDER BY in Python using the mysql-connector-python library to sort your query results.

Table of Contents

1. What is ORDER BY?

2. Prerequisites

3. Install MySQL Connector

4. Connect to MySQL Using Python

5. Use ORDER BY to Sort Results (Ascending)

6. Use ORDER BY to Sort Results (Descending)

7. Sort by Multiple Columns

8. Full Working Example

9. Tips and Common Pitfalls

✅ 1. What is ORDER BY?

The ORDER BY clause in SQL is used to sort the result set by one or more columns.

Syntax:

SELECT * FROM table_name ORDER BY column_name [ASC|DESC];

• ASC (default): Sorts in ascending order.

• DESC: Sorts in descending order.

⚙️ 2. Prerequisites

Before you begin, make sure you have:

• Python installed

• A MySQL server with a database and table

• The mysql-connector-python library installed

• Some data in your MySQL table

3. Install MySQL Connector

Install the official MySQL connector using pip:

pip install mysql-connector-python

4. Connect to MySQL Using Python

import mysql.connector

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

cursor = db.cursor()

Replace the placeholders with your actual MySQL credentials.

5. Use ORDER BY to Sort Results in Ascending Order

cursor.execute("SELECT * FROM users ORDER BY name ASC")
results = cursor.fetchall()

for row in results:
    print(row)

This sorts all users alphabetically by name (A to Z).

6. Use ORDER BY to Sort Results in Descending Order

cursor.execute("SELECT * FROM users ORDER BY age DESC")
results = cursor.fetchall()

for row in results:
    print(row)

This will list users by age, from oldest to youngest.

7. Sort by Multiple Columns

You can sort by multiple fields, such as by city first, then by age:

cursor.execute("SELECT * FROM users ORDER BY city ASC, age DESC")
results = cursor.fetchall()

for row in results:
    print(row)

This means:

• First, users are sorted by city (A–Z)

• Within each city, they are sorted by age (highest to lowest)

8. Full Working Example

import mysql.connector
from mysql.connector import Error

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

        cursor = db.cursor()

        # Sort users by age descending
        sql = "SELECT id, name, age, city FROM users ORDER BY age DESC"
        cursor.execute(sql)

        results = cursor.fetchall()

        print("Users sorted by age (descending):")
        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
sort_users()

⚠️ 9. Tips and Common Pitfalls

✅ Tips

• ORDER BY column_name ASC is the same as just ORDER BY column_name

• Use LIMIT with ORDER BY to get top or bottom N results

• Always use proper column names and double-check spelling

• Avoid using raw user input directly in ORDER BY clauses

Summary Table

Final Thoughts

The ORDER BY clause is a powerful SQL tool that allows you to control how your data is displayed. When paired with Python and MySQL, it helps you present sorted and meaningful output to your users or applications. Remember to sort only what you need and use placeholders properly to keep queries secure and efficient.