Python and PostgreSQL Introduction

PostgreSQL is a database that can be used in conjunction with Python to create desktop, web and console based application. It can be used to store data, as well as configuration information. Python, together with SQL, can be used to query the data stored within a PostgreSQL database, as well as insert, update and delete the data.

Connecting to a PostgreSQL Database

In order to access data within a PostgreSQL database using Python, a connection to the database must first be established. Below is an example of how this can be achieved. In order for this to work, the third-party module Psycopg2 must be installed.

import psycopg2

# Database connection variable.
connect = None

# Database connection arguments.
connectargs = {
    'host': 'localhost',
    'database': 'Demo',
    'user': 'DemoUN',
    'password': 'DemoPW'
}

try:

    # Connect to database.
    connect = psycopg2.connect(**connectargs)

    # Message confirming successful database connection.
    print("Database connection successful.")

except psycopg2.DatabaseError as e:

    # Confirm unsuccessful connection and stop program execution.
    print("Database connection unsuccessful.")
    quit()

In this example, a ‘try-except’ block is used to catch any exceptions that may arise in connecting to the database. A ‘try-except’ block can be used to handle any exceptions in a user friendly manner. The ‘quit’ statement, after the confirmation of an unsuccessful database connection, stops execution of the program completely, so no further statements are executed.

The database connection arguments, which include the server and database information, are contained within a dictionary and then incorporated into the database connection, using the shorthand method, ‘**connectargs’. It should be noted that this information can be incorporated directly into the database connection. It much depends on personal preference and whether the dictionary is going to be re-used elsewhere in the program. The database connection can be re-written as follows.

# Connect to database.
connect = psycopg2.connect(host='localhost', database='Demo',
                           user='DemoUN', password='DemoPW')