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 :