Summary: in this tutorial, you will learn how to update data in a PostgreSQL table in Python using psycopg database adapter.
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.
1 | 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.
1 | cur = conn.cursor() |
Then, execute the UPDATE statement with the input values by calling the execute() method of the cursor object.
1 | 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.
1 | conn.commit() |
Finally, close the communication with the PostgreSQL database server by calling the close() method of the cursor and connection objects.
1 2 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | #!/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 |
Before testing the function, we query data from the vendors table as follows:
1 2 3 4 5 | suppliers=# SELECT * FROM vendors WHERE vendor_id = 1; vendor_id | vendor_name -----------+------------- 1 | 3M Co. (1 row) |
Now we run the Python program to update the name of the vendor id 1 and query data from the vendors table again to verify the changes made by the Python program.
1 2 3 4 5 | suppliers=# 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 using psycopg database adapter.