# -*- coding: utf-8 -*-
"""
Created on Sun Jan 19 09:07:43 2020

@author: Jean

Python连接华为云DLI载入特征矩阵数据，返回给Shiny APP Radiant
"""

from dli.dli_client import DliClient
#这是自己添加的自定义python模块，存放访问DLI的access key与secret key，因为源码是网上可见的。
from dli import dlikeys

# print(dlikeys.dli_project_id)
# print(dlikeys.dliAK)
# print(dlikeys.dliSK)

import time
import pandas as pd

def init_aksk_dli_client():
    auth_mode = 'aksk'
    region = 'cn-south-1'
    project_id = dlikeys.dli_project_id
    ak = dlikeys.dliAK
    sk = dlikeys.dliSK
    dli_client = DliClient(auth_mode=auth_mode, region=region, project_id=project_id,ak=ak, sk=sk)
    return dli_client

def run_sql(dli_client, db_name, queue_name, sql):
    # execute SQL
    try:
        sql_job = dli_client.execute_sql(sql, db_name)
        result_set = sql_job.get_result()
    except DliException as e:
        print(e)
        return

    if result_set.row_count == 0:
        return None

    return result_set

dli_client = init_aksk_dli_client()

db_name = 'geshui'
queue_name = 'default'
# OBS bucket name
bucketName = 'gsmx'

t1 = time.time()
month = [] ; passengers=[] 
sql = "select month,passengers from airpassengers;"
result_set = run_sql(dli_client, db_name, queue_name, sql)
print("loading data......")
for row in result_set:
    cols = row.columns
    month.append(cols[0])
    passengers.append(cols[1])
t2 = time.time()
print("time: ",t2-t1)

# 从 list建立 pandas data frame
data = pd.DataFrame({"month":month,"passengers":passengers})
# print(data[0:5])