PYnative

Python Programming

  • Learn Python
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks
Home » Python » Databases » Python PostgreSQL Insert, Update, and Delete from a Table using Psycopg2

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

Updated on: March 9, 2021 | 8 Comments

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.

Also, Read:

  • Solve Python PostgreSQL Exercise
  • Read Python PostgreSQL Tutorial (Complete Guide)

Table of contents

  • Prerequisites
  • Python PostgreSQL INSERT into database Table
  • Python PostgreSQL UPDATE Table Data
  • Python PostgreSQL Delete Table row and columns
  • Insert, update and delete multiple rows from the PostgreSQL table using executemany()
    • Python Insert multiple rows into the PostgreSQL table
    • Update multiple rows of PostgreSQL table using a single Query in Python
    • Python PostgreSQL Delete multiple rows from a table
  • Next Steps:

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.

For this article, I am using a ‘mobile’ table created in my PostgreSQL database.

If a table is not present, you can refer to create a PostgreSQL table from Python.

PostgreSQL mobile table
PostgreSQL mobile table

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:

1 Record inserted successfully into mobile table PostgreSQL connection is closed
PostgreSQL table after insert operation
PostgreSQL table after insert operation

We used a parameterized query to use Python variables as parameter values at execution time. In the end, we used commit() to make our changes persistent into the database

Using a parameterized query, we can pass python variables as a query parameter using placeholders (%s).

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:

Table Before updating record  (3, 'Google Pixel', 700.0) 1 Record Updated successfully  

Table After updating record  (3, 'Google Pixel', 970.0) PostgreSQL connection is closed
PostgreSQL table after update operation
PostgreSQL table after update operation

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:

1 Record deleted successfully  PostgreSQL connection is closed 

1 Record deleted successfully  PostgreSQL connection is closed
PostgreSQL table after deleting row
PostgreSQL table after deleting row

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:

2 Record inserted successfully into mobile table
PostgreSQL connection is closed
PostgreSQL table after inserting multiple rows
PostgreSQL table after inserting multiple rows

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:

2 Records Updated
PostgreSQL connection is closed
PostgreSQL table after update multiple rows
PostgreSQL table after update multiple rows

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.

Filed Under: Python, Python Databases

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

TweetF  sharein  shareP  Pin

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Related Tutorial Topics:

Python Python Databases

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Posted In

Python Python Databases
TweetF  sharein  shareP  Pin

  Python PostgreSQL

  • Python PostgreSQL Guide
  • Python PostgreSQL Select
  • Python PostgreSQL CRUD
  • Python PostgreSQL call Function
  • Python PostgreSQL Transactions
  • Python PostgreSQL Connection Pool
  • Python Database Exercise

All Python Topics

Python Basics Python Exercises Python Quizzes Python File Handling Python OOP Python Date and Time Python Random Python Regex Python Pandas Python Databases Python MySQL Python PostgreSQL Python SQLite Python JSON

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Explore Python

  • Learn Python
  • Python Basics
  • Python Databases
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter
  • Facebook
  • Sitemap

Legal Stuff

  • About Us
  • Contact Us

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our Terms Of Use, Cookie Policy, and Privacy Policy.

Copyright © 2018–2023 pynative.com