Python MySQL ORDER BY

In this Python tutorial, we will see how to select data in mysql database using select with order by in an order through python.

SQL ORDER BY ASC/DESC

In sql, ORDER BY is used to sort the result set.

Sorting by default is in ascending order. It means if nothing is mentioned then result set will be sorted in ascending order.

Alternatively, you can also instruct to sort in ascending order by keyword ASC on ORDER BY.

To sort in descending order use keyword DESC.

Syntax for ORDER BY ASC

SELECT [*|columns]
FROM table_name
ORDER BY column1, column2,..., columnN ASC/DESC;

Now, let' see how to perform SELECT with ORDER BY clause in xampp server through python.

Before that make sure that you have created a database named - tutorialsinhand and table inside it market with the following records.

Steps:

1. Import mysql.connector module
    Syntax:
    import mysql.connector

2. Create the connection object to connect with xampp server using the below credentials.
    Syntax:
    connection_obj = mysql.connector.connect(
                          host="localhost",
                          user="root",
                          password=""
                                                                        )
3. Create cursor object to execute the query.
    Syntax:
    connection_obj.cursor()

4. Write sql query to select columns by applying ORDER BY clause from the table.
   
5. Fetch the resulted rows using fetchall().
    Syntax:
    cursor.fetchall()

6. Display using for loop 
     Syntax:
         for i in results:
            print(i)

Note - It will return the values in a tuple.

Example:

In this example, we are selecting all columns from market table and order the results by rating column in both ascending and descending order..

Hence the SQL query is

SELECT * FROM market ORDER BY rating ASC;

SELECT * FROM market ORDER BY rating DESC;

With Python Code:

#import the module
import mysql.connector

#create the connection by specifying the 
#hostname-localhost
#username-root
#password-''(empty) and
#database is tutorialsinhand
connection_obj = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database="tutorialsinhand"
)

#creating cursor object
cursor_obj = connection_obj.cursor()



#select all columns and order the result by rating column in ascending order
cursor_obj.execute("SELECT * FROM market  ORDER BY rating ASC")

#fetch the rows
results = cursor_obj.fetchall()

print("Ascending Order")

#get the values
for i in results:
  print(i)

print()

#select all columns and order the result by rating column in descending order
cursor_obj.execute("SELECT * FROM market  ORDER BY rating DESC")

#fetch the rows
results = cursor_obj.fetchall()
print("Descending Order")

#get the values
for i in results:
  print(i)

Output:

Ascending Order
('clothstore', 'bengal', 2)
('bakery', 'hyderabad', 5)
('hali', 'hyderabad', 7)
('koti-cente', 'kakumanu', 8)

Descending Order
('koti-cente', 'kakumanu', 8)
('hali', 'hyderabad', 7)
('bakery', 'hyderabad', 5)
('clothstore', 'bengal', 2)

Python MySql

Would you like to see your article here on tutorialsinhand. Join Write4Us program by tutorialsinhand.com

About the Author

Gottumukkala Sravan Kumar 171FA07058
B.Tech (Hon's) - IT from Vignan's University. Published 1200+ Technical Articles on Python, R, Swift, Java, C#, LISP, PHP - MySQL and Machine Learning

Page Views : Published Date : Sep 17,2022

Please Share this page