How to Connect to PostgreSQL Database Using Python

1. Install PostgreSQL in the System
2. Create a Data Server and a Database Using pgAdmin
3. Steps to Connect the PostgreSQL Database to Python
4. Create Configuration (.ini) File That Stores the Server Information
5. Create Python File That Parses the Configuration File
6. Steps to Connect to the PostgreSQL Database
7. Conclusion

This article explains the procedure to create a connection to databases that are made on PostgreSQL. We need prerequisites like installing PostgreSQL and creating a database, as explained below.

Install PostgreSQL in the System

As the name suggests, PostgreSQL is a SQL system software created to efficiently manage database systems.

A database needs to be created before it can be connected to Python. Postgres, implement it.

Confusion persists in lots of beginners who start out learning database development. It is perceived that the database is created using pgAdmin and not PostgreSQL.

In actuality, the former manages the databases while the latter is the framework on which it is built.

pgAdmin needs to be linked with PostgreSQL before a database can be created. So, at first, PostgreSQL is needed to be installed.

The installation package of Postgres can be found on the downloads section of the official website of PostgreSQL. The user may then download and install the application from there.

Various installers are available for operating systems like Mac, Linux, and Windows.

Users may also get the source code and manually compile and install pgAdmin4 on their PC.

pgAdmin is an efficient tool for managing databases that work on PostgreSQL. The downloadable files like the installer can be found on the application’s online webpage.

One can choose from a list of all the stable releases from pgAdmin, and other distributions of installation packages that were similar to the process of PostgreSQL.

Once pgAdmin is installed into the system, databases can be created.

Create a Data Server and a Database Using pgAdmin

This section contains two subsections. The first section explains how to create data servers, while the second focuses on databases.

Create a Data Server in pgAdmin

Before any database gets created, pgAdmin needs to be set up properly. A prompt asks for a master password that will be used when a new database is created or accessed.

The pgAdmin webpage appears when the password has been provided. A new server must be built to generate a new database.

The Add New Server button creates a dialogue window where a new server can be built.

The window displayed at first provides the functionalities of the server that is being set up. In this article, a few of them will be provided with user input, while others are system generated and will be left as it is.

At first, the name of the server is required. After that, head over to the connection menu.

The Hostname is required there, usually localhost. The port must be set at 5432.

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

Create Database in pgAdmin

Once the data server is up and operating, databases may be created. The created servers are displayed on the left-hand side of the application window, known as its dashboard.

A dropdown icon is present inside the left-hand side panel beside the server name. A password dialog pops when this icon is clicked, requesting the system’s master password.

A menu shows all the servers that are created inside the system. It remains deactivated until activated with a click followed by a password prompt.

Right-click the Databases area and select create. Give a name to the database, and select postgres inside the owner section; then, the database definition must be set.

Definition of a database has multiple options to set. The following are some of the more essential options among the many available.

1. Encoding must be set at - UTF - 8.
2. Template should be set at Postgres.
3. Tablespace should be set at pg_default.

Collation and Character type should be set as is, while the connection limit is set at -1. Heading over to the menu labeled sql will give an overview of the query used here.

Clicking on save will create a database.

Steps to Connect the PostgreSQL Database to Python

Connecting to a database using Python is a three-step process. At first, the server’s information is stored in a 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, a Python file is created that connects the database.

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

Create Configuration (.ini) File That Stores the Server Information

This file stores the details related to the server, which helps the config.py file configure the database. The file’s head, which declares the RDBMS used, is situated at the top of the file.

1. host - The host or the server used is provided here.
2. database - The specific database that needs to be targeted is given here.
3. user - The user should be given as postgres, as it is the RDBMS.
4. password - The master password given in pgAdmin while creating the database must be entered here.

Once the info file is created, it can be used inside the configure file.

Create Python File That Parses the Configuration File

This program uses the import package configparser. A method config is declared with two parameters, filename and section.

A variable parser is initialized that reads the file from the variable filename.

Below is the get method that extracts items from the database. The get section is put inside an if-else statement, where the else method handles the exception.

Finally, the variable database is returned.

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 the PostgreSQL Database

The program has two import files.

1. psycopg2
2. config

A method connect is created that connects to the PostgreSQL database server.

A variable param is declared that is used to read the parameters of the connection. These parameters are used to connect to the database server.

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

The version of PostgreSQL for the database gets displayed after the connection is made. var_cur.execute executes the statement SELECT version().

The version is loaded into the variable version_of_database, which is then displayed by the fetchone() function, which fetches single elements at one time. The variable is then printed.

After the database version is fetched, the cursor is closed using var_cur.close().

Exception handling block is added to raise error exceptions. Inside the except block, the program prints an error message when no connection could be made to the database or if the database is not found.

At the end of exception handling, a finally block is added that closes the connection using the syntax connection.close(). After the connection is closed, the database prints a message that confirms that the connection is closed.

Lastly, 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:

Conclusion

This article discusses database creation and explains how databases are created in PostgreSQL. The different functionalities of the software tool pgAdmin.

The reader gets to learn how to connect a database using Python in a detailed manner so that this learning can be picked up quickly and used in real-life projects.