Updating records in a database is a common operation in web and data applications. In PostgreSQL, the UPDATE statement is used to modify existing records. In this tutorial, you’ll learn how to execute UPDATE queries in PostgreSQL using Python with the help of the psycopg2 library.

Table of Contents

1. What You’ll Learn

2. Prerequisites

3. PostgreSQL UPDATE Syntax

4. Updating a Record in Python

5. Using Placeholders and Parameters

6. Updating Multiple Records

7. Full Example

8. Tips and Common Pitfalls

1. What You’ll Learn

• How to use the UPDATE statement in PostgreSQL

• How to execute UPDATE using Python’s psycopg2

• How to avoid SQL injection using parameters

• How to update multiple records at once

• How to verify and commit your changes

✅ 2. Prerequisites

Ensure you have the following before proceeding:

• PostgreSQL installed and running

• Python installed

• A PostgreSQL database and table to work with

• The psycopg2 library installed:

pip install psycopg2-binary

3. PostgreSQL UPDATE Syntax

The basic syntax in SQL:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

⚠️ Without a WHERE clause, all rows in the table will be updated!

4. Updating a Record in Python

Assume you have a table called students with columns: id, name, age, and grade.

Example: Update a student's grade

import psycopg2

conn = psycopg2.connect(
    dbname="school",
    user="postgres",
    password="your_password",
    host="localhost",
    port="5432"
)

cur = conn.cursor()

sql = "UPDATE students SET grade = %s WHERE name = %s"
cur.execute(sql, ('9th', 'Alice'))

conn.commit()
print("Record updated successfully.")

cur.close()
conn.close()

5. Using Placeholders and Parameters

To avoid SQL injection, always use %s placeholders in your query and pass values as a tuple.

✅ Good:

cur.execute("UPDATE students SET age = %s WHERE name = %s", (15, 'Bob'))

❌ Bad (vulnerable):

cur.execute(f"UPDATE students SET age = {age} WHERE name = '{name}'")

6. Updating Multiple Records

You can update multiple rows using a WHERE clause that matches more than one record.

cur.execute("UPDATE students SET grade = %s WHERE age >= %s", ('10th', 15))
conn.commit()

This will update all students who are 15 or older.

7. Full Working Example

import psycopg2

try:
    conn = psycopg2.connect(
        dbname="school",
        user="postgres",
        password="your_password",
        host="localhost",
        port="5432"
    )
    cur = conn.cursor()

    # Update student grade
    update_query = "UPDATE students SET grade = %s WHERE name = %s"
    cur.execute(update_query, ('10th', 'Carol'))
    conn.commit()

    print("Student record updated successfully.")

    # Confirm the update
    cur.execute("SELECT * FROM students WHERE name = %s", ('Carol',))
    record = cur.fetchone()
    print("Updated Record:", record)

    cur.close()
    conn.close()

except psycopg2.Error as e:
    print("Database error:", e)

8. Tips and Common Pitfalls

Conclusion

The UPDATE command in PostgreSQL is a powerful way to modify existing records. Using Python and psycopg2, you can update data efficiently and securely with full control over the process.