import json
import pandas as pd
import numpy as np
import seaborn as sns
from scipy import stats


import pymysql
pymysql.install_as_MySQLdb()

from sqlalchemy import create_engine
from sqlalchemy_utils import create_database, database_exists

## Loading json file
with open('Mock_Crowdsourcing_API_Results.json') as f:
    results = json.load(f)
results.keys()

## explore each key 
type(results['meta'])

## display meta
results['meta']

## display data
type(results['data'])

## preview the dictionary
# results['data']

## preview just the keys
results['data'].keys()

## what does the crowd key look like?
# results['data']['crowd']

## checking single entry of crowd
results['data']['crowd'][0]

## making crowd a dataframe
crowd = pd.DataFrame(results['data']['crowd'])
crowd

## making demographics a dataframe
demo = pd.DataFrame(results['data']['demographics'])
demo

## making financials a dataframe
financials = pd.DataFrame(results['data']['financials'])
financials

## making use a dataframe
use = pd.DataFrame(results['data']['use'])
use

## fixing funded amount column
financials['funded_amount'] = financials['funded_amount'].str.replace('$','')
financials['funded_amount'] = pd.to_numeric(financials['funded_amount'])
financials

## loading mysql credentials
with open('/Users/codingdojo/.secret/mysql.json') as f:
    login = json.load(f)
login.keys()

## creating connection to database with sqlalchemy
connection_str  = f"mysql+pymysql://{login['user']}:{login['password']}@localhost/mock-belt-exam"
engine = create_engine(connection_str)

## Check if database exists, if not, create it
if database_exists(connection_str) == False: 
    create_database(connection_str)
else: 
    print('The database already exists.')

## saving dataframes to database
financials.to_sql('financials', engine, index=False, if_exists = 'replace')
use.to_sql('use', engine, index=False, if_exists = 'replace')
demo.to_sql('demographics', engine, index=False, if_exists = 'replace')
crowd.to_sql('crowd',engine, index=False, if_exists = 'replace')

## checking if tables created
q= '''SHOW TABLES;'''
pd.read_sql(q,engine)