Connect to Postgresql with Python 3.x and get Pandas Dataframe

Upasana | February 13, 2020 | 2 min read | 656 views


We will be learning

  • How to connect to PostgreSQL in Python

  • How to get output of query as Pandas Dataframe

Connect to PostgreSQL in Python

We will be using PyGreSQL Library to make connection.

Install PyGreSQL

Create virtual environment in project directory

virtualenv -p python3 venv

Activate virtual environment

source venv/bin/activate

To know more about virtual environment, click here

Install package

pip install PyGreSQL

Define a class for connecting to PostgreSQL

from pgdb import connect

class PostGresql(object):

    def __init__(self):
        self.connection_pool = None
        self.database = "database" //(1)
        self.host = "host" //(2)
        self.user = "user" //(3)
        self.password = "password" //(4)

    def get_connection(self):
        connection = connect(database=self.database,host=self.host,user=self.user,password=self.password)
        return connection
  1. Database Name

  2. Host of database

  3. Username

  4. Password of DB

Now, we will be making another class to initiate the connection

import pandas as pd

class PostgreDao(object):

    def __init__(self, postSqlDb):
        self.postSqlDb = postSqlDb
        print("Acquiring connection")
        self.connection = postSqlDb.get_connection()

    def __del__(self):
        print("Closing db connection")
        self.connection.close()

    def read_table(self, query):
        """Read SQL Table
            Args:
              query: text, query to read table or multiple tables
            Returns:
              pandas dataframe, if query is valid
            """
        df = pd.read_sql_query(query, self.connection)
        return df
main.py
dbcon = PostGresql()
dbclass = PostgreDao(dbcon)

query = """
            select * from table
            """
data = dbclass.read_table(query)

Output of data will be a pandas dataframe

In case, you are looking to connect to MySQL Database then follow this article


Top articles in this category:
  1. Connect to MySQL with Python 3.x and get Pandas Dataframe
  2. Connect to Cassandra with Python 3.x and get Pandas Dataframe
  3. Google Colab: import data from google drive as pandas dataframe
  4. Top 100 interview questions on Data Science & Machine Learning
  5. Google Data Scientist interview questions with answers
  6. Python - Get Google Analytics Data
  7. Python send GMAIL with attachment

Recommended books for interview preparation:

Find more on this topic: