# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: jupyter.venv
#     language: python
#     name: jupyter.venv
# ---

# ## Install required python libraries

# !pip install requests

# !pip install psycopg2-binary

# !pip install pandas

# ## Import the libraries

import json
import psycopg2
import requests
import time
import logging
import pandas as pd

# ## Initialize database constants
# > Enter the `database` & `username` created in CockroachDB.
#
# > Enter the `tablename` you wish to create.

database = "employees"
username = "maxroach"
host = "localhost"
port = 26257
tablename = "jsontbl"

# ## Initialize API
# > We will be using `http://dummy.restapiexample.com/api/v1/employees` for the demonstration purpose.
#
# > Sample Json Output:
#
# ```json
# {
#     "status": "success",
#     "data": [
#         {
#             "id": "1",
#             "employee_name": "...",
#             "employee_salary": "...",
#             "employee_age": "...",
#             "profile_image": ""
#         },
#         {
#             "id": "2",
#             "employee_name": "...",
#             "employee_salary": "...",
#             "employee_age": "...",
#             "profile_image": ""
#         },
#         { 
#             ... 
#         }
#     ]
# }
# ```

url = "http://dummy.restapiexample.com/api/v1/employees"

# ## Create a connection to CockroachDB on Red Hat Marketplace

conn = psycopg2.connect(database=database, user=username, host=host, port=port)
conn.set_session(autocommit=True)
cur = conn.cursor()


# ## Create a table in CockroachDB

def create_table(conn):
    with conn.cursor() as cur:
        cur.execute('CREATE TABLE IF NOT EXISTS {} ( \
        id UUID DEFAULT uuid_v4()::UUID PRIMARY KEY, \
        posts JSONB \
        );'.format(tablename))
        logging.debug("create_table(): status message: {}".format(cur.statusmessage))
    conn.commit()
    print("Table {} created successfully!".format(tablename))


create_table(conn)

# ## Make API call and store the response

# +
req = requests.get(url, headers={"User-Agent": "Python"})

# Decode the JSON.
resp = req.json()

# Convert the JSON to a string to send to the database.
data = json.dumps(resp)
print(json.dumps(resp, indent=2))
# -

# ##### We structure our query so that we extract the `data` field, and then expand that and insert each individual element into the database as a separate row.

cur.execute("""INSERT INTO bank.jsontbl (posts)
        SELECT json_array_elements(%s->'data')""", (data,))


# ## Query the results from the table

def print_content(conn):
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM {}".format(tablename))
        logging.debug("print_content(): status message: {}".format(cur.statusmessage))
        rows = cur.fetchall()
        return rows 


result = print_content(conn)

# ## Read the queried unstructured JSON in Pandas Dataframe

df = pd.DataFrame(columns = ['Employee_ID', 'Employee_Name', 'Employee_Salary', 'Employee_Age'])

idx = 0
for i in result:
    df.loc[len(df)] = [i[1]['id'], i[1]['employee_name'], i[1]['employee_salary'], i[1]['employee_age']]

df.head(10)

# ## Close the connection to the CockroachDB Operator

cur.close()
conn.close()


# ## Summary
#
# We learn't how to get unstructured data from a REST API, created a table in CockroachDB and stored the unstructured Json data into the table. We also learn't to query the unstructured data from CockroachDB table into a pandas dataframe.
#
# ## Future Scope
#
# The pandas dataframe can be further used to clean, cleanse and refine the data. The data can be used to build Machine Learning models as well.

# #### (Optional) Delete the records from the table in CockroachDB

def delete_table_contents(conn):
    with conn.cursor() as cur:
        cur.execute("DELETE FROM {}".format(tablename))
        logging.debug("delete_table_contents(): status message: {}".format(cur.statusmessage))
    conn.commit()


# +
#delete_table_contents(conn)
# -

# #### (Optional) Delete the table from CockroachDB

def drop_table(conn):
    with conn.cursor() as cur:
        cur.execute("DROP TABLE {}".format(tablename))
        logging.debug("drop_table(): status message: {}".format(cur.statusmessage))
    conn.commit()

# +
#drop_table(conn)

StarcoderdataJupyterScriptsDedupFiltered