PostgreSQL Python: Update Data in a Table

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 psycopg module.

conn = psycopg2.connect(dns)
Code language: Python (python)

The connect() method returns a new connection object.

Next, create a new cursor object by calling the cursor() method of the connection object.

cur = conn.cursor()
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Then, execute the UPDATE statement with the input values by calling the execute() method of the cursor object.

cur.execute(update_sql, (value1,value2))
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 UPDATE statement.

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 execute() method.

After that, save the changes to the database permanently by calling the commit() method of the connection object.

conn.commit()
Code language: Python (python)

Finally, close the communication with the PostgreSQL database server by calling the close() method of the cursor and connection objects.

cur.close()
conn.close()
Code language: Python (python)

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")
Code language: Python (python)

First, log in to the suppliers database on the PostgreSQL database server using psql.

Second, query data from the vendors table:

SELECT * FROM vendors WHERE vendor_id = 1;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

 vendor_id | vendor_name
-----------+-------------
         1 | 3M Co.
(1 row)
Code language: Shell Session (shell)

Third, launch another terminal and run the Python program to update the name of the vendor id 1:

python update.py
Code language: Shell Session (shell)

Finally, from the psql program, query data from the vendors table to verify the update.

SELECT * FROM vendors WHERE vendor_id = 1;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

 vendor_id | vendor_name
-----------+-------------
         1 | 3M Corp
(1 row)
Code language: Shell Session (shell)

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.