Get Rows from Table Sorted in Order based on a Column in Python MySQL

Python MySQL - Get rows from table sorted in order based on a column

To fetch rows from a MySQL table, where the rows are sorted in order based on a column in Python program,

1. Create a connection to the MySQL database with user credentials and database name, using connect() function.
2. Get cursor object to the database using cursor() function.
3. Call execute() function on the cursor object, and pass the SELECT FROM table query with ORDER BY clause. This ORDER BY clause specifies the column(s) by which the records has to be ordered.
4. Call fetchall() function on the cursor object. The function returns an iterator to the rows.

Example

Consider that there is a schema named mydatabase in MySQL. The credentials to access this database are, user: root and password: admin1234, and there is a table named fruits in mydatabase.

In the following program, we fetch the rows from table with rows sorted in ascending order based on the column quantity.

Python Program

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="admin1234",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM fruits ORDER BY quantity")

myresult = mycursor.fetchall()

for row in myresult:
  print(row)

Output

('Mango', 15, 'India')
('Apple', 25, 'Canada')
('Avocado', 37, 'Mexico')
('Banana', 40, 'Mexico')

Summary

In this tutorial of Python Examples, we learned how to fetch the rows from a table in MySQL database, where the rows are ordered based on a column, from a Python program.