Connecting your Distilled data warehouse to Jupyter Notebook.
Welcome to this documentation guide on how to connect your Distilled database to Jupyter Notebook. By following the instructions outlined in this guide, you will be able to utilize the available connection string in your account to establish a connection between your Distilled data and Jupyter Notebook. This will enable you to access and manipulate your data in a Python environment, leveraging the power and flexibility of Python’s data analysis tools. Whether you are a data scientist, analyst, or researcher, this guide will equip you with the skills necessary to effectively analyze your data in a Jupyter Notebook environment.
Before you begin:
Before you begin, gather your connection string details for your Distilled PostgreSQL database. To find your Distilled database name and port details, access your Distilled account and navigate to Analyze > Fetch your data and use the connection string details
Libraries to Install:
- ipython-sql
- sqlalchemy
- A python database API (DBAPI) library
The libraries you’ll need can be installed by using the pip install commands:
pip install ipython-sql pip install sqlalchemy pip install psycopg2
ipython-sql
Created by catherinedevlin on Github, this enables the use of SQL magic functions that contain % and %% , allowing you to write SQL style code right in Jupyter Notebook.
sqlalchemy
sqlalchemy is touted as a “SQL toolkit and object-relational-mapper” for Python. It will mainly be used to store SQL queries into a pandas dataframe.
Now that we have our libraries installed, let’s move to Jupyter Notebook.
How to Start
To load ipython-sql, use the following magic command:
%load_ext sql
Next, we will only need the create_engine() function from sqlalchemy so let’s import that with the following line:
from sqlalchemy import create_engine
From here, we can now connect to your Distilled PostgreSQL database.
Connecting to Your Distilled PostgreSQL Database
To connect ipython-sql to your database, use the following format:
# Format %sql dialect+driver://username:password@host:port/database # Example format %sql postgresql://postgres:password123@localhost/dvdrental
- dialect+driver in this case would just be postgresql
- username:password is where you will substitute your username and password
- host is can be found in your connection details
- port can be found in your conncetion details
- database can be found in your connection details, but may not be needed in all use-cases
To see if it successfully connected, Jupyter Notebook will print the following:
'Connected: username@database'
To connect sqlalchemy to the database, the format will largely be the same, but we will create a new object called engine using the create_engine() function:
# Format
engine = create_engine('dialect+driver://username:password@host:port/database')
# Example format
engine = create_engine('postgresql://postgres:password123@localhost/dvdrental')
You should be able to copy & paste what you wrote after the %sql magic command when connecting to ipython-sql and encapsulate it in quotes as a parameter in the create_engine() function.
As long as you see no errors in the output, you should be connected to your Distilled PostgreSQL database.
