July 31, 2023
Summary: in this tutorial, you will learn how to update data in a PostgreSQL table from a Python program.
Steps for updating data in a PostgreSQL table using psycopg2
The steps for updating data are similar to the steps for inserting data into a PostgreSQL table.
First, connect to the PostgreSQL database server by calling the
connect() function of the
conn = psycopg2.connect(dns)
connect() method returns a new
Next, create a new
cursor object by calling the
cursor() method of the
cur = conn.cursor()
Then, execute the UPDATE statement with the input values by calling the
execute() method of the
execute() method accepts two parameters. The first parameter is an SQL statement to be executed, in this case, it is the
UPDATE statement. The second parameter is a list of input values that you want to pass to the
If you want to get the number of rows affected by the
UPDATE statement, you can get it from the
rowcount attribute of the
cursor object after calling the
After that, save the changes to the database permanently by calling the
commit() method of the connection object.
Finally, close the communication with the PostgreSQL database server by calling the
close() method of the cursor and connection objects.
Updating data example
We will use the
vendors table in the
suppliers database that we created in the creating table tutorial for the sake of demonstration.
Suppose a vendor changed its name and we want to update the changes in the
vendors table. To do this, we develop the
update_vendor() function that updates the vendor name based on the vendor id:
#!/usr/bin/python import psycopg2 from config import config def update_vendor(vendor_id, vendor_name): """ update vendor name based on the vendor id """ sql = """ UPDATE vendors SET vendor_name = %s WHERE vendor_id = %s""" conn = None updated_rows = 0 try: # read database configuration params = config() # connect to the PostgreSQL database conn = psycopg2.connect(**params) # create a new cursor cur = conn.cursor() # execute the UPDATE statement cur.execute(sql, (vendor_name, vendor_id)) # get the number of updated rows updated_rows = cur.rowcount # Commit the changes to the database conn.commit() # Close communication with the PostgreSQL database cur.close() except (Exception, psycopg2.DatabaseError) as error: print(error) finally: if conn is not None: conn.close() return updated_rows if __name__ == '__main__': # Update vendor id 1 update_vendor(1, "3M Corp")
First, log in to the
suppliers database on the PostgreSQL database server using psql.
Second, query data from the
SELECT * FROM vendors WHERE vendor_id = 1; vendor_id | vendor_name -----------+------------- 1 | 3M Co. (1 row)
Third, launch another terminal and run the Python program to update the name of the vendor id 1:
Finally, from the psql program, query data from the
vendors table to verify the update.
SELECT * FROM vendors WHERE vendor_id = 1; vendor_id | vendor_name -----------+------------- 1 | 3M Corp (1 row)
The name of the vendor id 1 has been changed as expected.
In this tutorial, you have learned how to update data in a PostgreSQL database table from a Python program.