Python SQLite – ORDER BY Clause

The `ORDER BY` clause in SQLite is used to sort the result set returned by a `SELECT` query based on one or more columns. It allows you to specify the order in which the rows should be displayed. In Python, you can use the `ORDER BY` clause in conjunction with SQL `SELECT` statements when querying data from SQLite database tables.

Here’s how to use the `ORDER BY` clause to sort rows in SQLite using Python:

1. Establish Connection: First, establish a connection to the SQLite database file using the `connect()` function from the `sqlite3` module.

2. Create Cursor: After establishing the connection, create a cursor object using the `cursor()` method.

3. Execute SQL Statement: Use the cursor’s `execute()` method to execute an SQL `SELECT` statement with an `ORDER BY` clause. Specify the column(s) to sort by in the `ORDER BY` clause.

4. Fetch Data: After executing the SQL statement, fetch the sorted data from the cursor using one of the fetch methods (`fetchone()`, `fetchall()`, or `fetchmany()`).

5. Process Data: Process the fetched data as needed.

6. Close Cursor and Connection: Finally, close the cursor and connection using the `close()` method to release any resources associated with them.

Here’s an example of how to use the `ORDER BY` clause to sort rows from a table named `employees` by the `salary` column in descending order:

“`python
import sqlite3

# Establish connection to SQLite database
connection = sqlite3.connect(‘example.db’)

# Create a cursor object
cursor = connection.cursor()

# Execute SQL statement with ORDER BY clause
cursor.execute(‘SELECT * FROM employees ORDER BY salary DESC’)

# Fetch all sorted data
rows = cursor.fetchall()

# Process fetched data
for row in rows:
print(row)

# Close cursor and connection
cursor.close()
connection.close()
“`

In this example:

– We establish a connection to an SQLite database file named `example.db`.
– We create a cursor object using the `cursor()` method.
– We execute an SQL `SELECT * FROM employees ORDER BY salary DESC` statement with an `ORDER BY` clause that specifies sorting by the `salary` column in descending order.
– We fetch all sorted data using the `fetchall()` method and store it in the `rows` variable.
– We iterate over the rows and print each row.
– Finally, we close the cursor and connection.