Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Python PostgreSQL – Update Table

  • Last Updated : 31 Aug, 2021

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

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course

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 module
import psycopg2
  
# establishing the connection
conn = psycopg2.connect(
   database="postgres",
    user='postgres',
    password='password',
    host='localhost',
    port= '5432'
)
  
# creating a curssor object
cursor = conn.cursor()
  
# query to update table with where clause 
sql='''update Geeks set state='Kashmir'; '''
  
# execute the query
cursor.execute(sql)
print('table updated..')
  
print('table after updation...')
sql2='''select * from Geeks;'''
cursor.execute(sql2);
  
# print table after modification
print(cursor.fetchall())
  
# Commit your changes in the database
conn.commit()
  
# Closing the connection
conn.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 module
import psycopg2
  
# establishing the connection
conn = psycopg2.connect(
   database="postgres",
    user='postgres',
    password='password',
    host='localhost',
    port= '5432'
)
  
# create a cursor object
cursor = conn.cursor()
  
# query to update table
sql='''update Geeks set state='Delhi' where id='2'; '''
  
# execute the qury
cursor.execute(sql)
print("Table updated..")
  
print('Table after updation...')
  
# query to display Geeks table
sql2='select * from Geeks;'
  
# execute query
cursor.execute(sql2);
  
# fetching all details
print(cursor.fetchall());
  
# Commit your changes in the database
conn.commit()
  
# Closing the connection
conn.close()

Output:

Table updated..
Table after updation...
[(1, 'Babita', 'Bihar'), (3, 'Anamika', 'Banglore'), 
(4, 'Sanaya', 'Pune'), (5, 'Radha', 'Chandigarh'),
 (2, 'Anushka', 'Delhi')]



My Personal Notes arrow_drop_up
Recommended Articles
Page :