This tutorial picks up from where theInserting Data Into Table Tutorial left off.

Steps for updating data in a PostgreSQL table from Python

To update data from a table in Python, you follow these steps:

• First,connect to the PostgreSQL server.
• Next, create acursor object from theconnection object.
• Then, execute anUPDATE statement by calling theexecute() method of thecursor object.
• After that, commit the changes by calling thecommit() method of theconnection object.
• Finally, optionally obtain the number of updated rows from therowcount property of thecursor object.

Updating data in a table example

We will use thevendors table in thesuppliers database for the demonstration:

1) Creating update.py module

Suppose a vendor changed its name, you need to reflect these changes in thevendors table.

To achieve this, you can define a functionupdate_vendor(), which updates the vendor name based on the vendor id.

First, create a new module calledupdate.py in the project directory.

Second, defineupdate_vendor() function in theupdate.py module:

import psycopg2from configimport load_configdef update_vendor(vendor_id, vendor_name):    """ Update vendor name based on the vendor id """    updated_row_count= 0    sql= """ UPDATE vendors                SET vendor_name =%s                WHERE vendor_id =%s"""    config= load_config()    try:        with  psycopg2.connect(**config) as conn:            with  conn.cursor() as cur:                # execute the UPDATE statement                cur.execute(sql, (vendor_name, vendor_id))                updated_row_count= cur.rowcount            # commit the changes to the database            conn.commit()    except (Exception, psycopg2.DatabaseError)as error:        print(error)    finally:        return updated_row_countif __name__ == '__main__':    update_vendor(1,"3M Corp")

2) Execute the update.py module

First, open the Command Prompt on Windows or Terminal on Unix-like systems.

Second, execute theupdate.py module:

python update.py

3) Verify the update

First, connect to the PostgreSQL server using thepsql client tool:

psql -U postgres

Second, change the current database tosuppliers:

\c suppliers

Third, retrieve data from thevendors table with the vendor id 1:

SELECT  *FROM  vendorsWHERE  vendor_id = 1;

Output:

vendor_id | vendor_name-----------+-------------         1 | 3M Corp(1 row)

The name of the vendor id 1 has been changed as expected.

Download the project source code

Summary

• Use theexecute() method of acursor object to execute anUPDATE statement that updates data in a table

Last updated onMay 19, 2024

On this page

• Steps for updating data in a PostgreSQL table from Python
• Updating data in a table example
• Summary