In this article, we will see how to Update data in PostgreSQL using python and Psycopg2. The update command is used to modify the existing record in the table. By default whole records of the specific attribute are modified, but to modify some particular row, we need to use the where clause along with the update clause.
Syntax for Update Clause
UPDATE table_name SET column1=value1,column2=value2,…
Table for demonstration:
Example 1: Update the columns of the table using Python – pscopg2
Here we are going to see how to update the columns of the table. The table after modification looks like the table shown below. As we can see for every tuple state value is changed to Kashmir.
Python3
# importing psycopg2 moduleimport psycopg2# establishing the connectionconn = psycopg2.connect( database="postgres", user='postgres', password='password', host='localhost', port= '5432')# creating a cursor objectcursor = conn.cursor()# query to update table with where clause sql='''update Geeks set state='Kashmir'; '''# execute the querycursor.execute(sql)print('table updated..')print('table after updation...')sql2='''select * from Geeks;'''cursor.execute(sql2);# print table after modificationprint(cursor.fetchall())# Commit your changes in the databaseconn.commit()# Closing the connectionconn.close()# code |
Output
table updated.. table after updation... [(1,'Babita','kashmir'),(2,'Anushka','Kashmir'),(3,'Anamika','Kashmir'), (4,'Sanaya','Kashmir'),(5,'Radha','Kashmir')]
Example 2: Update the columns using the where clause
Here we will use the where clause along with the update table.
Syntax: UPDATE Geeks set state=’Delhi’ where id=2;
We can see state of the row having id 2 is changed from Hyderabad to Delhi.
Python3
# importing psycopg2 moduleimport psycopg2# establishing the connectionconn = psycopg2.connect( database="postgres", user='postgres', password='password', host='localhost', port= '5432')# create a cursor objectcursor = conn.cursor()# query to update tablesql='''update Geeks set state='Delhi' where id='2'; '''# execute the querycursor.execute(sql)print("Table updated..")print('Table after updation...')# query to display Geeks tablesql2='select * from Geeks;'# execute querycursor.execute(sql2);# fetching all detailsprint(cursor.fetchall());# Commit your changes in the databaseconn.commit()# Closing the connectionconn.close() |
Output:
Table updated.. Table after updation... [(1, 'Babita', 'Bihar'), (3, 'Anamika', 'Banglore'), (4, 'Sanaya', 'Pune'), (5, 'Radha', 'Chandigarh'), (2, 'Anushka', 'Delhi')]
