Jupyter Lab and Postgresql on Ubuntu VMΒΆ

Let us go through the steps related to integrating Jupyter Lab with Postgresql on Ubuntu VM.

  • 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.

    • Activate the virtual environment.

cd delab
source delab-venv/bin/activate

  • 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

  • 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.

pip install ipython-sql
pip list
pip install psycopg2-binary

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

docker ps -a
docker start itv_pg

  • Let us also create a demo database to validate the connectivity.

docker exec -it itv_pg psql -U postgres

CREATE DATABASE demo_db;
CREATE USER demo_user WITH ENCRYPTED PASSWORD 'demo_password';
GRANT ALL ON DATABASE demo_db TO demo_user;

\q

  • Start jupyter lab.

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://demo_user:demo_password@localhost:5432/demo_db

%sql SELECT current_date
Jupyter Lab and Postgresql