Python MySQL: Sorting Data with the ORDER BY Clause

Sorting data is an essential part of working with databases, especially when you’re looking for meaningful insights. The ORDER BY clause in MySQL allows you to organize your query results based on one or more columns. In this tutorial, we’ll cover how to use the ORDER BY clause in MySQL with Python.

At The Coding College, we focus on delivering easy-to-understand tutorials for developers of all levels.

Prerequisites

To follow this tutorial, you need:

  • Python Installed: Get the latest version at python.org.
  • MySQL Server Installed: Download it from MySQL official site.
  • MySQL Connector for Python: Install it using pip: 
pip install mysql-connector-python

How to Use ORDER BY in Python MySQL

The ORDER BY clause is used to sort data in ascending (default) or descending order. Let’s break it down step-by-step.

Step 1: Connect to the Database

Start by connecting to your MySQL database:

import mysql.connector

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

print("Connected to the database!")

Replace your_username, your_password, and your_database with your actual MySQL credentials.

Step 2: Execute an ORDER BY Query

Use the ORDER BY clause in a SQL query to sort data. For example, to sort users by their age in ascending order:

cursor = db.cursor()

sql = "SELECT * FROM users ORDER BY age"
cursor.execute(sql)

# Fetch and print results
results = cursor.fetchall()
for row in results:
    print(row)

Step 3: Sort Data in Descending Order

To sort data in descending order, use the DESC keyword:

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

# Fetch and print results
results = cursor.fetchall()
for row in results:
    print(row)

Step 4: Sort by Multiple Columns

You can sort data by multiple columns by separating them with commas. For example, sort by city (ascending) and age (descending):

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

# Fetch and print results
results = cursor.fetchall()
for row in results:
    print(row)

Full Python Code Example

Here’s the complete code for sorting data with the ORDER BY clause:

import mysql.connector

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

cursor = db.cursor()

# Example 1: Sort by age (ascending)
sql = "SELECT * FROM users ORDER BY age"
cursor.execute(sql)

print("Users sorted by age (ascending):")
results = cursor.fetchall()
for row in results:
    print(row)

# Example 2: Sort by age (descending)
sql = "SELECT * FROM users ORDER BY age DESC"
cursor.execute(sql)

print("\nUsers sorted by age (descending):")
results = cursor.fetchall()
for row in results:
    print(row)

# Example 3: Sort by city (ascending) and age (descending)
sql = "SELECT * FROM users ORDER BY city ASC, age DESC"
cursor.execute(sql)

print("\nUsers sorted by city (ascending) and age (descending):")
results = cursor.fetchall()
for row in results:
    print(row)

# Close the connection
db.close()

Exercises

Exercise 1: Sort Products by Price

Write a Python script to retrieve all products from a products table, sorted by price in descending order.

Exercise 2: Sort Customers by Name

Retrieve all customers from a customers table and sort them alphabetically by their names.

Exercise 3: Sort by Multiple Criteria

Fetch data from an orders table and sort it by order date (descending) and total price (ascending).

Why Learn ORDER BY?

Sorting your data is a vital skill for analyzing and presenting information effectively. Whether you’re working with user lists, sales reports, or any dataset, the ORDER BY clause ensures you can view data in the order that matters most.

Conclusion

The ORDER BY clause is a simple yet powerful tool for sorting data in MySQL. With Python, you can leverage this feature to organize query results efficiently. Remember to practice using ascending and descending orders, as well as multiple-column sorting.

Leave a Comment