PostgreSQL CRUD example in Python with Psycopg2 (Part 2)

Introduction

This is part two of a tutorial series supplying a PostgreSQL crud example in Python with the Psycopg2 adapter. Part one of this series explained how to create a test database for Psycopg2, install psycopg2, connect to PostgreSQL using psycopg2, provided a Postgres ‘CREATE TABLE’ example and explained how to use psycopg2 to insert PostgreSQL record data. This part two of this series will explain the table data select, update and delete functions using psycopg2.

Prerequisites

  • The Psycopg2 or the python driver for PostgreSQL database cluster must be properly installed and working to execute a PostgreSQL crud example in python. Refer to part one of this tutorial series for the installation instructions.

psycopg2 select example

With data inserted into the table book, done in part one of this series, the data can now be recalled and displayed using psycopg2. Create a new file in Python and enter the following codes:

# Import the python driver for PostgreSQL
import psycopg2

# Use the same connection credentials of the PostgreSQL database
try:
    connection = psycopg2.connect(
        user = "postgres",
        password = "1234",
        host = "localhost",
        port = "5432",
        database = "demo"
    )

Now use the connection object instance created from the psycopg2.connect() method to create a new cursor object. As shown below, use this cursor object to execute the SQL statements to select table rows from the PostgreSQL table:

    # Create a connection instance for the PostgreSQL and fetch data from the table
    cursor = connection.cursor()
    pg_select = "SELECT * FROM book"

    cursor.execute(pg_select)
    # Execute and print the output
    print("Selected rows from book table")
    book_records = cursor.fetchall()

    print("Records of books in the table")
    for row in book_records:
        print("\nid = ", row[0])
        print("author = ", row[1])
        print("isbn  = ", row[2])
        print("title = ", row[3])
        print("date_published = ", row[4], "\n")

except (Exception, psycopg2.Error) as error:
    print("Error selecting data from table book", error)

# Close the connection to the PostgreSQL database
finally:
    if (connection):
        cursor.close()
        connection.close()
        print("PostgreSQL connection is now closed")

Refer to the following screenshot:

Screenshot of PostgreSQL CRUD example in Python for psycopg2 adapter

The result should resemble the following:

Selected rows from book table
Records of books in the table

id = 1
author = Layla Nowiztki
isbn = 789-1-46-268414-1
title = How to become a professional programmer
date_published = 2011-01-25

id = 2
author = Miya Grand
isbn = 123-7-58-364785-1
title = Coding for Beginners
date_published = 2009-02-23

id = 3
author = Grange Toll
isbn = 143-4-15-135147-7
title = Tutorials in Web Development for Beginners
date_published = 2007-03-12

PostgreSQL connection is now closed

psycopg2 ‘UPDATE’ example

Now modify the existing data in the table by using psycopg2 by creating a Python file. Alternatively, the following code can be placed into a new Python script on the same machine or server:

# Import the python driver for PostgreSQL
import psycopg2

# Create a function header to update the table
def update_table(bookid, title):
    try:
        connection = psycopg2.connect(
            user = "postgres",
            password = "1234",
            host = "localhost",
            port = "5432",
            database = "demo"
        )

        # Create a cursor connection object to a PostgreSQL instance and print the connection properties.
        cursor = connection.cursor()

        print("Book table before updating")
        pg_select = """SELECT * FROM book WHERE id = %s"""
        cursor.execute(pg_select, (bookid, ))
        book_record = cursor.fetchone()
        print(book_record)

        # Update the row from book
        pg_update = """Update book set title = %s where id = %s"""
        cursor.execute(pg_update, (title, bookid))
        connection.commit()
        count = cursor.rowcount
        print(count, "Successfully Updated!")

        print("Book table after updating")
        pg_select = """SELECT * FROM book where id = %s"""

        # Execute the above SQL string
        cursor.execute(pg_select, (bookid,))
        book_record = cursor.fetchone()
        print(book_record)

    except(Exception, psycopg2.Error) as error:
        print("Error in updating the data:", error)
        connection = None

    # Close the connection to the PostgreSQL database
    finally:
        if connection != None:
            cursor.close()
            connection.close()
            print("PostgreSQL connection is now closed")

In the last part of the script, outside of the function indentation, new values must be specified for the record being updated. Now pass them to the update_table() function call, as shown in the following example:

_id = 3
title = 'Tutorials in FrontEnd Developers'

# call the psycopg2 function to execute SQL
update_table(_id, title)

NOTE: In Python, id() is a built-in function that gives an object’s identity. To avoid confusion, or affecting its namespace, the above example uses _id with an underscore in the variable declaration instead. This is shown in the following screenshot:

Screenshot of PostgreSQL CRUD example in Python using psycopg2 to update a record

When the above script is executed using python3, the results should resemble the following:

