Jupyter Lab and Postgresql

Let us understand how we can integrate Jupyter Lab and Postgres so that we can leverage intuitive and interactive Jupyter based environment to practice SQL.

  • Using Jupyter Lab or Jupyter Notebook is optional. You can leverage SQL Workbench or psql to practice. However, using psql is a bit tricky and can take away considerable amount of time.

  • We need additional libraries to be setup as part of Jupyter environment for integrating Notebooks with Postgres to write queries with out writing any code. Before getting into setup let us understand the pre-requisites.

    • You should have Python3 installed.

    • Also you should have setup Jupyter Lab environment by now. If not you can follow our playlist for the same. You will get step by step instructions to setup Jupyter Lab on Ubuntu VM on GCP using Docker.

  • Once Jupyter Lab is setup we need to install the following to leverage Jupyter based notebooks to practice SQL.

    • You need to install ipython-sql library using pip with in the virtual environment used to setup Jupyter Lab.

    • You also need to install SQL Alchemy to facilitate the connectivity between Jupyter Notebooks and the databases. However, it will be installed along with ipython-sql. You can run pip list to validate whether SQL Alchemy is installed or not.

    • Also we need to install psycopg2 to connect to Postgres database. If you are using Mac to setup Jupyter Lab, you have to install Postgresql using brew install postgresql.

pip install ipython-sql
pip list
brew install postgresql # On Mac
pip install psycopg2

  • Here are the instructions to setup Postgresql on Ubuntu. You can get latest instructions from this link.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql-common

  • Make sure the Postgres database in docker is running fine. If not, start the docker container and then start Jupyter Lab.

docker ps -a
docker start itv_pg

jupyter lab --ip 0.0.0.0

  • Now it is time for us to connect to Jupyter Lab using browser and validate.

  • Once all the libraries are installed, we need to load sql extension and then create environment variable called as DATABASE_URL using all the connectivity information.

  • We can run a query to validate that we are connected to the database.

%load_ext sql

%env DATABASE_URL=postgresql://retail_user:retail_password@localhost:5432/retail_db

%sql SELECT current_date()
Troubleshooting Issues Perform Database Operations