Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

PostgreSQL – Connecting to the database using Python

  • Last Updated : 30 Aug, 2020

PostgreSQL API for Python allows user to interact with the PostgreSQL database using the psycopg2 module. In this article we will look into the process of connecting to a PostgreSQL database using Python.

Prerequisites:

First we will need to install the psycopg2 module with the below command in the command prompt:

pip install psycopg2

 

Creating a Database:

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

  • First open a PostgreSQL client tool like pgadmin4 or psql.
  • Second login to the database using your credentials.
  • 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.

To do so use the below syntax:

Syntax: conn = psycopg2.connect("dbname=suppliers user=postgres password=postgres")

To make it convenient to use you can use the below method:

1. First create a database.ini file with the credentials as shown below:

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

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:

Python3




#!/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

Example:

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

Python3




#!/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:


My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!