July 31, 2023
Summary: in this tutorial, you will learn how to update data in a PostgreSQL table from a Python program.
Table of Contents
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)
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()
Then, execute the UPDATE statement with the input values by calling the execute()
method of the cursor
object.
cur.execute(update_sql, (value1,value2))
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()
Finally, close the communication with the PostgreSQL database server by calling the close()
method of the cursor and connection objects.
cur.close()
conn.close()
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")
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;
vendor_id | vendor_name
-----------+-------------
1 | 3M Co.
(1 row)
Third, launch another terminal and run the Python program to update the name of the vendor id 1:
python update.py
Finally, from the psql program, query data from the vendors
table to verify the update.
SELECT * FROM vendors WHERE vendor_id = 1;
vendor_id | vendor_name
-----------+-------------
1 | 3M Corp
(1 row)
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.