PostgreSQL Python: Update Data in a Table with psycopg3

Summary: In this tutorial, you’ll learn how to update one or more rows and return updated rows from a table using Psycopg 3.

The basic steps for updating data in a PostgreSQL table from Python are as follows:

• First, connect to PostgreSQL from Python.
• Next, create a Cursor object from the Connection object.
• Then, execute the UPDATE statement with values by calling the execute() or executemany() method of the Cursor object
• After that, save the changes automatically when the with block completes successfully.
• Finally, optionally retrieve the updated row using the RETURNING clause and fetchone().

Prerequisites #

This tutorial assumes you already have a products table like this in the pyinventory database:

CREATE TABLE IF NOT EXISTS products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    safety_stock INTEGER NOT NULL,
    gross_weight NUMERIC(10, 2)
);
Copy

We also assume the table already contains some data. If not, you can follow this tutorial to insert some data into the table.

Update one row in a table #

The following script updates the price and safety stock of one product:

import psycopg
from config import config

sql = """
UPDATE products
SET price = %s, safety_stock = %s
WHERE product_id = %s
"""

data = (1099.99, 12, 1)

try:
    with psycopg.connect(**config) as conn:
        with conn.cursor() as cur:
            cur.execute(sql, data)
            print(f"{cur.rowcount} row updated successfully.")
except Exception as e:
    print(f"Error: {e}")
Copy

How it works

Step 1: Import the psycopg library

import psycopgCopy

The psycopg module lets Python connect to PostgreSQL, execute SQL statements, and retrieve result.

Step 2: Import the database configuration

from config import configCopy

This imports the connection settings stored in config.py file as a dictionary like this:

{
    "host": "localhost",
    "dbname": "pyinventory",
    "user": "postgres",
    "password": ""
}Copy

Step 3: Define the SQL UPDATE statement

sql = """
UPDATE products
SET price = %s, safety_stock = %s
WHERE product_id = %s
"""Copy

This SQL statement updates values in the products table:

• UPDATE products selects the table products to update.
• SET price = %s, safety_stock = %s specifies the columns (price and safety stock) to change.
• WHERE product_id = %s identifies which row to update.

The %s placeholders let psycopg safely bind values in Python to SQL parameters.

Step 4: Prepare the data

data = (1099.99, 12, 1)Copy

This tuple matches the placeholders in the UPDATE statement in the following order:

1. price → 1099.99
2. safety_stock → 12
3. product_id → 1

So this updates the product whose product_id is 1.

Step 5: Start error handling

try:Copy

We use a try block to catch any database errors.

Step 6: Open a database connection

with psycopg.connect(**config) as conn:Copy

This creates a connection to PostgreSQL using the values in the config dictionary.

The with statement makes resource handling easier by:

• Closing the database connection automatically.
• Committing the transaction if everything succeeds.
• Rolling back the transaction if an error occurs.

Step 7: Create a cursor

with conn.cursor() as cur:Copy

We create a cursor to execute the UPDATE statement.

Step 8: Execute the UPDATE statement

cur.execute(sql, data)Copy

The execute() method sends the UPDATE statement and parameters to PostgreSQL.

Psycopg safely replaces the placeholders with the values in data.

Step 9: Check how many rows were updated

print(f"{cur.rowcount} row updated successfully.")Copy

The rowcount property of the cursor object stores the number of rows affected by the UPDATE statement.

Step 10: Handle errors

except Exception as e:
    print(f"Error: {e}")Copy

If an error occurs, Python jumps to the except block and displays the error message instead of crashing the program.

Update multiple rows in a table #

The following script updates multiple products using executemany():

import psycopg
from config import config

sql = """
UPDATE products
SET price = %s, safety_stock = %s
WHERE product_id = %s
"""

products = [
    (1149.99, 14, 1),
    (949.99, 9, 2),
]

try:
    with psycopg.connect(**config) as conn:
        with conn.cursor() as cur:
            cur.executemany(sql, products)
            print(f"{cur.rowcount} rows updated successfully.")
