PostgresSQL_Get_data_from_database.md

Template request | Bug report | Generate Data Product

Tags: #postgressql #database #operations #snippet #dataframe

Author: Maxime Jublou

Description: This notebook provides instructions on how to query a PostgreSQL database and retrieve data.

Input

Import librairies

try:
    import psycopg2
except:
    !pip install psycopg2-binary --user
    import psycopg2
import pandas as pd
import naas

Setup PostgresSQL

# Credentials
PG_USER = "YOUR_PG_USER"
PG_PASSWORD = "YOUR_PG_PASSWORD"
PG_HOST = "YOUR_PG_HOST"
PG_DBNAME = "YOUR_PG_DBNAME"

# Database
DATABASE = "YOUR_DATABASE"
SELECTED_FIELD = "*"  # "*" or list of columns

# SQL Requests
SQL_REQUEST = f"SELECT {SELECTED_FIELD} FROM {DATABASE}"

Model

Connect to PostgresSQL

PG = psycopg2.connect(
    f"user={PG_USER} password={PG_PASSWORD} dbname={PG_DBNAME} host={PG_HOST}"
)

Get naas users

def get_data():
    cur = PG.cursor()
    cur.execute(SQL_REQUEST)
    res = cur.fetchall()
    cur.close()
    df = pd.DataFrame(res)
    return df


df = get_data()

Output

Display result

df