Python PostgreSQL Insert, Update, and Delete from a Table using Psycopg2

In this tutorial, we will learn how to perform PostgreSQL Insert, Update, Delete operations from Python. It is also known as DML operations. Also, learn how to pass parameters to SQL queries i.e., use Python variables in the PostgreSQL query to insert, update, and delete table data.

In the end, we will see the use of cursor.executemany() to insert, update, and delete multiple rows using a single query.

Prerequisites

Before executing the following programs, please make sure you have the following details in place

• Username and password that you need to connect PostgreSQL
• PostgreSQL database table for CRUD operations.

Python PostgreSQL INSERT into database Table

In this section, We learn how to execute INSERT Query from a Python application to insert rows to the PostgreSQL table using Psycopg2.

To perform a SQL INSERT query from Python, you need to follow these simple steps: –

• Install psycopg2 using pip.
• Second, Establish a PostgreSQL database connection in Python.
• Next, Define the Insert query. All you need to know is the table’s column details.
• Execute the INSERT query using cursor.execute(). In return, you will get the number of rows affected.
• After the successful execution of the query, commit your changes to the database.
• Close the cursor and PostgreSQL database connection.
• Most important, Catch SQL exceptions if any.
• At last, verify the result by selecting data from the PostgreSQL table.

import psycopg2

try:
    connection = psycopg2.connect(user="sysadmin",
                                  password="pynative@#29",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="postgres_db")
    cursor = connection.cursor()

    postgres_insert_query = """ INSERT INTO mobile (ID, MODEL, PRICE) VALUES (%s,%s,%s)"""
    record_to_insert = (5, 'One Plus 6', 950)
    cursor.execute(postgres_insert_query, record_to_insert)

    connection.commit()
    count = cursor.rowcount
    print(count, "Record inserted successfully into mobile table")

except (Exception, psycopg2.Error) as error:
    print("Failed to insert record into mobile table", error)

finally:
    # closing database connection.
    if connection:
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

Output:

Python PostgreSQL UPDATE Table Data

This section will learn how to update a PostgreSQL table’s data from a Python application using Psycopg2.

You will learn how to update a single and rows, single column, and multiple columns of the PostgreSQL table.

To perform a PostgreSQL UPDATE query from Python, you need to follow these steps: –

• Establish a PostgreSQL database connection in Python.
• Define the UPDATE statement query to update the data of the PostgreSQL table.
• Execute the UPDATE query using a cursor.execute()
• Close the cursor and database connection.

Now, Let see the example to update a single row of the database table.

import psycopg2

def updateTable(mobileId, price):
    try:
        connection = psycopg2.connect(user="sysadmin",
                                      password="pynative@#29",
                                      host="127.0.0.1",
                                      port="5432",
                                      database="postgres_db")

        cursor = connection.cursor()

        print("Table Before updating record ")
        sql_select_query = """select * from mobile where id = %s"""
        cursor.execute(sql_select_query, (mobileId,))
        record = cursor.fetchone()
        print(record)

        # Update single record now
        sql_update_query = """Update mobile set price = %s where id = %s"""
        cursor.execute(sql_update_query, (price, mobileId))
        connection.commit()
        count = cursor.rowcount
        print(count, "Record Updated successfully ")

        print("Table After updating record ")
        sql_select_query = """select * from mobile where id = %s"""
        cursor.execute(sql_select_query, (mobileId,))
        record = cursor.fetchone()
        print(record)

    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")

id = 3
price = 970
updateTable(id, price)

Output:

Verify the result of the above update operation by Selecting data from the PostgreSQL table using Python.

Python PostgreSQL Delete Table row and columns

This section will learn how to Delete a PostgreSQL table’s data from Python using Psycopg2.

Let see this with an example program. In this Python example, we prepared a query to delete a single row from the PostgreSQL table.

import psycopg2


def deleteData(mobileId):
    try:
        connection = psycopg2.connect(user="sysadmin",
                                      password="pynative@#29",
                                      host="127.0.0.1",
                                      port="5432",
                                      database="postgres_db")

        cursor = connection.cursor()

        # Update single record now
        sql_delete_query = """Delete from mobile where id = %s"""
        cursor.execute(sql_delete_query, (mobileId,))
        connection.commit()
        count = cursor.rowcount
        print(count, "Record deleted successfully ")

    except (Exception, psycopg2.Error) as error:
        print("Error in Delete operation", error)

    finally:
        # closing database connection.
        if connection:
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

