Page Title

Tutorial 5

PostgreSQL with Python

What is PostgreSQL?

PostgreSQL is an advanced, enterprise-class, and open-source relational database system. PostgreSQL supports both SQL (relational) and JSON (non-relational) querying.

PostgreSQL is used as a primary database for many web applications as well as mobile and analytics applications.

Non-relational database - A non-relational database is a database that does not use the tabular schema of rows and columns found in most traditional database systems. We will learn later about this.

Common Use of PostgreSQL

The following are the common use cases of PostgreSQL.

1 - A robust database in the LAPP stack
LAPP stands for Linux, Apache, PostgreSQL, and PHP (or Python and Perl).

PostgreSQL is primarily used as a robust back-end database that powers many dynamic websites and web applications.
2 - General purpose transaction database
Large corporations and startups alike use PostgreSQL as primary databases to support their applications and products.

Language support

PostgreSQL support most popular programming languages:

Python
Java
C#
C/C++
Ruby
Node.js
Perl
GO

PostgreSQL feature

PostgreSQL has many advanced features that other enterprise-class database management systems offer, such as:

User-defined types
Table inheritance
Sophisticated locking mechanism
Foreign key referential integrity
Views, rules, subquery
Nested transactions (savepoints)
Multi-version concurrency control (MVCC)
Asynchronous replication
Tablespaces
Point-in-time recovery

PostgreSQL is designed to be extensible. PostgreSQL allows you to define your own data types, index types, functional languages, etc.

Integrate Postgres with Python

In Python, we have serval modules available to connect and work with PostgreSQL. the following are the list.

Psycopg2
pg8000
py-postgresql
PyGreSQL
ocpgdb
bpgsql
SQLAlchemy

But Psycopg2 is the most popular python driver for PostgreSQL. It is required for most Python and Postgres frameworks. It is thread-safe (threads can share the connections). It was designed for heavily multi-threaded applications.

Psycopg2 is a Postgres database adapter for Python. To use this module, you should first install it.

Install Psycopg2

You need to install Psycopg2 on your machine to use PostgreSQL from Python. This module is available on pypi.org.

Using pip command, we can install Psycopg2 on any operating system like Windows, macOS, Linux, Unix and Ubuntu.

Use this command:

pip install psycopg2

We can also install a specific version using the following command.

pip install psycopg2=3.5

If you are facing pip install error like connection error: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:598)". You can resolve this error by setting pypi.org and files.pythonhosted.org as trusted hosts like :

python -m pip install --trusted-host pypi.org --trusted-host files.pythonhosted.org --trusted-host pypi.python.org psycopg2

Once the module has been installed, you can use it to connect to your database in your application.

Python PostgreSQL Database Connection

To connect to your database, you should first create a connection object representing the database.

By using the connection object, you can create a new cursor to execute any SQL statements.

Create a cursor object using the connection object returned by the connect method to execute PostgreSQL queries from Python.

The following example demonstrates how to establish a connection:

import psycopg2

con = psycopg2.connect(database="postgres", user="postgres", password="", host="127.0.0.1", port="5432")

Let's know about the each parameters used for stablish a conection:

database - The name of the database.
user - The username to be used for authentication.
password - The database password for the security purpose because we don't want that other person can access the database
host - The address of the database server. For example, a domain name, "localhost", or an IP address.
port - The port number. If you don't provide this, the default one will be used, which is 5432.

You can also use a configuration file to store all connection parameters.

The following shows the contents of the database.ini file:

[postgresql]
host = localhost
database = postgres
user = postgres
password = SecurePas$1

Points to remeber

Use try-except-finally block to catch the database exceptions and errors that may occur during this process.
Close the Cursor object and PostgreSQL database connection in finally block after your work completes.

Connection Objects

Connection objects should respond to the following methods.

.close()

Close the connection now. The connection will be unusable from this point forward; an Error (or subclass) exception will be raised if any operation is attempted with the connection. The same applies to all cursor objects trying to use the connection.

.commit()

Commit any pending transaction to the database.

If the database supports an auto-commit feature, this must be initially off. An interface method may be provided to turn it back on.

.rollback()

This method is optional since not all databases provide transaction support.

In case a database does provide transactions this method causes the database to roll back to the start of any pending transaction.

Closing a connection without committing the changes first will cause an implicit rollback to be performed.

.cursor()

Return a new Cursor Object using the connection.

Cursor Objects

These objects represent a database cursor, which is used to manage the context of a fetch operation.

Cursors created from the same connection are not isolated, i.e., any changes done to the database by a cursor are immediately visible by the other cursors.

Cursor attributes & methods

.rowcount