Book table before updating
(3, 'Grange Toll', '143-4-15-135147-7', 'Tutorials in Web Developers for Beginners', datetime.date(2007, 3, 12))
1 Successfully Updated!

Book table after updating
(3, 'Grange Toll', '143-4-15-135147-7', 'Tutorials in FrontEnd Developers', datetime.date(2007, 3, 12))
PostgreSQL connection is now closed

psycopg2 ‘DELETE’ example

Lastly, the table data will now be delete using psycopg2. This is performed in the same manner as the DELETE SQL statement in the psql command line interface is used to delete rows of Postgres data. Execute the following code:

# Import the python driver for PostgreSQL
import psycopg2

# Create a delete function
def delete_data(bookid):
    try:
        connection = psycopg2.connect(
            user = "postgres",
            password = "1234",
            host = "localhost",
            port = "5432",
            database = "demo"
        )

        cursor = connection.cursor()

        # Delete the data from table book
        pg_delete = 'DELETE FROM book where id = ${}'.format(bookid)

        cursor.execute( pg_delete )
        connection.commit()
        count = cursor.rowcount
        print("Successfully deleted", count, "rows.")

    except(Exception, psycopg2.Error) as error:
        print("Error in Deleting the data:", error)
        connection = None

    # Close the connection to the PostgreSQL database
    finally:
        if connection != None:
            cursor.close()
            connection.close()
            print("PostgreSQL connection is now closed")

_id = 2
delete_data(_id)
1 Successfully deleted
PostgreSQL connection is now closed

Finally, check the results using the following SELECT statement:

Selected rows from book table
Records of books in the table
id = 1
author = Layla Nowiztki
isbn = 789-1-46-268414-1
title = How to become a professional programmer
date_published = 2011-01-25

id = 3
author = Grange Toll
isbn = 143-4-15-135147-7
title = Tutorials in FrontEnd Developers
date_published = 2007-03-12

PostgreSQL connection is now closed

Conclusion

This was part two in a tutorial series supplying a PostgreSQL crud example in python with the Psycopg2 adapter. Part two built on the functions explained in part one of this series and specifically covered a psycopg2 ‘SLECT’ example, a psycopg2 ‘UPDATE’ example and psycopg2 ‘DELETE’ example. Part two also covered how to check the result of these functions using the Slect statement. Remember that either Psycopg2 or the Python driver for the PostgreSQL database must be properly installed to execute a PostgreSQL crud example in Python. Installation instructions for the Python driver for the PostgreSQL database can be found in part one of this tutorial series.

Just the Code

#!/usr/bin/python3

# Import the python driver for PostgreSQL
import psycopg2

# Create a function header to update the table
def update_table(bookid, title):
    try:
        connection = psycopg2.connect(
            user = "objectrocket",
            password = "1234",
            host = "localhost",
            port = "5432",
            database = "demo"
        )

        # Create a cursor connection object to a PostgreSQL instance and print the connection properties.
        cursor = connection.cursor()

        print("Book table before updating")
        pg_select = """SELECT * FROM book WHERE id = %s"""
        cursor.execute(pg_select, (bookid, ))
        book_record = cursor.fetchone()
        print(book_record)

        # Update the row from book
        pg_update = """Update book set title = %s where id = %s"""
        cursor.execute(pg_update, (title, bookid))
        connection.commit()
        count = cursor.rowcount
        print(count, "Successfully Updated!")

        print("Book table after updating")
        pg_select = """SELECT * FROM book where id = %s"""

        # Execute the above SQL string
        cursor.execute(pg_select, (bookid,))
        book_record = cursor.fetchone()
        print(book_record)

    except (Exception, psycopg2.Error) as error:
        print("Error in updating the data:", error)
        connection = None

    # Close the connection to the PostgreSQL database
    finally:
        if connection != None:
            cursor.close()
            connection.close()
            print("PostgreSQL connection is now closed")

_id = 3
title = 'Tutorials in FrontEnd Developers'

# call the psycopg2 function to execute SQL
update_table(_id, title)


# Create a delete function
def delete_data(bookid):
    try:
        connection = psycopg2.connect(
            user = "postgres",
            password = "1234",
            host = "localhost",
            port = "5432",
            database = "demo"
        )

        cursor = connection.cursor()

        # Delete the data from table book
        pg_delete = 'DELETE FROM book where id = ${}'.format(bookid)

        cursor.execute( pg_delete )
        connection.commit()
        count = cursor.rowcount
        print("Successfully deleted", count, "rows.")

    except (Exception, psycopg2.Error) as error:
        print("Error in Deleting the data:", error)
        connection = None

    # Close the connection to the PostgreSQL database
    finally:
        if connection != None:
            cursor.close()
            connection.close()
            print("PostgreSQL connection is now closed")

_id = 2
delete_data(_id)

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.