Python MySQL Tutorial – How to Use ORDER BY to Sort Data

Last updated 2 weeks, 1 day ago | 35 views 75     5

Tags:- MySQL Python

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

Pitfall Solution
Not seeing sorted output Make sure the SQL query includes ORDER BY
Sorting by column not in SELECT Include the column in your SELECT or sort by index
Case-sensitive sort behavior Use COLLATE in SQL if needed (e.g., ORDER BY name COLLATE utf8_general_ci)
Using input values in ORDER BY Don't use user input directly; validate it first to prevent SQL injection

✅ 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

Task SQL Example
Sort by name (A–Z) ORDER BY name ASC
Sort by age (high to low) ORDER BY age DESC
Sort by city then age ORDER BY city ASC, age DESC

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.

 

Tips and Tricks


What is pass in Python?

Python | Pass Statement

The pass statement is used as a placeholder for future code. It represents a null operation in Python. It is generally used for the purpose of filling up empty blocks of code which may execute during runtime but has yet to be written.

 

def myfunction():
    pass

 


How can you generate random numbers?

Python | Generate random numbers

Python provides a module called random using which we can generate random numbers. e.g: print(random.random())

 

 

We have to import a random module and call the random() method as shown below:

 import random

 print(random.random())

The random() method generates float values lying between 0 and 1 randomly.


To generate customized random numbers between specified ranges, we can use the randrange() method
Syntax: randrange(beginning, end, step)
 

import random

print(random.randrange(5,100,2))

 


What is lambda in Python?

Python | Lambda function

A lambda function is a small anonymous function. This function can have any number of parameters but, can have just one statement.
 

 

Syntex: 
lambda arguments : expression
 

a = lambda x,y : x+y

print(a(5, 6))

It also provides a nice way to write closures. With that power, you can do things like this.

def adder(x):
    return lambda y: x + y

add5 = adder(5)

add5(1)    #6

As you can see from the snippet of Python, the function adder takes in an argument x and returns an anonymous function, or lambda, that takes another argument y. That anonymous function allows you to create functions from functions. This is a simple example, but it should convey the power lambdas and closures have.
 


What is swapcase() function in the Python?

Python | swapcase() Function

It is a string's function that converts all uppercase characters into lowercase and vice versa. It automatically ignores all the non-alphabetic characters.
 

string = "IT IS IN LOWERCASE."  

print(string.swapcase())  

 


How to remove whitespaces from a string in Python?

Python | strip() Function | Remove whitespaces from a string 

To remove the whitespaces and trailing spaces from the string, Python provides a strip([str]) built-in function. This function returns a copy of the string after removing whitespaces if present. Otherwise returns the original string.
 

string = "  Python " 
 
print(string.strip())  

 


What is the usage of enumerate() function in Python?

Python | enumerate() Function

The enumerate() function is used to iterate through the sequence and retrieve the index position and its corresponding value at the same time.
 

lst = ["A","B","C"] 
 
print (list(enumerate(lst)))

#[(0, 'A'), (1, 'B'), (2, 'C')]

 


Can you explain the filter(), map(), and reduce() functions?

Python | filter(), map(), and reduce() Functions

  • filter()  function accepts two arguments, a function and an iterable, where each element of the iterable is filtered through the function to test if the item is accepted or not.
    >>> set(filter(lambda x:x>4, range(7)))
    
    # {5, 6}
    
    

     

  • map() function calls the specified function for each item of an iterable and returns a list of result

    >>> set(map(lambda x:x**3, range(7)))
    
    # {0, 1, 64, 8, 216, 27, 125}

     

  • reduce() function reduces a sequence pair-wise, repeatedly until we arrive at a single value..
     

    >>> reduce(lambda x,y:y-x, [1,2,3,4,5])
    
    # 3
    

    Let’s understand this:

    2-1=1
    3-1=2
    4-2=2
    5-2=3

    Hence, 3.

 


What is a namedtuple?

Python | namedtuple

A namedtuple will let us access a tuple’s elements using a name/label. We use the function namedtuple() for this, and import it from collections.

>>> from collections import namedtuple

#format
>>> result=namedtuple('result','Physics Chemistry Maths') 

#declaring the tuple
>>> Chris=result(Physics=86,Chemistry=92,Maths=80) 

>>> Chris.Chemistry
# 92

 


Write a code to add the values of same keys in two different dictionaries and return a new dictionary.

We can use the Counter method from the collections module

from collections import Counter

dict1 = {'a': 5, 'b': 3, 'c': 2}
dict2 = {'a': 2, 'b': 4, 'c': 3}

new_dict = Counter(dict1) + Counter(dict2)


print(new_dict)
# Print: Counter({'a': 7, 'b': 7, 'c': 5})


 


Python In-place swapping of two numbers

 Python | In-place swapping of two numbers

>>> a, b = 10, 20
>>> print(a, b)
10 20

>>> a, b = b, a
>>> print(a, b)
20 10

 


Reversing a String in Python

Python | Reversing a String

>>> x = 'PythonWorld'
>>> print(x[: : -1])
dlroWnohtyP

 


Python join all items of a list to convert into a single string

Python | Join all items of a list to convert into a single string

>>> x = ["Python", "Online", "Training"]
>>> print(" ".join(x))
Python Online Training

 


python return multiple values from functions

Python | Return multiple values from functions

>>> def A():
	return 2, 3, 4

>>> a, b, c = A()

>>> print(a, b, c)
2 3 4

 


Python Print String N times

Python | Print String N times

>>> s = 'Python'
>>> n = 5

>>> print(s * n)
PythonPythonPythonPythonPython

 


Python check the memory usage of an object

Python | Check the memory usage of  an object

>>> import sys
>>> x = 100

>>> print(sys.getsizeof(x))
28