How to Get Data From a PostgreSQL Database in Jupyter Notebook

This article will show how to get data from a PostgreSQL database in a script runs in a jupyter notebook. There are several steps in order to get a data from a PostgreSQL database via jupyter notebook. The most important thing is just run the jupyter notebook. The following is the execution of the jupyter notebook :

(myenv) C:\python\data-science>jupyter notebook
[I 17:08:28.062 NotebookApp] Serving notebooks from local directory: C:\python\data-science
[I 17:08:28.062 NotebookApp] The Jupyter Notebook is running at:
[I 17:08:28.067 NotebookApp] http://localhost:8888/?token=4dd9801ef2aacad1d445955b0ae4621b4c669da84c617e7b
[I 17:08:28.068 NotebookApp]  or http://127.0.0.1:8888/?token=4dd9801ef2aacad1d445955b0ae4621b4c669da84c617e7b
[I 17:08:28.070 NotebookApp] Use Control-C to stop this server and shut down all kernels (twice to skip confirmation).
[C 17:08:28.225 NotebookApp]
    To access the notebook, open this file in a browser:
        file:///C:/Users/Personal/AppData/Roaming/jupyter/runtime/nbserver-8796-open.html
    Or copy and paste one of these URLs:
        http://localhost:8888/?token=4dd9801ef2aacad1d445955b0ae4621b4c669da84c617e7b
     or http://127.0.0.1:8888/?token=4dd9801ef2aacad1d445955b0ae4621b4c669da84c617e7b

After successfully run the jupyter notebook, start inserting and executing the following script :

#import library psycopyg2
import psycopg2
#import library pandas
import pandas as pd
#import library sqlio
import pandas.io.sql as sqlio
#create database connection variable 
conn = psycopg2.connect(user="user", password="password", host="xxx.xxx.xxx.xxx", database="db_name")

Just execute the above script accordingly. Adjust the the above database connection parameter so that the it can work as is. It is important to define the value of the ‘user’, ‘password’, ‘host’, port and also ‘database’ representing the name of the database.

#define query
query = "select * from table_name"

After successfully defining the above connection, define the query. Finally, execute the following script to start to get the data from the database.

#execute query and save it to a variable
dataset = sqlio.read_sql_query(query,conn)

The above script example exist in the following image :