Python MySQL Order By

To sort data from a MySQL table using Python, you can use the ORDER BY clause in your SQL query. The ORDER BY clause allows you to sort the result set by one or more columns in either ascending (ASC) or descending (DESC) order.

1. Install MySQL Connector

If you haven’t installed the MySQL connector yet, use this command:

pip install mysql-connector-python

2. Connect to the MySQL Database

You need to connect to the database and use the ORDER BY clause in your SQL queries.

Example Python Script to Select Data Using ORDER BY

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",         # Change this to your MySQL host if needed
  user="yourusername",       # Change this to your MySQL username
  password="yourpassword",   # Change this to your MySQL password
  database="mynewdatabase"   # Change this to your database name
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query to select data and order it by the 'name' column in ascending order
mycursor.execute("SELECT * FROM customers ORDER BY name ASC")

# Fetch all the rows
result = mycursor.fetchall()

# Print the result
for row in result:
    print(row)

Explanation:

• ORDER BY name ASC: Sorts the result set by the name column in ascending order (ASC). If you don’t specify ASC or DESC, it defaults to ascending.
• mycursor.fetchall(): Fetches all rows from the table and returns them sorted by the specified column.

3. Ordering in Descending Order

To sort the data in descending order, use DESC instead of ASC:

mycursor.execute("SELECT * FROM customers ORDER BY name DESC")

This will sort the result set by the name column in descending order.

4. Sorting by Multiple Columns

You can also sort by more than one column. For example, to sort first by name in ascending order and then by address in descending order:

mycursor.execute("SELECT * FROM customers ORDER BY name ASC, address DESC")

This query will sort the result set by name in ascending order and, for rows with the same name, by address in descending order.

5. Full Example: Sorting Data Using ORDER BY

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mynewdatabase"
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query to select and order the data by 'name' column in descending order
sql = "SELECT * FROM customers ORDER BY name DESC"

# Execute the query
mycursor.execute(sql)

# Fetch all the rows
result = mycursor.fetchall()

# Print the result
for row in result:
    print(row)

# Close the connection
mydb.close()

6. Sorting with Filtering (WHERE + ORDER BY)

You can combine the ORDER BY clause with a WHERE clause to filter and sort the results:

sql = "SELECT * FROM customers WHERE address = %s ORDER BY name DESC"
val = ("123 Elm Street",)

mycursor.execute(sql, val)

# Fetch all matching rows and sort them by 'name'
result = mycursor.fetchall()

for row in result:
    print(row)

7. Closing the Connection

Always close the connection after you’re done:

mydb.close()

Summary:

• Use ORDER BY to sort the result set by one or more columns.
• You can sort the results in ascending (ASC) or descending (DESC) order.
• Combine it with WHERE for filtered, ordered results.