How to use PostgreSQL with Jupyter Notebook in Python

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
  • You need the Python modules Psycopg, SQLAlchemy (Version 1.4.46) and pandas

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://postgres:your_password@localhost:5438/postgres')

Jupyter Notebook SQL-extension

Install the Jupyter Notebook extension ipython-sql:

conda install -c conda-forge ipython-sql

Load the sql extension:

%load_ext sql

Set up the connection:

%sql $engine.url
'Connected: postgres@postgres'

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
 * postgresql://postgres:***@localhost:5438/postgres
3 rows affected.
index country continent year lifeExp pop gdpPercap
0 Afghanistan Asia 1952 28.801 8425333 779.4453145
1 Afghanistan Asia 1957 30.332 9240934 820.8530296
2 Afghanistan Asia 1962 31.997 10267083 853.10071
Jan Kirenz

I’m a data scientist educator and consultant.