Python PostgreSQL: 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)
);
Code language: SQL (Structured Query Language) (sql)

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

How it works

Step 1: Import the psycopg library

import psycopgCode language: Python (python)

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

Step 2: Import the database configuration

from config import configCode language: Python (python)

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

{
    "host": "localhost",
    "dbname": "pyinventory",
    "user": "postgres",
    "password": ""
}Code language: Python (python)

Step 3: Define the SQL UPDATE statement

sql = """
UPDATE products
SET price = %s, safety_stock = %s
WHERE product_id = %s
"""Code language: Python (python)

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

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

price → 1099.99
safety_stock → 12
product_id → 1

So this updates the product whose product_id is 1.

Step 5: Start error handling

try:Code language: Python (python)

We use a try block to catch any database errors.

Step 6: Open a database connection

with psycopg.connect(**config) as conn:Code language: Python (python)

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

We create a cursor to execute the UPDATE statement.

Step 8: Execute the UPDATE statement

cur.execute(sql, data)Code language: Python (python)

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

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

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

How it works

Step 1. Import required modules

import psycopg
from config import configCode language: Python (python)

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

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

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

This creates a cursor that executes the UPDATE statement.

Step 6. Execute multiple updates

cur.executemany(sql, products)Code language: Python (python)

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

This displays the number of rows affected.

Step 8. Handle errors

except Exception as e:
    print(f"Error: {e}")Code language: Python (python)

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

Output:

Updated row:
(1, 'iPhone 14 Pro', Decimal('1199.99'), 20, Decimal('0.24'))Code language: Python (python)

How it works

Step 1. Import the required modules

import psycopg
from config import configCode language: Python (python)

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

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

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

Step 5. Fetch the updated row

row = cur.fetchone()Code language: Python (python)

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

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

Step 7. Handle errors

except Exception as e:
    print(f"Error: {e}")Code language: Python (python)

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

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.

Was this tutorial helpful ?