This read-only attribute specifies the number of rows that the last .execute*() produced (for DQL statements like SELECT) or affected (for DML statements like UPDATE or INSERT).

.callproc( procname [, parameters ] )

Call a stored database procedure with the given name.

The sequence of parameters must contain one entry for each argument that the procedure expects.

The procedure may also provide a result set as output.

.execute(operation [, parameters])

Prepare and execute a database operation (query or command).

Parameters may be provided as sequence or mapping and will be bound to variables in the operation.

.executemany( operation, seq_of_parameters )

Prepare a database operation (query or command) and then execute it against all parameter sequences or mappings found in the sequence seq_of_parameters.

.fetchone()

Fetch the next row of a query result set, returning a single sequence, or None when no more data is available.

.fetchmany([size=cursor.arraysize])

Fetch the next set of rows of a query result, returning a sequence of sequences (e.g. a list of tuples). An empty sequence is returned when no more rows are available.

.fetchall()

Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples).

Example to Connect PostgreSQL Database

This is a complete example of your postgres database connection. You should know about each functionality used in this example.

import psycopg2
try:
    connection = psycopg2.connect(user = "postgres",
        password = "12345",
        host = "127.0.0.1",
        port = "5432",
        database = "postgres_db")

    cursor = connection.cursor()
    # Print PostgreSQL Connection properties
    print ( connection.get_dsn_parameters(),"\n")

    # Print PostgreSQL version
    cursor.execute("SELECT version();")
    record = cursor.fetchone()
    print("You are connected to - ", record,"\n")

except (Exception, psycopg2.Error) as error :
    print ("Error while connecting to PostgreSQL", error)
finally:
    #closing database connection.
    if(connection):
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

Creating a Table

To create a Postgres table in Python, we use the CREATE TABLE SQL statement. This query should be executed after establishing a connection to the database. We also create a cursor object by calling the cursor() method that belongs to the connection object. This cursor object is used to actually execute your commands.

We then call the execute() method of the cursor object to help us in creating the table. Finally, we need to commit and close the connection.

Note : "Committing" the connection tells the driver to send the commands to the database. You should always commit the connection before closing the connection otherwise data won't be affect in database.

Example :

import psycopg2