id4 = 4
id5 = 5
deleteData(id4)
deleteData(id5)

Output:

Verify the result of the above delete operation by Selecting data from the PostgreSQL table using Python.

Insert, update and delete multiple rows from the PostgreSQL table using executemany()

Note: Use cursor.executemany() method to insert, update, delete multiple rows of a table using a single query.

The cursor.executemany() method executes the database query against all the parameters.

Most of the time, you need to run the same query multiple times but with different data. Like insert each student’s attendance, attendance percentage is different, but the SQL query is the same.

Syntax of executemany()

executemany(query, vars_list)

• Here query can be any SQL query (Insert, update, delete)
• The vars_list is nothing but the list of tuples as an input to the query.
• Each tuple in this list contains a single row of data to be inserted or updated into a table.

Now, Let see how to use this method.

Python Insert multiple rows into the PostgreSQL table

Use parameterized query and cursor’s executemany() method to add multiple rows into a table. Using placeholders in the parameterized query, we can pass column values at runtime.

import psycopg2

def bulkInsert(records):
    try:
        connection = psycopg2.connect(user="sysadmin",
                                      password="pynative@#29",
                                      host="127.0.0.1",
                                      port="5432",
                                      database="postgres_db")
        cursor = connection.cursor()
        sql_insert_query = """ INSERT INTO mobile (id, model, price) 
                           VALUES (%s,%s,%s) """

        # executemany() to insert multiple rows
        result = cursor.executemany(sql_insert_query, records)
        connection.commit()
        print(cursor.rowcount, "Record inserted successfully into mobile table")

    except (Exception, psycopg2.Error) as error:
        print("Failed inserting record into mobile table {}".format(error))

    finally:
        # closing database connection.
        if connection:
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

records_to_insert = [(4, 'LG', 800), (5, 'One Plus 6', 950)]
bulkInsert(records_to_insert)

Output:

Update multiple rows of PostgreSQL table using a single Query in Python

For example, you want to update the salary of the employees. Now the pay is different for each employee, but the update query remains the same.
We can update multiple rows of a table using a cursor.executemany() and parameterized query

import psycopg2

def updateInBulk(records):
    try:
        ps_connection = psycopg2.connect(user="sysadmin",
                                         password="pynative@#29",
                                         host="127.0.0.1",
                                         port="5432",
                                         database="postgres_db")
        cursor = ps_connection.cursor()

        # Update multiple records
        sql_update_query = """Update mobile set price = %s where id = %s"""
        cursor.executemany(sql_update_query, records)
        ps_connection.commit()

        row_count = cursor.rowcount
        print(row_count, "Records Updated")

    except (Exception, psycopg2.Error) as error:
        print("Error while updating PostgreSQL table", error)

    finally:
        # closing database connection.
        if ps_connection:
            cursor.close()
            ps_connection.close()
            print("PostgreSQL connection is closed")


tuples = [(750, 4), (950, 5)]
updateInBulk(tuples)

Output:

Note: Use cursor.rowcount to get the total number of rows affected by the executemany() method.

Python PostgreSQL Delete multiple rows from a table

In this example, We defined SQL Delete query with a placeholder that contains customer IDs to delete. Also, prepared a list of records to be deleted. This List contains a tuple for each row. Here we created two tuples, to delete two rows.

import psycopg2


def deleteInBulk(records):
    try:
        ps_connection = psycopg2.connect(user="postgres",
                                         password="vishal@#29",
                                         host="127.0.0.1",
                                         port="5432",
                                         database="postgres_db")
        cursor = ps_connection.cursor()
        ps_delete_query = """Delete from mobile where id = %s"""
        cursor.executemany(ps_delete_query, records)
        ps_connection.commit()

        row_count = cursor.rowcount
        print(row_count, "Record Deleted")

    except (Exception, psycopg2.Error) as error:
        print("Error while connecting to PostgreSQL", error)

    finally:
        # closing database connection.
        if ps_connection:
            cursor.close()
            ps_connection.close()
            print("PostgreSQL connection is closed")

# list of tuples contains database IDs
tuples = [(5,), (4,), (3,)]
deleteInBulk(tuples)

Output:

2 Records Deleted
PostgreSQL connection is closed

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.