Performing Read operation in PostgreSQL database using Python

Performing Insert operation in PostgreSQL database using Python

Performing Update operation in PostgreSQL database using Python

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 creating a table to the execute() function of psycopg2.
The query result will be stored in the cursor object named the engine. Use fetchall() method to get all the rows of the resulting query.
Now, iterate through each row to see the query result as shown. In order to capture any errors while selecting the records in a database and close the connection smoothly after committing all changes, use try, expect, and finally block as shown in the below code.

Python3

import psycopg2
 
try:
    connection = psycopg2.connect(user="username",
                                  password="password",
                                  host="hostname",
                                  port="5432",
                                  database="databasename")
    cursor = connection.cursor()
    postgreSQL_select_Query = "select * from publisher"
 
    cursor.execute(postgreSQL_select_Query)
    print("Selecting rows from publisher table using cursor.fetchall")
    publisher_records = cursor.fetchall()
 
    print("Print each row and it's columns values")
    for row in publisher_records:
        print("publisher_Id = ", row[0], )
        print("publisher_name = ", row[1])
        print("publisher_estd  = ", row[2])
        print("publisher_location  = ", row[3])
        print("publisher_type  = ", row[4], "\n")
except (Exception, psycopg2.Error) as error:
    print("Error while fetching data from PostgreSQL", error)
 
finally:
    # closing database connection.
    if connection:
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

Output:

Reading 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 Read operation in PostgreSQL database using Python

Python3

Perform PostgreSQL CRUD operations from Python

Similar Reads

Contact Us