Connect to a PostgreSQL database using Python

This article describes the process of creating a connection to a database on PostgreSQL. We need prerequisites like installing PostgreSQL and creating a database as explained below.

Install PostgreSQL on the system

As the name suggests, PostgreSQL is a SQL system software created for efficient management of database systems.

You need to create a database before connecting to Python. Postgres, make it happen.

Many beginners who are just starting to learn database development are still confused. It is understood that the database was created using pgAdmin and not PostgreSQL.

In fact, the former manages the database, while the latter is the framework on which the database is built.

Before creating the database, pgAdmin needs to be linked with PostgreSQL. Therefore, you first need to install PostgreSQL.

The Postgres installation package can be found in the download area of the PostgreSQL official website. Users can then download and install the app from there.

There are various installers available for operating systems such as Mac, Linux, and Windows.

Users can also obtain the source code and manually compile and install pgAdmin4 on their PC.

pgAdmin is an efficient tool for managing databases running on PostgreSQL. Downloadable files such as installers can be found on the application’s online page.

One can choose from a list of all stable versions of pgAdmin, as well as other installation package distributions similar to the PostgreSQL process.

Once pgAdmin is installed on the system, the database can be created.

Use pgAdmin to create data servers and databases

This section contains two subsections. The first part explains how to create a data server, and the second part focuses on databases.

Create a data server in pgAdmin

Before creating any database, pgAdmin needs to be set up correctly. You will be prompted for the master password, which will be used when creating or accessing new databases.

After providing the password, the pgAdmin web page appears. A new server must be built to generate a new database.

The Add New Server button creates a dialog window in which a new server can be built.

The window that appears first provides the functionality of the server being set up. In this article, some of them will be provided by user input, while others will be generated by the system and left as is.

First, you need the name of the server. After that, go to the connection menu.

The hostname is required there, usually localhost. The port must be set to 5432.

Following the above points is enough to create a useful data server.

Create database in pgAdmin

Once the data server is up and running, the database can be created. The created server is displayed on the left side of the application window, called the dashboard.

There is a drop-down icon next to the server name in the left panel. When this icon is clicked a password dialog pops up requesting the system’s master password.

The menu displays all servers created within the system. It remains deactivated until it is activated by clicking on it and a password prompt appears.

Right-click the database area and select Create. Give the database a name and select postgres in the Owner section; then, the database definition must be set up.

The database definition has several options that need to be set. Here are some of the more important options out of the many available.

1. Encoding must be set to -UTF-8.
2. Templates should be set in Postgres.
3. The tablespace should be set to pg_default.

The collation and character type should be set as is, while the connection limit is set to -1. Going to the menu labeled sql will give an overview of the queries used here.

Clicking “Save” will create a database.

Steps to connect a PostgreSQL database to Python

Connecting to a database using Python is a three-step process. First, the server’s information is stored in the configuration file.

A Python file is created that parses the configuration (.ini) file and loads the server in the next step. In the final step, create a Python file that connects to the database.

In this article, the program uses the psycopg2 import package to connect to a PostgreSQL database, obtain the database versions, and then print them.

Create a configuration (.ini) file that stores server information

This file stores server-related details and helps the config.py file configure the database. The file header is at the top of the file and declares the RDBMS used.

1. host – The host or server used is provided here.
2. database – The specific database to target is given here.
3. user – The user should be specified as postgres since it is an RDBMS.
4. password – The master password given in pgAdmin when creating the database must be entered here.

After creating the information file, you can use it in the configuration file.

Create a Python file that parses the configuration file

This program uses the import package configparser. Method configuration is declared with two parameters: filename and section.

The variable parser is initialized, reading the file from the variable filename.

Below is the get method to extract items from the database. The get part is placed inside the if-else statement, where the else method handles exceptions.

Finally return to the variable database.

from configparser import ConfigParser

def config(filename='server_info.ini', section='postgresql'):
    parser = ConfigParser()
    parser.read(filename)

    database = {<!-- -->}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            database[param[0]] = param[1]

    else:
        raise Exception('Section {0} not found in the {1} file'.format(section, filename))

    return database

Steps to connect to PostgreSQL database

The program has two import files.

1. psycopg2
2. config

Created a method connect to a PostgreSQL database server.

A variable param is declared for reading the connection parameters. These parameters are used to connect to the database server.

Syntax psycopg2.connect(**params) loads connection parameters and connects to the database server. Another variable, var_cur, is declared to store the cursor created by the connection.cursor syntax.

Once the connection is established, the PostgreSQL version of the database is displayed. var_cur.execute executes the statement SELECT version().

This version is loaded into the variable version_of_database and then displayed by the fetchone() function, which fetches a single element at a time. Then print the variable.

After getting the database version, use var_cur.close() to close the cursor.

Add an exception handling block to throw error exceptions. Within the except block, the program prints an error message when it cannot connect to the database or the database is not found.

At the end of exception handling, add a finally block to close the connection using the syntax connection.close(). After the connection is closed, the database prints a message confirming that the connection has been closed.

Finally, the method connection is called.

import psycopg2
from config import config


def connect():
    connection=None
    try:
        params = config()

        print('Connection made to the postgresql database')
        connection = psycopg2.connect(**params)

        var_cur = connection.cursor()

        print('Database version is - ')
        var_cur.execute('SELECT version()')

        version_of_database = var_cur.fetchone()
        print(version_of_database)

        var_cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if connection is not None:
            connection.close()
            print('Database connection closed.')


if __name__ == '__main__':
    connect()

Output:

Summary

This article discusses database creation and explains how to create a database in PostgreSQL. Different functions of the software tool pgAdmin.

Readers will learn in detail how to use Python to connect to the database so that they can quickly master and use it in actual projects.