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:
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:
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' )
- 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)
- 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()
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.
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.