Brogrammersnepal

Connect And Run PostgreSQL From Jupyter Notebook

Jupyter Notebook is a popular tool among data scientists and software developers. If you are a fan of jupyter notebook like me, and want to integrate with Postgres then you are at the right place. This article will guide you to set up a Jupyter Notebook on your Linux machine. The process should work for all Debian-based machines. Also if you are on Windows then also the steps should be fairly similar.

Prerequisites:

  • Jupyter Notebook or Jupyter-Lab
  • Python3
  • PostgresSql
  • Ubuntu or Debian based distro(Optional)

Libraries:

  • Psycopg2 – PostgreSQL database adapter for the Python programming language
  • SQLAlchemy – Object-relational mapper for the Python programming
  • Optional Libraries:

Pandas: Popular data analysis library available in Python. It can also convert any query into a pandas dataframe which can be used with visualization libraries to grab insight from data.

Matplotlib: Popular data analysis, visualization tool

Follow these 2 steps to set up Jupyter Notebook:

I). Installing Libraries

II). Configuring in Jupyter Notebook

I). Installing Libraries:

Follow the steps listed below to install dependencies.

  1. Psycopg2: This can be easily installed with pip3 or conda. In the case of windows pip(only)/conda should be used.
    • pip install psycopg2 or pip3 install psycopg2

The above command will install psycopg2. But, if an error like in the screenshot occurred then you should follow the steps below:

  • Install psycopg2 binary:
    • pip install psycopg2-binary

  • Install libpq: It is a set of library functions that allow client programs to pass queries to the PostgreSQL
    • sudo apt install libpq-dev python3-dev

  • Install psycog2 again
    • pip install psycopg2

  1. SQLAlchemy: This can be easily installed with pip3 or conda.
    • pip3 install sqlalchemy

ii). Configuring in Jupyter Notebook:

  1. Open jupyter notebook and on notebook and run following commands in cell
    • from sqlalchemy import create_engine
    • %load_ext sql
    • %sql postgresql://your_user_name:password@localhost/database_name

Eg: %sql postgresql://postgres:1234@localhost/suprim

Here postgres is my user name, suprim is my database which is running on localhost. For, a reference you can see the screenshot below used to connect to Postgres database by postgres user.

    • engine = create_engine(‘postgresql://postgres:1234@localhost/suprim’)

This will connect you with your database.

2. If you have followed the mentioned step properly, then you should be connected to some database in your local Postgres server. Now, to query we need to write %%sql always on the top of each cell.

—Back To Top—

I am the Author

Share on facebook
Share on twitter
Share on linkedin

Related Articles

Recent Posts

Like us on Facebook

Sponsors

This is the heading

Lorem ipsum dolor sit amet consectetur adipiscing elit dolor

This is the heading

Lorem ipsum dolor sit amet consectetur adipiscing elit dolor

This is the heading

Lorem ipsum dolor sit amet consectetur adipiscing elit dolor