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) andpandas
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 |