import pymysql
import datetime
import json
import redis
import pandas as pd
from sqlalchemy import create_engine


def get_mysql_data(host,port,user,passwd,db,sql):
    db = pymysql.connect(host=host, port=port, user=user, passwd=passwd,db=db)
    cursor = db.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    db.close()
    return result


def get_user():
    sql = "select user_id,phone,name,city_id,channel,auth_type from api_userextra"
    df = pd.DataFrame(list(get_mysql_data(host,port,user,passwd,db,sql)))
    df = df.rename(columns={0: "user_id", 1: "phone",2:"name",3:"city_id",4:"channel",5:"auth_type"})
    print(df.shape)
    l = 0
    r = 2000
    length = len(df)
    while l < length:
        pd.io.sql.to_sql(df[l:r], "user", yconnect, schema='jerry_test', if_exists='append', index=False)
        l += 2000
        r += 2000
        print("insert done")


def get_device():
    sql = "select device_id,platform,version,model,screen,channel,idfv from statistic_device"
    df = pd.DataFrame(list(get_mysql_data(host, port, user, passwd, db, sql)))
    df = df.rename(columns={0: "device_id", 1: "platform", 2: "version", 3: "model", 4: "screen",
                            5: "channel",6:"idfv"})
    print(df.shape)
    l = 0
    r = 10000
    length = len(df)
    while l < length:
        pd.io.sql.to_sql(df[l:r], "device", yconnect, schema='jerry_test', if_exists='append', index=False)
        l += 10000
        r += 10000
        print("insert done")


def get_du():
    sql = "select b.device_id,a.user_id from statistic_device_user a " \
          "left join statistic_device b on a.device_id = b.id"
    df = pd.DataFrame(list(get_mysql_data(host, port, user, passwd, db, sql)))
    df = df.rename(columns={0: "device_id", 1: "user_id"})
    print(df.shape)
    l = 0
    r = 200000
    length = len(df)
    while l < length:
        pd.io.sql.to_sql(df[l:r], "device_user", yconnect, schema='jerry_test', if_exists='append', index=False)
        l += 200000
        r += 200000
        print("insert done")


def get_order():
    sql = "select user_id,service_id,created_time from api_order"
    df = pd.DataFrame(list(get_mysql_data(host, port, user, passwd, db, sql)))
    df = df.rename(columns={0: "user_id", 1: "service_id",2:"created_time"})
    print(df.shape)
    l = 0
    r = 10000
    length = len(df)
    while l < length:
        pd.io.sql.to_sql(df[l:r], "meigou_order", yconnect, schema='jerry_test', if_exists='append', index=False)
        l += 10000
        r += 10000
        print("insert done")

def get_meigou_tag():
    sql = "select service_id,tag_id from api_servicetag"
    df = pd.DataFrame(list(get_mysql_data(host, port, user, passwd, db, sql)))
    df = df.rename(columns={0: "service_id", 1: "tag_id"})

    pd.io.sql.to_sql(df, "meigou_tag", yconnect, schema='jerry_test', if_exists='append', index=False)
    print("insert done")


def meigou_to_csv():
    sql = "select device_id,service_id,created_time from meigou_order"
    df = pd.DataFrame(list(get_mysql_data(host, port, user, passwd, db, sql)))
    df = df.rename(columns={0: "user_id", 1: "service_id", 2: "created_time"})
    df.to_csv('/tmp/device/meigou_order.csv', index=False)

def user_device_to_csv():
    sql = "select device_id,user_id from device_user"
    df = pd.DataFrame(list(get_mysql_data(host, port, user, passwd, db, sql)))
    df = df.rename(columns={0: "device_id", 1: "user_id"})
    df.to_csv('/tmp/device/device_user.csv', index=False)

def devcie_to_csv():
    sql = "select device_id,platform,version,model,screen,channel,idfv from device"
    df = pd.DataFrame(list(get_mysql_data(host, port, user, passwd, db, sql)))
    df = df.rename(columns={0: "device_id", 1: "platform", 2: "version", 3: "model", 4: "screen",
                            5: "channel", 6: "idfv"})
    df.to_csv('/tmp/device/device.csv', index=False)


def user_to_csv():
    sql = "select user_id,phone,name,city_id,channel,auth_type from user"
    df = pd.DataFrame(list(get_mysql_data(host, port, user, passwd, db, sql)))
    df = df.rename(columns={0: "user_id", 1: "phone", 2: "name", 3: "city_id", 4: "channel", 5: "auth_type"})
    df.to_csv('/tmp/device/user.csv', index=False)


def location_to_csv():
    sql = "select device_id,lat,lng,partition_date from device_location"
    df = pd.DataFrame(list(get_mysql_data(host, port, user, passwd, db, sql)))
    df = df.rename(columns={0: "device_id", 1: "lat", 2: "lng", 3: "partition_date"})
    df.to_csv('/tmp/device/location.csv', index=False)

def meigou_tag_to_csv():
    sql = "select a.service_id,b.name,a.tag_id from api_servicetag a left join api_tag b on a.tag_id = b.id " \
          "where b.tag_type <= '3'"
    df = pd.DataFrame(list(get_mysql_data(host, port, user, passwd, db, sql)))
    df = df.rename(columns={0: "service_id", 1: "name",2:"tag_id"})
    df.to_csv('/tmp/device/meigou_tag.csv', index=False)


def hospital_to_csv():
    sql = "select id,name,city_id,baidu_loc_lng,baidu_loc_lat from api_hospital"
    df = pd.DataFrame(list(get_mysql_data(host, port, user, passwd, db, sql)))
    df = df.rename(columns={0: "id", 1: "name",2:"city_id",3:"lng",4:"lat"})
    df.to_csv('/tmp/device/hospital.csv', index=False)


if __name__ == "__main__":
    host = "172.16.30.141"
    port = 3306
    user = "work"
    passwd = "BJQaT9VzDcuPBqkd"
    db = "zhengxing"
    yconnect = create_engine('mysql+pymysql://root:3SYz54LS9#^9sBvC@172.16.40.170:4000/jerry_test?charset=utf8')
    meigou_tag_to_csv()
    print("end")

    # host = "172.16.40.170"
    # port = 4000
    # user = "root"
    # passwd = "3SYz54LS9#^9sBvC"
    # db = "jerry_test"




















