Performing Update operation in PostgreSQL database using Python

Performing Read operation in PostgreSQL database using Python

Performing Delete operation in PostgreSQL database using Python

The syntax for updating a table is similar to that of the syntax used in conventionally SQL statements. Here we write an update query that updates publisher name by publisher id as shown.

Establish a connection with the PostgreSQL database as discussed above.
Now, instantiate a new cursor() object. A cursor is a read-only pointer that allows a program, to access the result set of a query.
Then, pass the query for updating a table to the execute() function of psycopg2.
Use rowcount function to count the number of rows updated.
Finally call the commit() method, indicating the connector object which was created to establish a connection with the database to commit all changes to the database. This will ensure that the tables are updated.
In order to capture any errors while updating tables in a database and close the connection smoothly after committing all changes, use try, expect, and finally block as shown in the below code.

Test the update_publisher() function by calling the function and checking for the updates.

Python3

import psycopg2
 
 
def updateTable(publisherId, establishedYear):
    try:
        connection = psycopg2.connect(user="username",
                                      password="password",
                                      host="hostname",
                                      port="5432",
                                      database="databasename")
 
        cursor = connection.cursor()
        # Update single record now
        sql_update_query = """Update publisher set \
        publisher_estd = %s where publisher_id = %s"""
        cursor.execute(sql_update_query,
                       (establishedYear,
                        publisherId))
        connection.commit()
        count = cursor.rowcount
        print(count, "Record Updated successfully ")
 
    except (Exception, psycopg2.Error) as error:
        print("Error in update operation", error)
 
    finally:
        # closing database connection.
        if connection:
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")
 
 
# call the update function
publisherId = 3
establishedYear = 2000
updateTable(publisherId, establishedYear)

Output:

1 Record Updated successfully 
PostgreSQL connection is closed

Here, row corresponding to id = 3 is updated with a new value for publsher_estd.

Updating records in a table in PostgreSQL using Python

Perform PostgreSQL CRUD operations from Python

The DDL is comprised of the Create, Read, Update, Delete (CRUD) operations which form the backbone of any SQL database system. Let us discuss how to perform CRUD operations on a PostgreSQL database using python. Pyscopg2 is the most preferred mode and is widely used to connect the PostgreSQL database using python. Pyscopg2 is a Database API which is a PostgreSQL compatible driver that manages connection pools.

In this article, we will learn how to connect to the PostgreSQL database using SQLAlchemy in python and also understood the procedure on how to perform CRUD operations against the PostgreSQL database.

Performing Update operation in PostgreSQL database using Python

Python3

Perform PostgreSQL CRUD operations from Python

Similar Reads

Contact Us