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’)]
