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

Carvia Tech | February 13, 2020 | 2 min read | 286 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. Python Flask Interview Questions
  3. Top 100 interview questions on Data Science & Machine Learning
  4. Python coding challenges for interviews
  5. Google Data Scientist interview questions with answers
  6. Connect to Cassandra with Python 3.x and get Pandas Dataframe
  7. Google Colab: import data from google drive as pandas dataframe


Find more on this topic:
Machine Learning image
Machine Learning

Data science, machine learning, python, R, big data, spark, the Jupyter notebook, and much more

Last updated 1 week ago


Recommended books for interview preparation:

This website uses cookies to ensure you get the best experience on our website. more info