def createTable():

    try:
        connection = psycopg2.connect(user = "postgres",
            password = "12345",
            host = "127.0.0.1",
            port = "5432",
            database = "postgres_db")

        cursor = connection.cursor()
    
        create_table_query = '''CREATE TABLE sololean_post
              (POST_ID INT PRIMARY KEY  NOT NULL, POST TEXT NOT NULL); '''

        cursor.execute(create_table_query)
        connection.commit()
        print("Table created successfully in PostgreSQL ")

    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
    finally:
        #closing database connection.
        if (connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")
            
createTable()

You Should get the following output

Table created successfully in PostgreSQL
PostgreSQL connection is closed

Note : The commit() method helps to apply the changes made to the database, and these changes cannot be undone if commit() runs successfully.

Create multiple table

To create multiple table you need to write script with comma seperated and store into a variable and through loop statement you can execute each table.

Example :

import psycopg2

def createMultipleTable():

    tables = (
    """
        CREATE TABLE sololearn_user (
            user_id SERIAL PRIMARY KEY,
            user_name VARCHAR(255) NOT NULL
    )
    """,
    """
        CREATE TABLE sololearn_post (
            post_id SERIAL PRIMARY KEY,
            post VARCHAR(255) NOT NULL,
            PRIMARY KEY (post_id),
            FOREIGN KEY (user_id)
                REFERENCES sololearn_user (user_id)
                ON UPDATE CASCADE ON DELETE CASCADE
    )
    """,
    """ 
        CREATE TABLE sololearn_code (
            code_id SERIAL PRIMARY KEY,
            code_name VARCHAR(255) NOT NULL,
            PRIMARY KEY (code_id),
            FOREIGN KEY (user_id)
                REFERENCES sololearn_user (user_id)
                ON UPDATE CASCADE ON DELETE CASCADE
    )
    """,
    """
        CREATE TABLE sololearn_qa (
            qa_id SERIAL PRIMARY KEY,
            qa_description VARCHAR(1024) NOT NULL,
            PRIMARY KEY (qa_id),
            FOREIGN KEY (user_id)
                REFERENCES sololearn_user (user_id)
                ON UPDATE CASCADE ON DELETE CASCADE
    )
    """)
    try:
        connection = psycopg2.connect(user = "postgres",
            password = "12345",
            host = "127.0.0.1",
            port = "5432",
            database = "sololearn_info_db")

        cursor = connection.cursor()
    
        # create table one by one
        for table in tables:
            cursor.execute(table)

        # commit the changes
        connection.commit()
        print("Tables created successfully in PostgreSQL ")

    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
    finally:
        #closing database connection.
        if (connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

createMultipleTable()

After executing the above program. you will see 4 tables created in your database sololearn_info_db.

To check this you need to login to the postgres database server. After that use the \dt command to display the table list from the sololearn_info_db database.

Schema	Name	Type	Owner
public	sololearn_user	table	postgres
public	sololearn_post	table	postgres
public	sololearn_code	table	postgres
public	sololearn_qa	table	postgres

Insert Data Into a Table

To insert data execute the INSERT statement with the input values by calling the execute() method of the cursor object.

cursor.execute(sql, value)

The first parameter is INSERT statment and second parameter is value.

You can also insert multiple record just passing the values as a second parameter like:

cursor.execute(sql, (value1, value2))

After inserting you can get the generated ID. To do this, use the RETURNING id clause in the INSERT statement. After calling the execute() method, you call the fetchone() method of the cursor object to get the id value like this:

id = cursor.fetchone()[0]

Example :

import psycopg2

def insertRecord(userName):
    sql = """INSERT INTO sololearn_user(user_name)
             VALUES(%s) RETURNING user_id;"""
             
    try:
        connection = psycopg2.connect(user = "postgres",
            password = "12345",
            host = "127.0.0.1",
            port = "5432",
            database = "postgres_db")

        cursor = connection.cursor()
    
        cursor.execute(sql, (userName))
    
        user_id = cursor.fetchone()[0]
    
        print("Data inserted, id is ", user_id,"\n")

    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
    finally:
        #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")
            
    return user_id

userName = "AJ Anant"           
insertRecord(userName)

Output :

Data inserted, id is 12345
PostgreSQL connection is closed

Insert multiple records.

To insert multiple records you can use executemany() method. In this case you will not get return id.

Example :

import psycopg2

def insertMultipleRecords(userList):
    sql = """INSERT INTO sololearn_user(user_name)
             VALUES(%s) RETURNING user_id;"""
             
    try:
        connection = psycopg2.connect(user = "postgres",
            password = "12345",
            host = "127.0.0.1",
            port = "5432",
            database = "postgres_db")

        cursor = connection.cursor()
    
        cursor.executemany(sql, userList)
    
        print("Data inserted\n")

    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
    finally:
        #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

userList = [('AJ',), ("AJ Anant",), ("Sololearn"),]

insertMultipleRecords(userList)

Output :

Data inserted
PostgreSQL connection is closed

Update Data in a Table

To update data execute the UPDATE statement with the input values by calling the execute() method of the cursor object.

cursor.execute(update_sql, (value1, value2))

The execute() method accepts two parameters. The first parameter is an SQL statement to be executed, in this case, it is the UPDATE statement. The second parameter is a list of input values that you want to pass to the UPDATE statement.

If you want to know how many row affeted after updating, you can get it from rowcount attribute of the cursor object

updated_rows = cursor.rowcount

Example :

import psycopg2

def updateRecord(userName, userId);
    update_sql = """UPDATE sololearn_user SET user_name = %s WHERE user_id = %s"""

    updated_rows = 0
             
    try:
        connection = psycopg2.connect(user = "postgres",
            password = "12345",
            host = "127.0.0.1",
            port = "5432",
            database = "postgres_db")

        cursor = connection.cursor()
    
        # execute the UPDATE  statement
        cursor.execute(update_sql, (userName, userId))
    
        # get the number of updated rows
        updated_rows = cursor.rowcount
    
        print("Data Updated Count", updated_rows, "\n")

    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
    finally:
        #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

updateRecord("AJ Anant", 12345)

Output :

Data Updated Count 1
PostgreSQL connection is closed

You can varify updated data using select query.

SELECT * FROM sololearn_user WHERE user_id = 12345;

Update multiple records

Sometimes we need to update multiple records. We can do by using executemany() method. Let's see the example.

Example :

import psycopg2

def updateMultipleRecord(records);
    update_sql = """UPDATE sololearn_user SET user_name = %s WHERE user_id = %s"""

    updated_rows = 0
             
    try:
        connection = psycopg2.connect(user = "postgres",
            password = "12345",
            host = "127.0.0.1",
            port = "5432",
            database = "postgres_db")

        cursor = connection.cursor()
    
        # execute the UPDATE  statement
        cursor.executemany(update_sql, records)
    
        # get the number of updated rows
        updated_rows = cursor.rowcount
    
        print("Data Updated Count", updated_rows, "\n")

    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
    finally:
        #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

records = [("AJ Anant", 1234), ("AJ", 5)]

updateMultipleRecord(records)

Output :

Data Updated Count 2
PostgreSQL connection is closed

Delete records from table

We can delete records from the table. Let's see the below code.

Example :

import psycopg2

def deleteRecord(userId);
    delete_sql = """DELETE FROM sololearn_user WHERE user_id = %s"""

    updated_rows = 0
             
    try:
        connection = psycopg2.connect(user = "postgres",
            password = "12345",
            host = "127.0.0.1",
            port = "5432",
            database = "postgres_db")

        cursor = connection.cursor()
    
        # execute the DELETE  statement
        cursor.execute(delete_sql, (userId,))
    
        # get the number of updated rows
        updated_rows = cursor.rowcount
    
        print("Record deleted, Count", updated_rows, "\n")

    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
    finally:
        #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

deleteRecord(12345)

Output :

Record deleted, Count 1
PostgreSQL connection is closed

Delete multiple records

In this example, We defined SQL Delete query with placeholder which contains user IDs to delete. Also, prepared List of records to be deleted. This List contains tuple for each row.

Example :

import psycopg2

def deleteMultipleRecord(userIdList);
    delete_sql = """DELETE FROM sololearn_user WHERE user_id = %s"""

    updated_rows = 0
             
    try:
        connection = psycopg2.connect(user = "postgres",
            password = "12345",
            host = "127.0.0.1",
            port = "5432",
            database = "postgres_db")

        cursor = connection.cursor()
    
        # execute the DELETE  statement
        cursor.executemany(delete_sql, userIdList)
        
        #Commit the connection
        connection.commit()
    
        # get the number of updated rows
        updated_rows = cursor.rowcount
    
        print("Record deleted, Count", updated_rows, "\n")

    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
    finally:
        #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

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

Output :

Record deleted, Count 3
PostgreSQL connection is closed

Python PostgreSQL Transaction management

In most of the cases, we want to execute a PostgreSQL database operation only when another operation completes. For example, in the Online Bank Transaction system we can only deposit money to account B from account A only when a withdrawal is successful from account A.

In the transaction, either all of the operations are executed, or none of the operations performed. i.e., We need to complete all the operations under a single transaction successfully to call it a successful transaction.

Process of the transaction

We need to follow the some process before start any transaction:

Enabling and Disabling auto-commit Mode.
Commit transactions to make changes persistent in the database.
Maintain the ACID property of the transaction.
Rollback all the operations in case of failure.

Psycopg2 Transactions control

In Psycopg transactions are handled by the connection class. By default, the first time a command is sent to the database (using one of the cursors created by the connection), a new transaction is created.

The following database commands will be executed in the context of the same transaction – not only the commands issued by the first cursor, but the ones issued by all the cursors created by the same connection.

Should any command fail, the transaction will be aborted and no further command will be executed until a call to the rollback() method.

The connection is responsible for terminating its transaction, calling either the commit() or rollback() method.

Committed changes are immediately made persistent in the database. If the connection is closed or destroyed while a transaction is in progress, the server will discard the transaction.

1 - autocommit

It is possible to set the connection in autocommit mode. By default, the connection is in auto-commit mode. i.e., default auto-commit property is True. This way all the commands executed will be immediately committed and no rollback is possible. So we need make it False otherwise changes in database will not be rollback, in case of transaction failure..

The Syntax of AutoCommit

connection.autocommit=True or False

2 - commit()

Commit any pending transaction to the database. If commit() is not called, the effect of any data manipulation will be lost.

The syntax of Commit

connection.commit()

3 - rollback()

If any of the operations fail inside of a running transaction, you can revert the changes made by all of the previous operations using a rollback()

Closing a connection without committing the changes first may also be the reason for rollback.

The syntax of Rollback

connection.rollback()

4 - close()

Close the connection after finishing the work.

The syntax of Close

connection.close()

closing a connection without committing the changes first will cause any pending change to be discarded as if a ROLLBACK was performed

Example :

import psycopg2

def updateRecord(userName, userId);
    update_sql = """UPDATE sololearn_user SET user_name = %s WHERE user_id = %s"""

    updated_rows = 0
             
    try:
        connection = psycopg2.connect(user = "postgres",
            password = "12345",
            host = "127.0.0.1",
            port = "5432",
            database = "postgres_db")

        connection.autocommit = False
        cursor = connection.cursor()
    
        # execute the UPDATE  statement
        cursor.execute(update_sql, (userName, userId))
    
        # get the number of updated rows
        updated_rows = cursor.rowcount
    
        print("Data Updated Count", updated_rows, "\n")
        
        # commiting the transction to database
        connection.commit()
        print("Transaction completed successfully")

    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
        
        #rollback the changes in case of transaction failure
        connection.rollback()
    finally:
        #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

updateRecord("AJ Anant", 12345)

You will get the below output in case of all transaction completed successfully.

Data Updated Count 1
Transaction completed successfully
PostgreSQL connection is closed

In case of transaction failure, output will come like:

Error while connecting to PostgreSQL
PostgreSQL connection is closed

With Statement to control Transactions

If the connection is used in a with statement, the method is automatically called if no exception is raised in the with block.

psycopg2’s connections and cursors are context managers and can be used with the with statement:

Syntax:

with psycopg2.connect(connection_arguments) as conn:
    with conn.cursor() as curs:
        curs.execute(SQL)

When a connection exits the with block, if no exception has been raised by the block, the transaction is committed. In case of exception the transaction is rolled back.

When a cursor exits the with block it is closed, releasing any resource eventually associated with it. The state of the transaction is not affected.

A connection can be used in more than a with statement and each with block is effectively wrapped in a separate transaction:

conn = psycopg2.connect(connection_arguments)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL1)
        
with conn:
    with conn.cursor() as curs:
        curs.execute(SQL2)

Example :

import psycopg2

def updateRecord(userName, userId);
    update_sql = """UPDATE sololearn_user SET user_name = %s WHERE user_id = %s"""

    updated_rows = 0
             
    try:
        connection = psycopg2.connect(user = "postgres",
            password = "12345",
            host = "127.0.0.1",
            port = "5432",
            database = "postgres_db")

        with connection:
            with connection.cursor() as cursor:
    
                # execute the UPDATE  statement
                cursor.execute(update_sql, (userName, userId))
    
                # get the number of updated rows
                updated_rows = cursor.rowcount
    
                print("Data Updated Count"    , updated_rows, "\n")
        
                print("Transaction completed successfully")

    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
    finally:
        #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

updateRecord("AJ Anant", 12345)

In the above example, we did not use commit and rollback because with statement will handle everything.

Exceptions

Warning

Exception raised for important warnings like data truncations while inserting, etc.

Error

Exception that is the base class of all other error exceptions. You can use this to catch all errors with one single except statement.

Warnings are not considered errors and thus should not use this class as base.

InterfaceError

Exception raised for errors that are related to the database interface rather than the database itself.

DatabaseError

Exception raised for errors that are related to the database.

DataError

Exception raised for errors that are due to problems with the processed data like division by zero, numeric value out of range, etc.

OperationalError

Exception raised for errors that are related to the database's operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc.

IntegrityError

Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails.

InternalError

Exception raised when the database encounters an internal error, e.g. the cursor is not valid anymore, the transaction is out of sync, etc.

ProgrammingError

Exception raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement, wrong number of parameters specified, etc.

NotSupportedError

Exception raised in case a method or database API was used which is not supported by the database, e.g. requesting a .rollback() on a connection that does not support transaction or has transactions turned off.

PostgreSQL function in Python

callproc() is used to call the PostgrSQL functions. Just pass the name of the function and the optional input values to the callproc() method.

Internally, the callproc() method translates the function call and input values into the following statement:

SELECT * FROM function_name(value1, value2);

Therefore, you can use the execute() method of the cursor object to call a function as follows:

cursor.execute("SELECT * FROM function_name( %s,%s); ", (value1, value2))

After that, process the result set returned by the function using the fetchone(), fetchall(), or fetchmany() method.

Example :

The following get_posts_by_user_id() function returns a list of posts posted by a specified user.

CREATE OR REPLACE FUNCTION get_posts_by_user_id(id integer)
  RETURNS TABLE(user_id INTEGER, user_posts_description VARCHAR) AS
$$
BEGIN
    RETURN QUERY

    SELECT users.user_id, user_posts.user_posts_description
    FROM users
    INNER JOIN user_posts on user_posts.user_id = users.user_id
    WHERE user_id = id;

END; $$

LANGUAGE plpgsql;

The following get_user_posts() function calls the get_posts_by_user_id() function:

import psycopg2

def get_user_posts(user_id):
    try:
        connection = psycopg2.connect(user = "postgres",
            password = "12345",
            host = "127.0.0.1",
            port = "5432",
            database = "postgres_db")

        cursor = connection.cursor()

        cursor.callproc('get_posts_by_user_id', (user_id,))
        row  = cursor.fetchone()
    
        while row is not None:
            print(row)
            row = cursor.fetchone()

    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
    finally:
        #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

To be continue for more...

Tutorial 5 - PostgreSQL with Python