PostgreSQL - Connecting to the Database using Python

PostgreSQL in Python offers a robust solution for developers looking to interact with databases seamlessly. With the psycopg2 tutorial, we can easily connect Python to PostgreSQL, enabling us to perform various database operations efficiently. In this article, we will walk you through the essential steps required to use PostgreSQL in our Python applications.

PostgreSQL - Connecting to the Database using Python

To get started, we first need to ensure that we have the psycopg2 package installed. This package serves as the PostgreSQL database adapter for Python, allowing us to communicate with our PostgreSQL database effectively.

Prerequisites for Using psycopg2

Before diving into the examples, ensure we have Python installed on our system. We will need the psycopg2 module, which can be installed using the following command in our command prompt or terminal:

pip install psycopg2

Alternatively, for a more straightforward installation that doesn't require a compiler, we can use:

pip install psycopg2-binary

Creating a PostgreSQL Database

For the purpose of example we will be needing a sample database. To create so, follow the below steps:

1. First open a PostgreSQL client tool like pgadmin4 or psql.
   
2. Second login to the database using your credentials.
   
3. Finally use the below command to create a database (say, School)

CREATE DATABASE school;

Connecting to the database

To connect to the above created database (ie, school), we use the connect () function. The connect() function is used to create a new database session and it returns a new connection class instance.

Basic Connection Example

Use the following syntax to connect:

import psycopg2

# Connect to the School database
conn = psycopg2.connect(
    dbname="school",
    user="postgres",
    password="your_password",
    host="localhost"
)

Using a Configuration File

To make your connection more convenient and secure, we can use a configuration file. Create a file named database.ini with the following content:

[postgresql]
host=localhost
database=school
user=postgres
password=your_password

Now, the following config() function reads the database.ini file and returns connection parameters. The same config() function is added in the config.py file:

#!/usr/bin/python
from configparser import ConfigParser


def config(filename='database.ini', section='postgresql'):
    # create a parser
    parser = ConfigParser()
    # read config file
    parser.read(filename)

    # get section, default to postgresql
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception('Section {0} not found in the {1} file'.format(section, filename))

    return db

Fetching PostgreSQL Version

The following connect() function connects to the school database that we created earlier and returns  the PostgreSQL database version.

#!/usr/bin/python
import psycopg2
from config import config

def connect():
    """ Connect to the PostgreSQL database server """
    conn = None
    try:
        # read connection parameters
        params = config()

        # connect to the PostgreSQL server
        print('Connecting to the PostgreSQL database...')
        conn = psycopg2.connect(**params)
        
        # create a cursor
        cur = conn.cursor()
        
    # execute a statement
        print('PostgreSQL database version:')
        cur.execute('SELECT version()')

        # display the PostgreSQL database server version
        db_version = cur.fetchone()
        print(db_version)
       
    # close the communication with the PostgreSQL
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
            print('Database connection closed.')


if __name__ == '__main__':
    connect()

Output

Conclusion

In this article, we explored how to create a PostgreSQL database with Python and manage PostgreSQL databases with Python using the psycopg2 module. This powerful PostgreSQL database adapter for Python streamlines the process of interacting with our database, allowing us to perform operations efficiently. Mastering these skills will undoubtedly enhance our capabilities as a developer working with data.