Edume Ec2

Edume ec2

rom pandas.io.json import json_normalize
import json
import psycopg2
import pandas as pd
import requests
mm_get = requests.get('https://edume-api.herokuapp.com/stats/users',headers={"Authorization":'XXXXXXXXXXXXXXXXXXXXXXX'})mm_json=mm_get.json()
mm_json=mm_get.json()
data_1 = json_normalize(data         = mm_json['data'],
                       record_path   = ['courses','modules'],
                       record_prefix = 'courses.modules.',
                       meta          = [['courses', 'id'],
                                        ['courses', 'title'],
                                        'activated',
                                        'createdAt',
                                        'email',
                                        'employeeId',
                                        'firstName',
                                        'group',
                                        'id',
                                       'group',
                                        'id',
                                        'lastName',
                                        'phone',
                                        'teams'
                                       ]
                       )
data_2 = json_normalize(data         = mm_json['data'],
                       record_path   = 'lessons',
                       record_prefix = 'lessons.',
                       meta          = 'id',
                       meta_prefix   = 'user.'
                       )
data_3 = data_1.merge(
    data_2,
    how = 'outer',
    left_on = ['courses.modules.id', 'id'],
    right_on = ['lessons.moduleId', 'user.id']
)
    right_on = ['lessons.moduleId', 'user.id']
)
cols = data_3.columns
cols = cols.tolist()
cols = pd.DataFrame(cols)
re_cols = pd.DataFrame(cols.loc[:,0].str.replace('.','_').tolist(),index=cols.index)
data_3.teams = data_3.teams.astype(str)
data_3.teams = data_3.teams.str.replace('[','')
data_3.teams = data_3.teams.str.replace(']','')
data_3.teams = data_3.teams.str.replace("'","")

con=psycopg2.connect(dbname='dev',
                     host='xxxxxxxxxxxxxxxxxxxxxxxxxxxx',
                     port='xxxxxxxxxxxxx',user='xxxxxxxxxxxxx',password='xxxxxxxxxxxxxx')
cur = con.cursor()
cur.execute('create table if not exists edume.test (courses_modules_completionDate DATE, courses_modules_id int,courses_modules_st$
con.commit()
cur.execute('truncate edume.test;')
cur.close()