Using `internal_utils` functions from the `jmspack` package¶

Showing the usage of the following internal_utils functions¶

postgresql_data_extraction()
postgresql_table_names_list()
create_postgresql_table_based_on_df()
add_data_to_postgresql_table()
delete_postgresql_table()

[1]:

import os
tmp = os.getcwd()
os.chdir(tmp.split("jmspack")[0] + "jmspack")

[2]:

import psycopg2
import pandas as pd
from dotenv import load_dotenv, find_dotenv
from jmspack.internal_utils import (postgresql_data_extraction,
                                    postgresql_table_names_list,
                                   create_postgresql_table_based_on_df,
                                    add_data_to_postgresql_table,
                                   delete_postgresql_table)

[ ]:

os.chdir(tmp)

[3]:

load_dotenv(find_dotenv())

[3]:

True

Extract table names from an external postgresql database¶

[4]:

table_list = postgresql_table_names_list(database_name = 'tracker',
                                         user='tracker',)
table_list

could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

[4]:

False

Create an external postgresql table based on a pandas dataframe¶

[5]:

import seaborn as sns
iris_df = sns.load_dataset("iris")

[6]:

create_postgresql_table_based_on_df(df=iris_df,
                         database_name="tracker",
                             user="tracker",
                            table_name="iris_test",
                            )

could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

[6]:

'CREATE TABLE iris_test (\n                sepal_length float,\nsepal_width float,\npetal_length float,\npetal_width float,\nspecies text\n    )'

Add data from a pandas dataframe to an external postgresql table¶

[7]:

add_data_to_postgresql_table(df=iris_df,
                         database_name="tracker",
                             user="tracker",
                            table_name="iris_test",
                            )

could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

[7]:

'INSERT INTO iris_test (sepal_length, sepal_width, petal_length, petal_width, species) VALUES (%s, %s, %s, %s, %s)'

Read in data from an external postgresql database¶

[8]:

df = postgresql_data_extraction(table_name = 'sleep_data',
                                database_name = 'tracker',
                                user='tracker',)

I am unable to connect to the database

[9]:

df

[9]:

[10]:

# df = postgresql_data_extraction(table_name = 'suggested_energy_intake',
#                                 database_name = 'tracker',
#                                 user='tracker',)

df = postgresql_data_extraction(table_name = 'iris_test',
                                database_name = 'tracker',
                                user='tracker',)

I am unable to connect to the database

[11]:

df.head()

[11]:

Delete an external postgresql database¶

Printing table names before and after to check removal occurs¶

[12]:

postgresql_table_names_list(database_name = 'tracker',
                                         user='tracker',)

could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

[12]:

False

[13]:

delete_postgresql_table(database_name="tracker",
                             user="tracker",
                            table_name="iris_test",)

could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

[13]:

'iris_test has been deleted from tracker'

[14]:

postgresql_table_names_list(database_name = 'tracker',
                                         user='tracker',)

could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

[14]:

False