Python is a popularly used programming language that is used across various fields/domains, such as Web development, artificial intelligence, machine learning, etc. While working with mega projects developers often require a proper database to securely store and retrieve data. In such cases, Postgres emerges as the developer's first choice. Connecting Python with Postgres allows users to create, search, update, or delete data easily and securely.

This post illustrates how to update a PostgreSQL table using Python.

How to Update/Modify a Postgres Table Using Python?

“psycopg2” is a widely used library that helps us in creating a connection to a PostgreSQL database using Python. Follow the provided instructions to update a Table in the PostgreSQL database using Python programming:

Prerequisite Step

Before updating a table using Python, users must ensure that “psycopg2” is installed on their system. To do that, execute the following “pip” command:

pip install psycopg2

The following snippet illustrates that psycopg2 has been successfully installed on our system:

Step 1: Sample Table

Open psql and execute the “SELECT” query with the “*” wildcard to fetch the sample table’s data:

Step 2: Create a Python Program/File

Launch your favorite code editor and make a Python file with the “.py” extension. Import the “psycopg2” at the start of the file:

import psycopg2

Step 3: Create a Connection

Make a Postgres Python connection by setting the required connection details, such as database name, username, port number, etc:

conn_details = psycopg2.connect(
   host="localhost",
   database="postgres",
   user="postgres",
   password="*****",
   port= '5432'
)

Here:

• The connect() function is utilized to make a connection with the “postgres” database and as a “postgres” user.
• The password must be valid according to the specified Postgres user.

Step 4: Update Table Record

Suppose we want to update the joining_date of employees whose id is greater than 4 to the current date. For that particular purpose, let’s execute the below-given UPDATE query:

cursor = conn_details.cursor()
table_modification = "UPDATE emp_info SET joining_date = CURRENT_DATE WHERE emp_id > 4;"
cursor.execute(table_modification)

Here:

• First, a cursor object is created.
• Next, the UPDATE query is executed to modify the “emp_info” table.
• Finally, the query is executed using the “execute()” function to update the table.

Step 5: Save Changes

In the below-given snippet, the commit() and close() functions are invoked to save the changes and close the cursor and the connection:

conn.commit()
cursor.close()
conn.close()

Output

The output snippet shows that the cursor moves to the next line without any error, which proves that a table has been successfully updated.

Step 6: Verify Table Updation

Now open the “psql” terminal and run the “SELECT *” query one more time to confirm the modified table’s data:

SELECT * FROM emp_info;

The resultant table confirms that the selected Postgres table has been successfully updated using Python.

Conclusion

To update a Postgres table using Python, first, create a Python file, import the “psycopg2” library, make a connection between Postgres and Python, update the selected table, save changes, and close the cursor as well as the connection. This post has illustrated a comprehensive guide on updating a Postgres table using Python programming.