How to Connect Jupyter Notebook to PostgreSQL: A Step-by-Step Guide.

Jupyter Notebook is a popular tool for data exploration, while PostgreSQL is one of the most widely used open-source relational databases that supports both relational (SQL) and non-relational (JSON) querying. Connecting the two allows you to run SQL queries, pull in data for analysis, and visualize results all within a single, interactive environment.

Let's see the way to connect to PostgreSQL from Jupyter Notebook using Python with an example.

Requirement Example:

To Connect to database using PostgreSQL and increase the temperature 2 degrees for participant with maximum humidity and display the result.

Prerequisites:

Initially we need to:

• Have Jupyter Notebook launched from Anaconda Navigator

• Connect to PostgreSQL and create a Database (example:Treadmill Database)

• Connect to the dataset by uploading the file to the Jupyter Notebook.

Here we are using Subject-Info Dataset that has the Age, Height, Weight, Humidity, Temperature, gender, ID, Heart Rate(HR), Speed and Respiratory Rate(RR) of the athletes working out on a treadmill.

Steps to follow:

Step 1: Install Required Libraries

Install the following libraries to make sure that can help in smooth connection to PostgreSQL.

Even though the most commonly used libraries are ipython-sql and sqlalchemy, In some cases there might be some issues with the connectivity due to the use of different versions of Windows.

In my case --user psycopg2 installation helped me..

Using the pip install commands, these libraries can be installed.

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

Psycopg is the most popular PostgreSQL database adapter for the Python programming language. Its main features are the complete implementation of the Python DB API 2.0 specification and the thread safety (several threads can share the same connection).

Psycopg2-binary package is meant for beginners to start playing with Python and PostgreSQL without the need to meet the build requirements. It was designed for heavily multi-threaded applications that create and destroy lots of cursors and make a large number of concurrent “INSERT”s or “UPDATE”s. Psycopg 2 is mostly implemented in C as a libpq wrapper, resulting in being both efficient and secure.

ipython-sql is a %sql magic for python. This is a magic extension that allows you to immediately write SQL queries into code cells and read the results into pandas DataFrames.

Also Import Pandas as it provides the read_sql() function.

Pandas is a Python library. Specifically, it is an open-source library designed for data manipulation and analysis. It provides data structures like DataFrames and Series that facilitate working with structured data. Pandas is built on top of NumPy and integrates well with other data science libraries like Matplotlib.

Run and wait for them to be installed.

Step 2: Connect to the Database

We can connect to the database using SQL alchemy and creating an engine

or by creating a connection using Magic Commands using ipython_sql.

I used the latter as my Windows was not supporting SQL alchemy.

The code looks something like below:

If we want to try giving the password externally without actually displaying it.

We can import getpass as shown below:

The getpass function in Python's getpass module is used to securely obtain password input from the user by preventing the entered characters from being displayed on the console. This is particularly useful when prompting for sensitive information, such as passwords or API keys, where echoing the input to the screen would pose a security risk.

To load the SQL extension within your Jupyter notebook, We can do it with the magic command `%load_ext sql`. This command prepares your environment for SQL execution, connect to SQL databases (like PostgreSQL, MySQL, SQLite, etc.) and display query results as DataFrames.

Step 3:Create Table and load data in PostgreSQL

Once we are done with connecting to the Database.

• Create a Table with the column headers and the datatypes.

• Load the data from the CSV file from your local:

  • When loading data from a CSV or Excel file, the file name used in the code must exactly match the file name on the local system. In this case, there was a special issue with a dataset named subject-info, which did not work correctly when referenced in the file path due to the hyphen.

  • To resolve this, we created a copy of the dataset, renamed the file from subject-info to subject_info, and then successfully loaded the data using the updated file path.

• View the table created.

  
  

  

Step 4: Write and execute the required the updated query and run it in Jupyter notebook

Run the required query to view the data.

Step 5: Write the updated query and run it in Jupyter notebook

Write the Updated Query based on the given requirements and execute it from the Jupyter notebook.

Step 6: Cross verify the output in Jupyter notebook and PostgreSQL

Now when we run the same query in Jupyter that we used in Step 4, we can see the updated Output:

Here, df_pg is the DataFrame into which the result of the query is loaded after it is executed.

The output in PostgreSQL:

Conclusion:

With just a few lines of setup using libraries like psycopg2, SQLAlchemy, or ipython-sql, we can quickly start working with live database data directly in our notebooks with no exporting or manual CSV handling required.

This enables us to seamlessly work with editing or fetching the required data and prepare reports efficiently.

Happy Exploring!