Skip to content
Related Articles

Related Articles

Improve Article

PostgreSQL – Connecting to the database using Python

  • Last Updated : 30 Aug, 2020
Geek Week

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:

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course




My Personal Notes arrow_drop_up
Recommended Articles
Page :