except Exception as e:
    print(f"Error: {e}")Copy

How it works

Step 1. Import required modules

import psycopg
from config import configCopy

Step 2. Define the SQL UPDATE statement

This UPDATE statement modifies the price and safety_stock columns for a product identified by product_id.

Step 3. Prepare multiple rows of data

products = [
    (1149.99, 14, 1),
    (949.99, 9, 2),
]Copy

The products variable stores a list of tuples representing rows to update. Each tuple represents one row:

• first value → new price
• second value → new safety_stock
• third value → target product_id

Step 4. Open the database connection

with psycopg.connect(**config) as conn:Copy

The connect() method creates a PostgreSQL connection. The with statement automatically handles commit or rollback.

Step 5. Create a cursor

with conn.cursor() as cur:Copy

This creates a cursor that executes the UPDATE statement.

Step 6. Execute multiple updates

cur.executemany(sql, products)Copy

The executemany() method runs the same UPDATE statement once for each tuple in the products list.

Step 7. Display the result

print(f"{cur.rowcount} rows updated successfully.")Copy

This displays the number of rows affected.

Step 8. Handle errors

except Exception as e:
    print(f"Error: {e}")Copy

If an error occurs, we display the message instead of letting the program crash.

Update a row and return it #

Sometimes, you may want to update a row and immediately get the new values back. In PostgreSQL, you can do that with the RETURNING clause.

The following script updates one row and returns the updated record:

import psycopg
from config import config

sql = """
UPDATE products
SET price = %s, safety_stock = %s
WHERE product_id = %s
RETURNING product_id, name, price, safety_stock, gross_weight
"""

data = (1199.99, 20, 1)

try:
    with psycopg.connect(**config) as conn:
        with conn.cursor() as cur:
            cur.execute(sql, data)
            row = cur.fetchone()

            if row:
                print("Updated row:")
                print(row)
            else:
                print("No row found to update.")
except Exception as e:
    print(f"Error: {e}")Copy

Output:

Updated row:
(1, 'iPhone 14 Pro', Decimal('1199.99'), 20, Decimal('0.24'))Copy

How it works

Step 1. Import the required modules

import psycopg
from config import configCopy

Step 2. Define the SQL statement

sql = """
UPDATE products
SET price = %s, safety_stock = %s
WHERE product_id = %s
RETURNING product_id, name, price, safety_stock, gross_weight
"""
Copy

This UPDATE statement does two things:

• Updates the price and safety stock of a product identified by product id.
• Returns the updated row immediately

The RETURNING clause is handy when you want to verify the result of the update.

Step 3. Prepare the input data

data = (1199.99, 20, 1)Copy

This updates:

• price to 1199.99
• safety_stock to 20
• for the product with product_id = 1

Step 4. Execute the update

cur.execute(sql, data)Copy

Step 5. Fetch the updated row

row = cur.fetchone()Copy

Since the query uses RETURNING, PostgreSQL sends the updated row back to Python. We use fetchone() method to read the result.

Step 6. Check whether a row was updated

if row:
    print("Updated row:")
    print(row)
else:
    print("No row found to update.")Copy

If no row matches the WHERE condition, fetchone() returns None.

Step 7. Handle errors

except Exception as e:
    print(f"Error: {e}")Copy

Important note about the WHERE clause #

If you forgot to include a WHERE clause in the UPDATE statement, like this:

UPDATE products
SET price = 999.99Copy

It’ll update every row in the products table.

In most cases, you should include a WHERE condition in the UPDATE statement, so you update only the intended rows.

Summary #

• Use psycopg.connect() to open a PostgreSQL connection.
• Use cursor() to execute an UPDATE statement.
• Use %s placeholders and pass values separately to keep your SQL safe.
• Use execute() to update one row.
• Use executemany() to update multiple rows.
• Use cur.rowcount to check how many rows were affected.
• Use RETURNING with fetchone() if you want the updated row back immediately.
• Use with blocks to manage connections and cursors automatically.
• Use try/except to catch errors and make debugging easier.