MySQL ORDER BY in Python

The ORDER BY clause in SQL is used to sort the results of a query by one or more columns. You can specify ascending (ASC) or descending (DESC) order. In Python, you can use the mysql-connector-python library to interact with MySQL and apply the ORDER BY clause to sort the query results.

1. Install MySQL Connector

If you haven't installed the MySQL connector, install it using:

pip install mysql-connector-python

2. Connect to MySQL Database

First, establish a connection to your MySQL database.

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase"  # Change to your database name)# Create a cursor objectcursor = conn.cursor()print("Connected to MySQL database successfully!")

3. Create Sample Table and Insert Data

Let's assume you have a table named employees with some data.

# Create employees tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    age INT,    department VARCHAR(50))""")# Insert sample data into employees tablecursor.execute("""INSERT INTO employees (name, age, department) VALUES ('Alice', 30, 'HR'), ('Bob', 25, 'IT'), ('Charlie', 28, 'Finance'),('David', 40, 'HR'),('Eve', 35, 'IT')""")conn.commit()

4. Using ORDER BY to Sort Results

You can use the ORDER BY clause to sort your results. By default, ORDER BY sorts in ascending order (ASC), but you can specify DESC for descending order.

4.1 Sort by One Column (Ascending Order)

To sort by a specific column, such as age, in ascending order:

# SQL query to order by age in ascending orderorder_query = "SELECT * FROM employees ORDER BY age ASC"cursor.execute(order_query)# Fetch and print the resultsresults = cursor.fetchall()print("Employees sorted by age (ascending):")for row in results:    print(row)

4.2 Sort by One Column (Descending Order)

To sort by age in descending order:

# SQL query to order by age in descending orderorder_query_desc = "SELECT * FROM employees ORDER BY age DESC"cursor.execute(order_query_desc)# Fetch and print the resultsresults = cursor.fetchall()print("Employees sorted by age (descending):")for row in results:    print(row)

4.3 Sort by Multiple Columns

You can also sort by multiple columns. For example, you can first sort by department in ascending order, and then by age in descending order:

# SQL query to order by department and then by ageorder_query_multiple = "SELECT * FROM employees ORDER BY department ASC, age DESC"cursor.execute(order_query_multiple)# Fetch and print the resultsresults = cursor.fetchall()print("Employees sorted by department (ascending) and age (descending):")for row in results:    print(row)

5. Closing the Connection

After performing your operations, close the connection.

cursor.close()conn.close()print("MySQL connection closed.")

Final Complete Code

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase")cursor = conn.cursor()# Create employees tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    age INT,    department VARCHAR(50))""")# Insert sample data into employees tablecursor.execute("""INSERT INTO employees (name, age, department) VALUES ('Alice', 30, 'HR'), ('Bob', 25, 'IT'), ('Charlie', 28, 'Finance'),('David', 40, 'HR'),('Eve', 35, 'IT')""")conn.commit()# Sort by age in ascending orderorder_query = "SELECT * FROM employees ORDER BY age ASC"cursor.execute(order_query)print("Employees sorted by age (ascending):")results = cursor.fetchall()for row in results:    print(row)# Sort by age in descending orderorder_query_desc = "SELECT * FROM employees ORDER BY age DESC"cursor.execute(order_query_desc)print("\nEmployees sorted by age (descending):")results = cursor.fetchall()for row in results:    print(row)# Sort by department (ascending) and age (descending)order_query_multiple = "SELECT * FROM employees ORDER BY department ASC, age DESC"cursor.execute(order_query_multiple)print("\nEmployees sorted by department (ascending) and age (descending):")results = cursor.fetchall()for row in results:    print(row)# Close connectioncursor.close()conn.close()

Summary of ORDER BY

This is how you can use the ORDER BY clause in MySQL queries using Python! ?