Python PostgreSQL – Order By

In this article, we will discuss how to use order by clause in PostgreSQL using python.

The Order By clause is used to sort the records of a table returned by the SELECT clause in ascending order by default, yet asc keyword can be used. If we want to sort the records in descending order then we have to write desc word. 

Syntax :

SELECT
    column1, column2, ....
FROM
    table_name
ORDER BY
    column1, colum2,.... [ASC | DESC]

Data in use:

To start, first, import all the required libraries into the working space and then establish the connection to the database.  Now initialize a cursor and pass the SQL statement to be executed. Print the result set generated and close the connection.

Example 1: Python code to display state name in descending order

Python3

# importing psycopg2 module
import psycopg2

# establishing the connection
conn = psycopg2.connect(
    database="postgres",
    user='postgres',
    password='password',
    host='localhost',
    port='5432'
)

# creating cursor object
cursor = conn.cursor()

# creating table
sql = '''CREATE TABLE Geeks(
 id  SERIAL NOT NULL,
 name varchar(20) not null,
 state varchar(20) not null
)'''
cursor.execute(sql)

# inserting values in the table
cursor.execute('''INSERT INTO Geeks(name , state) VALUES ('Babita','Bihar')''')
cursor.execute(
    '''INSERT INTO Geeks(name , state) VALUES ('Anushka','Hyderabad')''')
cursor.execute(
    '''INSERT INTO Geeks(name , state) VALUES ('Anamika','Banglore')''')
cursor.execute('''INSERT INTO Geeks(name , state) VALUES ('Sanaya','Pune')''')
cursor.execute(
    '''INSERT INTO Geeks(name , state) VALUES ('Radha','Chandigarh')''')


# query to sort table by descending order of state
sql2 = 'select * from Geeks order by state desc;'
# executing query
cursor.execute(sql2)
# fetching records
print(cursor.fetchall())

# Commit your changes in the database
conn.commit()

# Closing the connection
conn.close()

Output:

[(4, ‘Sanaya’, ‘Pune’), (2, ‘Anushka’, ‘Hyderabad’), (5, ‘Radha’, ‘Chandigarh’), (1, ‘Babita’, ‘Bihar’), (3, ‘Anamika’, ‘Banglore’)]

Example 2: Python code for displaying records of Geeks in ascending order of name

Python3

# importing psycopg2 module
import psycopg2

# establishing the connection
conn = psycopg2.connect(
    database="postgres",
    user='postgres',
    password='password',
    host='localhost',
    port='5432'
)

# creating cursor object
cursor = conn.cursor()

# creating table
sql = '''CREATE TABLE Geeks(
 id  SERIAL NOT NULL,
 name varchar(20) not null,
 state varchar(20) not null
)'''
cursor.execute(sql)

# inserting values in the table
cursor.execute('''INSERT INTO Geeks(name , state) VALUES ('Babita','Bihar')''')
cursor.execute(
    '''INSERT INTO Geeks(name , state) VALUES ('Anushka','Hyderabad')''')
cursor.execute(
    '''INSERT INTO Geeks(name , state) VALUES ('Anamika','Banglore')''')
cursor.execute('''INSERT INTO Geeks(name , state) VALUES ('Sanaya','Pune')''')
cursor.execute(
    '''INSERT INTO Geeks(name , state) VALUES ('Radha','Chandigarh')''')


# query to sort table by name
sql2 = 'select * from Geeks order by name;'
# executing query
cursor.execute(sql2)
# fetching records
print(cursor.fetchall())

# Commit your changes in the database
conn.commit()

# Closing the connection
conn.close()

Output:

[(3, ‘Anamika’, ‘Banglore’), (2, ‘Anushka’, ‘Hyderabad’), (1, ‘Babita’, ‘Bihar’), (5, ‘Radha’, ‘Chandigarh’), (4, ‘Sanaya’, ‘Pune’)]