How to use PostgreSQL with Jupyter Notebook in Python

In this tutorial, you will learn how to make SQL queries inside a Jupyter Notebook

Prerequisites:

You need to have PostgreSQL installed on your machine and the Python modules Psycopg, SQLAlchemy and pandas.
Take a look at this tutorial to setup your machine

Database connection

Open Postgres on your machine and confirm that your database is running. Now we import sqalchemy as sa and create an postgresql engine to make a connection to our database. To learn more about the engine configuration, visit the documentation:

import sqlalchemy as sa

engine = sa.create_engine('postgresql://your_username:your_password@localhost:your_port/your_database')

Jupyter Notebook SQL-extension

Install the Jupyter Notebook extension ipython-sql:

pip install ipython-sql

Load the sql extension:

%load_ext sql

Set up the connection:

%sql $engine.url

Now we use the magic command %sql to make a SQL query to the table “gapminder” inside a database called “postgres”:

%sql SELECT * FROM gapminder LIMIT 3