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 moduleimport psycopg2 # establishing the connectionconn = psycopg2.connect( database="postgres", user='postgres', password='password', host='localhost', port='5432') # creating cursor objectcursor = conn.cursor() # creating tablesql = '''CREATE TABLE Geeks( id SERIAL NOT NULL, name varchar(20) not null, state varchar(20) not null)'''cursor.execute(sql) # inserting values in the tablecursor.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 statesql2 = 'select * from Geeks order by state desc;'# executing querycursor.execute(sql2)# fetching recordsprint(cursor.fetchall()) # Commit your changes in the databaseconn.commit() # Closing the connectionconn.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 moduleimport psycopg2 # establishing the connectionconn = psycopg2.connect( database="postgres", user='postgres', password='password', host='localhost', port='5432') # creating cursor objectcursor = conn.cursor() # creating tablesql = '''CREATE TABLE Geeks( id SERIAL NOT NULL, name varchar(20) not null, state varchar(20) not null)'''cursor.execute(sql) # inserting values in the tablecursor.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 namesql2 = 'select * from Geeks order by name;'# executing querycursor.execute(sql2)# fetching recordsprint(cursor.fetchall()) # Commit your changes in the databaseconn.commit() # Closing the connectionconn.close() |
Output:
[(3, ‘Anamika’, ‘Banglore’), (2, ‘Anushka’, ‘Hyderabad’), (1, ‘Babita’, ‘Bihar’), (5, ‘Radha’, ‘Chandigarh’), (4, ‘Sanaya’, ‘Pune’)]
.png)